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.