SQL 2016 CTP

Always Encrypted–PowerShell Scripts Part-2

Applying the encryption to the table column is easy with the PowerShell. Please make sure to follow the below steps before run the below scripts.

1. Connect to SQL Server from PowerShell

2. Create column master key & column encryption key   – You can create by using wizard or PowerShell Scripts

Wizard method

PowerShell Script

3. Choose the column which you want to apply the encryption.

4. Defined the encryption type

 

Run the below script just continue with this script

$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.SSN” -EncryptionType “Deterministic” -EncryptionKey “CEK1”

Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces

You can append multiple encryption by adding + symbol, like below

$ces = @()
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.SSN” -EncryptionType “Deterministic” -EncryptionKey “CEK1”
$ces += New-SqlColumnEncryptionSettings -ColumnName “dbo.Patients.BirthDate” -EncryptionType “Randomized” -EncryptionKey “CEK1”
Set-SqlColumnEncryption -InputObject $database -ColumnEncryptionSettings $ces

Always Encrypted–PowerShell Scripts

We have discussed that how to create a Always encrypted columns and steps to create custom column master key & column encryption key.

This article explains about the creation of keys with the help of PowerShell scripts.

Latest PowerShell Cmdelt’s update from Microsoft, https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

Pre-Requisites:

Since Always Encrypted feature introduced in SQL Server 2016, we need to upgrade our SQLPS.exe. If you use the older version of SQLPS then that will not recognize the some of the below cmdlets. So make sure to upgrade SQLPS.exe first.

You can upgrade by installing the latest version of SSMS (SQL Server management studio) 2016.

Check for the below modules installed in PowerShell,

#List directory wise installed modules

Get-Module –ListAvailable

Check for SQLASCMDLETS & SQLPS. If there is no modules then make sure you have SQLPS.exe file available in below location

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules

# Import the SQL Server Module.
Import-Module Sqlps -DisableNameChecking;

 

Steps to Create a Column Master Key & Column Encryption Key.

In the Below script, we need to specify following information,

• Server Name
• Database Name
• Column Master Key Name
• Column Encryption Key Name

First Connect to the Server and Database using PowerShell Script

# Connect to your database.
$serverName = “<server name>”
$databaseName = “<database name>”

$connStr = “Server = ” + $serverName + “; Database = ” + $databaseName + “; Integrated Security = True”
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

As we know, we have different key stores available. This below script using “Windows Certification Store – Current User”.

# Create a column master key in Windows Certificate Store.
$cert1 = New-SelfSignedCertificate -Subject “AlwaysEncryptedCert” -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

In the above script, you can check the “CertStoreLocation”. To identify the location, you can check in T-SQL Statement to create master key. I just highlighted in below script for understanding purpose.

USE [Sample_Encrypt]
CREATE COLUMN MASTER KEY [test1]
WITH
(
    KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
    KEY_PATH = N’CurrentUser/My/55A4DDAF58D274547B9C3BA5ABA154662CDF62D0′
)

GO

# Create a SqlColumnMasterKeySettings object for your column master key.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation “CurrentUser” -Thumbprint $cert1.Thumbprint

# Create column master key metadata in the database.
$cmkName = “<cmk name>”
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

# Generate a column encryption key, encrypt it with the column master key and create column encryption key metadata in the database.
$cekName = “<cek name>”
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName

Verify the keys in SQL Server Database Security folder.

Always Encrypted–Key Stores

As you know, we can create “Column Master Key” & “Column Encryption Key” to encrypt the column values. You can check here to learn about key creation. Usually these keys are associated with the certificate which includes the details like,

Issued To, Issued By, Expiration Date, Thumbprint & etc.

In Always encryption, we have four different Key store options are available which helps us to secure our keys. These keys are associated with the table columns. We can decrypt the values using these keys so we need to make sure to secure the keys.

Key stores,

1. Windows Certificate Store – Current User
2. Windows Certificate Store – Local Machine
3. Azure Key Vault
4. Key Storage Provider (CNG)

 

Widows Certificate Store – Current User

This type of certificate store is local to current user on the computer. This is located in the registry of the computer, specifically on HKEY_CURRENT_USER root

We need to compare the current user certificate store with local machine certificate store. Current user certificate always inherit the content of the local machine certificate.

