Aprovisionamiento de claves de Always Encrypted mediante PowerShell

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

En este artículo se proporcionan los pasos necesarios para aprovisionar claves de Always Encrypted con el módulo SqlServer PowerShell. Puede usar PowerShell para aprovisionar claves de Always Encrypted con y sin separación de roles, lo que proporciona control sobre quién tiene acceso a las claves de cifrado reales del almacén de claves y quién tiene acceso a la base de datos.

Para obtener información general sobre la administración de claves de Always Encrypted, incluidos algunos procedimientos recomendados de alto nivel, vea Información general de administración de claves de Always Encrypted. Para obtener información sobre el uso del módulo SqlServer PowerShell para Always Encrypted, vea Configure Always Encrypted using PowerShell (Configurar Always Encrypted con PowerShell).

Aprovisionamiento de claves sin separación de roles

El método de aprovisionamiento clave descrito en esta sección no admite la separación de roles entre administradores de seguridad y DBA. Algunos de los pasos de esta sección combinan operaciones en claves físicas con operaciones en metadatos de clave. Por lo tanto, use este método para aprovisionar las claves si su organización usa el modelo de DevOps o si la base de datos está hospedada en la nube y el objetivo principal es restringir a los administradores de la nube (pero no a los DBA locales) de acceder a datos confidenciales. No use este método si los posibles adversarios incluyen DBA o si los DBA no deben tener acceso a datos confidenciales.

Antes de ejecutar los pasos que impliquen el acceso a las claves de texto no cifrado o al almacén de claves (identificado en la columna Accesses plaintext keys/key store de la tabla siguiente), asegúrese de que el entorno de PowerShell se ejecuta en una máquina segura diferente de un equipo que hospeda la base de datos. Para obtener más información, consulte Security Considerations for Key Management (Consideraciones de seguridad para la administración de claves).

Almacén de certificados de Windows sin separación de roles (ejemplo)

Este script es un ejemplo completo para generar una clave maestra de columna que sea un certificado en el Almacén de certificados de Windows, generar y cifrar una clave de cifrado de columna y crear los metadatos de clave en una base de datos de SQL Server.

[CmdletBinding()]
param(
	[Parameter(Mandatory = $false)]
	[string]$DatabaseName = '<database name>',

	[Parameter(Mandatory = $false)]
	[string]$ServerName = "<server name>",

	[Parameter(Mandatory = $false)]
	[string]$CertificateSubject = "AlwaysEncryptedCert",

	[Parameter(Mandatory = $false)]
	[string]$CmkName = "CMK",

	[Parameter(Mandatory = $false)]
	[string]$CekName = "CEK"
)

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

Import-Module SqlServer -MinimumVersion 22.0.50 -ErrorAction Stop

Write-Host "[AE] Locating certificate '$CertificateSubject' in CurrentUser\\My"
$cert = Get-ChildItem -Path Cert:CurrentUser\My |
	Where-Object { $_.Subject -eq "CN=$CertificateSubject" } |
	Sort-Object NotAfter -Descending |
	Select-Object -First 1

if (-not $cert) {
	Write-Host "[AE] Certificate not found. Creating self-signed certificate."
	$cert = New-SelfSignedCertificate `
		-Subject $CertificateSubject `
		-CertStoreLocation Cert:CurrentUser\My `
		-KeyExportPolicy Exportable `
		-Type DocumentEncryptionCert `
		-KeyUsage DataEncipherment `
		-KeySpec KeyExchange
}

Write-Host "[AE] Connecting to SQL Server '$ServerName' / Database '$DatabaseName'"
$connStr = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30"

try {
	$database = Get-SqlDatabase -ConnectionString $connStr -ErrorAction Stop
}
catch {
	Write-Error "Failed to connect to '$ServerName' database '$DatabaseName'. Verify instance name SQL2025, database existence, and local permissions."
	throw
}

Write-Host "[AE] Creating CMK settings from certificate thumbprint"
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation "CurrentUser" -Thumbprint $cert.Thumbprint

Write-Host "[AE] Ensuring CMK '$CmkName' exists"
$existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
if (-not $existingCmk) {
	New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
}

