使用 PowerShell 設定永遠加密Configure Always Encrypted using PowerShell

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 是Azure SQL DatabaseAzure SQL DatabaseYesAzure SQL DatabaseAzure SQL Database

SqlServer PowerShell 模組提供 Cmdlet 讓您在 Azure SQL DatabaseAzure SQL DatabaseSQL ServerSQL Server 中設定 Always EncryptedThe SqlServer PowerShell module provides cmdlets for configuring Always Encrypted in both Azure SQL DatabaseAzure SQL Database or SQL ServerSQL Server.

使用 PowerShell 設定 Always Encrypted 時的安全性考量Security Considerations when using PowerShell to Configure Always Encrypted

因為永遠加密的主要目標是為了確保已加密的敏感性資料安全無虞,即使資料庫系統遭到入侵亦然,所以在 SQL Server 電腦上執行處理金鑰或敏感性資料的 PowerShell 指令碼,可能會降低或損害此功能的優勢。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.

無論是否有角色隔離,您都可以使用 PowerShell 來管理 Always Encrypted 金鑰來控制能夠存取金鑰存放區中實際加密金鑰以及能夠存取資料庫的人員。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

在未裝載 SQL Server 執行個體的安全電腦上安裝 SqlServer 模組Install the SqlServer module on a secure computer that is NOT a computer hosting your SQL Server instance. 此模組可以直接從 PowerShell 資源庫進行安裝。The module can be installed directly from the PowerShell gallery. 如需更多詳細資料,請參閱下載指示。See the download instructions for more details.

匯入 SqlServer 模組Importing the SqlServer Module

若要載入 SqlServer 模組:To load the SqlServer module:

  1. 使用 Set-ExecutionPolicy Cmdlet,設定適當的指令碼執行原則。Use the Set-ExecutionPolicy cmdlet to set the appropriate script execution policy.
  2. 使用 Import-Module Cmdlet,匯入 SqlServer 模組。Use the Import-Module cmdlet to import the SqlServer module.

此範例會載入 SqlServer 模組。This example loads the SqlServer module.

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

連接到資料庫Connecting to a Database

某些永遠加密 Cmdlet 會處理資料庫中的資料或中繼資料,因此要求您必須先連接到資料庫。Some of the Always Encrypted cmdlets work with data or metadata in the database and require that you connect to the database first. 使用 SqlServer 模組設定永遠加密時,有兩種建議的方法可連接到資料庫:There are two recommended methods of connecting to a database when configuring Always Encrypted using the SqlServer module:

  1. 使用 Get-SqlDatabase Cmdlet 進行連線。Connect using the Get-SqlDatabase cmdlet.
  2. 使用 SQL Server PowerShell 提供者進行連線。Connect using SQL Server PowerShell Provider.

我們會持續聽取您的意見: 如果您發現本文中有過時或不正確的內容 (例如步驟或程式碼範例),請告訴我們。We are listening: If you find something outdated or incorrect in this article, such as a step or a code example, please tell us. 您可以按一下此頁面底部 [意見反應] 區段中的 [本頁] 按鈕。You can click the This page button in the Feedback section at the bottom of this page. 我們通常會在隔天閱讀有關 SQL 的每一項意見反應。We read every item of feedback about SQL, typically the next day. 謝謝。Thanks.

使用 Get-SqlDatabaseUsing Get-SqlDatabase

Get-SqlDatabase Cmdlet 可讓您連線至 SQL Server 或 Azure SQL Database 中的資料庫。The Get-SqlDatabase cmdlet allows you to connect to a database in SQL Server or in Azure SQL Database. 會傳回資料庫物件,然後使用連線至資料庫之 Cmdlet 的 InputObject 參數來傳遞。It returns a database object, which you can then pass using the InputObject parameter of a cmdlet that connects to the database.

使用 SQL Server PowerShellUsing SQL Server PowerShell

# Import the SqlServer module
Import-Module "SqlServer"  

# 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

使用 SQL Server PowerShell 提供者Using SQL Server PowerShell Provider

SQL Server PowerShell 提供者會公開類似於檔案系統路徑之路徑中的 SQL Server 物件階層。The SQL Server PowerShell Provider exposes the hierarchy of SQL Server objects in paths similar to file system paths. 透過 SQL Server PowerShell,您可以使用 Windows PowerShell 別名來巡覽路徑,這與於一般用來巡覽檔案系統路徑的命令類似。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. 一旦您瀏覽至目標執行個體和資料庫後,後續的 Cmdlet 就會以該資料庫為目標,如下列範例所示。Once you navigate to the target instance and the database, the subsequent cmdlets target that database, as shown in the following example.

注意

此連線至資料庫的方法只適用於 SQL Server (Azure SQL Database 不支援)。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

