Snippet Name: SQL_Server_Encryption_Error
Tags:
Created Date: 2025-06-16 13:12:50
Last Modified Date: 2025-05-31 10:32:11
Dependencies:
DBA Notes:
Sample Output:
Title: SQL Server Encryption Error
Author: user
in SQL server, i am trying to encrypt some columns via the command provided by the SSMS gui. i get an error "Set-SqlColumnEncryption : Unsupported SQL Server version error: Invalid SQL Server version 15." i am pretty sure it is due to LoadwithPartialName instead of an explicit version to match SQL2019: [reflection.assembly]::LoadwithPartialName('System.Data.SqlClient') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.SMO') | Out-Null
[reflection.assembly]::LoadwithPartialName('Microsoft.SQLServer.ConnectionInfo') | Out-Null
Author: ChatGPT
You're correct that the issue is likely due to the use of `[reflection.assembly]::LoadWithPartialName()`, which is considered outdated and may not load the correct version of the assemblies required for SQL Server 2019 (version 15). Instead, you should load the assemblies explicitly by specifying the appropriate version using `Add-Type` or by referencing the assembly's exact path.
### Solution:
#### 1. Use `Add-Type` with specific assembly versions:
Instead of `LoadWithPartialName`, explicitly specify the version:
```powershell
Add-Type -AssemblyName "System.Data.SqlClient"
Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=15.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
```
#### 2. Load assemblies from the SQL Server installation folder:
Find the actual path of the required SQL Server assemblies and load them directly. For SQL Server 2019, the assemblies are typically located in:
```powershell
$SqlServerPath = "C:\Program Files\Microsoft SQL Server\150\SDK\Assemblies\"
Add-Type -Path "$SqlServerPath\Microsoft.SqlServer.Smo.dll"
Add-Type -Path "$SqlServerPath\Microsoft.SqlServer.ConnectionInfo.dll"
```
#### 3. Update SQL Server PowerShell module:
If you're using the `SqlServer` PowerShell module, make sure it's updated to the latest version, as older versions may not support SQL Server 2019 properly:
```powershell
Update-Module -Name SqlServer
```
#### 4. Verify the correct module is used:
Ensure you are using the `SqlServer` module (which supports Always Encrypted) instead of the older `SQLPS` module:
```powershell
Import-Module SqlServer
```
Then try running the column encryption command again.
---
Give these solutions a try and see if the issue is resolved! Let me know if you need further assistance.
Author: user
my path on my server is slightly different, but the dlls do not exist in that folder: D:\Program Files\Microsoft SQL Server\150\SDK\Assemblies
Author: ChatGPT
If the necessary DLLs are missing from the `D:\Program Files\Microsoft SQL Server\150\SDK\Assemblies` folder, there are a few things you can do to resolve the issue:
### 1. **Verify SQL Server Feature Installation**
Ensure that the **Client Tools SDK** feature was installed during the SQL Server setup. You can check this by:
- Opening **SQL Server Installation Center** ??? Go to **"Installation"** ??? Select **"New SQL Server stand-alone installation"** ??? Ensure the **Client Tools SDK** and **Client Tools Connectivity** features are installed.
If these features are missing, you may need to install them.
---
### 2. **Find and Load the Assemblies Manually**
Try searching for the required assemblies manually in other potential installation paths. Run the following PowerShell command to locate them:
```powershell
Get-ChildItem -Path "D:\" -Recurse -Filter "Microsoft.SqlServer.Smo.dll" -ErrorAction SilentlyContinue
```
Typical alternative paths include:
- `C:\Windows\Microsoft.NET\assembly\GAC_MSIL\`
- `C:\Program Files (x86)\Microsoft SQL Server\150\SDK\Assemblies\`
- `C:\Program Files (x86)\Microsoft SQL Server Management Studio\`
Once found, load them explicitly:
```powershell
Add-Type -Path "D:\Path\To\Microsoft.SqlServer.Smo.dll"
Add-Type -Path "D:\Path\To\Microsoft.SqlServer.ConnectionInfo.dll"
```
---
### 3. **Install SQL Server Management Studio (SSMS)**
If SSMS is installed on the machine, the assemblies might be bundled with it. You can check the following location:
```
C:\Program Files (x86)\Microsoft SQL Server Management Studio\Common7\IDE\PrivateAssemblies\
```
If they exist there, load them:
```powershell
Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server Management Studio\Common7\IDE\PrivateAssemblies\Microsoft.SqlServer.Smo.dll"
```
If SSMS isn't installed, consider installing it to get the required libraries.
---
### 4. **Install the SQL Server Feature Pack**
If the assemblies aren't found, download the **SQL Server 2019 Feature Pack**, which includes the necessary SDK components. You can get it from the official Microsoft website.
Look for the following components:
- **SQLSysClrTypes.msi**
- **SharedManagementObjects.msi**
After installing, check if the DLLs appear in the expected folders.
---
### 5. **Using the Correct PowerShell Module**
Instead of manually loading assemblies, consider using the `SqlServer` PowerShell module, which provides better compatibility with modern SQL Server versions. Install it via:
```powershell
Install-Module -Name SqlServer -Force
```
Once installed, you can run encryption commands directly using:
```powershell
Import-Module SqlServer
Set-SqlColumnEncryption -Column "ColumnName" -Table "Schema.TableName" -EncryptionType Deterministic -EncryptionKey "YourColumnEncryptionKey"
```
---
### 6. **Fallback: Copy Assemblies from Another Server**
If you have another SQL Server 2019 instance where the assemblies exist, you can manually copy them to the required directory on your current server.
---
Try these approaches and let me know if you're able to locate and load the necessary assemblies.