Forum Discussion
Powershell v7 connect to SQL Always Encrypted Keys
HI
I have a PS 7 console, and i need to connect to a SQL database that has implemented Always Encrypted Keys.
The code to connect to the sql database is the following
$conn="Server=sv1;User=user; Password=pwd; Initial Catalog=BD; Column Encryption Setting=enabled;"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = $conn
The error that i get is below
Exception setting "ConnectionString": "Keyword not supported: 'column encryption setting'."
This code run on PS v5 but not on 7 that is where I must run it.
Can anyone help?
Hi, Tiago.
Windows PowerShell uses the .NET Framework:
PowerShell (currently in the 7.x incarnation) does not use the .NET Framework. It uses plain .NET (or .NET Core as it was known previously):
Column Encryption Setting is not supported in the .NET (Core) System.Data.SqlClient connection string. Instead, you have to install (likely from NuGet) and use the newer Microsoft.Data.SqlClient package.
Taking the NuGet installation pathway is excruciatingly painful though - which is fair enough since it's not targeting PowerShell scripters but rather fully-fledged users of Visual Studio.
Alternative installation and usage under PowerShell
Install the SqlServer PowerShell module instead (the first-party module from Microsoft), which you can then import and all the pain associated with the NuGet approach disappears.
The following screenshot illustrates a failed call to System.Data.SqlClient.SqlConnection::new followed by a successful call to Microsoft.Data.SqlClient.SqlConnection::new, with both calls using the Column Encryption Setting entry:
The takeaway
Do not assume that something that works under Windows PowerShell will work under PowerShell - or vice versa, as under the hood and even at the commandlet level, they can be quite different.
Cheers,
Lain
1 Reply
Hi, Tiago.
Windows PowerShell uses the .NET Framework:
PowerShell (currently in the 7.x incarnation) does not use the .NET Framework. It uses plain .NET (or .NET Core as it was known previously):
Column Encryption Setting is not supported in the .NET (Core) System.Data.SqlClient connection string. Instead, you have to install (likely from NuGet) and use the newer Microsoft.Data.SqlClient package.
Taking the NuGet installation pathway is excruciatingly painful though - which is fair enough since it's not targeting PowerShell scripters but rather fully-fledged users of Visual Studio.
Alternative installation and usage under PowerShell
Install the SqlServer PowerShell module instead (the first-party module from Microsoft), which you can then import and all the pain associated with the NuGet approach disappears.
The following screenshot illustrates a failed call to System.Data.SqlClient.SqlConnection::new followed by a successful call to Microsoft.Data.SqlClient.SqlConnection::new, with both calls using the Column Encryption Setting entry:
The takeaway
Do not assume that something that works under Windows PowerShell will work under PowerShell - or vice versa, as under the hood and even at the commandlet level, they can be quite different.
Cheers,
Lain