Configure Always Encrypted using PowerShell

Artikkel
04/06/2023

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

The SqlServer PowerShell module provides cmdlets for configuring Always Encrypted in both Azure SQL Database or SQL Server.

Security Considerations when using PowerShell to Configure Always Encrypted

Because the primary goal of Always Encrypted is to ensure encrypted sensitive data is safe, even if the database system gets compromised, executing a PowerShell script that processes keys or sensitive data on the SQL Server computer can reduce or defeat the benefits of the feature. For additional security-related recommendations, see Security Considerations for Key Management.

You can use PowerShell to manage Always Encrypted keys both with and without role separation, providing control over who has access to the actual encryption keys in the key store, and who has access to the database.

For additional recommendations, see Security Considerations for Key Management.

Prerequisites

Install the SqlServer PowerShell module version 22.0.50 or later on a secure computer that is NOT a computer hosting your SQL Server instance. The module can be installed directly from the PowerShell gallery. See the download instructions for more details.

Importing the SqlServer module

To load the SqlServer module:

Use the Set-ExecutionPolicy cmdlet to set the appropriate script execution policy.
Use the Import-Module cmdlet to import the SqlServer module.

This example loads the SqlServer module.

# Import the SQL Server Module.  
Import-Module "SqlServer" -MinimumVersion 22.0.50

Connecting to a database

Some of the Always Encrypted cmdlets work with data or metadata in the database and require that you connect to the database first. There are two recommended methods of connecting to a database when configuring Always Encrypted using the SqlServer module:

Connect using the Get-SqlDatabase cmdlet.
Connect using SQL Server PowerShell Provider.

Using Get-SqlDatabase

The Get-SqlDatabase cmdlet allows you to connect to a database in SQL Server or in Azure SQL Database. It returns a database object, which you can then pass using the InputObject parameter of a cmdlet that connects to the database.

Using SQL Server PowerShell

# Import the SqlServer module
Import-Module "SqlServer" -MinimumVersion 22.0.50

# Connect to your database
# Set the valid server name, database name and authentication keywords in the connection string
$serverName = "<Azure SQL server name>.database.windows.net"
$databaseName = "<database name>"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Authentication = Active Directory Integrated"
$database = Get-SqlDatabase -ConnectionString $connStr

# List column master keys for the specified database.
Get-SqlColumnMasterKey -InputObject $database

Alternatively, you can use piping:

$database | Get-SqlColumnMasterKey

Using SQL Server PowerShell Provider

The SQL Server PowerShell Provider exposes the hierarchy of SQL Server objects in paths similar to file system paths. With SQL Server PowerShell, you can navigate the paths using Windows PowerShell aliases similar to the commands you typically use to navigate file system paths. Once you navigate to the target instance and the database, the subsequent cmdlets target that database, as shown in the following example.

Note

This method of connecting to a database works only for SQL Server (it is not supported in Azure SQL Database).

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
# Navigate to the database in the remote instance.
cd SQLSERVER:\SQL\servercomputer\DEFAULT\Databases\yourdatabase
# List column master keys in the above database.
Get-SqlColumnMasterKey

Alternatively, you can specify a database path using the generic Path parameter, instead of navigating to the database.

# Import the SqlServer module.
Import-Module "SqlServer" -MinimumVersion 22.0.50
# List column master keys for the specified database.
Get-SqlColumnMasterKey -Path SQLSERVER:\SQL\servercomputer\DEFAULT\Databases\yourdatabase

Always Encrypted Tasks using PowerShell

Always Encrypted Cmdlet Reference

The following PowerShell cmdlets are available for Always Encrypted:

CMDLET	Description
Add-SqlAzureAuthenticationContext	Performs authentication to Azure and acquires an authentication token.
Add-SqlColumnEncryptionKeyValue	Adds a new encrypted value for an existing column encryption key object in the database.
Complete-SqlColumnMasterKeyRotation	Completes the rotation of a column master key
Get-SqlColumnEncryptionKey	Returns all column encryption key objects defined in the database, or returns one column encryption key object with the specified name.
Get-SqlColumnMasterKey	Returns the column master key objects defined in the database, or returns one column master key object with the specified name.
Invoke-SqlColumnMasterKeyRotation	Initiates the rotation of a column master key.
New-SqlAzureKeyVaultColumnMasterKeySettings	Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in Azure Key Vault.
New-SqlCngColumnMasterKeySettings	Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store supporting the Cryptography Next Generation (CNG) API.
New-SqlColumnEncryptionKey	Creates a column encryption key object in the database.
New-SqlColumnEncryptionKeyEncryptedValue	Produces an encrypted value of a column encryption key.
New-SqlColumnEncryptionSettings	Creates a SqlColumnEncryptionSettings object that encapsulates information about a single column's encryption, including CEK and encryption type.
New-SqlColumnMasterKey	Creates a column master key object in the database.
New-SqlColumnMasterKeySettings	Creates a SqlColumnMasterKeySettings object for a column master key with the specified provider and key path.
New-SqlCspColumnMasterKeySettings	Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store with a Cryptography Service Provider (CSP) supporting Cryptography API (CAPI).
Remove-SqlColumnEncryptionKey	Removes the column encryption key object from the database.
Remove-SqlColumnEncryptionKeyValue	Removes an encrypted value from an existing column encryption key object in the database.
Remove-SqlColumnMasterKey	Removes the column master key object from the database.
Set-SqlColumnEncryption	Encrypts, decrypts, or re-encrypts specified columns in the database.

Del via