We can generate the certificate by our self and that will be available for one year and we can use that to encrypt the column values.

T-SQL Statement to create a master key, we can select the certificate (Thumbprint) which I highlighted.

Windows Certificate Store – Local Machine

This will be available on the computer which is global to all users on the computer. This is located under HKEY_LOCAL_MACHINE registry.

This will list out all the available certificates in your computer and choose for encryption.

 

Azure Key Vault

This should be used to safeguard the keys in cloud (Azure). It requires a Azure subscription.

 

Key Storage Provider (CNG)

It provides different providers,

1. Microsoft Passport Key Storage Provider
2. Microsoft Smart Card Key Storage Provider
3. Microsoft Software Key Storage Provider

We can choose any of the above provider, for more information about the algorithm, key length & etc., check Microsoft article here.

Always Encrypted in SQL Server – Part 2

We can easily enable the encryption using wizard which you can check it here.

Disabling the encryption is also an easy task through wizard.

Steps to disable the encryption,

Step 1: Right Click on your database and choose Tasks->Encrypt Columns..

Step 2: Select next on the introduction page.

Step 3: When you are enter into column selection page, you can see the lock symbol on the columns which you enabled the encryption. Under encryption type, you can see the drop down values which includes “Plaintext”, “Deterministic” & “Randomized”. Choose the “Plaintext” and proceed next.

Step 4: Go through further steps and finish it.

Step 5: Now, run the select statement on table and you should see the result without an encryption.

 

We can define the “Encryption Key” & “Master key” Name manually.

Follow the steps to create custom names and to make use for encryption.

Step 1: Go to Security folder of your database.

Step 2: Expand the security folder and you should see the folder called “Always Encrypted Keys”. Expand the folder.

Step 3: You should see another two folders, Column Master Keys & Column Encryption Keys. Right click on Column Master Keys folder and choose “New Column Master Key..”

Step 4: Define the name of the master key and choose the key store where you want to store your key safely and click OK. Master will be created.

Step 5: Right click on Column Encryption Keys folder and choose “New Column Encryption Keys”

Step 6: Give the name and choose the master key from drop down. You should see the master key which you created in above step 4 and click OK.

Step 7: Now, go and create an encrypted column and you should see the option to choose your encryption key. Master key also will be applied automatically.

Always Encrypted SQL Server 2016

I have discussed “Always Encrypted” concept and covered steps to create Always encrypted columns through T-SQL Statement. Check column level encryption using Always Encrypted in SQL Server 2016

We can enable the encryption to the database table columns through wizard as well .

Before Always Encryption,

After Always Encryption,

Steps to encrypt the column,

Step 1: Right click your database where your tables are there to encrypt.

Step 2: Go to Tasks->Encrypt Columns… and choose.

Step 3: You will see the Introduction page, click Next

Step 4: Choose the columns from table and choose the encryption type. To know more about encryption type, visit here. The encryption key name will be created like “CEK_Auto”. It will keep extend like “CEK_Auto1”, …Auto2, etc. You can use the same encryption key for multiple columns.

Step 5: Once selected the required options, Next option will be enabled and click Next

Step 6: Master Key Configuration will be created for your database. It is an one time activity.

Step 7: Click Next and you can see two options,

1. Generate PowerShell Script to run later – Basically this enabling the encryption should be done while doing database maintenance.
2. Proceed to finish now

Step 8: Check the configuration in summary page and click next to proceed. The scripts will start run in the background and will encrypt the selected columns.

Step 9: Run the table and check the column values.

R Plots in SQL Server 2016

As we know, Microsoft related the latest version of SQL Server 2016 RC3, download it here. From RC2, we no need to install R components separately. Actually, R database and standalone server both are installed as part of SQL Server 2016 installation itself.

It makes the R integration with SQL Server 2016 is easy.

As part of the integration, we just need to enable the external scripts and start using the R scripts inside SQL Server 2016.

Creating a plot in R console is easy, just write a code for plot and the plot will be displayed in the new window. In SQL Server 2016, we need to store the R script result in VARBINARY and then need to convert to an image file in reporting or using BCP command line tool.

I have provided the steps to get the image (plot) from R scripts in SQL Server 2016.

Solution 1:

