SQL 2016 CTP

ByHariharan Rajendran

Limitations in Stretch DB in SQL Server 2016 CTP 3

As per Microsoft article, we have many limitations that we need to understand before start enabling the stretch database in your environment.

I have discussed few limitations here and also what will happen when we override those limitations.

Limitations

1. We cannot perform UPDATE and DELETE operations on stretch enabled tables

image

2. We cannot create a index for a view that includes the stretch tables. “New Index” option is disabled.

image

3. We cannot perform update or delete a view that includes stretch enabled tables however insert is possible.

image

ByHariharan Rajendran

SQL Server Management Studio Firewall Settings

We need to follow the below steps to connect the azure based SQL Server in SSMS

1. Add the client IP address in azure firewall

2. Use the azure SQL Server name with proper credentials.

The above steps should be carried out until SQL Server 2016 to access the SQL databases in SSMS.

If IP is not configured then SSMS will not allow to connect, it will through the below error message.

image

 

SQL Server 2016 CTP 3.3 has built in feature to add the IP in azure SQL Server firewall from SSMS itself.

Follow the below steps,

1. There is no IP configured in Azure SQL Server Firewall

image

2. Enter the server name and credentials and click connect. It will open a Azure account sign in window

image

 

image

3. Server will be connected and can access the databases. Client IP address added into firewall.

image

ByHariharan Rajendran

SQL Server Service Accounts in 2016

SQL Server 2016 has two new service accounts to enable PolyBase feature. There is a configuration page added to setup PolyBase.

Service Accounts:

image

PolyBase Config

image

ByHariharan Rajendran

Test Drive on Always Encrypted in SQL Server 2016

To test the always encrypted feature in SQL Server 2016, Follow the below test steps,

  1. Create a table with encrypted column, refer here to set up.
  2. Insert a data into the table
    1. Use client application to insert a data, refer here.
    2. Export and import from another table, refer here.
  3. Check the table result

Tables used in my test,

  1. Employees – Always Encrypted on TaxID & Salary fields
  2. Employees_NoEncrypt

Encryption

 

ByHariharan Rajendran

Insert a data into Encrypted Table (Always Encrypted)

Following my previous post, you can insert a data into an encrypted table from another table using Export and Import option.

Create a table with same structure without encrypt settings- Compare the table (Employees) structure of encrypted table here

CREATE TABLE [dbo].[Employees_NoEncrypt]

( [UserID] int IDENTITY(1,1),

[TaxID] varchar(11) NOT NULL,

[FirstName] nvarchar(50)NOT NULL,

[LastName] nvarchar(50) NULL,

[Designation] nvarchar(50) NULL,

[Email] nvarchar(50) NOT NULL,

[Phone Number] nvarchar(10) NOT NULL,

[Salary] decimal(10,2)  NOT NULL

 PRIMARY KEY CLUSTERED ([UserID] ASC) ON [PRIMARY] )

Insert a data into Employees_NoEncrypt table using “Insert” statement or “Edit Top 200 Rows”. Once done then go to next step.

Use Import and Export Data wizard – This will be installed when you install SQL Server.

In my case, both the tables are residing in same database and server.

Import1

import2

import3

import4

import5

import6

import7

import8

 

ByHariharan Rajendran

Column Level Encryption using Always Encrypted in SQL Server 2016

Always encrypted feature in SQL Server 2016 is the best option who wants to encrypt the certain column values in a table.

Steps to implement the always encrypted option,

  1. Set up column encryption key with the help of column master key – Go through column master and encryption article here to set up.
  2. Create a table with column level encryption setting
    1. Column encryption key
    2. Algorithm – Currently only one algorithm is supported which is AEAD_AES_256_CBC_HMAC_SHA_256
    3. Type of encryption – DETERMINISTIC or RANDOMIZED. Visit here to know detailed information

Syntax to apply encryption on a column,

CREATE TABLE <tablename>

 (<column name<data type>

 ENCRYPTED WITH (ENCRYPTION_TYPE = <Type of Encryption>,

 ALGORITHM AEAD_AES_256_CBC_HMAC_SHA_256COLUMN_ENCRYPTION_KEY =<column encryption key name>)

 )

