Search code examples
sqlpowershellcredentials

Locally save SqlCredential file with powershell - SQL connection

I want to connect to a SQL database with powershell using a credential file.

To create my credential file, I simply used :

$Credential = Get-Credential
$Credential | Export-CliXml -Path "mypath\sql.cred"

Then I run :

$ServerInstance = 'myserverinstance.net'
$Database = 'myDB'
$cred = Import-CliXml -Path "mypath\sql.cred"

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=$ServerInstance; Database=$Database;"
$conn.Credential = $cred
$conn.Open()

I got 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 to create a Sql credential file (that I can save locally on my computer) so that my scrip can work ?

Solution

  • System.Data.SqlClient.SqlConnection.Credential does not take a System.Management.Automation.PSCredential directly. You need to create a SqlCredential object from the values of PSCredential.

    $cred.Password.MakeReadOnly()
$conn.Credential = [System.Data.SqlClient.SqlCredential]::new($cred.UserName, $cred.Password)