·
4 min read

SQL PowerShell: July 2016 update

This post was authored by Ayo Olubeko, Program Manager, Data Developer Group.

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, but firstly, there is a very important change we had to make to be able to ship monthly updates to the SQL PowerShell component.

Historically, SQL Server’s PowerShell components are included in both the SSMS (tools) installer as well as with the SQL Server engine install. In order to be able to ship SQL PowerShell update, we had to change the identity of the SQL PowerShell module as well as the wrapper known as SQLPS.exe. This change has an impact to scripts doing Import-Module.

This month we introduce CMDLETs for the following areas:

  • Always Encrypted
  • SQL Agent
  • SQL Error Logs

Additionally, we have made some nice improvements to Invoke-SqlCmd and the SQL provider.

New SQL PowerShell module

As alluded to above, in order to ship monthly updates, we have created a new SQL PowerShell module as well as have introduced a new wrapper EXE that SSMS uses to instantiate the SQL PowerShell environment. The SQL PowerShell module that ships with SSMS has changed from SQLPS to SqlServer (there is no change to the module used by SQL Agent). 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. Additionally, if you happen to have a script that is using a 3rd-party or community version of a module named SqlServer, you should add use of the Prefix parameter to avoid name collisions.

The motivation for these changes is that the tooling components are being moved to be “application local” and not share any components with the SQL Server engine. This is an important step to enable monthly tooling updates while not negatively impacting the components setup and updated by the SQL Server setup program.

SSMS has been updated to integrate with SQLTOOLSPS.exe rather than SQLPS.exe. Hence, if you launch PowerShell from within SSMS, it will launch PowerShell and configure the session with the new SQL PowerShell module. It is advised to avoid using these EXE wrappers; they exist for legacy reasons within SSMS and are likely to be removed in a future monthly update.

The new version of SQL Server PowerShell included with SSMS does not update the version of PowerShell used by SQL Server. This means that scripts executed by SQL Agent will not be able to use the new CMDLETs. Updates to SQLPS (the version used by SQL Agent) will be done through the traditional SQL Server update mechanisms; more specifically, major changes will be done as part of the next major version of SQL Server as it becomes available.

New CMDLETs

In the July SSMS update, you will find several new CMDLETs. Once again, we owe thanks to our SQL PowerShell community leaders for helping us prioritize these investments. The CMDLETs all provide help within PowerShell for detailed information. As with the majority of the features in SSMS, the SQL PowerShell CMDLETs work against all supported versions of SQL Server. In some cases, such as Always Encrypted, the CMDLETs obviously only work on versions of SQL Server that support that specific feature set.

CMDLET Description
Add-SqlAzureAuthenticationContext Performs authentication to Azure and acquires an authentication token.
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-SqlCertificateStoreColumnMasterKeySettings Creates a SqlColumnMasterKeySettings object referencing the specified certificate.
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.
 
Get-SqlAgent Returns a SQL Agent (JobServer) object that is present in the target instance of the SQL Server.
Get-SqlAgentJob Returns a SQL Agent Job object for each job that is present in the target instance of SQL Agent.
Get-SqlAgentJobHistory Returns the JobHistory present in the target instance of SQL Agent.
Get-SqlAgentJobSchedule Returns a JobSchedule object for each schedule that is present in the target instance of SQL Agent Job.
Get-SqlAgentJobStep Returns a SQL JobStep object for each step that is present in the target instance of SQL Agent Job.
Get-SqlAgentSchedule Returns a SQL JobSchedule object for each schedule that is present in the target instance of SQL Agent.
 
Get-SqlErrorLog Retrieves the SQL Server Logs.
Set-SqlErrorLog Sets or resets the maximum number of error log files before they are recycled.

Invoke-SqlCmd improvements

Invoke-SqlCmd now supports an OutputAs parameter (or its alias -As). This parameter allows you to specify DataRows, DataTables or DataSet as the object type to return. These types map to the .Net types you find in System.Data. DataRows is the default, and corresponds to the old behavior.

Additionally, we added the ConnectionString parameter which allows the script author complete control over the connection context. This unlocks new capabilities such as connecting to SQL Azure using Azure Active Directory authentication.

SQL PowerShell provider enhancements

The SQL PowerShell provider now properly supports the WhatIf and Confirm parameters. This allows you to see the potential impact of a script operation and have the ability to confirm an operation before it is executed.