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.