Write-Host "[AE] Ensuring CEK '$CekName' exists"
$existingCek = Get-SqlColumnEncryptionKey -InputObject $database | Where-Object { $_.Name -eq $CekName }
if (-not $existingCek) {
	New-SqlColumnEncryptionKey -Name $CekName -InputObject $database -ColumnMasterKey $CmkName | Out-Null
}

Write-Host "Completed successfully"

Azure Key Vault sin separación de roles (ejemplo)

Este script es un ejemplo completo para aprovisionar y configurar un almacén de claves en Azure Key Vault, generar una clave maestra de columna en el almacén, generar y cifrar una clave de cifrado de columna y crear los metadatos de clave en una base de datos de Azure SQL.

param(
	[Parameter(Mandatory = $true)] [string]$SubscriptionId,
	[Parameter(Mandatory = $true)] [string]$ResourceGroupName,
	[Parameter(Mandatory = $true)] [string]$AzureLocation,
	[Parameter(Mandatory = $true)] [string]$KeyVaultName,
	[Parameter(Mandatory = $true)] [string]$KeyName,
	[Parameter(Mandatory = $true)] [string]$ServerName,
	[Parameter(Mandatory = $true)] [string]$DatabaseName,
	[string]$CmkName = "CMK",
	[string]$CekName = "CEK",
	[bool]$AssignRbacToCurrentPrincipal = $true
)

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

Import-Module Az.Accounts -ErrorAction Stop
Import-Module Az.Resources -ErrorAction Stop
Import-Module Az.KeyVault -ErrorAction Stop
Import-Module SqlServer -ErrorAction Stop

function Get-CurrentPrincipalObjectId {
	param([string]$AccountId)

	$userSignedIn = Get-AzADUser -SignedIn -ErrorAction SilentlyContinue
	if ($userSignedIn) { return $userSignedIn.Id }

	$user = Get-AzADUser -UserPrincipalName $AccountId -ErrorAction SilentlyContinue
	if ($user) { return $user.Id }

	$sp = Get-AzADServicePrincipal -DisplayName $AccountId -ErrorAction SilentlyContinue | Select-Object -First 1
	if ($sp) { return $sp.Id }

	throw "Could not resolve Microsoft Entra object id for account '$AccountId'."
}

try {
	Write-Host "[AE] Signing in and selecting subscription"
	Connect-AzAccount | Out-Null
	$ctx = Set-AzContext -SubscriptionId $SubscriptionId

	Write-Host "[AE] Ensuring resource group exists"
	$resourceGroup = Get-AzResourceGroup -Name $ResourceGroupName -ErrorAction SilentlyContinue
	if (-not $resourceGroup) {
		$resourceGroup = New-AzResourceGroup -Name $ResourceGroupName -Location $AzureLocation
	}

	Write-Host "[AE] Ensuring key vault exists (RBAC mode)"
	$vault = Get-AzKeyVault -VaultName $KeyVaultName -ResourceGroupName $ResourceGroupName -ErrorAction SilentlyContinue
	if (-not $vault) {
		$vault = New-AzKeyVault -VaultName $KeyVaultName -ResourceGroupName $ResourceGroupName -Location $AzureLocation -EnableRbacAuthorization
	}

	if (-not $vault.EnableRbacAuthorization) {
		throw "Key Vault '$KeyVaultName' is not using RBAC authorization. Enable RBAC authorization on the vault before running this script."
	}

	if ($AssignRbacToCurrentPrincipal) {
		Write-Host "[AE] Ensuring RBAC role assignment"
		$principalSignInName = $ctx.Account.Id
		$roleName = "Key Vault Crypto Officer"
		$existingRole = Get-AzRoleAssignment -SignInName $principalSignInName -Scope $vault.ResourceId -RoleDefinitionName $roleName -ErrorAction SilentlyContinue
		if (-not $existingRole) {
			New-AzRoleAssignment -SignInName $principalSignInName -Scope $vault.ResourceId -RoleDefinitionName $roleName | Out-Null
		}
	}

	Write-Host "[AE] Ensuring column master key material exists in Key Vault"
	$akvKey = Get-AzKeyVaultKey -VaultName $KeyVaultName -Name $KeyName -ErrorAction SilentlyContinue
	if (-not $akvKey) {
		$akvKey = Add-AzKeyVaultKey -VaultName $KeyVaultName -Name $KeyName -Destination "Software"
	}

	Write-Host "[AE] Connecting to Azure SQL and creating metadata"
	$keyVaultAccessToken = (Get-AzAccessToken -ResourceUrl "https://vault.azure.net").Token
	$connStr = "Server=tcp:$ServerName.database.windows.net,1433;Database=$DatabaseName;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Interactive"
	$database = Get-SqlDatabase -ConnectionString $connStr -Encrypt Mandatory
	$cmkSettings = New-SqlAzureKeyVaultColumnMasterKeySettings -KeyUrl $akvKey.Key.Kid

	$existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
	if (-not $existingCmk) {
		New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
	}

	$existingCek = Get-SqlColumnEncryptionKey -InputObject $database | Where-Object { $_.Name -eq $CekName }
	if (-not $existingCek) {
		New-SqlColumnEncryptionKey -Name $CekName -InputObject $database -ColumnMasterKey $CmkName -KeyVaultAccessToken $keyVaultAccessToken | Out-Null
	}

	Write-Host "Completed successfully"
}
catch {
	Write-Error "Script failed: $($_.Exception.Message)"
	throw
}

