Monday, 3 July 2017

Building Always On Availability Groups - Part 1


By David Postlethwaite

Ensuring that your databases can survive a major outage such as a server failure and can continue to work is an important task of being a DBA.
Database Mirroring was introduced in SQL 2005 and was quite straight forward to setup.
Mirroring only allowed for one secondary server and there was no way of grouping databases to failover together.

In SQL Server 2012 mirroring was replaced with Always On Availability Groups (AOAG). It can have multiple secondary servers and databases could be grouped to failover together along with other feature making it much more resilient and reliable.

SQL Server 2014 increased support for up to eight secondary replicas that are still available for read-only workloads even when the primary has gone down. It also gave support for In-Memory OLTP databases.

SQL 2016 made improvements by allowing failover not just on server failure but optionally also at the database level. So if the server is still running but a database has become corrupt, say because a disk has gone down, then previously nothing would have happened. Now all the databases in the Availability Group can failover to the secondary.

Windows Server 2012 introduced the use of multi-site clusters. That is, servers that are not in the same location and not on the same network subnet and do not require shared storage.  SQL Always On Availability Groups can be built in a multi-site or single-site failover cluster.

Always On Availability Groups require a Windows server Failover Cluster and Active Directory domain which means the DBA is reliant on the Windows server team to build the Windows part correctly.

 The aim of this document is to provide the steps for a basic two node AOAG setup so the DBA has some understanding of what the Windows server team need to do.

This document describes the building of an AOAG on a two node Windows cluster. I’ve used SQL 2012 and Windows 2012 R2 because I was building a failover group for SCOM 2012 R2 which only support SQL 2012. But the process will be the same for later versions.

SQL Server 2017 will allow us to create Always On Availability groups without the need for a cluster or without the need for the servers to be on a Windows domain. There will also be equivalent features for Linux. That is for another day.


You will require

  • Two Windows 2012 R2 (Standard or Datacentre) servers that will be joined into a cluster. These two servers will be known as cluster nodes
  • SQL Server 2012 or above Enterprise Edition installed on both servers (nodes)
  • A file Share on another locations  (SMB 2 or above)
  • For a multi-site cluster the servers must be on separate subnets
  • A fixed IP address for each server
  • A second fixed IP address reserved for each server. This is for the Cluster Listener. The Listener is the name that you will use to connect to the cluster.
In my next post I will look at configuring the Windows Server Failover Over Clusetr

Tuesday, 9 May 2017

Dynamic Data Masking

By David Postlethwaite


Dynamic data masking is a really useful new tool in SQL Server 2016. Keeping data safe is one of the most important tasks that a company has; this goes further than just making sure that users can only see their own data and hackers can’t get access. One case that will be familiar to many is where a copy of production data is required by developers for testing or for production support issues. In this case it is often important that the data is obfuscated so that real personal data is protected. With large data sets running an obfuscation script can take a long time and hold up support or testing. What would be really useful is a way of masking the data without having to run these long obfuscation scripts. Introducing Dynamic Data Masking.

Microsoft has introduced a solution to this in SQL Server 2016 and Azure SQL database called Dynamic Data Masking. With DDM, whenever a SELECT statement is run a mask is placed over the data that is returned so that the original values are obfuscated from those who shouldn’t be allowed to see it.

DDM is very easy to configure using T-SQL or via the Azure portal. You can control which users and groups will be subject to the mask. Once enabled SQL will mask the data regardless of how it is viewed so you won’t get staff using SSMS as a backdoor to the real data. 

The great advantage of DDM is that you can build it into your production database but not actually enable it. When you copy the database to Production Support or Testing then it is simply a case of enabling masking and the data is instantly obfuscated.

In SQL 2016 there are four masking functions available. There has been no mention of this being expanded in SQL 2017. 

  • Default:   Replaces all characters with ‘XXXX’ and numbers with 0
  • Email: Replaces the part before the @ with the first letter and XXXXand puts a ‘’ at the end
  • Random: Replaces numbers with random values
  • Partial:  Here you can define the number of characters to display at the beginning and end and the masking characters to use in the middle
