Forum Discussion
Powershell v7 connect to SQL Always Encrypted Keys
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
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