This repository has been archived by the owner on Aug 7, 2020. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 7
/
New-EncryptionKeys.ps1
87 lines (77 loc) · 3.28 KB
/
New-EncryptionKeys.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
# Modified from script generated by SQL Server Management Studio at 10:27 PM on 2/5/2016
#Requires -Modules sqlserver
[cmdletbinding()]
param(
[Parameter(Mandatory = $true, ValueFromPipeline = $true)] [string] $ConnectionString,
[string] $MasterKeyDNSName = "CN=Always Encrypted Sample Cert",
[switch] $RemoveExistingCerts,
[switch] $ExportCertificate,
[switch] $ExportCertificateKeys,
[string] $MasterKeySQLName = "AlwaysEncryptedSampleCMK",
[string] $AuthColumnKeyName = "AuthColumnsKey",
[string] $AppColumnKeyName = "AppColumnsKey",
[string] $LogColumnKeyName = "LogColumnsKey"
)
try {
$smoDatabase = Get-SqlDatabase -ConnectionString $ConnectionString
}
catch {
Write-Error $_
break
}
if ($RemoveExistingCerts) {
Write-Verbose "Removing All Existing Certificates Named $($MasterKeyDNSName)"
$existingColumns = Get-SqlColumnEncryptionKey -InputObject $smoDatabase
$existingColumns | ForEach-Object {
Remove-SqlColumnEncryptionKey -Name $_.Name -InputObject $smoDatabase
}
Remove-SqlColumnMasterKey -Name $MasterKeySQLName -InputObject $smoDatabase
Get-ChildItem Cert:\CurrentUser\My | Where-Object subject -eq $MasterKeyDNSName | Remove-Item
}
$Cert = (Get-ChildItem Cert:\CurrentUser\My | Where-Object subject -eq 'CN=Always Encrypted Sample Cert') | Select-Object Thumbprint -First 1
if ($Cert) {
Write-Verbose "Certificate `"$($MasterKeyDNSName)`" Already exists"
}
else {
Write-Host "Creating Self Signed Certificate `"$($MasterKeyDNSName)`""
$Cert = New-SelfSignedCertificate `
-Subject $MasterKeyDNSName `
-CertStoreLocation Cert:\CurrentUser\My `
-KeyExportPolicy Exportable `
-Type DocumentEncryptionCert `
-KeyUsage DataEncipherment `
-KeySpec KeyExchange
$CmkPath = "Cert:\CurrentUser\My\$($cert.ThumbPrint)"
Write-Verbose "Column Master Key Certificate Path: $($CmkPath)"
}
if ($ExportCertificate) {
Get-ChildItem Cert:\CurrentUser\My |
Where-Object subject -eq "CN=Always Encrypted Sample Cert" |
Export-Certificate -FilePath "$($MasterKeySQLName).cer" | Out-Null
}
if ($ExportCertificateKeys) {
Get-ChildItem Cert:\CurrentUser\My |
Where-Object subject -eq "CN=Always Encrypted Sample Cert" |
Export-PfxCertificate -FilePath "$($MasterKeySQLName).pfx" -Password (ConvertTo-SecureString -String "1234" -Force -AsPlainText) | Out-Null
}
if ($smoDatabase.ColumnMasterKeys['AlwaysEncryptedSampleCMK']) {
Write-Warning "Master Key Reference $($MasterKeySQLName) already exists in the database."
}
else {
# Create a SqlColumnMasterKeySettings object for your column master key.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings `
-CertificateStoreLocation "CurrentUser" `
-Thumbprint $Cert.Thumbprint
New-SqlColumnMasterKey -Name $MasterKeySQLName -InputObject $smoDatabase -ColumnMasterKeySettings $cmkSettings | Out-Null
}
$ExistingColumnKeys = $smoDatabase.ColumnEncryptionKeys
@($AuthColumnKeyName, $AppColumnKeyName, $LogColumnKeyName) | ForEach-Object {
if ($ExistingColumnKeys[$_]) {
Write-Warning "Column Encryption Key already $_ exists."
}
else {
$smoDatabase | New-SqlColumnEncryptionKey `
-ColumnMasterKey $MasterKeySQLName `
-Name $_ | Out-Null
}
}