In Azure, the portal also offers a mask called “Credit Card” which is a prefilled partial function of partial(0, "xxxx-xxxx-xxxx-", 4).

You can create a table with masking functions using T-SQL

CREATE TABLE [dbo].[Customers](
[CustomerID] [int] NOT NULL,
[FirstName] [dbo].[Name] NOT NULL,
[LastName] [dbo].[Name] MASKED WITH (FUNCTION='partial(2,"XXXX",2)') NOT NULL,
[EmailAddress] [nvarchar](50) MASKED WITH (FUNCTION = 'email()') NULL,
[PhoneNumber] [nvarchar](25) MASKED WITH (FUNCTION = 'default()') NULL,
[TerritoryID] [int] NULL

Or you can alter a table to add masking functions
ALTER TABLE [dbo].[Customers]
ALTER TABLE [dbo].[Customers]
ALTER TABLE [dbo].[Customers]

Let’s insert some data for our example

--insert some data
INSERT INTO [Customers] VALUES('19169','Arianna','Bryant','816-14-3866','77778052342171','','725-555-0131',1)
INSERT INTO [Customers] VALUES('17423','Arianna','Alexander','513-30-2965','77776580079386','','140-555-0167',1)
INSERT INTO [Customers] VALUES('17722','Jerome','Serrano','319-51-1661','55559881820491','','1 (11) 500 555-0136',8)
INSERT INTO [Customers] VALUES('22728','Arianna','Russell','239-35-4068','33338016910017','','179-555-0197',6)
INSERT INTO [Customers] VALUES('29187','Jerome','Ramos','918-75-6173','11115373226233','','1 (11) 500 555-0162',9)
INSERT INTO [Customers] VALUES('14902','Arianna','Diaz','162-76-6285','33331128313730','','142-555-0189',4)
INSERT INTO [Customers] VALUES('21817','Jerome','Carlson','833-14-5928','11113048024782','','1 (11) 500 555-0113',10)
INSERT INTO [Customers] VALUES('25991','Arianna','Hayes','302-26-4124','33336197015476','','582-555-0144',6)

A user with db_owner permissions can view the unmasked data but a user who isn’t a db_owner will see masked data so it is important that your database security has been properly designed. I know from experience that many developers simply give everyone db_owner permissions to ensure that all procedures and functions work correctly. This will need to be changed to use DDM effectively.

Let’s create a user for this demonstration.
-- create user and give data reader role
ALTER ROLE [db_datareader] ADD MEMBER [MaskUser]

If the user tries to view the data it will be returned with the mask applied.

--run select as Maskuser - see the masked data
SELECT [CustomerID]
  FROM [dbo].[Customers]


You can remove the mask from a user with the GRANT UNMASK command

-- Granting users or roles the UNMASK permission will enable them to see unmasked data:

The select statement will now show the real data.

You can re-enable the mask with the REVOKE UNMASK

-- Revoking users or roles the UNMASK permission will force users to see masked data:

Masking Data in SQL Server 2016 is that simple. 
However, there are some drawbacks. 
  • The underlying data is still intact so if someone was able to take a copy of the database they would have access to all the personal data.
  • DBAs with their higher privileges will almost certainly be able to see the unmasked data.
  • As already mentioned, database security must be configured correctly to ensure DDM works for all users.
  • The “WHERE” and “ORDER BY” clauses use the underlying data not the masked data so clever users who can run adhoc queries could use this weakness to work out what the masked data actually is. For instance, if there was a salary field they could use an order by clause to see who is the highest and lowest paid staff. 
--clever users can work out the underlying data by using where clause
SELECT [CustomerID]
  FROM [dbo].[Customers]
  WHERE LastName = 'Bryant'

In the above statement the user has guessed a last name and this has returned a row even though the data returned has a mask on it.

However, if you insert the masked data to write data to a new table hoping to see the unmasked data you will be out of luck. SQL copies the masked data to a new table; you will not see the underlying data.

--if you write data to a new table you still get the masked data
SELECT CustomerId,Lastname, EmailAddress, PhoneNumber INTO #temp_table
FROM [dbo].[Customers] -- Masked Email and Phone

SELECT CustomerId,Lastname, EmailAddress, PhoneNumber FROM #temp_table

Azure SQL Database supports Dynamic Data Masking. You can configure it from the portal. In fact the portal will suggest columns for masking.

Dynamic Data Masking looks like a useful tool in the fight to protect your production data but you will have to use it wisely to ensure that users can’t use the limitations to get around it and access the underlying data. 
I haven’t seen any improvements mentioned in SQL 2017 but I am sure it will be improved at some point.

Monday, 24 April 2017

Always Encrypted

By David Postlethwaite

Always Encrypted is new features in SQL Server 2016 and it is also available in Azure SQL Database. Here you can encrypt columns in a table with a master key and a certificate so that they will appear as encrypted strings to those who don’t have the required certificate installed on their pc.
Once the certificate is installed on the computer then the unencrypted data can then be seen as normal.

The data passes from database to your application as the encrypted value, only the application with the correct certificate can unencrypt the data so it is secure across the wire. This will go some way to resolving the concern of people worried about putting their sensitive data on a shared server in the cloud such as Microsoft Azure and accessing the data across the Internet.

At the time of writing Always Encrypted is only supported with ADO.NET 4.6, JDBC 6.0 and ODBC 13.1 but expect other driver to become available.

The calling application (including SSMS) must also have an extra parameter in the connection string: column encryption setting=enabled.

To edit the data you must use a parameterised query, you cannot update it using a “normal” update query. Additionally you can’t update encrypted tables using SSMS, it must be done using one of the supported drivers such as PowerShell or .NET application using ADO.NET 4.6.
Overall, Always Encrypted is seen as a major step forward in protecting your data and has received an enthusiastic response since it was launched.

Create a Table

The easiest way is to implement Always Encrypted is to right click on the table you wish to encrypt and select “Always Encrypted” although it can be done using T-SQL.
First let’s create a table with some data

DROP DATABASE IF EXISTS EncryptedDB -- new SQL 2016 command
-- create a new database

USE EncryptedDB

DROP table if exists [Customers] -- new SQL 2016 command

--create a table
CREATE TABLE [Customers](
       [CustomerID] [int] NOT NULL,
       [FirstName] [nvarchar](25) NOT NULL,
       [LastName] [nvarchar](25) NOT NULL,
       [SIN] [nvarchar](11) NULL,
       [CreditCardNumber] [nvarchar](25) NULL,
       [EmailAddress] [nvarchar](50) NULL,
       [PhoneNumber] [nvarchar](25) NULL,
       [TerritoryID] [int] NULL

--insert some data
insert into [Customers] VALUES('19169','Arianna','Bryant','816-14-3866','77778052342171', '','725-555-0131',1)
insert into [Customers] VALUES('17423','Arianna','Alexander','513-30-2965','77776580079386', '','140-555-0167',1)
insert into [Customers] VALUES('17722','Jerome','Serrano','319-51-1661','55559881820491', '','1 (11) 500 555-0136',8)
insert into [Customers] VALUES('22728','Arianna','Russell','239-35-4068','33338016910017', '','179-555-0197',6)
insert into [Customers] VALUES('29187','Jerome','Ramos','918-75-6173','11115373226233', '','1 (11) 500 555-0162',9)
insert into [Customers] VALUES('14902','Arianna','Diaz','162-76-6285','33331128313730', '','142-555-0189',4)
insert into [Customers] VALUES('21817','Jerome','Carlson','833-14-5928','11113048024782', '','1 (11) 500 555-0113',10)
insert into [Customers] VALUES('25991','Arianna','Hayes','302-26-4124','33336197015476', '','582-555-0144',6)

--view the data
SELECT [CustomerID]
  FROM [dbo].[Customers]

Encrypting the data using the Wizard

We will use the wizard to encrypt the data. 

There are two ways to start the wizard, either right click on the table and select “Encrypt Columns” or Right Click the database and select Tasks -> Encrypt

Click Next

Select the columns that you wish to encrypt. 

There are two types of encryption available; deterministic and randomised. Deterministic encryption always produces the same encrypted value for a particular text string when you run a query. This means that same string in a column will always return the same encrypted value, if you have a column with many repeated values it might be possible for a hacker to deduce what the real values are.

Randomised produces a different value each time the query is run. This makes randomised the more secure of the two options since it will always return a different encrypted value. But the drawback of randomised is that the columns can’t be used in equality or search operations, grouping, indexing, or be used for joining tables.

The master encryption key can be stored in your local Windows certificate store or in an Azure key vault. I haven’t tried the Azure key vault so in this example I will use the local Windows certificate store and place it in Current User’s store.

The encryption process can take a long time to run and it is not something that you would do on a live production system where you could corrupt records by encrypting them as they are being updated. This page allows you to create a PowerShell script that you can run when you have downtime.
In this case we will continue and run the encryption straight away.

If everything is correct select finish to start the encryption process.

Even with only eight records in this example it can take a few minutes to complete.

Viewing the Encrypted Data

Once the process has completed select the data and you will see that the encrypted columns now display encrypted values.

SELECT [CustomerID]
  FROM [dbo].[Customers]

If you try and insert or update the table you will get an error

insert into Customers VALUES (1,'David','Postlethwaite','12345','1111-1233-1231-1233','','426999',1)

insert into Customers VALUES (1,'David','Postlethwaite','12345','1111-1233-1231-1233','','426999',1)

Managing your Certificate

Your certificate is held in your certificate store. 
To view your certificate store type “mmc” into the Windows Run box on the Start menu.
In the management console window select File -> Add / Remove Snap-ins and select Certificate from the list and then select current user

Click OK and you will now see your certificates on your PC.

Drill down Personal and you will see the Always Encrypted Certificate. Export this and distribute it to all those who need to view the data. And keep it somewhere safe, without this certificate your data will not be readable, ever.

Viewing Unencrypted Data

In SSMS reconnect your query but add the parameter “column encryption setting=enabled” to your connection parameters. Run your select statement again and you will now see the data in clear text.

But if you try and insert or update data you will still get an error

insert into Customers VALUES (1,'David','Postlethwaite','12345','1111-1233-1231-1233','','426312',1)

Msg 206, Level 16, State 2, Line 93
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 = 'CEK_Auto1', column_encryption_key_database_name = 'EncryptedDB') collation_name = 'Latin1_General_CI_AS'As I said previously, you cannot update encrypted data using a standard query, even in SSMS.
Updating Data using Stored Procedure
To be able to update your table you must use a parameterised query. Below is an example 

As I said previously, you cannot update encrypted data using a standard query, even in SSMS.

Updating Data using Stored Procedure

To be able to update your table you must use a parameterised query. Below is an example 

  CREATE PROCEDURE dbo.AddCustomer
  @CustomerID int,
  @FirstName nvarchar(25),
  @LastName nvarchar(25),
  @SIN nvarchar(11),
  @CreditCardNumber nvarchar(25),
  @EmailAddress nvarchar(50),
  @PhoneNumber nvarchar(25),
  @TerritoryID int
 INSERT INTO [dbo].[Customers]

Running the procedure from SSMS will not work. SSMS does not use one of the supported connection types such as ADO to connect to the database.

DECLARE @CustomerID int,
@FirstName nvarchar(25),
@LastName nvarchar(25),
@SIN nvarchar(11),
@CreditCardNumber nvarchar(25),
@EmailAddress nvarchar(50),
@PhoneNumber nvarchar(25),
@TerritoryID int
SET @CustomerID = 1
SET @FirstName = 'David'
SET @LastName = 'Postlethwaite'
SET @SIN = '12345-3-ee-3'
SET @CreditCardNumber = '1111-1233-1231-1233'
SET @EmailAddress = ''
SET @PhoneNumber = '406555'
SET @TerritoryID = 1
exec dbo.AddCustomer @CustomerID,@FirstName,@LastName,@SIN,@CreditCardNumber,@EmailAddress,@PhoneNumber,@TerritoryID

Msg 33299, Level 16, State 6, Line 146
Encryption scheme mismatch for columns/variables '@SIN'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '0' expects it to be (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1', column_encryption_key_database_name = 'EncryptedDB') (or weaker). 

To be able to insert data you must connect using one of the supported connections currently ADO.NET 4.6, JDBC 6.0 and ODBC 13.1

Using PowerShell to update Data

Start PowerShell IDE . The following command will read the data from your encrypted table.

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=sqlserver1;Database=EncryptedDB;Integrated Security=SSPI; Column Encryption Setting=enabled;"

$Datatable = New-Object System.Data.DataTable
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $conn
$Command.CommandText = "Select * from customers"
$Reader = $Command.ExecuteReader()

The following will run the parametrised stored procedure we created earlier. You will notice that the two encrypted values require a full set of parameters values to work correctly, it is not enough just to pass the parameter and the new value like the non encrypted columns.

$SqlConn1 = New-Object System.Data.SqlClient.SqlConnection
$SqlConn1.ConnectionString = "Server=SQLServer1;Database=EncryptedDB;Integrated Security=SSPI; Column Encryption Setting=enabled;"

$SqlCmd1 = New-Object System.Data.SqlClient.SqlCommand
$sqlcmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd1.Connection = $SqlConn1
$SqlCmd1.CommandText = "dbo.AddCustomer"

$SqlCmd1.Parameters.Add("@CustomerID",[]::VarChar) | out-Null
$SqlCmd1.Parameters['@CustomerID'].Direction = []::Input
$SqlCmd1.Parameters['@CustomerID'].value = 2
$SqlCmd1.Parameters.AddWithValue("@FirstName", 'David'
$SqlCmd1.Parameters.AddWithValue("@LastName", 'Postlethwaite')

$SSN = New-Object -TypeName System.Data.SqlClient.SqlParameter
$SSN.ParameterName = "@SSN"
$SSN.SqlDbType = [System.Data.SqlDbType]::VarChar
$SSN.Direction = [System.Data.ParameterDirection]::Input
$SSN.Value = '12345-3-ee-3'

$CreditCardNumber = New-Object -TypeName System.Data.SqlClient.SqlParameter
$CreditCardNumber.ParameterName = "@CreditCardNumber"
$CreditCardNumber.SqlDbType = [System.Data.SqlDbType]::VarChar
$CreditCardNumber.Size = 25
$CreditCardNumber.Direction = [System.Data.ParameterDirection]::Input
$CreditCardNumber.Value = '1111-1233-1231-1233'

$SqlCmd1.Parameters.AddWithValue("@EmailAddress", '')
$SqlCmd1.Parameters.AddWithValue("@PhoneNumber", '0111 421233')
$SqlCmd1.Parameters.AddWithValue("@TerritoryID", 1)


Microsoft have implied that converting your application to use Always Encrypted is simply a case of encrypting your data and recompiling your .NET application to use ADO.NET 4.6 but as you will have seen from the examples above it may be a bit more complicated than that. All of your queries will need to be checked to ensure they are parameterised and even if they are the parameters being passed may need altering to add the extra options required.

Despite this SQL Server 2016 Always Encrypted is brilliant new addition to keeping your data secure either on premises or in the cloud and is well worth investigating.


If you try to view data using “column encryption setting=enabled” without a certificate installed you will get an error

Msg 0, Level 11, State 0, Line 2
Certificate with thumbprint '8C5AE6DCC176752931B33BFE03B7E4EA3A73572C' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store.
Parameter name: masterKeyPath

You can naturally create encrypted columns using t-sql as shown below. Note the binary collation.

CREATE TABLE [dbo].[Customers2](
[CreditCardNumber] [nvarchar](25)

The certificate can be placed in an Azure key vault rather than local certificate store, more information can be found here