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
GO
-- create a new database
CREATE DATABASE EncryptedDB
GO
USE EncryptedDB
GO
DROP table if exists [Customers] -- new SQL 2016 command
GO
--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
)
GO
--insert some data
insert into [Customers] VALUES('19169','Arianna','Bryant','816-14-3866','77778052342171', 'arianna16@adventure-works.com','725-555-0131',1)
insert into [Customers] VALUES('17423','Arianna','Alexander','513-30-2965','77776580079386', 'arianna17@adventure-works.com','140-555-0167',1)
insert into [Customers] VALUES('17722','Jerome','Serrano','319-51-1661','55559881820491', 'jerome15@adventure-works.com','1 (11) 500 555-0136',8)
insert into [Customers] VALUES('22728','Arianna','Russell','239-35-4068','33338016910017', 'arianna18@adventure-works.com','179-555-0197',6)
insert into [Customers] VALUES('29187','Jerome','Ramos','918-75-6173','11115373226233', 'jerome16@adventure-works.com','1 (11) 500 555-0162',9)
insert into [Customers] VALUES('14902','Arianna','Diaz','162-76-6285','33331128313730', 'arianna19@adventure-works.com','142-555-0189',4)
insert into [Customers] VALUES('21817','Jerome','Carlson','833-14-5928','11113048024782', 'jerome17@adventure-works.com','1 (11) 500 555-0113',10)
insert into [Customers] VALUES('25991','Arianna','Hayes','302-26-4124','33336197015476', 'arianna20@adventure-works.com','582-555-0144',6)
--view the data
SELECT [CustomerID]
,[FirstName]
,[LastName]
,[SIN]
,[CreditCardNumber]
,[EmailAddress]
,[PhoneNumber]
,[TerritoryID]
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]
,[FirstName]
,[LastName]
,[SIN]
,[CreditCardNumber]
,[EmailAddress]
,[PhoneNumber]
,[TerritoryID]
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','david@clunyweb.co.uk','426999',1)
insert into Customers VALUES (1,'David','Postlethwaite','12345','1111-1233-1231-1233','david@clunyweb.co.uk','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','david@clunyweb.co.uk','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
DROP PROCEDURE IF EXISTS dbo.AddCustomer
GO
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
AS
BEGIN
INSERT INTO [dbo].[Customers]
([CustomerID]
,[FirstName]
,[LastName]
,[SIN]
,[CreditCardNumber]
,[EmailAddress]
,[PhoneNumber]
,[TerritoryID])
VALUES
(@CustomerID,
@FirstName,
@LastName,
@SIN,
@CreditCardNumber,
@EmailAddress,
@PhoneNumber,
@TerritoryID)
END
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 = 'david@clunyweb.co.uk'
SET @PhoneNumber = '406555'
SET @TerritoryID = 1
execdbo.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-ObjectSystem.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=sqlserver1;Database=EncryptedDB;Integrated Security=SSPI; Column Encryption Setting=enabled;"
$conn.Open()
$Datatable = New-ObjectSystem.Data.DataTable
$Command= New-ObjectSystem.Data.SQLClient.SQLCommand
$Command.Connection = $conn
$Command.CommandText = "Select * from customers"
$Reader= $Command.ExecuteReader()
$Datatable.Load($Reader)
$Datatable
$conn.Close()
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-ObjectSystem.Data.SqlClient.SqlConnection
$SqlConn1.ConnectionString = "Server=SQLServer1;Database=EncryptedDB;Integrated Security=SSPI; Column Encryption Setting=enabled;"
$SqlConn1.Open()
$SqlCmd1= New-ObjectSystem.Data.SqlClient.SqlCommand
$sqlcmd.CommandType = [System.Data.CommandType]::StoredProcedure
$SqlCmd1.Connection = $SqlConn1
$SqlCmd1.CommandText = "dbo.AddCustomer"
$SqlCmd1.Parameters.Add("@CustomerID",[system.data.SqlDbType]::VarChar) |out-Null
$SqlCmd1.Parameters['@CustomerID'].Direction =[system.data.ParameterDirection]::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.Size=11
$SSN.Direction = [System.Data.ParameterDirection]::Input
$SSN.Value = '12345-3-ee-3'
$SqlCmd1.Parameters.Add($SSN);
$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.Add($CreditCardNumber);
$SqlCmd1.Parameters.AddWithValue("@EmailAddress", 'ddd@clunyweb.ca')
$SqlCmd1.Parameters.AddWithValue("@PhoneNumber", '0111 421233')
$SqlCmd1.Parameters.AddWithValue("@TerritoryID", 1)
$SqlCmd1.ExecuteNonQuery();
$SqlConn1.Close()
Conclusion
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.
Extras
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)
COLLATELatin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK_Auto1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL)
ON[PRIMARY]
The certificate can be placed in an Azure key vault rather than local certificate store, more information can be found here
https://thuru.net/2016/03/03/provisioning-always-encrypted-in-sql-databases-with-azure-key-vault-using-ssms-2016/
https://thuru.net/2016/03/03/provisioning-always-encrypted-in-sql-databases-with-azure-key-vault-using-ssms-2016/