CNG/KSP sin separación de roles (ejemplo)

El script siguiente es un ejemplo completo para generar una clave maestra de columna en un almacén de claves que implemente la API Cryptography Next Generation (CNG), generar y cifrar una clave de cifrado de columna y crear los metadatos de clave en una base de datos de SQL Server.

En el ejemplo se usa el almacén de claves que usa el proveedor de almacenamiento de claves de software de Microsoft. Puede modificar el ejemplo para usar otro almacén, por ejemplo, el módulo de seguridad de hardware. Para ello, debe asegurarse de que el proveedor de almacén de claves (KSP) que implementa CNG para el dispositivo esté instalado correctamente en la máquina. Deberá reemplazar Microsoft Software Key Storage Provider por el nombre del KSP de su dispositivo.

[CmdletBinding()]
param(
	[Parameter(Mandatory = $false)]
	[string]$ServerName = "<server name>",

	[Parameter(Mandatory = $true)]
	[string]$DatabaseName = "<database name>",

	[Parameter(Mandatory = $false)]
	[string]$CngKeyName = "AlwaysEncryptedKey",

	[Parameter(Mandatory = $false)]
	[string]$CmkName = "CMK",

	[Parameter(Mandatory = $false)]
	[string]$CekName = "CEK"
)

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

# Local key store provider and key settings.
$cngProviderName = "Microsoft Software Key Storage Provider"
$cngAlgorithmName = "RSA"
$cngKeySize = 2048

Import-Module SqlServer -ErrorAction Stop

Write-Host "[AE] Creating local CNG key '$CngKeyName'"
$cngProvider = New-Object System.Security.Cryptography.CngProvider($cngProviderName)
$cngKeyParams = New-Object System.Security.Cryptography.CngKeyCreationParameters
$cngKeyParams.Provider = $cngProvider
$cngKeyParams.KeyCreationOptions = [System.Security.Cryptography.CngKeyCreationOptions]::OverwriteExistingKey
$keySizeProperty = New-Object System.Security.Cryptography.CngProperty(
	"Length",
	[System.BitConverter]::GetBytes($cngKeySize),
	[System.Security.Cryptography.CngPropertyOptions]::None
)
$cngKeyParams.Parameters.Add($keySizeProperty)
$cngAlgorithm = New-Object System.Security.Cryptography.CngAlgorithm($cngAlgorithmName)
[System.Security.Cryptography.CngKey]::Create($cngAlgorithm, $CngKeyName, $cngKeyParams) | Out-Null

Write-Host "[AE] Connecting to $ServerName / $DatabaseName"
$connStr = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;Encrypt=True;TrustServerCertificate=True"
$database = Get-SqlDatabase -ConnectionString $connStr

Write-Host "[AE] Preparing column master key settings"
$cmkSettings = New-SqlCngColumnMasterKeySettings -CngProviderName $cngProviderName -KeyName $CngKeyName

Write-Host "[AE] Ensuring CMK exists"
$existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
if (-not $existingCmk) {
	New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
}

