The SqlServer module also contains plenty of commands around security-related features. For the code samples, we will be using a connection object, as follows:

$server =  Get-SqlInstance -ServerInstance sql2017

Using the Get-SqlCredential and Set-SqlCredential commands, you can check and update credentials the Sql Instance is using. This makes rotating service account configurations simple to automate:

# Check on credential
$server | Get-SqlCredential -Name "ADser_SqlPowerShell" | fl *

# Update Credential
$cred = Get-Credential
$server | Set-SqlCredential -Name "ADser_SqlPowerShell" -Secret $cred.Password

Moving on, you can also set up the AlwaysEncrypted feature which keeps columns in a permanently encrypted state. First of all, we need to have a certificate suitable for the task:

$paramNewSelfSignedCertificate = @{
    Subject            = "AlwaysEncryptedCert"
    CertStoreLocation  = 'Cert:CurrentUserMy'
    KeyExportPolicy    = 'Exportable'
    Type               = 'DocumentEncryptionCert'
    KeyUsage           = 'DataEncipherment'
    KeySpec            = 'KeyExchange'
}
$cert = New-SelfSignedCertificate @paramNewSelfSignedCertificate

Then, we need the database to protect:

$database = $server | Get-SqlDatabase databasename

Finally, we use the SqlServer module to apply column encryption to the selected database using the certificate we created, by creating the master-key settings, generating a master key from them, and finally, applying it:

$paramNewSqlCertificateStoreColumnMasterKeySettings = @{
    CertificateStoreLocation  = "CurrentUser"
    Thumbprint                = $cert.Thumbprint
}
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings @paramNewSqlCertificateStoreColumnMasterKeySettings
 
$paramNewSqlColumnMasterKey = @{
    Name                     = "ColumnMasterKey1"
    InputObject              = $database
    ColumnMasterKeySettings  = $cmkSettings
}
New-SqlColumnMasterKey @paramNewSqlColumnMasterKey

$paramNewSqlColumnEncryptionKey = @{
    Name                 = "ColumnEncryptionKey1"
    InputObject          = $database
    ColumnMasterKeyName  = " ColumnMasterKey1"
}
New-SqlColumnEncryptionKey @paramNewSqlColumnEncryptionKey