Skip to content

configure-always-encrypted-using-powershell.md

Latest commit

 

History

History
142 lines (102 loc) · 10 KB

configure-always-encrypted-using-powershell.md

File metadata and controls

142 lines (102 loc) · 10 KB
title Configure Always Encrypted using PowerShell | Microsoft Docs
ms.custom
ms.date 10/01/2019
ms.prod sql
ms.reviewer vanto
ms.technology security
ms.topic conceptual
ms.assetid 12f2bde5-e100-41fa-b474-2d2332fc7650
author jaszymas
ms.author jaszymas
monikerRange =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Configure Always Encrypted using PowerShell

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md]

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

Always Encrypted cmdlets in the SqlServer module work with keys or sensitive data, so it is important that you run the cmdlets on a secure computer. When managing Always Encrypted, execute the cmdlets from a different computer than the computer hosting your SQL Server instance.

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.

Links to the individual cmdlet articles are at the bottom of this page.

Prerequisites

Install the SqlServer module 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:

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

This example loads the SqlServer module.

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

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:

  1. Connect using SQL Server PowerShell.
  2. Connect using SQL Server Management Objects (SMO).

[!INCLUDEfreshInclude]

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 using the InputObject parameter of a cmdlet that connects to the database.

# Import the SqlServer module
Import-Module "SqlServer"  

# Connect to your database (Azure SQL database).
$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"
# 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" 
# 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.

See Also