Locally save SqlCredential file with powershell - SQL connection

Question

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 ?

For obvious security reasions, credentials can only be decripted under the same account as they have been encrypted. It looks like you trying to build some Security through obscurity, instead you should use different accounts (or authentication) at your database side. — iRon
– iRon, Commented Aug 24, 2021 at 15:02
System.Data.SqlClient.SqlConnection.Credential does not take a 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). Also, what iRon said. Importing the clixml will only work if you are using the same windows account that you created it with on the same computer. — Daniel
– Daniel, Commented Aug 24, 2021 at 15:54
One more thing is the windows user's password must also be the same. PSCredential objects are literally encrypted with your current username+password, so if you change your password, then you will not be able to import the credentials anymore. These days, a better option is usually through the CredentialManager module — Cpt.Whale
– Cpt.Whale, Commented Aug 24, 2021 at 16:16

Daniel · Accepted Answer · 2021-08-24 18:48:25Z

2

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)

answered Aug 24, 2021 at 18:48

Daniel

5,1422 gold badges9 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Locally save SqlCredential file with powershell - SQL connection

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related