When I run my script I always receive this error:
New-SqlColumnEncryptionKey : Cannot validate argument on parameter ‘InputObject’. The argument is null or empty. Provide an argument that is not null or empty, and then try the command again.
At line:1 char:64
- … SqlColumnEncryptionKey -Name $cekName -InputObject $database -Column …
Basically the $database is null, but I have already set it in the above code!! Any pointers much appreciated.
it breaks at this line
$cek = New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
# Create Cert for Encrypting
$splat =@{
subject = "AlwaysEncryptedCert 1"
CertStoreLocation = "Cert:CurrentUser\My"
KeyExportPolicy = "Exportable"
Type = "DocumentEncryptionCert"
KeyUsage = "DataEncipherment"
KeySpec = "KeyExchange"
}
$cert = New-SelfSignedCertificate @splat
# import SQLServer module this must exist on server if not download it with install-module command.
Import-Module "SqlServer"
# create connection to example database
$databaseName = "Example" #name of database
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient") | Out-Null
$connectionString = "Server=#####;Database=Example;User Id=###;Password=####;"
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]
# create column master key (CMK) using thumbprint
Install-Module -Name SqlServer
Import-Module -Name SqlServer
# create column master key (CMK) using thumbprint
$thumbprint = "f5a1a2ab690e0c2b8f116d66bcf8cd5f6e86bea4"
$cert = Get-Item -Path Cert:\CurrentUser\My\$thumbprint
$keyPath = "CurrentUser\My\$($cert.Thumbprint)"
$cmkName = "CMK3"
$sqlCommand = "CREATE COLUMN MASTER KEY [$cmkName]
WITH (
KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
KEY_PATH = '$keyPath'
)"
# Create the CMK2
$connectionString = "Server=####; Database=####; TrustServerCertificate=True; Integrated Security=True;"
Invoke-Sqlcmd -ConnectionString $connectionString -Query $sqlCommand
# verifiy the change
$cmk | Select-Object -Property *
# create column encryption key (CEK)
$cekName = "CEK3"
$cek = New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName
# verifiy the change
$cek.ColumnEncryptionKeyValues | Select-Object -Property *
# select columns to encrypet
# changes column to Latin1_General_BIN2
$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Senstive.Postcode" -EncryptionType "Deterministic" -EncryptionKey "CEK3"
$ces += New-SqlColumnEncryptionSettings -ColumnName "dbo.Senstive.BirthDate" -EncryptionType "Randomized" -EncryptionKey "CEK3"
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces -LogFileDirectory .
# verify encripyion
$serverName = "$env:COMPUTERNAME"
$databaseName = "Example"
$connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True"
Invoke-Sqlcmd -Query "SELECT TOP(1) * FROM Senstive" -ConnectionString $connStr
# check how it looks with cert to decrypt
$connStr = $connStr + "; Column Encryption Setting = Enabled"
Invoke-Sqlcmd -Query "SELECT TOP(1) * FROM Senstive" -ConnectionString $connStr