If you need to store confidential data in your database, you can use data encryption. SQL Server supports encryption with symmetric keys, asymmetric keys, certificates, and password phrases. Let's first have a theoretical look at each of these encryption techniques.
When you use symmetric key encryption, the party that encrypts the data shares the same key with the party that decrypts the data. Because the same key is used for encryption and decryption, this is called symmetric key encryption. This encryption is very fast. However, if an unauthorized party somehow acquires the key, that party can decrypt the data. Protecting symmetric keys is a challenge. The symmetric key must remain secret. Symmetric encryption is also called secret key encryption.
In asymmetric key encryption, you use two different keys that are mathematically linked. You must keep one key secret and prevent unauthorized access to it; this is the private key. You make the other key public to anyone; this is the public key. If you encrypt the data with the public key, you can decrypt the data with the private key; if you encrypt the data with the private key, you can decrypt it with the public key. Asymmetric encryption is very strong; however, it is much slower than symmetric encryption. Asymmetric encryption is useful for digital signatures. A developer applies a hash algorithm to the code to create a message digest, which is a compact and unique representation of data. Then the developer encrypts the digest with the private key. Anybody with a public key from the same pair can decrypt the digest and use the same hash algorithm to calculate the digest from the code again. If the re-calculated and decrypted digests match, you can identify who created the code.
A certificate is a digitally signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. It identifies the owner of the public/private keys. You can use certificates for authentication. A certificate can be issued by a trusted authority or by SQL Server. You can create a certificate from a file (if the certificate was issued by a trusted authority) or a digitally signed executable file (assembly), or you can create a self-signed certificate in SQL Server directly. You can use certificates to encrypt the data; of course, this way you are actually using asymmetric encryption.
You should use symmetric keys to encrypt the data because secret key encryption is much faster than public-key encryption. You can then use asymmetric encryption to protect symmetric keys and use certificates for authentication. You combine certificates and keys to encrypt data in the following manner:
When encrypting data, you should consider all possible surface areas for an attack. For example, if you encrypt the data in SQL Server, but send clear text over the network, an attacker could use a network monitor to intercept the clear text. You should use on-the-wire encryption, such as Internet Protocol Security (IPSec), a framework of open source standards for network security, or Secure Sockets Layer / Transport Layer Security (SSL/TLS), which are protocols based on public key cryptography. An attacker can even sniff client computer memory to retrieve clear text. Therefore, you should use .NET encryption in client applications in addition to or instead of server encryption.
Consider the following trade-offs when you design a solution that uses data encryption:
Instead of storing all keys in SQL Server, you can also use an external cryptographic provider to store the asymmetric keys used to encrypt and decrypt the symmetric keys stored in SQL Server, or to store both asymmetric and symmetric keys outside SQL Server. This is called Extensible Key Management (EKM). For example, you can use the Azure Key Vault service as the external cryptographic provider.
As already mentioned, you can protect symmetric keys with asymmetric keys or certificates. In addition, you can protect them with passwords or even other symmetric keys. You can protect certificates and asymmetric keys stored in SQL Server with the Database Master Key (DMK) and passwords. You protect the DMK when you create it with the Service Master Key (SMK) and password. The SMK is created by SQL Server Setup, and is protected by the Windows system Data Protection Application Programming Interface (DPAPI). This whole encryption hierarchy looks quite complex. The following figure shows the encryption hierarchy in a condensed way. It shows all components and a couple of possible paths to the encrypted data.
Encryption hierarchy
SQL Server supports many encryption algorithms. You can choose from DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES. However, from SQL Server 2016 onwards, you should use only the AES_128, AES_192, and AES_256 algorithms; all other algorithms have been deprecated.
You don't need to encrypt all of the data all of the time. SQL Server provides many encryption options, and you should choose the one that suits you the best. In addition, you should also consider encrypting and decrypting the data in the client application. In this section, you will learn about the different encryption options in SQL Server, including the strengths and weaknesses of each option. These options include:
SQL Server encryption options start with backup encryption. This encryption was introduced in version 2014. You can encrypt the data while creating a backup. You need to specify an encryptor, which can be either a certificate or an asymmetric key, and define which algorithm to use for the encryption. The supported algorithms are AES_128, AES_192, AES_256, and Triple DES. Of course, you also need to back up the encryptor, and store it in a different, probably even off-site, location from the backup files. Without the encryptor, you can't restore an encrypted backup. You can also use EKM providers for storing your encryptor safely outside SQL Server. Actually, if you are using an asymmetric key as an encryptor instead of a certificate, then this key must reside in an EKM provider.
The restore process of an encrypted backup is completely transparent. You don't need to specify any particular encryption options. However, the encryptor must be available on the instance of SQL Server you are restoring to. In addition to the regular restore permissions, you also need to have at least the VIEW DEFINITION
permission on the encryptor.
In the following code, showing the start of the backup encryption process, first a master database DMK is created. This key is used to protect a self-issued certificate, also created in the master database.
USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; CREATE CERTIFICATE DemoBackupEncryptCert WITH SUBJECT = 'SQLDevGuideDemoDb Backup Certificate'; GO
The master DMK is encrypted using the SMK created during the setup. You can check both keys with the following query:
SELECT name, key_length, algorithm_desc FROM sys.symmetric_keys;
The query returns the following result set:
Name key_length algorithm_desc ------------------------ ----------- -------------- ##MS_DatabaseMasterKey## 256 AES_256 ##MS_ServiceMasterKey## 256 AES_256
For a test, the following code creates an unencrypted backup in the C:SQL2016DevGuide folder
, which should be created in advance:
BACKUP DATABASE SQLDevGuideDemoDb TO DISK = N'C:SQL2016DevGuideSQLDevGuideDemoDb_Backup.bak' WITH INIT;
Next, you can create an encrypted backup:
BACKUP DATABASE SQLDevGuideDemoDb TO DISK = N'C:SQL2016DevGuideSQLDevGuideDemoDb_BackupEncrypted.bak' WITH INIT, ENCRYPTION ( ALGORITHM = AES_256, SERVER CERTIFICATE = DemoBackupEncryptCert );
Note that this time you get a warning telling you that the certificate used for encrypting the database encryption key has not been backed up. Therefore, you should back up the certificate used for the backup encryption, and, in addition, the master DMK used to protect the certificate and the SQL Server SMK used to protect the master DMK, as the following code shows:
-- Backup SMK BACKUP SERVICE MASTER KEY TO FILE = N'C:SQL2016DevGuideSMK.key' ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; -- Backup master DMK BACKUP MASTER KEY TO FILE = N'C:SQL2016DevGuidemasterDMK.key' ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; -- Backup certificate BACKUP CERTIFICATE DemoBackupEncryptCert TO FILE = N'C:SQL2016DevGuideDemoBackupEncryptCert.cer' WITH PRIVATE KEY ( FILE = N'C:SQL2016DevGuideDemoBackupEncryptCert.key', ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ); GO
Now you are ready to simulate a failure. Drop the demo database, the certificate used for the encryption, and the master DMK:
DROP DATABASE SQLDevGuideDemoDb; DROP CERTIFICATE DemoBackupEncryptCert; DROP MASTER KEY;
Try to restore the encrypted backup. You should get error 33111, telling you that SQL Server cannot find server certificate.
RESTORE DATABASE SQLDevGuideDemoDb FROM DISK = N'C:SQL2016DevGuideSQLDevGuideDemoDb_BackupEncrypted.bak' WITH FILE = 1;
You have to start the restore process by restoring the master DMK:
RESTORE MASTER KEY FROM FILE = N'C:SQL2016DevGuidemasterDMK.key' DECRYPTION BY PASSWORD = 'Pa$$w0rd' ENCRYPTION BY PASSWORD = 'Pa$$w0rd';
Next, you open the master DMK and restore the certificate:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pa$$w0rd'; CREATE CERTIFICATE DemoBackupEncryptCert FROM FILE = N'C:SQL2016DevGuideDemoBackupEncryptCert.cer' WITH PRIVATE KEY (FILE = N'C:SQL2016DevGuideDemoBackupEncryptCert.key', DECRYPTION BY PASSWORD = 'Pa$$w0rd');
Now you are ready to restore the encrypted backup. The following code should restore the demo database successfully:
RESTORE DATABASE SQLDevGuideDemoDb FROM DISK = N'C:SQL2016DevGuideSQLDevGuideDemoDb_BackupEncrypted.bak' WITH FILE = 1, RECOVERY;
Finally, you can check which backups are encrypted by querying the msdb.dbo.backupset
table:
SELECT b.database_name, c.name, b.encryptor_type, b.encryptor_thumbprint FROM sys.certificates AS c INNER JOIN msdb.dbo.backupset AS b ON c.thumbprint = b.encryptor_thumbprint;
Backup encryption encrypts backups only. It does not encrypt the data in the data files. You can encrypt data in tables with T-SQL using column-level encryption. Column-level encryption is present in SQL Server from version 2008 onwards. You encrypt the data in a specific column by using a symmetric key. You protect the symmetric key with an asymmetric key or a certificate. The keys and the certificate are stored inside your database where the tables with the encrypted columns are. You protect the asymmetric key or the certificate with the database master key. The following code, which created the DMK in the demo database, issues an SQL Server certificate and then creates the symmetric key used for the column encryption:
USE SQLDevGuideDemoDb; -- Create the SQLDevGuideDemoDb database DMK CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; -- Create the column certificate in SQLDevGuideDemoDb CREATE CERTIFICATE DemoColumnEncryptCert WITH SUBJECT = 'SQLDevGuideDemoDb Column Certificate'; -- Create the symmetric key CREATE SYMMETRIC KEY DemoColumnEncryptSimKey WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE DemoColumnEncryptCert; GO
Next, you can prepare an additional column to store the encrypted data. The dbo.Table1
should already exist from the demo code earlier in this chapter.
ALTER TABLE dbo.Table1 ADD tableContainer_Encrypted VARBINARY(128); GO
Now you are ready to encrypt the data in the new column. You need to open the symmetric key and decrypt it with the certificate used for the encryption. The following code opens the symmetric key and then updates the new column in the table with the values from an unencrypted column. The code uses the ENCRYPTBYKEY()
T-SQL
function to encrypt the data with a symmetric key:
OPEN SYMMETRIC KEY DemoColumnEncryptSimKey DECRYPTION BY CERTIFICATE DemoColumnEncryptCert; UPDATE dbo.Table1 SET tableContainer_Encrypted = ENCRYPTBYKEY(Key_GUID('DemoColumnEncryptSimKey'), tableContainer); GO
You can check the data with the following query, which uses the DECRYPTBYKEY()
T-SQL
function for the decryption:
OPEN SYMMETRIC KEY DemoColumnEncryptSimKey DECRYPTION BY CERTIFICATE DemoColumnEncryptCert; -- All columns SELECT id, tableContainer, tableContainer_Encrypted, CAST(DECRYPTBYKEY(tableContainer_Encrypted) AS CHAR(5)) AS tableContainer_Decrypted FROM dbo.Table1; GO
Here are the results, with the encrypted value abbreviated for simpler reading:
Id tableContainer tableContainer_Encrypted tableContainer_Decrypted ----- -------------- ------------------------ ------------------------ 1 dbo 0x003D10428AE86248A44F70 dbo 2 dbo 0x003D10428AE86248A44F70 dbo
You can use the following code to clean up your SQL Server instance. The code also deletes the backups in the demo folder. You need to run SSMS as administrator and turn on the SQLCMD
mode in SSMS to successfully execute the clean-up code (go to Query menu and select the SQLCMD
mode option).
USE master; !!del C:SQL2016DevGuideDemoBackupEncryptCert.cer !!del C:SQL2016DevGuideDemoBackupEncryptCert.key !!del C:SQL2016DevGuidemasterDMK.key !!del C:SQL2016DevGuideSMK.key !!del C:SQL2016DevGuideSQLDevGuideDemoDb_Backup.bak !!del C:SQL2016DevGuideSQLDevGuideDemoDb_BackupEncrypted.bak GO IF DB_ID(N'SQLDevGuideDemoDb') IS NOT NULL DROP DATABASE SQLDevGuideDemoDb; DROP LOGIN LoginA; DROP LOGIN [BuiltinPower Users]; DROP LOGIN LoginB; DROP CERTIFICATE DemoBackupEncryptCert; DROP MASTER KEY; GO
Column-level encryption protects the data in the database, not just backups. However, it protects data at rest only. When the data is used by an application, the data is decrypted. If you don't use network encryption, the data travels over the network in an unencrypted way. All the keys are in a SQL Server database, and therefore a DBA can always decrypt the data. End users who don't have access to the certificates and keys can't decrypt the encrypted data. In addition, the implementation of the column-level encryption might be quite complex because you might need to modify a lot of T-SQL code. The column-level encryption is available in all editions of SQL Server.
Another option to protect data at rest is Transparent Data Encryption (TDE). You can use the TDE for the real-time encryption and decryption of the data and log files. You encrypt the data with the database encryption key (DEK), which is a symmetric key. It is stored in the database boot record and is therefore already available during the database recovery process. You protect the DEK with a certificate in the master database. You can also use an asymmetric key instead of the certificate; however, the asymmetric key must be stored in an EKM module. TDE uses the AES and Triple DES encryptions only. TDE was first implemented in SQL Server with version 2012.
You can use TDE on user databases only. You cannot export the database encryption key. This key is used by the SQL Server database engine only. End users never use it. Even if you change the database owner, you don't need to regenerate the DEK.
TDE encrypts data on a page level. In addition, it also encrypts the transaction log. You should backup the certificate used to protect the DEK and the private key used to protect the certificate immediately after you enable TDE. If you need to restore or attach the encrypted database to another SQL Server instance, you need to restore both the certificate and the private key, or you are not able to open the database. Note again that you don't export the DEK as it is a part of the database itself. You need to keep and maintain the certificate used to protect the DEK even after you disable the TDE on the database. This is because parts of the transaction log might still be encrypted. The certificate is needed until you perform a full database backup.
The following code starts the process of enabling the TDE by creating a DMK in the master database:
USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO
You can check whether the master DMK was created successfully with the following code:
SELECT name, key_length, algorithm_desc FROM sys.symmetric_keys;
Let's backup the SMK and the master DMK immediately, as the next part of the code shows:
BACKUP SERVICE MASTER KEY TO FILE = N'C:SQL2016DevGuideSMK.key' ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; -- Backup master DMK BACKUP MASTER KEY TO FILE = N'C:SQL2016DevGuidemasterDMK.key' ENCRYPTION BY PASSWORD = 'Pa$$w0rd'; GO
The next portion of the code creates a demo database:
IF DB_ID(N'TDEDemo') IS NULL CREATE DATABASE TDEDemo; GO
While still in the context of the master database, use the following code to create the certificate you will use to protect the DEK:
CREATE CERTIFICATE DemoTDEEncryptCert WITH SUBJECT = 'TDEDemo TDE Certificate'; GO
Of course, you need to backup this certificate immediately:
BACKUP CERTIFICATE DemoTDEEncryptCert TO FILE = N'C:SQL2016DevGuideDemoTDEEncryptCert.cer' WITH PRIVATE KEY ( FILE = N'C:SQL2016DevGuideDemoTDEEncryptCert.key', ENCRYPTION BY PASSWORD = 'Pa$$w0rd' ); GO
You create the database encryption key in the demo user database:
USE TDEDemo; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE DemoTDEEncryptCert; GO
The final step of this process is to actually turn the TDE
on:
ALTER DATABASE TDEDemo SET ENCRYPTION ON; GO
You can check which databases are encrypted by querying the sys. dm_database_encryption_keys
dynamic management view. This view exposes the information about the encryption keys and the state of encryption of a database.
SELECT DB_NAME(database_id) AS DatabaseName, key_algorithm AS [Algorithm], key_length AS KeyLength, encryption_state AS EncryptionState, CASE encryption_state WHEN 0 THEN 'No database encryption key present, no encryption' WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' END AS EncryptionStateDesc, percent_complete AS PercentComplete FROM sys.dm_database_encryption_keys;
The results of this query are as follows.
DatabaseName Algorithm KeyLength EncryptionState EncryptionStateDesc PercentComplete ------------ --------- --------- --------------- ------------------- ------------ Tempdb AES 256 3 Encrypted 0 TDEDemo AES 128 3 Encrypted 0
Note that the Tempdb
system database also inherited the encryption. The demo database is empty and thus very small. The encryption process on such a small database is very fast. However, in a production database, you would be able to monitor the percentage complete rising from zero to one hundred, while the encryption state would be "Encryption in progress." SQL Server needs to scan all of the data files and log files to finish the encryption.
Now let's turn the encryption off for the demo database:
ALTER DATABASE TDEDemo SET ENCRYPTION OFF; GO
Using the same query, you can check the encryption status again:
SELECT DB_NAME(database_id) AS DatabaseName, key_algorithm AS [Algorithm], key_length AS KeyLength, encryption_state AS EncryptionState, CASE encryption_state WHEN 0 THEN 'No database encryption key present, no encryption' WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' END AS EncryptionStateDesc, percent_complete AS PercentComplete FROM sys.dm_database_encryption_keys;
Please note the result. The tempdb
system database is still encrypted.
DatabaseName Algorithm KeyLength EncryptionState EncryptionStateDesc PercentComplete ---------- --------- --------- --------------- ------------------- -------------- Tempdb AES 256 3 Encrypted 0 TDEDemo AES 128 1 Unencrypted 0
Restart your SQL Server instance and execute the previous query again. This time, the tempdb system database is unencrypted.
You can use the following code to clean up your SQL Server instance. Again, use the SQLCMD
mode to execute it.
USE master; !!del C:SQL2016DevGuideDemoTDEEncryptCert.cer !!del C:SQL2016DevGuideDemoTDEEncryptCert.key !!del C:SQL2016DevGuidemasterDMK.key !!del C:SQL2016DevGuideSMK.key IF DB_ID(N'TDEDemo') IS NOT NULL DROP DATABASE TDEDemo; DROP CERTIFICATE DemoTDEEncryptCert; DROP MASTER KEY; GO
SQL Server 2016 Enterprise Edition introduces a new level of encryption, namely the Always Encrypted (AE) feature. This feature enables the same level of data protection as encrypting the data in the client application. Actually, although this is an SQL Server feature, the data is encrypted and decrypted on the client side. The encryption keys are never revealed to the SQL Server Database Engine. This way, a DBA can't also see sensitive data without the encryption keys, just by having sysadmin permissions on the SQL Server instance with the encrypted data. This way, AE makes a separation between the administrators who manage the data and the users who own the data.
You need two keys for AE. First you create the column master key (CMK). Then you create the column encryption key (CEK) and protect it with the CMK. An application uses the CEK to encrypt the data. SQL Server stores only encrypted data, and can't decrypt it. This is possible because the column master keys aren't really stored in a SQL Server database. In the database, SQL Server stores only the link to those keys. The column master keys are stored outside SQL Server, in one of the following possible places:
The column encryption keys are stored in the database. Inside an SQL Server database, only the encrypted part of the values of the column encryption keys are stored, together with the information about the location of the column master keys. CEKs are never stored as plain text in a database. CMKs are, as mentioned, actually stored in external trusted key stores.
An application can use the AE keys and encryption by using an AE-enabled driver, such as .NET Framework Data Provider for SQL Server version 4.6 or higher, Microsoft JDBC Driver for SQL Server 6.0 or higher, or Windows ODBC driver for SQL Server version 13.1 or higher. The application must send parameterized queries to SQL Server. The AE-enabled driver works together with the SQL Server Database Engine to determine which parameters should be encrypted or decrypted. For each parameter that needs to be encrypted or decrypted, the driver obtains the metadata needed for the encryption from the Database Engine, including the encryption algorithm, the location of the corresponding CMK, and the encrypted value for the corresponding CEK. Then the driver contacts the CMK store, retrieves the CMK, decrypts the CEK, and uses the CEK to encrypt or decrypt the parameter. Next the driver caches the CEK in order to speed up the next usage of the same CEK. The following figure shows the process graphically:
Always Encrypted process
The figure represents the whole process in these steps:
The Database Engine never operates on the plain text data stored in the encrypted columns. However, some queries on the encrypted data are possible, depending on the encryption type. There are two types of encryption:
It is time to show how AE works through some demo code. First, let's create and use a demo database:
USE master; IF DB_ID(N'AEDemo') IS NULL CREATE DATABASE AEDemo; GO USE AEDemo; GO
Next, create the CMK in SSMS GUI. In Object Explorer, refresh the Databases folder to see the AEDemo
database. Expand this database folder, expand the Security subfolder and the Always Encrypted Keys subfolder, and right-click on the Column Master Key subfolder and select the New Column Master Key option from the pop-up menu. In the Name text box, write AE_ColumnMasterKey
, and make sure you select the Windows Certificate Store--Local Machine option in the Key Store drop-down list, as shown in the following screenshot. Then click OK.
Creating a CMK
You can check if the CMK was created successfully with the following query:
SELECT * FROM sys.column_master_keys;
Next, you create the CEK. In SSMS, in Object Explorer, right-click on the Column Encryption Keys subfolder that is right under the Column Master Key subfolder, and select the New Column Encryption Key option from the pop-up menu. Name the CEK AE_ColumnEncryptionKey
and use the AE_ColumnMasterKey
CMK to encrypt it. You can check whether the CEK creation was successful with the following query:
SELECT * FROM sys.column_encryption_keys; GO
Now try to create a table with one deterministic encryption column and one randomized encryption column. My database used the default SQL_Latin1_General_CP1_CI_AS
collation.
CREATE TABLE dbo.Table1 (id INT, SecretDeterministic NVARCHAR(10) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey, ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL, SecretRandomized NVARCHAR(10) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL ); GO
The previous statement produced an error number 33289, which tells me that I cannot create an encrypted column for the character strings that use a non-BIN2 collation. Currently, only the new binary collations (that is, the collations with the BIN2
suffix) are supported for AE.
So let's try to create the table again, this time with correct collations for the character columns:
CREATE TABLE dbo.Table1 (id INT, SecretDeterministic NVARCHAR(10) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey, ENCRYPTION_TYPE = DETERMINISTIC, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL, SecretRandomized NVARCHAR(10) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = AE_ColumnEncryptionKey, ENCRYPTION_TYPE = RANDOMIZED, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL ); GO
This time, table creation succeeds. Now you can try to insert a row of data with the following statement:
INSERT INTO dbo.Table1 (id, SecretDeterministic, SecretRandomized) VALUES (1, N'DeterSec01', N'RandomSec1');
You get the error 206 with error text "Operand type clash: nvarchar is incompatible with nvarchar(4000) encrypted with (encryption_type = 'DETERMINISTIC'
, encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256'
, column_encryption_key_name = 'AE_ColumnEncryptionKey'
, column_encryption_key_database_name = 'AEDemo')
." SQL Server cannot encrypt or decrypt the data. You need to modify the data from a client application. You can do a limited set of operations on the table from SQL Server. For example, you can use the TRUNCATE TABLE
statement on a table with AE columns.
I created a very simple client Windows Console application in Visual C#. The application actually just retrieves the keys and inserts a single row into the table that was created with the code above. Here is the C# code. The first part of the code just defines the namespaces used in the application or added by default in a new project in Visual Studio 2015.
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks;
The next part of the code defines the connection string to my local SQL Server. Please note the new connection string property in .NET 4.6 and above, the Column Encryption Setting=enabled
property. Then the application opens the connection:
namespace AEDemo { class Program { static void Main(string[] args) { string connectionString = "Data Source=localhost; " + "Initial Catalog=AEDemo; Integrated Security=true; " + "Column Encryption Setting=enabled"; SqlConnection connection = new SqlConnection(connectionString); connection.Open();
The next part is just a simple check whether three arguments were passed. Please note that in a real application you should use a try-catch block when parsing the first argument to an integral number.
if (args.Length != 3) { Console.WriteLine("Please enter a numeric " + "and two string arguments."); return; } int id = Int32.Parse(args[0]);
The next part of the code defines the parameterized INSERT
statement and executes it:
{ using (SqlCommand cmd = onnection.CreateCommand()) { cmd.CommandText = @"INSERT INTO dbo.Table1 " + "(id, SecretDeterministic, SecretRandomized)" + " VALUES (@id, @SecretDeterministic, @SecretRandomized);"; SqlParameter paramid= cmd.CreateParameter(); paramid.ParameterName = @"@id"; paramid.DbType = DbType.Int32; paramid.Direction = ParameterDirection.Input; paramid.Value = id; cmd.Parameters.Add(paramid); SqlParameter paramSecretDeterministic = cmd.CreateParameter(); paramSecretDeterministic.ParameterName = @"@SecretDeterministic"; paramSecretDeterministic.DbType = DbType.String; paramSecretDeterministic.Direction = ParameterDirection.Input; paramSecretDeterministic.Value = "DeterSec1"; paramSecretDeterministic.Size = 10; cmd.Parameters.Add(paramSecretDeterministic); SqlParameter paramSecretRandomized = cmd.CreateParameter(); paramSecretRandomized.ParameterName = @"@SecretRandomized"; paramSecretRandomized.DbType = DbType.String; paramSecretRandomized.Direction = ParameterDirection.Input; paramSecretRandomized.Value = "RandomSec1"; paramSecretRandomized.Size = 10; cmd.Parameters.Add(paramSecretRandomized); cmd.ExecuteNonQuery(); } }
Finally, the code closes the connection and informs you that a row was inserted successfully:
connection.Close(); Console.WriteLine("Row inserted successfully"); } } }
If you don't have Visual Studio installed, you can just run the AEDemo.exe application provided with the code examples associated with this book. As mentioned, the application inserts a single row into the previously created table with two AE-enabled columns. Please run the application from SSMS in SQLCMD mode, as the following example shows; there is no prompting for values in the application:
!!C:SQL2016DevGuideAEDemo 1 DeterSec01 RandomSec1 !!C:SQL2016DevGuideAEDemo 2 DeterSec02 RandomSec2
Now try to read the data from the same session in SSMS that you used to create the table:
SELECT * FROM dbo.Table1;
You can see only encrypted data. Now open a second query window in SSMS. Right-click in this window and choose Connection, then Change Connection. In the connection dialog, click the Options button at the bottom. Type in AEDemo for the database name and then click the Additional Connection Parameters tab. In the text box, enter Column Encryption Setting=enabled
(without the double quotes). Then click on Connect.
Try again to insert a row from SSMS. Use the following query:
INSERT INTO dbo.Table1 (id, SecretDeterministic, SecretRandomized) VALUES (2, N'DeterSec2', N'RandomSec2');
When I ran this when writing this book, I got the same error 206 again. At the time of writing, I used SSMS version 13.0.15700.28
. This SSMS version still can't parametrize ad hoc inserts. However, let's try to read the data with the following query:
SELECT * FROM dbo.Table1;
This time, the query works and you get the following result:
Id SecretDeterministic SecretRandomized --- -------------------- ---------------- 1 DeterSec1 RandomSec1 2 DeterSec2 RandomSec2
You can now close this query window and continue in the first one. Try to index the column with deterministic encryption. The following code creates a nonclustered index on the dbo.Table1 with the SecretDeterministic
column used as the key:
CREATE NONCLUSTERED INDEX NCI_Table1_SecretDeterministic ON dbo.Table1(SecretDeterministic); GO
The creation succeeds. Now try to also create an index on the column with randomized encryption:
CREATE NONCLUSTERED INDEX NCI_Table1_SecretRandomized ON dbo.Table1(SecretRandomized); GO
This time you get an error message telling you that you cannot index a column with randomized encryption. Finally, execute the following code to clean up your SQL Server instance:
USE master; IF DB_ID(N'AEDemo') IS NOT NULL DROP DATABASE AEDemo; GO
You have already seen some of the limitations of AE, including:
Refer to Books Online for a more detailed list of AE's limitations. However, please also note the strengths of AE. It is simple to implement because it does not need modifications in an application, except the modification for connection strings. Data is encrypted end-to-end, from client memory, through network to database storage. Even DBAs can't view the data within SQL Server only; they need access to the key storage outside SQL Server to read the CMK. AE and other encryption options in SQL Server provide a complete set of possibilities, and it is up to you to select the appropriate method for the business problem you are solving.