Write-Host "[AE] Ensuring CEK exists"
$existingCek = Get-SqlColumnEncryptionKey -InputObject $database | Where-Object { $_.Name -eq $CekName }
if (-not $existingCek) {
	New-SqlColumnEncryptionKey -Name $CekName -InputObject $database -ColumnMasterKey $CmkName | Out-Null
}

Write-Host "Completed successfully"

Aprovisionamiento clave con separación de roles

En esta sección se proporcionan los pasos necesarios para configurar el cifrado cuando los administradores de seguridad no tienen acceso a la base de datos y los administradores de base de datos no tienen acceso al almacén de claves ni a las claves de texto no cifrado.

Administrador de seguridad

Antes de ejecutar los pasos que impliquen el acceso a las claves de texto no cifrado o al almacén de claves (identificado en la columna Accesses plaintext keys/key store de la tabla siguiente), asegúrese de que:

• El entorno de PowerShell se ejecuta en un equipo seguro distinto al equipo que hospeda la base de datos.
• Los DBA de la organización no tienen acceso al equipo (eso iría contra el fin de la separación de roles).

Para obtener más información, consulte Security Considerations for Key Management (Consideraciones de seguridad para la administración de claves).

DBA

Los DBA usan la información que reciben del Administrador de seguridad (paso 7 anterior) para crear y administrar los metadatos de clave de Always Encrypted en la base de datos.

Almacén de certificados de Windows con separación de roles (ejemplo)

Administrador de seguridad

