SQL Server connection using Get-StoredCredential

  • Hi,

    I am currently able to log in using a trusted connection. I stored credentials for an SQL login in the Windows Credential Manager, but I'm not sure how I can use this for authenticating from a PowerShell script. Currently, I'm connecting:

    $con = New-Object System.Data.SqlClient.SqlConnection;
    $con.ConnectionString = "Data Source=my-apps-server;Initial Catalog=myDatabase;Integrated Security=true";
    $con.Open();

    # execute some stored procedures here.

    $con.Dispose();
    $con = $null;

    So I've tried the following to switch to using Get-StoredCredential:

    $creds = Get-StoredCredential -Target dbStoredCredentials

    $newConnection = New-Object System.Data.SqlClient.SqlConnection
    $newConnection.ConnectionString = "Data Source=my-apps-server;Initial Catalog=myDatabase;"
    $newConnection.Credential = $creds

    And now get the following error:

    Exception setting "Credential": "Cannot convert the "System.Management.Automation.PSCredential" value of type

    "System.Management.Automation.PSCredential" to type "System.Data.SqlClient.SqlCredential"."

    • This topic was modified 1 month, 1 week ago by  ram302.
  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • as a wild thought - have you tired casting the credentials to the appropriate datatype?

    Looks like you may need to do something like this:

    https://gist.github.com/Jaykul/81fd8ee55ce4df656d36d23e59788151

    The above is a powershell script to allow casting of credentials to be cast to other supported credential types.

    Now, an alternate method (which is less risky in my opinion) as provided by this site:

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

    is to pass the username and password separately by using $cred.username and $cred.password.  Something along the lines of this:

    $cred = Get-StoredCredential -Target dbStoredCredentials
    Add-Type -AssemblyName
    "Microsoft.SqlServer.Smo,Version=13.0.0.0,Culture=neutral,PublicKeyToken=8984
    5dcd8080cc91"
    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server MyServer
    $srv.ConnectionContext.LoginSecure = $false
    $srv.ConnectionContext.set_Login($cred.username)
    $srv.ConnectionContext.set_SecurePassword($cred.password)
    $srv.ConnectionContext.Connect()

    NOTE - the above is copy-pasted from the second link above with minor changes to meet what you had in your code.  The above is fully untested by me and I would recommend testing it on a test system prior to any live database connections.  Same applies to the first comment about casting - test it out on a test system first.

     

    And now - the EASY way - DBATOOLS:

    Import-Module dbatools
    $cred = Get-Credential
    $srv = Connect-DbaSqlServer -Sqlserver MyServer -Credential $cred

    And another solution (copy-pasted from 2nd link with no changes this time):

    # First create the PSCredential object
    $cred = Get-Credential

    #set the password as read only
    $cred.Password.MakeReadOnly()

    # Create the SqlCredential object
    $sqlCred = New-Object
    System.Data.SqlClient.SqlCredential($cred.username,$cred.password)

    $sqlConn = New-Object System.Data.SqlClient.SqlConnection
    $sqlConn.ConnectionString = “Server=localhost\sql12;Initial Catalog=master”
    $sqlConn.Credential = $sqlCred

    You will need to update that "Get-Credential" bit to pull from the credential store.

     

    So there are options.  Which one you use is up to you.  I personally like DBATOOLS, but your company may not allow importing that.  If they don't, I like the last option.  The other 2, to me, are harder to support.  You will need to adjust whichever one of the above you choose to use to connect to your database as none of the above suggestions took that into consideration.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • I went for the last solution in your post. I read them thoroughly, and this last solution turned out to work with minimal changes to my existing code. Thanks!!!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply