none
Invoke-SqlCmd with -Credential flag, cannot get it working RRS feed

  • Question

  • MyDomain\MyUser is a local admin account on my laptop, and a sysadmin on my local db instance, and is set to dbowner on mydatabase.

    I ran this command to enable the SqlServer module:

    Install-Module SqlServer -Force -AllowClobber

    But when I run the following command:

    Invoke-Sqlcmd -Credential Get-Credential -ServerInstance '(localdb)\mssqllocaldb' -Database 'MyDatabase' -Query "select * from mytable" 

    I get this: 

    Invoke-Sqlcmd : Login failed for user 'MyDomain\MyUser'.
    At line:1 char:1
    + Invoke-Sqlcmd -Credential Get-Credential -ServerInstance '(localdb)\m ...
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlException
        + FullyQualifiedErrorId : SqlExceptionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand



    (the above message contains the reflected username that I entered in the Get-Credential dialog box).

    ---

    What am I doing wrong?

    I've tripled-checked the user account, instance name and database name and they all work from code, but not with Invoke-SqlCmd -Credential





    • Edited by jimasp1 Wednesday, July 29, 2020 8:02 PM
    Wednesday, July 29, 2020 7:57 PM

All replies

  • Replace


    -Credential Get-Credential

    with


    -Credential (Get-Credential)

    The reason is that you want Get-Credential to be executed as an expression, so place it in ( ).


    -- Bill Stewart [Bill_Stewart]

    Wednesday, July 29, 2020 8:17 PM
    Moderator
  • Thanks, but that makes no difference.

    I've also tried the following:
    Running SSMS as the same windows user and deleting and recreating the database.
    Running SSMS as the same windows user, connecting to a full SQL instance (as opposed to localdb) and creating the database and then changing Invoke-Sqlcmd to use that. 
    Running it with -Hostname instead of -ServerInstance.

    But I still get login failed from Invoke-Sqlcmd with -Credential.





    • Edited by jimasp1 Thursday, July 30, 2020 4:43 AM
    Thursday, July 30, 2020 4:38 AM
  • Does -Credential work for you?
    Thursday, July 30, 2020 4:38 AM
  • You cannot prompt for credentials in an SMSS session.  Please post you issue in a the SMSS forum and they will help you understand how to do what you ask if it is possible.


    \_(ツ)_/

    Thursday, July 30, 2020 4:46 AM
  • Perhaps -Credential is expecting a sql user not a windows user? I've just noticed the docs for the command don't actually specify which it is. 

    (But according to this post it should work: https://social.technet.microsoft.com/Forums/en-US/212debc4-926d-46a2-96f4-4913a64432c3/connecting-remotely-to-a-sql-using-windows-authentication?forum=winserverpowershell#:~:text=If%20you%20want%20to%20use%20alternate%20domain%20credentials%20then%20just%20do%20this)

    The ultimate intention is for me to use Invoke-Sqlcmd to connect to the SQL instance with a windows account that is not the user running the powershell session.




    • Edited by jimasp1 Thursday, July 30, 2020 5:18 AM
    Thursday, July 30, 2020 5:07 AM
  • I suggest that you learn PowerShell and Windows.

    The answer to your question can be answered by your DBA as it is teh server that dete5rmines how authentication works.  That is controlled by teh DBA or the person who set up the SQL Server.


    \_(ツ)_/

    Thursday, July 30, 2020 5:52 AM

  • Digging further, I've noticed that this fails:

    $serverInstance = '(localdb)\mssqllocaldb'

    $sql = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
    $sql.ConnectionContext.ConnectAsUser = $true
    $username = 'myuser@domain.com'
    $password = "mypassword"   
    $sql.ConnectionContext.ConnectAsUserName = $username
    $sql.ConnectionContext.ConnectAsUserPassword = $password
    $sql.ConnectionContext.ServerInstance = $serverInstance
    $sql.ConnectionContext.Connect()
    $sql.Status

    But if I use the database in a full sql instance (i.e. not localdb), and then change the top line to this:

    $serverInstance = 'localhost'

    Then it works.

    Btw, if I change $username to the netbios format "DOMAIN\myuser" it also fails. (I suspect this could be a bug that's been fixed in later versions?)



    • Edited by jimasp1 Thursday, July 30, 2020 6:40 AM
    Thursday, July 30, 2020 6:36 AM
  • You are just guessing.  Without a full understanding of your database and its authentication method there is no way to answer your question.

    When using a login with SQLServer authentication the username is the name registered in the database.  It is NOT a domain name.

    The default instance name is always the machine name and you can use localhost as the name when connecting from the local server.  Remotely you must use the remote server name.

    Again - this is not a SQL for beginners forum.  You need to ask your questions in the SQLServer forum.


    \_(ツ)_/

    Thursday, July 30, 2020 7:14 AM