commands

^

Start-DbaDbEncryption

Author Chrissy LeMaire (@cl), netnerds.net
Availability Windows, Linux, macOS

 

Want to see the source code for this command? Check out Start-DbaDbEncryption on GitHub.
Want to see the Bill Of Health for this command? Check out Start-DbaDbEncryption.

Synopsis

Combo command that encrypts all instances on a database and backs up all keys and certs

Description

Combo command that encrypts all instances on a database and backs up all keys and certs

  • Ensures a database master key exists in the master database and backs it up
  • Ensures a database certificate or asymmetric key exists in the master database and backs it up
  • Creates a database encryption key in the target database and backs it up
  • Enables database encryption on the target database and backs it up

Syntax

Start-DbaDbEncryption
    [[-SqlInstance] <DbaInstanceParameter[]>]
    [[-SqlCredential] <PSCredential>]
    [[-EncryptorName] <String>]
    [[-EncryptorType] <String>]
    [[-Database] <String[]>]
    [-BackupPath] <String>
    [-MasterKeySecurePassword] <SecureString>
    [[-CertificateSubject] <String>]
    [[-CertificateStartDate] <DateTime>]
    [[-CertificateExpirationDate] <DateTime>]
    [-CertificateActiveForServiceBrokerDialog]
    [-BackupSecurePassword] <SecureString>
    [[-InputObject] <Database[]>]
    [-AllUserDatabases]
    [-Force]
    [-EnableException]
    [-WhatIf]
    [-Confirm]
    [<CommonParameters>]

 

Examples

 

Example: 1
PS C:\> $masterkeypass = (Get-Credential justneedpassword).Password
PS C:\> $certbackuppass = (Get-Credential justneedpassword).Password
PS C:\> $params = @{
>>      SqlInstance             = "sql01"
>>      AllUserDatabases        = $true
>>      MasterKeySecurePassword = $masterkeypass
>>      BackupSecurePassword    = $certbackuppass
>>      BackupPath              = "C:\temp"
>>      EnableException         = $true
>>  }
PS C:\> Start-DbaDbEncryption @params

Prompts for two passwords (the username doesn't matter, this is just an easy & secure way to get a secure password)
Then encrypts all user databases on sql01, creating master keys and certificates as needed, and backing all of them up to C:\temp, securing them with the password set in $certbackuppass

Required Parameters

-BackupPath

The path (accessible by and relative to the SQL Server) where master keys and certificates are backed up

Alias
Required True
Pipeline false
Default Value
-MasterKeySecurePassword

A master service key will be created and backed up if one does not exist
MasterKeySecurePassword is the secure string (password) used to create the key
This parameter is required even if no master keys are made, as we won't know if master key creation will be required until each server is processed

Alias
Required True
Pipeline false
Default Value
-BackupSecurePassword

This command will perform backups of all maskter keys and certificates. Use this parameter to set the backup password

Alias
Required True
Pipeline false
Default Value

Optional Parameters

-SqlInstance

The target SQL Server instance or instances.

Alias
Required False
Pipeline false
Default Value
-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

Alias
Required False
Pipeline false
Default Value
-EncryptorName

The name of the encryptor (Certificate or Asymmetric Key) in master that will be used. Tries to find one if one is not specified. If certificate does not exist and -Force is specified, one will be
created with the given Encryptor Name.
In order to encrypt the database encryption key with an asymmetric key, you must use an asymmetric key that resides on an extensible key management provider.

Alias Certificate,CertificateName
Required False
Pipeline false
Default Value
-EncryptorType

Type of Encryptor - either Asymmetric or Certificate

Alias
Required False
Pipeline false
Default Value Certificate
Accepted Values AsymmetricKey,Certificate
-Database

The database or databases that will be encrypted

Alias
Required False
Pipeline false
Default Value
-CertificateSubject

Optional subject that will be used when creating all certificates

Alias
Required False
Pipeline false
Default Value
-CertificateStartDate

Optional start date that will be used when creating all certificates
By default, certs will start immediately

Alias
Required False
Pipeline false
Default Value (Get-Date)
-CertificateExpirationDate

Optional expiration that will be used when creating all certificates
By default, certs will last 5 years

Alias
Required False
Pipeline false
Default Value (Get-Date).AddYears(5)
-CertificateActiveForServiceBrokerDialog

Microsoft has not provided a description so we can only assume the cert is active for service broker dialog

Alias
Required False
Pipeline false
Default Value False
-InputObject

Enables piping from Get-DbaDatabase

Alias
Required False
Pipeline true (ByValue)
Default Value
-AllUserDatabases

Run command against all user databases
This was added to emphasize that all user databases will be encrypted

Alias
Required False
Pipeline false
Default Value False
-Force

If EncryptorName is specified and certificate does not exist, one will be created with the given Encryptor Name.

Alias
Required False
Pipeline false
Default Value False
-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

Alias
Required False
Pipeline false
Default Value False
-WhatIf

Shows what would happen if the command were to run. No actions are actually performed.

Alias wi
Required False
Pipeline false
Default Value
-Confirm

Prompts you for confirmation before executing any changing operations within the command.

Alias cf
Required False
Pipeline false
Default Value