Step 1: Create a sample database and table. Insert some values into the table.

Step 2: Do the modification on the table name and column name and run it.

EXEC sp_execute_external_script

@language = N’R’

,@script = N’ df <- inputDataSet;

image_file = tempfile()

jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device

hist(df$Values);

dev.off();

OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

‘

,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name

,@input_data_1_name = N’inputDataSet’

,@output_data_1_name = N’OutputDataset’

WITH RESULT SETS ((plot varbinary(max)));

Step 3: Make sure that you are getting an varbinary result as a output.

Step 4: Wrap the above code within stored procedure, like below and check the result again.

CREATE PROCEDURE Sp_Test1

As

EXEC sp_execute_external_script

@language = N’R’

,@script = N’ df <- inputDataSet;

image_file = tempfile()

jpeg(filename = image_file, width=500, height=500); #create a JPEG graphic device

hist(df$Values); #column name

dev.off();

OutputDataset <- data.frame(data=readBin(file(image_file,”rb”),what=raw(),n=1e6));

‘

,@input_data_1 = N’SELECT * FROM MyValues;’ –Provide your table name

,@input_data_1_name = N’inputDataSet’

,@output_data_1_name = N’OutputDataset’

WITH RESULT SETS ((plot varbinary(max)));

Step 5: Use below bcp command and convert the Varbinay into jpeg format.

bcp “exec Sp_Test1” queryout “Test.jpg” -S <server name> -d <database name>  -U <user name> -P <password>

Step 6: Verify the plot in a above image.

 

Solution 2:

Step 1: You can create a table and insert this varbinary result and can use in SSRS report with image option.

Step 2: Verify the plot.

 

Solution 3:

We can export the result as an image file without using bcp tool itself.

Step 1: Do the following changes in the script. Replace the tempfile() with the exact path where you want to store the plot image.

Step 2: Run the script and check the above specified location. Image file will be available with plot.

SQL Server 2016 RC2–Enhancements in R Integration

Microsoft released the latest version of SQL Server 2016 RC2 which has many enhancements. This article exploring the enhancements related R integration in SQL Server.

Before RC2, we need to install SQL Server 2016 prior versions and then R related components separately. This scenario has been changed, We can install R services or standalone R server through SQL Server 2016 RC2 installation itself.

Look at the below changes in the installation center.

New feature to create a standalone R Server.

 

Confirmation to install Microsoft R Open.

Stretch in SQL Server RC0

SQL Server Release Candidate 0 version has been released. We have been discussed about stretch database in CTP 3.3. There are many improvements in RC0. This article is about, how to enable the stretch in RC0.

Go through Stretch in SQL Server 2016 CTP 3.3.

Steps to enable the Stretch in RC0 Version,

Step 1:  Right click on your database and go to Tasks->Stretch->Enable

Step 2: Choose the tables you want to stretch.

Step 3: Provide your Azure subscription to configure SQL Server and database for stretch.

Step 4: Set up master key for your database.

Step 5: Provide your IP, in case if you have configured.

Step 6: Configuration process started to enable stretch for database and table.

As you can see, there are some difference in steps to configure when compare with CTP 3.3 version. There is no changes on Monitoring the stretch.

Enable Stretch for Tables

Previously, we need to select Reconfigure option to add new tables to stretch but that “Reconfigure” option has been removed. Now, we have “Enable”, “Disable” & “Monitor”.

To add new tables to stretch, follow the below steps.

Choose the new table to add into stretch. Right click –> Stretch –>Enable

Disable Stretch

To Disable the stretch for the database, we need to disable the stretch for tables first. We don’t have option to disable in one short if multiple tables are in stretch. Need to manually disable all the tables from stretch.

If we try to disable stretch for database without disabling tables then below message will be appeared.

There are two options in disable on table,

1. Bring data back from Azure

2. Leave data in Azure.

Once stretch is enabled for a table, data migration will be initiated from local to azure through batch process. If we decided to disable the stretch then make sure whether we want to bring data back to local or keep them on azure it self. Based on  that choose any of the above option.

Note: The table in Azure database will not be deleted by process, you need to delete it by going to azure management portal.

If you are selected “Bring data back from Azure” then below message will be prompted.

Once disable the stretch for all your tables, then disable the stretch on your database.