dbatools.io = command-line SQL Server Management Studio - Keys, certificates
This post is part of the series showing practical usage examples. The main post covering links to all posts can be found here: dbatools.io = command-line SQL Server Management Studio: Table of contents.
Service Master Key
There are multiple security-related objects that are not easily accessible via SQL Server Management Studio. The first one would be Service Master Key, if exists, can be seen under the master database. Luckily, dbatools can help us to take a backup.
New-DbaServiceMasterKey
# set the secure password that will be used later on
$securePassword = ('<YourStrong@Passw0rd>' | ConvertTo-SecureString -asPlainText -Force)
# creating Service Master Key if does not exist
New-DbaServiceMasterKey -SqlInstance $server -SecurePassword $securePassword -WhatIf
<#
What if: Performing the operation "Creating New MasterKey" on target "localhost,1433".
#>
Backup-DbaServiceMasterKey
# backup the Service Master Key
Backup-DbaServiceMasterKey -SqlInstance $server -SecurePassword $securePassword
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Path : /var/opt/mssql/data\localhost,1433-SMK-20200915225130.key
Status : Success
#>
Database Master Key
There is no way to see database master keys via SSMS GUI, so the only way would be to manage it using T-SQL, or… dbatools.
New-DbaDbMasterKey
# new Database Master Key for multiple databases
New-DbaDbMasterKey -SqlInstance $server -Database keys, model -SecurePassword $securePassword -Confirm:$false
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : keys
CreateDate : 15/09/2020 21:58:00
DateLastModified : 15/09/2020 21:58:00
IsEncryptedByServer : True
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : model
CreateDate : 15/09/2020 21:58:56
DateLastModified : 15/09/2020 21:58:56
IsEncryptedByServer : True
#>
Get-DbaDbMasterKey
# list all Database Master Keys available on the instance
Get-DbaDbMasterKey -SqlInstance $server | Format-Table
<#
ComputerName InstanceName SqlInstance Database CreateDate DateLastModified IsEncryptedByServer
------------ ------------ ----------- -------- ---------- ---------------- -------------------
localhost MSSQLSERVER e6928404da5d keys 15/09/2020 21:58:56 15/09/2020 21:58:56 True
localhost MSSQLSERVER e6928404da5d model 15/09/2020 21:58:00 15/09/2020 21:58:00 True
#>
Backup-DbaDbMasterKey
# backup all Database Master Keys
Backup-DbaDbMasterKey -SqlInstance $server -SecurePassword $securePassword
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : keys
Path : /var/opt/mssql/data\localhost,1433-keys-20200915230106.key
Status : Success
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : model
Path : /var/opt/mssql/data\localhost,1433-model-20200915230106.key
Status : Success
#>
Remove-DbaDbMasterKey
# remove all the Database Master Keys
Remove-DbaDbMasterKey -SqlInstance $server -All -Confirm:$false
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : keys
Status : Master key removed
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : model
Status : Master key removed
#>
Database Asymmetric Key
A very similar case to the service master key - we can see them in the object explorer, but not much can be done via GUI. dbatools to the rescue.
New-DbaDbAsymmetricKey
New-DbaDbAsymmetricKey -SqlInstance $server -Name AsymmKey1 -Database keys -SecurePassword $securePassword -Algorithm Rsa4096
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : keys
Name : AsymmKey1
Subject :
StartDate :
ActiveForServiceBrokerDialog :
ExpirationDate :
Issuer :
LastBackupDate :
Owner : dbo
PrivateKeyEncryptionType : Password
Serial :
#>
Get-DbaDbAsymmetricKey
# see the list of the asymmetric keys
Get-DbaDbAsymmetricKey -SqlInstance $server | Format-Table
<#
ComputerName InstanceName SqlInstance Database Name Owner KeyEncryptionAlgorithm KeyLength PrivateKeyEncryptionType Thumbprint
------------ ------------ ----------- -------- ---- ----- ---------------------- --------- ------------------------ ----------
localhost MSSQLSERVER e6928404da5d keys AsymmKey1 dbo CryptographicProviderDefined 4096 Password {47, 222, 249, 180...}
#>
Remove-DbaDbAsymmetricKey
# remove a selected asymmetric key
Remove-DbaDbAsymmetricKey -SqlInstance $server -Database keys -Confirm:$false
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : keys
Name : AsymmKey1
Status : Success
#>
Database Certificate
Another object from the “family” where we can see it in the Object Explorer, but the only available option is “Delete”.
New-DbaDbCertificate
# get a new database certificate
New-DbaDbCertificate -SqlInstance $server -Database keys -Name CertKey -SecurePassword $securePassword
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : keys
Name : CertKey
Subject : CertKey Database Certificate
StartDate : 15/09/2020 00:00:00
ActiveForServiceBrokerDialog : False
ExpirationDate : 15/09/2025 00:00:00
Issuer : CertKey Database Certificate
LastBackupDate : 01/01/0001 00:00:00
Owner : dbo
PrivateKeyEncryptionType : Password
Serial : c1 51 6e d0 f5 ea 35 f8
#>
Get-DbaDbCertificate
# list all the certificates
Get-DbaDbCertificate -SqlInstance $server | Format-Table
<#
ComputerName InstanceName SqlInstance Database Name Subject
------------ ------------ ----------- -------- ---- -------
localhost MSSQLSERVER e6928404da5d master ##MS_AgentSigningCertificate## MS_AgentSigningCertificate
localhost MSSQLSERVER e6928404da5d master ##MS_PolicySigningCertificate## MS_PolicySigningCertificate
localhost MSSQLSERVER e6928404da5d master ##MS_SchemaSigningCertificate8CAFA80B4BDA63350F588D8F60D61E8EAA3FECC0## MS_SchemaSigningCertificate8CAF...
localhost MSSQLSERVER e6928404da5d master ##MS_SmoExtendedSigningCertificate## MS_SmoExtendedSigningCertificate
localhost MSSQLSERVER e6928404da5d master ##MS_SQLAuthenticatorCertificate## MS_SQLAuthenticatorCertificate
localhost MSSQLSERVER e6928404da5d master ##MS_SQLReplicationSigningCertificate## MS_SQLReplicationSigningCertifi...
localhost MSSQLSERVER e6928404da5d master ##MS_SQLResourceSigningCertificate## MS_SQLResourceSigningCertificate
localhost MSSQLSERVER e6928404da5d msdb ##MS_AgentSigningCertificate## MS_AgentSigningCertificate
localhost MSSQLSERVER e6928404da5d msdb ##MS_SchemaSigningCertificate8CAFA80B4BDA63350F588D8F60D61E8EAA3FECC0## MS_SchemaSigningCertificate8CAF...
localhost MSSQLSERVER e6928404da5d keys CertKey CertMsdb Database Certificate
#>
Backup-DbaDbCertificate
# backup the certificates
Backup-DbaDbCertificate -SqlInstance $server -EncryptionPassword $securePassword -DecryptionPassword $securePassword
<#
Certificate : CertKey
ComputerName : localhost
Database : keys
InstanceName : MSSQLSERVER
Key : Password required to export key
Path : /var/opt/mssql/data/\CertKey202009152318431843.cer
SqlInstance : e6928404da5d
Status : Success
#>
Remove-DbaDbCertificate
# removed a given certificate
Remove-DbaDbCertificate -SqlInstance $server -Certificate CertKey -Confirm:$false
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : keys
Certificate : CertKey
Status : Success
#>
Restore-DbaDbCertificate
# restore the certificate from the backup
Restore-DbaDbCertificate -SqlInstance $server -Path '/var/opt/mssql/data/\CertMsdb2020091523230623620200916120717717.cer' -Database keys -EncryptionPassword $securePassword -DecryptionPassword $securePassword -Confirm:$false
<#
ComputerName : localhost
InstanceName : MSSQLSERVER
SqlInstance : e6928404da5d
Database : keys
Name : CertMsdb2020091523230623620200916120717717
Subject : CertMsdb Database Certificate
StartDate : 15/09/2020 00:00:00
ActiveForServiceBrokerDialog : True
ExpirationDate : 15/09/2025 00:00:00
Issuer : CertMsdb Database Certificate
LastBackupDate : 01/01/0001 00:00:00
Owner : dbo
PrivateKeyEncryptionType : Password
Serial : c7 64 80 e0 c4 e6 f4 37
#>
That post concludes the series of twelve summer Wednesdays with the dbatools as command-line version of SQL Server Management Studio.
Thank you,
Mikey
