PowerShell CMDLets added for SQL2016 Always Encrypted

The post on the SQLServer blog at TechNet by the SQL Server Tools Team today made me jump out of my seat.

The July update for SSMS includes the first substantial improvement in SQL PowerShell in many years. We owe a lot of thanks for this effort to the great collaboration with our community. We have several new CMDLETs to share with you

In one release there are twenty-five new CMDLets for the new sqlserver module

This means that if you have a PowerShell script doing Import-Module SQLPS, it will need to be changed to be Import-Module SqlServer in order to take advantage of the new provider functionality and new CMDLETs. The new module will be installed to “%Program Files\WindowsPowerShell\Modules\SqlServer” and hence no update to $env:PSModulePath is required.

So SQLPS will still continue to work but will not be updated and will not contain the new CMDlets or the future new CMDlets.

So what new things do we have?

This month we introduce CMDLETs for the following areas:

  • Always Encrypted
  • SQL Agent
  • SQL Error Logs

Chrissy LeMaire has written about the new SQL Agent cmdlets

Aaron Nelson has written about the new Get-SqlErrorLog cmdlet

Laerte Junior has written about Invoke-SQLCmd

All four of us will be presenting a webinar on the new CMDlets via the PowerShell Virtual Chapter Wed, Jul 06 2016 12:00 Eastern Daylight Time If you cant make it a recording will be made available on YouTube on the VC Channel https://sqlps.io/video

Always Encrypted CMDlets

That leaves the Always Encrypted CMDLets and there are 17 of those!

Add-SqlColumnEncryptionKeyValue Adds a new encrypted value for an existing column encryption key object in the database.
Complete-SqlColumnMasterKeyRotation Completes the rotation of a column master key.
Get-SqlColumnEncryptionKey Returns all column encryption key objects defined in the database, or returns one column encryption key object with the specified name.
Get-SqlColumnMasterKey Returns the column master key objects defined in the database, or returns one column master key object with the specified name.
Invoke-SqlColumnMasterKeyRotation Initiates the rotation of a column master key.
New-SqlAzureKeyVaultColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in Azure Key Vault.
New-SqlCngColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store supporting the Cryptography Next Generation (CNG) API.
New-SqlColumnEncryptionKey Crates a new column encryption key object in the database.
New-SqlColumnEncryptionKeyEncryptedValue Produces an encrypted value of a column encryption key.
New-SqlColumnEncryptionSettings Creates a new SqlColumnEncryptionSettings object that encapsulates information about a single column’s encryption, including CEK and encryption type.
New-SqlColumnMasterKey Creates a new column master key object in the database.
New-SqlCspColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object describing an asymmetric key stored in a key store with a Cryptography Service Provider (CSP) supporting Cryptography API (CAPI).
Remove-SqlColumnEncryptionKey Removes the column encryption key object from the database.
Remove-SqlColumnEncryptionKeyValue Removes an encrypted value from an existing column encryption key object in the database.
Remove-SqlColumnMasterKey Removes the column master key object from the database.
Set-SqlColumnEncryption Encrypts, decrypts or re-encrypts specified columns in the database.
 

 

That seems to cover setting up Always Encrypted with Powershell , removing it and getting information about it. When the new SSMS update is dropped you will be able to start using all of this new functionality.

Just remember Import-Module sqlserver

CALL TO ACTION

Microsoft are engaging with the community to improve the tools we all use in our day to day work. There is are two Trello boards set up for YOU to use to contribute

https://sqlps.io/vote  for SQLPS  sqlserver PowerShell module

https://sqlps.io/ssms for SSMS

Go and join them and upvote YOUR preferred choice of the next lot of CMDlets

trellocount

 

We have also set up a SQL Community Slack for anyone in the community to discuss all things related to SQL including the Trello board items and already it seems a good place for people to get help with 150+ members in a few days. You can get an invite here https://sqlps.io/slack

Come and join us

Advertisements

5 thoughts on “PowerShell CMDLets added for SQL2016 Always Encrypted

  1. Pingback: Out With The SQLPS, In With The SqlServer | Art of the DBA

  2. Hi Bob, wondering if I can get some help from you.
    When calling Set-SqlColumnEncryption from an ISE window, the encryption works fine and the progress bar moves along. But when calling the same script from a PS window, the script is stuck at “Encrypting your data” and doesn’t make any progress nor actually encrypts the data. Can you shed some light on this problem? Thanks, Walter

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s