SQL Server connection using Get-StoredCredential

Question

Post reply

SQL Server connection using Get-StoredCredential

ram302

SSCommitted

Points: 1618
More actions
September 16, 2021 at 7:35 pm
Go to Answer
#3930290
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 3 years, 2 months ago by ram302.
Mr. Brian Gale

SSC-Insane

Points: 24800
More actions
September 20, 2021 at 4:37 pm
Answer
#3931217
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 recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

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

ram302 SSCommitted Points: 1618 More actions · Answer 2

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!!!