[CmdletBinding()]
param(
	[Parameter(Mandatory = $false)]
	[string]$ServerName = '<server name>',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$DatabaseName = '<database name>',

	[Parameter(Mandatory = $false)]
	[string]$CertificateSubject = 'AlwaysEncryptedCert',

	[Parameter(Mandatory = $false)]
	[string]$CmkName = 'CMK1',

	[Parameter(Mandatory = $false)]
	[string]$CekName = 'CEK1',

	[Parameter(Mandatory = $false)]
	[string]$ExportKeyDataPath = 'C:\temp\keydata.txt'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

Import-Module SqlServer -MinimumVersion 22.0.50 -ErrorAction Stop

Write-Host "[AE] Finding certificate '$CertificateSubject' in CurrentUser\\My"
$cert = Get-ChildItem -Path 'Cert:CurrentUser\My' |
	Where-Object { $_.Subject -eq "CN=$CertificateSubject" } |
	Sort-Object NotAfter -Descending |
	Select-Object -First 1

if (-not $cert) {
	Write-Host '[AE] Certificate not found. Creating a new self-signed certificate.'
	$cert = New-SelfSignedCertificate `
		-Subject $CertificateSubject `
		-CertStoreLocation 'Cert:CurrentUser\My' `
		-KeyExportPolicy Exportable `
		-Type DocumentEncryptionCert `
		-KeyUsage DataEncipherment `
		-KeySpec KeyExchange
}

Write-Host "[AE] Connecting to SQL Server '$ServerName' / Database '$DatabaseName'"
$connStr = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30"

try {
	$database = Get-SqlDatabase -ConnectionString $connStr -ErrorAction Stop
}
catch {
	Write-Error "Failed to connect to '$ServerName' / '$DatabaseName'. Verify instance name, database, and local permissions."
	throw
}

Write-Host '[AE] Building CMK settings from certificate'
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation 'CurrentUser' -Thumbprint $cert.Thumbprint

Write-Host "[AE] Ensuring CMK '$CmkName' exists"
$existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
if (-not $existingCmk) {
	New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
}

Write-Host "[AE] Ensuring CEK '$CekName' exists"
$existingCek = Get-SqlColumnEncryptionKey -InputObject $database | Where-Object { $_.Name -eq $CekName }
if (-not $existingCek) {
	New-SqlColumnEncryptionKey -Name $CekName -InputObject $database -ColumnMasterKey $CmkName | Out-Null
}

if ($ExportKeyDataPath) {
	Write-Host "[AE] Exporting key metadata to '$ExportKeyDataPath'"
	$encryptedValue = New-SqlColumnEncryptionKeyEncryptedValue -TargetColumnMasterKeySettings $cmkSettings
	"KeyStoreProviderName,KeyPath,EncryptedValue" | Set-Content -Path $ExportKeyDataPath -Encoding UTF8
	"$($cmkSettings.KeyStoreProviderName),$($cmkSettings.KeyPath),$encryptedValue" | Add-Content -Path $ExportKeyDataPath -Encoding UTF8
}

Write-Host 'Completed successfully'

DBA

[CmdletBinding()]
param(
	[Parameter(Mandatory = $false)]
	[string]$ServerName = 'localhost\SQL2025',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$DatabaseName = 'AdventureWorks2025',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$KeyDataFile = 'C:\temp\keydata.txt',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$CmkName = 'CMK1',

	[Parameter(Mandatory = $false)]
	[ValidateNotNullOrEmpty()]
	[string]$CekName = 'CEK1'
)

Set-StrictMode -Version Latest
$ErrorActionPreference = 'Stop'

Import-Module SqlServer -MinimumVersion 22.0.50 -ErrorAction Stop

if (-not (Test-Path -Path $KeyDataFile -PathType Leaf)) {
	throw "Key data file not found: $KeyDataFile"
}

Write-Host "[AE] Loading key metadata from '$KeyDataFile'"
$keyData = Import-Csv -Path $KeyDataFile
if (-not $keyData) {
	throw "Key data file '$KeyDataFile' is empty."
}

$keyDataRow = $keyData | Select-Object -First 1
if (-not $keyDataRow.KeyStoreProviderName -or -not $keyDataRow.KeyPath -or -not $keyDataRow.EncryptedValue) {
	throw "Key data file must include non-empty columns: KeyStoreProviderName, KeyPath, EncryptedValue."
}

Write-Host "[AE] Connecting to SQL Server '$ServerName' / Database '$DatabaseName'"
$connStr = "Server=$ServerName;Database=$DatabaseName;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30"

try {
	$database = Get-SqlDatabase -ConnectionString $connStr -ErrorAction Stop
}
catch {
	Write-Error "Failed to connect to '$ServerName' / '$DatabaseName'. Verify instance name, database, and local permissions."
	throw
}

Write-Host "[AE] Building CMK settings for provider '$($keyDataRow.KeyStoreProviderName)'"
$cmkSettings = New-SqlColumnMasterKeySettings -KeyStoreProviderName $keyDataRow.KeyStoreProviderName -KeyPath $keyDataRow.KeyPath

Write-Host "[AE] Ensuring CMK '$CmkName' exists"
$existingCmk = Get-SqlColumnMasterKey -InputObject $database | Where-Object { $_.Name -eq $CmkName }
if (-not $existingCmk) {
	New-SqlColumnMasterKey -Name $CmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings | Out-Null
}

Write-Host "[AE] Ensuring CEK '$CekName' exists"
$existingCek = Get-SqlColumnEncryptionKey -InputObject $database | Where-Object { $_.Name -eq $CekName }
if (-not $existingCek) {
	New-SqlColumnEncryptionKey -Name $CekName -InputObject $database -ColumnMasterKey $CmkName -EncryptedValue $keyDataRow.EncryptedValue | Out-Null
}

Write-Host 'Completed successfully'

Contenido relacionado

• Configuración del cifrado de columnas mediante Always Encrypted con PowerShell
• Rotación de claves de Always Encrypted con PowerShell
• Desarrollo de aplicaciones con Always Encrypted
• Always Encrypted
• Información general sobre la administración de claves de Always Encrypted
• Creación y almacenamiento de claves maestras de columna para Always Encrypted
• Configurar Always Encrypted con PowerShell
• Aprovisionamiento de claves de Always Encrypted mediante SQL Server Management Studio
• CREATE COLUMN MASTER KEY (Transact-SQL)
• DROP COLUMN MASTER KEY (Transact-SQL)
• CREATE COLUMN ENCRYPTION KEY (Transact-SQL)
• ALTER COLUMN ENCRYPTION KEY (Transact-SQL)
• DROP COLUMN ENCRYPTION KEY (Transact-SQL)
• sys.column_master_keys (Transact-SQL)
• sys.column_encryption_keys (Transact-SQL)