locked
Accessing SQL server with PowerShell RRS feed

  • Question

  • Hi Folks,

      I am trying to use Invoke-Command to access a data table in my local SQL server instance.

      If I run the command from a powershell window as my account (xyz), it retuns data, but the SQL Server log shows Error 18456, State 5 (Incorrect UserId) - it is showing domain\machine$ as the username.

      If I try and provide a PSCredential the command does not work with a - Login failed for user xyz  (The sql log still reports machine$ as the username, not xyz).

    Invoke-SqlCmd -ServerInstance "myserver" -Database "MyDB" -Query "SELECT * FROM dbo.tablex"   # this works
    
    $cred = Get-Credential
    Invoke-SqlCmd -Credential $cred -ServerInstance "myserver" -Database "MyDB" -Query "SELECT * FROM dbo.tablex"   # this does not
    
    Invoke-SqlCmd -Credential $cred -Query "SELECT * FROM dbo.tablex" -ConnectionString Data Source=MyServer; Initial Catalog=MyDB;Integrated Security=True;ApplicationIntent=READONLY"  # Also does not work
    Can anyone tell me how to get Invoke-Command to use a credential - it is the last version with the connection string that I am interested in.

    edit: Updated, typed in the Invoke-Command when I meant Invoke-SQLCmd.
    • Edited by AndyW2007 Wednesday, October 24, 2018 2:39 AM
    Wednesday, October 24, 2018 1:25 AM

