Home > Software design >  SQL Server connection using Get-StoredCredential
SQL Server connection using Get-StoredCredential

Time:09-21

I'm trying to update my script to use Get-StoredCredential for creating a connection to SQL-Server. Below is what I've tried so far without success:

$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

This causes 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"."

How can I get the SQL connection to authenticate with StoredCredential?

CodePudding user response:

Using a System.Data.SqlClient.SqlCredential means you're using SQL Server Authentication. It's equivalent to putting the username and password in the connection string.

If you want to use a stored Windows credential, then create a Windows Credential targeting YourSQLServer:port (eg myserver.mydomain.com:1433) and use Integrated Security=true in your connection string. The driver will access the credential store for you and perform NTLM authentication using the stored credential rather than the identity of the thread opening the connection.

  • Related