In this below table, I want to encrypt employee Tax ID and Salary fields,

CREATE TABLE [dbo].[Employees]

( [UserID] int IDENTITY(1,1),

[TaxID] varchar(11)

COLLATE Latin1_General_BIN2 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC,

ALGORITHM ‘AEAD_AES_256_CBC_HMAC_SHA_256’, COLUMN_ENCRYPTION_KEY = MyColumnKeyNOT NULL,

[FirstName] nvarchar(50) NOT NULL,

[LastName] nvarchar(50NULL,

[Designation] nvarchar(50NULL,

[Email] nvarchar(50NOT NULL,

[Phone Number] nvarchar(10NOT NULL,

[Salary] decimal(10,2

ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZEDALGORITHM ‘AEAD_AES_256_CBC_HMAC_SHA_256’,

COLUMN_ENCRYPTION_KEY = MyColumnKeyNOT NULL

PRIMARY KEY CLUSTERED ([UserID] ASCON [PRIMARY] )

 

By using the above script, we can create a table with encrypted columns.

If you try to insert data into the table using insert statement then you will get an error message like below,

Msg 206, Level 16, State 2, Line 16

Operand type clash: varchar is incompatible with varchar(8000) encrypted with (encryption_type = ‘DETERMINISTIC’, encryption_algorithm_name = ‘AEAD_AES_256_CBC_HMAC_SHA_256’, column_encryption_key_name = ‘MyColumnKey’, column_encryption_key_database_name = ‘Databrain’) collation_name = ‘SQL_Latin1_General_CP1_CI_AS’

It will not allow you to insert a data directly because of applied encryption. This can be done through a client application. Refer Microsoft article here for the detailed explanation.

 

ByHariharan Rajendran

[Step by Step] Column Master & Encryption Keys in SQL Server 2016

Following my previous article, this article will help you to understand how to create encryption keys which are the prerequisites for encrypting the table columns.

Steps to create Column Master Key

  1. Open SQL Server Management Studio
  2. Choose the database where you want to apply encryption for tables
  3. Expand the (+) sign
  4. Go to Security > Always Encrypted Keys > Column Master Key Definition
  5. Right click Column Master Key Definition folder and choose New Column Master Key Definition
  6. Provide the name
  7. Select the Key Definition Source from drop down (if no value then refresh)
  8. Select your machine
  9. Click Ok.

Screenshots for your reference,

EncryptionKey1    EncryptionKey3

 

Steps to create Column Encryption Key

  1. Open SQL Server Management Studio
  2. Choose the database where you want to apply encryption for tables
  3. Expand the (+) sign
  4. Go to Security > Always Encrypted Keys > Column Encryption Keys
  5. Right click Column Encryption Keys folder and choose New Column Encryption Key…
  6. Provide the name
  7. Choose the Column Master Key Definition
  8. Click Ok.

Screenshots for your reference,

EncryptionKey2

ByHariharan Rajendran

Always Encrypted in SQL Server 2016

SQL Server 2016 introduces a new feature called “Always Encrypted”. It allows to encrypt the data in storage and also in motion (when reading a data). This helps us to secure the data.

There are several concepts involved in Always Encrypted,

  1. Column Master Key
    1. This is an encryption key that protects the column encryption keys.
    2. At least one master key should be available before encrypting any columns.
  2. Column Encryption Key
    1. This is the encryption key that actually protects the encrypted columns.
    2. This will make use of column master key.
  3. Column-Level Encryption setting
    1. Column must be set to encrypted using
      1. Column encryption key
      2. Algorithm
      3. Type of encryption

Encryption Types.

  1. Deterministic
    1. It is always encrypted to the same cyphertext (The result of encryption performed on plaintext using an algorithm).
    2. It can be used for operations like lookup (join), distinct, group by.
    3. It can be indexed.
  2. Randomized
    1. It is more secure.
    2. It cannot be used for operations like deterministic.
    3. Write and Read only possible
    4. It cannot be indexed.