All replies

  • "Invoke-Command" is not a SQLServer command.  It is used to remote to another system and execute a PowerShell command.

    Please see the following documentation for assistance.

    help Invoke-Command -online


    \_(ツ)_/


    • Edited by jrv Wednesday, October 24, 2018 1:33 AM
    Wednesday, October 24, 2018 1:31 AM
  • To learn how to execute queries against SQLServer please read the following carefully

    https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps


    \_(ツ)_/

    Wednesday, October 24, 2018 1:35 AM
  • Sorry,

        I typed in the wrong command, have updated the original post.

        I have also tried Read-SQLTableData as well, but basically the same issue - it doesnt seem to accept the user credential, so I am not sure if it is getting passed through correctly.

    If I use a SQL Server login and specify -Username -Password parameters I can get it to work, unfortunately, I would like to connect to SQL server using Windows Authentication.

    Wednesday, October 24, 2018 2:44 AM
  • Hi,

    I found this link on the Internet.

    I hope it is useful to you.

    https://www.sqlshack.com/connecting-powershell-to-sql-server-using-a-different-account/

    Best Regards,

    Lee


    Just do it.

    Wednesday, November 7, 2018 9:45 AM
  • Sorry,

        I typed in the wrong command, have updated the original post.

        I have also tried Read-SQLTableData as well, but basically the same issue - it doesnt seem to accept the user credential, so I am not sure if it is getting passed through correctly.

    If I use a SQL Server login and specify -Username -Password parameters I can get it to work, unfortunately, I would like to connect to SQL server using Windows Authentication.

    TO avoid a lot of noise about what you are trying to do start by carefully reading the help

    help Invoke-Sqlcmd -full

    This comamd is almost an exact replacement for "sqlcmd" most of the parameters are the same.

    To connect with a SQLServer login use the "Username" and "Password"  parameters.  The Credentail object is intended to work only with a Windows login.

    If you do not understand how SQLServer logins work then post in the SQLServer forum for help understanding how SQLServer logins are designed and how to use them

    If you have used sqlcmd or osql to login then the method is the same with Invoke-SqlCmd.


    \_(ツ)_/

    Wednesday, November 7, 2018 9:55 AM
  • Hi,

      Thanks for your reply.

       I am sure people have been 'carefully' reading the documentation.  However, one assumes that the documentation is accurate and that the Credential property has been implimented consistently.

    The issue is that it appears that the Credential object when supplied with a valid windows credential is not sent through to SQL server. In my testing, the sql server logs showed 'login failed for machine$' rather than 'login failed for domain\xyz'.

    Regards

    Wednesday, November 7, 2018 7:07 PM
  • The credential property does not allow you to use a SQLServer login.   It is only for a Windows login and that is how it is documented.  Read the help carefully.

    Here is part of what you would read in the help.  It is explicit.

    PS D:\scripts> help invoke-sqlcmd -par username
    
    -Username <String>
        Specifies the login ID for making a SQL Server Authentication connection to an instance of the Database Engine.
    
        The password must be specified through the Password parameter.
    
        If Username and Password are not specified, this cmdlet attempts a Windows Authentication connection using the Windows account running the Windows
        PowerShell session. When possible, use Windows Authentication.
    
        Required?                    false
        Position?                    named
        Default value                None
        Accept pipeline input?       False
        Accept wildcard characters?  false
    

    Ask your DBA to explain the difference between SQLServer authentication and Windows Authentication (sometimes called Windows Integrated).

    By default "Credentials" relates to you current login account.   They allow you to specify a different Windows account from your login.  This is true across all of Windows and has always been true.  It is also true for all CmdLets that take a Credential parameter. For non-domain connections the credential may be turned into a network credential and transmitted as plain text unless other =wise specified.

    Learning basic Windows and Basic PowerShell is critical to  understanding how to do these things.  In your case learning SQLServer is also critical.  Your DBA should be able to help you with connections to SQLServer from any tool.  You should always check the help first then check the documentation for the service or application.  If that fails ask the DBA and after that and after having gathered all needed information post in a forum.

    The fact that you are required to use a username and password with SQLServer means that you must use SQLServer credentials and not Windows credentials.  SQS credentials are sent as command parameters of Username and Password on the command line in Invoke-SqlCmd and in all SQS commandline utilities such as sqlcmd aand osql.

    TO be a technician you must study carefully all technologies you need to use.  This is what a technician is.  It requires constant study but the study becomes easier as you learn more.  It is always painfully slow in the beginning if you have no engineering or computer science background.


    \_(ツ)_/


    • Edited by jrv Wednesday, November 7, 2018 7:23 PM
    Wednesday, November 7, 2018 7:21 PM
  • if you want to use windows credentials use your $creds to connect to the ps session , then run your invoke-sqlcmd with no credentials switch. It will use those $creds
    Wednesday, November 7, 2018 7:26 PM
  • Thanks,

     I am sure your post was well meant but way off topic and also I might point out rather insulting.

    The fact is, using credentials does not work and people want to know why, or a work around.

    Wednesday, November 7, 2018 7:31 PM
  • Hi thanks,

      This is a good work-a-round, however, it would be great if one could avoid creating a ps-session to the machine one is already on just to get credentials to work.

    Wednesday, November 7, 2018 7:32 PM
  • if you want to use windows credentials use your $creds to connect to the ps session , then run your invoke-sqlcmd with no credentials switch. It will use those $creds
    That is not how it works here and not what the discussion is about. You cannot use Windows Credentials with a SQLServer login. Please read my previous post.

    \_(ツ)_/

    Wednesday, November 7, 2018 7:33 PM
  • Thanks,

     I am sure your post was well meant but way off topic and also I might point out rather insulting.

    The fact is, using credentials does not work and people want to know why, or a work around.

    The "why" is posted above and there is no need for a workaround.


    \_(ツ)_/

    Wednesday, November 7, 2018 7:34 PM
  • Hi thanks,

      This is a good work-a-round, however, it would be great if one could avoid creating a ps-session to the machine one is already on just to get credentials to work.

    There is no need for a PsSession.  If you can use username and password with any SQLServer utility from you workstation then you can use Username and Password parameters on Invoke-SqlCmd.


    \_(ツ)_/

    Wednesday, November 7, 2018 7:37 PM
  • Her is a quick way to test your access to SQLServer.

    At  prompt (CMD or PowerShell)

    sqlcmd -S <server> -U <userid> -P <password>

    You want to use the alternate account here. 

    There is another issue that you need to be aware of..  If you already have a connection to the SQLServer and you are trying to use credentials in a connection the credentials will be rejected.  You cannot authentication to a resource with two sets of credentials.

    You can start PowerShell with alternate credentials and then you will be able to use them because that would not be impersonation but would be a new Windows session running under different credentials.

    The fact that you first method works also says that SQS is able to use Windows credentials with your account.  If it is in mixed mode this may not work for other accounts that may not have logins defined.

    I recommend contacting your DBA to find out how SQS is configured at your location.


    \_(ツ)_/

    Wednesday, November 7, 2018 8:10 PM