Test.ps1
|
function Encrypt-ColumnEncryptionKey{ Param( [X509Certificate] $Certificate, [string] $MasterKeyPath, #[string] $EncryptionAlgorithm, [byte[]] $ColumnEncryptionKey ) # Construct the encryptedColumnEncryptionKey # Format is # version + keyPathLength + ciphertextLength + ciphertext + keyPath + signature # We currently only support one version $version = [byte[]] @( 0x01 ) # Get the Unicode encoded bytes of cultureinvariant lower case masterKeyPath $masterKeyPathBytes = [System.Text.UnicodeEncoding]::Unicode.GetBytes($masterKeyPath.ToLowerInvariant()) $keyPathLength = [System.BitConverter]::GetBytes([Int16]$masterKeyPathBytes.Length) # Encrypt the plain text $rsa = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPublicKey($Certificate) $cipherText = $rsa.Encrypt($columnEncryptionKey, [System.Security.Cryptography.RSAEncryptionPadding]::OaepSHA1) $cipherTextLength = [System.BitConverter]::GetBytes([Int16]$cipherText.Length) # Compute hash # SHA-2-256(version + keyPathLength + ciphertextLength + keyPath + ciphertext) $sha256 = [System.Security.Cryptography.SHA256CryptoServiceProvider]::new() try { $sha256.TransformBlock($version, 0, $version.Length, $version, 0) > $null $sha256.TransformBlock($keyPathLength, 0, $keyPathLength.Length, $keyPathLength, 0) > $null $sha256.TransformBlock($cipherTextLength, 0, $cipherTextLength.Length, $cipherTextLength, 0) > $null $sha256.TransformBlock($masterKeyPathBytes, 0, $masterKeyPathBytes.Length, $masterKeyPathBytes, 0) > $null $sha256.TransformFinalBlock($cipherText, 0, $cipherText.Length) > $null $hash = $sha256.Hash } finally{ $sha256.Dispose() } # Sign the hash $rsaPrivate = [System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($Certificate) $rsaFormatter = [System.Security.Cryptography.RSAPKCS1SignatureFormatter]::new($rsaPrivate) $rsaFormatter.SetHashAlgorithm("SHA256") $signedHash = $rsaFormatter.CreateSignature($hash) # Construct the encrypted column encryption key # EncryptedColumnEncryptionKey = version + keyPathLength + ciphertextLength + keyPath + ciphertext + signature $encryptedColumnEncryptionKeyLength = $version.Length + $cipherTextLength.Length + $keyPathLength.Length + $cipherText.Length + $masterKeyPathBytes.Length + $signedHash.Length $encryptedColumnEncryptionKey = [byte[]]::new($encryptedColumnEncryptionKeyLength) # Copy version byte $currentIndex = 0; [System.Buffer]::BlockCopy($version, 0, $encryptedColumnEncryptionKey, $currentIndex, $version.Length) $currentIndex += $version.Length # Copy key path length [System.Buffer]::BlockCopy($keyPathLength, 0, $encryptedColumnEncryptionKey, $currentIndex, $keyPathLength.Length) > $null $currentIndex += $keyPathLength.Length # Copy ciphertext length [System.Buffer]::BlockCopy($cipherTextLength, 0, $encryptedColumnEncryptionKey, $currentIndex, $cipherTextLength.Length) > $null $currentIndex += $cipherTextLength.Length # Copy key path [System.Buffer]::BlockCopy($masterKeyPathBytes, 0, $encryptedColumnEncryptionKey, $currentIndex, $masterKeyPathBytes.Length) > $null $currentIndex += $masterKeyPathBytes.Length # Copy ciphertext [System.Buffer]::BlockCopy($cipherText, 0, $encryptedColumnEncryptionKey, $currentIndex, $cipherText.Length) > $null $currentIndex += $cipherText.Length # copy the signature [System.Buffer]::BlockCopy($signedHash, 0, $encryptedColumnEncryptionKey, $currentIndex, $signedHash.Length) > $null return $encryptedColumnEncryptionKey } # $masterKeyName = "TESTMK4" $masterKeyCertificatePath = "CurrentUser/My/797151D4A7F9F4C69912CBB871E12CBEAC315F39" $masterKeyCertificate = [X509Certificate] (Get-Item -Path "Cert://$masterKeyCertificatePath") # $newMasterKeyScript = "CREATE COLUMN MASTER KEY $masterKeyName WITH ( # KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE', # KEY_PATH = '$masterKeyCertificatePath' # ); # " $rng = [System.Security.Cryptography.RandomNumberGenerator]::Create() try{ $encryptionKey = [byte[]]::new(32) $rng.GetBytes($encryptionKey) } finally{ $rng.Dispose() } # $encryptionKeyName = "CEK3" $encryptedKey = Encrypt-ColumnEncryptionKey -Certificate $masterKeyCertificate -MasterKeyPath $masterKeyCertificatePath -ColumnEncryptionKey $encryptionKey $encryptedKeySerialized = "0x" + [BitConverter]::ToString($encryptedKey).Replace("-", "") $encryptedKeySerialized # $newEncryptionKeyScript = "CREATE COLUMN ENCRYPTION KEY $encryptionKeyName WITH VALUES ( # COLUMN_MASTER_KEY = $masterKeyName, # ALGORITHM = 'RSA_OAEP', # ENCRYPTED_VALUE = $encryptedKeySerialized # ); # " # $script = " # BEGIN TRANSACTION # $newMasterKeyScript # GO # $newEncryptionKeyScript # GO # COMMIT TRANSACTION # " # Invoke-SqlCmd -ServerInstance "(local)" -Database "STTI_INT" -Query $script -ErrorAction Stop |