或者,您可以使用泛型 Path 參數來指定資料庫路徑,而不是巡覽至資料庫。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

使用 PowerShell 的永遠加密工作Always Encrypted Tasks using PowerShell

永遠加密 Cmdlet 參考Always Encrypted Cmdlet Reference

下列 PowerShell Cmdlet 可用於永遠加密:The following PowerShell cmdlets are available for Always Encrypted:

CMDLETCMDLET 描述Description
Add-SqlAzureAuthenticationContextAdd-SqlAzureAuthenticationContext 對 Azure 執行驗證,並取得驗證權杖。Performs authentication to Azure and acquires an authentication token.
Add-SqlColumnEncryptionKeyValueAdd-SqlColumnEncryptionKeyValue 為資料庫中現有的資料行加密金鑰物件新增加密值。Adds a new encrypted value for an existing column encryption key object in the database.
Complete-SqlColumnMasterKeyRotationComplete-SqlColumnMasterKeyRotation 完成資料行主要金鑰的輪替Completes the rotation of a column master key
Get-SqlColumnEncryptionKeyGet-SqlColumnEncryptionKey 傳回資料庫中定義的所有資料行加密金鑰物件,或傳回具有指定名稱的一個資料行加密金鑰物件。Returns all column encryption key objects defined in the database, or returns one column encryption key object with the specified name.
Get-SqlColumnMasterKeyGet-SqlColumnMasterKey 傳回資料庫中定義的資料行主要金鑰物件,或傳回具有指定名稱的一個資料行主要金鑰物件。Returns the column master key objects defined in the database, or returns one column master key object with the specified name.
Invoke-SqlColumnMasterKeyRotationInvoke-SqlColumnMasterKeyRotation 起始資料行主要金鑰的輪替。Initiates the rotation of a column master key.
New-SqlAzureKeyVaultColumnMasterKeySettingsNew-SqlAzureKeyVaultColumnMasterKeySettings 建立 SqlColumnMasterKeySettings 物件,描述儲存在 Azure 金鑰保存庫中的非對稱金鑰。Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in Azure Key Vault.
New-SqlCngColumnMasterKeySettingsNew-SqlCngColumnMasterKeySettings 建立 SqlColumnMasterKeySettings 物件,描述儲存在支援新一代密碼編譯 (CNG) API 之金鑰存放區中的非對稱金鑰。Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store supporting the Cryptography Next Generation (CNG) API.
New-SqlColumnEncryptionKeyNew-SqlColumnEncryptionKey 在資料庫中建立資料行加密金鑰物件。Creates a column encryption key object in the database.
New-SqlColumnEncryptionKeyEncryptedValueNew-SqlColumnEncryptionKeyEncryptedValue 產生資料行加密金鑰的加密值。Produces an encrypted value of a column encryption key.
New-SqlColumnEncryptionSettingsNew-SqlColumnEncryptionSettings 建立 SqlColumnEncryptionSettings 物件,以封裝單一資料行加密的相關資訊,包括 CEK 和加密類型。Creates a SqlColumnEncryptionSettings object that encapsulates information about a single column's encryption, including CEK and encryption type.
New-SqlColumnMasterKeyNew-SqlColumnMasterKey 在資料庫中建立資料行主要金鑰物件。Creates a column master key object in the database.
New-SqlColumnMasterKeySettingsNew-SqlColumnMasterKeySettings 使用指定的提供者與機碼路徑,為資料行主要金鑰建立 SqlColumnMasterKeySettings 物件。Creates a SqlColumnMasterKeySettings object for a column master key with the specified provider and key path.
New-SqlCspColumnMasterKeySettingsNew-SqlCspColumnMasterKeySettings 建立 SqlColumnMasterKeySettings 物件,描述使用支援密碼編譯 API 之密碼編譯服務提供者 (CSP) 儲存在金鑰存放區中的非對稱金鑰。Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store with a Cryptography Service Provider (CSP) supporting Cryptography API (CAPI).
Remove-SqlColumnEncryptionKeyRemove-SqlColumnEncryptionKey 從資料庫移除資料行加密金鑰物件。Removes the column encryption key object from the database.
Remove-SqlColumnEncryptionKeyValueRemove-SqlColumnEncryptionKeyValue 從資料庫中現有的資料行加密金鑰物件移除加密值。Removes an encrypted value from an existing column encryption key object in the database.
Remove-SqlColumnMasterKeyRemove-SqlColumnMasterKey 從資料庫移除資料行主要金鑰物件。Removes the column master key object from the database.
Set-SqlColumnEncryptionSet-SqlColumnEncryption 加密、解密或重新加密資料庫中指定的資料行。Encrypts, decrypts, or re-encrypts specified columns in the database.

另請參閱See Also