共用方式為

Getting Started With Always Encrypted

  • 發行項

Updates:  

  1. The syntax for column master keys have been updated. Please refer to https://blogs.msdn.com/b/sqlsecurity/archive/2015/10/28/new-enhancements-in-always-encrypted.aspx for details on what is new in Always Encrypted.
  2. We have updated the schema for our sample table to follow best practices. Big thanks to Denny Cherry for his feedback and help on greatly improving our sample schema.

 

The recently released SQL Server 2016 Community Technology Preview 2 introduced Always Encrypted , a new security feature that ensures sensitive data is never seen in plaintext in a SQL Server instance. Always Encrypted works by transparently encrypting the data in the application, so that SQL Server will only handle the encrypted data and not plaintext values. Even if the SQL instance or the host machine is compromised, all an attacker can get is ciphertext of sensitive data.

We will begin a series of articles on Always Encrypted with a simple example of the technology that can help everyone to get started. We will show how to develop a simple console application that uses Always Encrypted to protect patient information stored in a database.

For this example, you will need to install the following:

  1. Database Engine from CTP2 of SQL Server 2016 (on a SQL Server machine).
  2. SQL Server Management Studio from CTP2 of SQL Server 2016 (on your development machine). Update: This article has been updated to reflect the syntax changes introduced in CTP3
  3. Visual Studio, preferably 2015 RC (on your development machine). Update: Visual Studio 2015 is now available.

Create a Database Schema using Always Encrypted

For this simple example, we will perform the following steps using SSMS (SQL Server Management Studio) on the development machine:

  1. Create a local, self-signed certificate on the development machine, which will act as a column master key (CMK). The CMK will be used to protect column encryption keys (CEK), which encrypts the sensitive data. We will then create a column master key definition object in the database, which will store the information about the location of the CMK. Please notice that the certificate will never be copied to the database or to the SQL Server machine.
  2. Create a column encryption key on the development machine, encrypt it using the CMK and then create a column encryption key object in the database uploading the encrypted value of the key.
  3. Create a simple table with encrypted columns.

Step 1 - Configure a Column Master Key

a) Create a new database named Clinic.

b) Using Object Explorer, locate and open the Always Encrypted Keys folder under Security for your database. Right-click on Column Master Key and select New Column Master Key …. This will open a dialog which you will use to define a column master key for your database. The easiest option for developing new apps using Always Encrypted is to use a certificate, stored in your personal Certificate Store, as a column master key.

c) Simply, enter CMK1 as a name of your column master key, click Generate Self-Signed Certificate, and click OK. This will generate a self-signed certificate, put it in your personal store (Certificate Store: Current User), and create a definition of the column master key in the database.

Step 2 - Configure a Column Encryption Key

To generate a column encryption key that will be used to encrypt sensitive data in the database, right-click on the Column Encryption Keys folder, select New Column Encryption Key, enter CEK1 as a key name and select CMK1 as an encrypting column master key for your new column encryption key. Once you click OK, a new column encryption key gets created, encrypted with the certificate you configured in step 1, and the encrypted value is uploaded to the database.

 

Step 3 – Create a Table using Always Encrypted:

Using a New Query window in SSMS, issue the following statement:

 CREATE TABLE [dbo].[Patients](
 [PatientId] [int] IDENTITY(1,1), 
 [SSN] [char](11) COLLATE Latin1_General_BIN2 
 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,
 [FirstName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL, 
 [MiddleName] [nvarchar](50) NULL,
 [StreetAddress] [nvarchar](50) NULL,
 [City] [nvarchar](50) NULL,
 [ZipCode] [char](5) NULL,
 [State] [char](2) NULL,
 [BirthDate] [date] 
 ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
 PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] )
 GO

[Updated] Please notice that the schema for our table has changed to reflect best practices. The following is a summary of the changes:

SSN changed to char(11) as this is not a variable-length field. No need for this field to be nchar either as SSN will not contain any Unicode characters.

ZIP code changed to char(5) as there are certain ZIP codes that start with 0s, and therefore an integer will not represent them correctly.

State has changed to 2 char characters also notice that Unicode is not needed.

Changed Birthdate from datetime2 to date as the time and precision are not suitable for this field.

Once again, thanks a lot to Denny Cherry for his feedback on how to improve our schema.

The above T-SQL creates the Patients table with two encrypted columns: SSN and BirthDate. SSN is configured to be encrypted using deterministic encryption, which supports equality lookups, joins and group by. BirthDate is encrypted using randomized encryption, which does not support any operations, but that is ok, as the app is not going to perform any computations on the BirthDate column.

 

Create an Application using Always Encrypted

Now that we have the Always Encrypted keys and the schema configured, we can create a small application that will be able to insert data into the Patients table & query it.

In Visual Studio, we will create a new console application using C#. Since the SqlClient enhancements to support Always Encrypted were introduced in .Net Framework 4.6, we need to ensure the application is using the right version of the framework. Right click on the project, select Properties, then go to the Application tab, and make sure that Target Framework option is set to “.Net Framework 4.6”.

Next we will add very simple code that connects to the database, inserts and selects data using SqlClient. You will notice that the only change required to use Always Encrypted is including “Column Encryption Setting=Enabled;” in the connection string. The complete code is included as a file attachment.

For this exmple, we are enabling the Column Encryption Setting in the connection string using a SqlConnectionStringBuilder object and setting SqlConnectionStringBuilder.ColumnEncryptionSetting to Enabled… and that’s pretty much it.

  strbldr.ColumnEncryptionSetting = SqlConnectionColumnEncryptionSetting.Enabled;

Something to remark is that in order to send values that will correspond to encrypted columns, you need to use SqlParameter class. It is not possible to use literals to pass such values.

  cmd.CommandText = @"INSERT INTO [dbo].[Patients] ([SSN], [FirstName], [LastName], [BirthDate]) VALUES (@SSN, @FirstName, @LastName, @BirthDate);";
 
 SqlParameter paramSSN = cmd.CreateParameter();
 paramSSN.ParameterName = @"@SSN";
 paramSSN.DbType = DbType.AnsiStringFixedLength;
 paramSSN.Direction = ParameterDirection.Input;
 paramSSN.Value = ssn;
 paramSSN.Size = 11;
 cmd.Parameters.Add(paramSSN);
 …
 SqlParameter paramBirthdate = cmd.CreateParameter();
 paramBirthdate.ParameterName = @"@BirthDate";
 paramBirthdate.SqlDbType = SqlDbType.Date;
 paramBirthdate.Direction = ParameterDirection.Input;
 paramBirthdate.Value = birthdate;
 cmd.Parameters.Add(paramBirthdate);
 
 cmd.ExecuteNonQuery();
 …
 cmd.CommandText = @"SELECT [SSN], [FirstName], [LastName], [BirthDate] FROM [dbo].[Patients] WHERE [SSN] = @SSN;";
 
 SqlParameter paramSSN = cmd.CreateParameter();
 paramSSN.ParameterName = @"@SSN";
 paramSSN.DbType = DbType.AnsiStringFixedLength;
 paramSSN.Direction = ParameterDirection.Input;
 paramSSN.Value = ssn;
 paramSSN.Size = 11;
 cmd.Parameters.Add(paramSSN);
 
 SqlDataReader reader = cmd.ExecuteReader();

At this point many readers may be doubting we really encrypted anything, after all, the application seems to be simply handling plaintext as naturally as before; so, how can we verify that the data was properly encrypted?

We can use SSMS query for that. If we simply select our table, you will notice that the SSN & BirthDate columns seem to be displaying binary data.

Conclusion

As we have seen, Always Encrypted transparently encrypts/decrypts sensitive data in the application as long as the application has access to the certificate acting as a CMK. Users and applications without access to the CMK, including SQL Server itself, will not be able to decrypt the sensitive data.

If you connect SQL Profiler to the database while running this application, you will notice that SQL Server will receive data corresponding to the encrypted values only as ciphertext, never as plaintext.

Special thanks the Always Encrypted team for their help writing this article.

AlwaysEncryptedDemo.cs

Comments

  • Anonymous
    June 04, 2015
    In your article, how did the c# app have access to the CMK master key? Why does SSMS not have access to that same key? What would happen if you tried to do am INSERT statement in SSMS?

  • Anonymous
    June 05, 2015
    The comment has been removed

  • Anonymous
    June 05, 2015
    The comment has been removed

  • Anonymous
    June 08, 2015
    Thank you very much for your excellent introduction to Always Encrypted. We distribute a free program, "MedicalRecords", which provides an integrated, personal medical record. It uses Access 2013 as a front end and SQL Server 2014 or 2016 as the database manager. I have two questions about Always Encrypted. First, is programming in VBA currently supported ? Second, our program uses binding of variables between Access and SQL. That is, Access displays forms the fields in which are bound to same named and defined SQL fields.  Updating between Access and SQL is "automatic". Thank you very much. We are looking forward to your next article.

  • Anonymous
    June 09, 2015
    Thanks for the article! When creating the column encryption key (CEK1) and selecting the CMK1 Master Key Definition and clicking OK, I am getting an error "Object reference not set to an instance of an object. (SqlManagerUI)" Any reasons why??

  • Anonymous
    June 10, 2015
    @SQLROD: The issue, you described, may occur if you do not reboot the machine after installing SSMS. A reboot is required to complete the installation of .NET 4.6 that comes with SSMS. Please, restart the machine and check if it addresses the problem.

  • Anonymous
    June 11, 2015
    Hi! Since the certificate this creates is non-exportable, how do I get the certificate from SQL Server to my client machine?

  • Anonymous
    June 11, 2015
    I resolved my  own question by cloning and exporting the generated certificate. I recreated my CMK and CEK using the cloned certificate, which is fine in testing. However, when users have saved important encrypted data using a non-exportable certificate, is there any path to accessing that data from outside the machine, other than starting over?

  • Anonymous
    June 16, 2015
    The comment has been removed

  • Anonymous
    June 22, 2015
    The comment has been removed

  • Anonymous
    June 23, 2015
    @Vladimir. The error you are getting is not from the server, but form the client machine where you are trying to generate the column encryption key (CEK). The reason why we are accessing the private key during the CEK generation is that the encrypted CEK material is also going to be signed by the certificate acting as a column master key. When the client application is decrypting data, it will get the encrypted CEK from SQL Server, and before decrypting it, it will verify that the key is signed by the column master key (CMK). You are correct, SQL Server itself doesn’t need to have access to the CMK at all. The only information it has about the CMK is the definition (i.e. metadata describing the location to the actual key). I hope this helps to clarify the scenario. -Raul

  • Anonymous
    June 23, 2015
    Hi Raul Thanks for a detailed explanation. Now I see why the Sql Server management studio (and not the Sql Server) needs to access the private Master key when generating a CEK. Cheers Vladimir

  • Anonymous
    June 25, 2015
    Even after adding the additional parameter 'Column Encryption Setting=Enabled' to SSMS it doesn't appear you are able to INSERT, only SELECT the data encrypted. Is there something specific that can be done to allow inserts once you have enabled the Column Encryption Setting?

  • Anonymous
    June 25, 2015
    The comment has been removed

  • Anonymous
    June 30, 2015
    In CMK rotation scenario described in msdn.microsoft.com/.../mt146388.aspx , do we have to obtain the decrypted data which was encrypted with the old CMK1, drop CMK1 definition from CEK, and re-insert the data encrypting with the new CMK2 ? Could you show us the detailed steps to rotate CMK? Thanks, Jun

  • Anonymous
    July 01, 2015
    @JunKawano We are planning to answer this question on an upcoming blog post. We appreciate your patience.

  • Anonymous
    July 08, 2015
    @Jakub Szymaszek  Finally got a chance to check back with this, and after a reboot, the problem is resolved. Thanks for the feedback!

  • Anonymous
    July 11, 2015
    The comment has been removed

  • Anonymous
    August 17, 2015
    How can we clear the plaintext CEK cache? I tried two approaches, the one is invalidating the self-signed certificate and the other is reboot the client machine with invalidating the self-signed certificate, but the query returns the plaintext value in both approach. If admin found suspicious client, invalidating the CEK cache or some other option would be useful way to stop showing plaintext. Or CMK rotation is the only way to achieve this? Thanks, Jun

  • Anonymous
    August 18, 2015
    @JunKawano The only way to clear the CEK cache is by restaring the client application. CEK is cached per process, by restarting a process the cache will be cleared and rebuilt. Invalidating the certificate is not enough. You will have to either revoke permissions to the certificate or remove it entirely from the machine and restart the process. If admin found a suspicious client, invalidating the CEK and reencrypting the data would be the only option.

  • Anonymous
    September 11, 2015
    Hi, Since SQL Server requires the private key of the CMK to sign the CEK during the generation, does it store / cache the CMK private key ? Does Entity Framework support Always Encrypted ? Is it possible to use the Azure Key Vault to generate a key for me and use it as  a CMK ?

  • Anonymous
    September 14, 2015
    @Thuru Since SQL Server requires the private key of the CMK to sign the CEK during the generation, does it store / cache the CMK private key ? No, we only cache the CEK. The CMK will be accessed every time it is needed. Does Entity Framework support Always Encrypted ? We have published an article that covers this one. I would like to encourage you to look at this article: blogs.msdn.com/.../using-always-encrypted-with-entity-framework-6.aspx I would recommend asking any further questions you may have on this particular topic on the blog article above in order to make it easier for other people who may be interested in the question/answer. Is it possible to use the Azure Key Vault to generate a key for me and use it as a CMK ? All I can say right now is “stay tuned”. I hope this information helps, -Raul

  • Anonymous
    October 10, 2015
    I am trying to script the whole process. I managed to create a certificate for Always Encrypted using Powershell: New-SelfSignedCertificate -CertStoreLocation Cert:CurrentUserMy -DNSName "CN=Always Encrypted Certificate" -KeyUsage KeyEncipherment -TextExtension @("2.5.29.37={text}1.3.6.1.5.5.8.2.2,1.3.6.1.4.1.311.10.3.11") -provider "Microsoft Strong Cryptographic Provider" I can generate the column master key definition fine however when creating the column encryption key how do I know the encrypted value to use? "The encrypted value is assumed to be produced by encrypting the plaintext of the column encryption key using the specified column master key and the specified algorithm." It is not clear how to do this to generate the encrypted value!

  • Anonymous
    October 10, 2015
    I am trying to script the process, how do we generate the encrypted value in the create column encrpytion key statement?

  • Anonymous
    October 12, 2015
    @David You will have to use PowerShell for the key encryption operation as well: • Instantiate System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider. For example: $cmkprov = New-Object System.Data.SqlClient.SqlColumnEncryptionCertificateStoreProvider • Generate a new key securely. You could use System.Security.Cryptography.RNGCryptoServiceProvider to generate the bytes. • Then you will have to call $cmkprov.EncryptColumnEncryptionKey($masterKeyPath, $encryptionAlgol, $keyBytes) • The return value is the byte stream that should be uses as the encrypted column encryption key. For more details, you can look into msdn.microsoft.com/.../mt147923.aspx & msdn.microsoft.com/.../system.data.sqlclient.sqlcolumnencryptioncertificatestoreprovider.encryptcolumnencryptionkey.aspx Please let us know if you have any further question. -Raul

  • Anonymous
    October 14, 2015
    Thanks Raul, I have this working now: justdaveinfo.wordpress.com/.../always-on-encrypted-generating-certificates-and-column-encryption-key-encrypted_value

  • Anonymous
    October 17, 2015
    Raul, Can you please update your sample code so that it doesn't show horrible table design practices such as Unicode support for social security numbers, and using INT to store Zip Codes?

  • Anonymous
    October 27, 2015
    @mrdenny Thanks for the comments. I understand your request, but unfortunately we have been using this schema for many articles on Always Encrypted, so I would prefer to not change the schema. The schema was intended for demonstration purposes only, and we wanted to showcase certain data types such as Unicode strings. Hopefully a note in the article specifying that the data types we used may not be the best option for the schema. Although, something to consider is that the typical recommendation for choosing certain data types such as char(11) for a SSN field in a schema have a lot to do about how the SQL engine will optimize and use this data for storage & indexing. It is important to remark that when encrypting data using Always Encrypted, the plaintext data type metadata is used by the client driver (i.e. .Net) to normalize and present the data to the application, and by SQL Server Engine for certain validations, but the SQL engine will not be able to use this data to optimize for storage/indexing as the data is really stored as binary data, and padded. For example, you will find that there will be no difference in terms of storage for an encrypted int vs. an encrypted short as both data types are less than 1 block (16 bytes) and the ciphertext for both data types will have the same length in storage.

  • Anonymous
    October 27, 2015
    @mrdenny, @raul On a second thought, we are reconsidering our answer to your feedback. We will contact you directly to get feedback on how to change the schema and follow best practices. Thanks

  • Anonymous
    November 02, 2015
    Updated. We made some changes to the article to reflect the new syntax in CTP3 and to update the schema based on @mrdenny's recommendation. Thanks a lot.

  • Anonymous
    November 13, 2015
    I'm having trouble creating a CEK from SSMS 2016 CTP3 using Azure Key Vault. By using the "Encrypt Column" wizard and the "New Columny Encryption Key" wizard, the same error is displayed at the step where the CEK is created. When choosing a CMK from Azure Key Vault, the following error is displayed: Operation "wrapKey" is not allowed (Microsoft.Azure.KeyVault). Any hints?

  • Anonymous
    November 13, 2015
    Update to my previous comment. I was able to solve that error by giving my subscription user the proper key permissions using the Set-AzureKeyVaultAccessPolicy.

  • Anonymous
    November 13, 2015
    @Justin: to use the Always Encrypted wizard with Azure Key Vault, you'll need to run the following PowerShell command to grant the correct permissions: Set-AzureKeyVaultAccessPolicy -VaultName "<your vault name>" -ResourceGroupName "<your resource group>" -PermissionsToKeys create,get,wrapKey,unwrapKey,sign,verify -UserPrincipalName "<your Azure username>" A heads-up: this PowerShell cmdlet will be changing its name to "Set-AzureRmKeyVaultAccessPolicy" with the impending 1.0 release.  You can use the new names now with the Azure Powershell 1.0 Preview: azure.microsoft.com/.../azps-1-0-pre Also, for more information on how to integrate support for Azure Key Vault column master keys into your client applications, check out the new blog post on it: blogs.msdn.com/.../using-the-azure-key-vault-key-store-provider.aspx

  • Anonymous
    January 01, 2016
    My asp.net application works locally dev area  where i have the certificate in my  local store.  When i deploy top azure it fails, what additional configuration is needed for asp.net web apps. How do I export the certificates. Any pointer is appreciated. Regards Janu

  • Anonymous
    February 05, 2016
    Where do I download the "Always Encrypted enabled driver"?

  • Anonymous
    July 10, 2016
    Awesome article.. i have few question about this column ? .How does the index help on Encryption column ? does it degrade performance ? How many maximum column i can keep as Encryption column ? is there any limitation ? Can i have integer column encrypted ? Can we do JOIN on this column ?

    • Anonymous
      July 11, 2016
      @kalpesh - Thank you for your questions. You can index columns using deterministic encryption, but not randomized encryption. Performance impact is observed primarily on the client side, as encryption/decryption is performed inside a client application. The impact on database server’s performance is minimal. There is no limit to how many columns you can encrypt. However, keep in mind that T-SQL functionality on encrypted columns is limited. For example, equality comparison is the only operation allowed in where clauses and only when using deterministic encryption. Other operations, including range queries and LIKE predicates are not supported. Please, see https://msdn.microsoft.com/en-us/library/mt163865.aspx#Anchor_5 for details. The general recommendation is to encrypt a small subset of columns containing truly sensitive data, so that you can benefit from the full T-SQL functionality on columns that do not contain critical data, and to minimize performance impact of encryption.You can encrypt integer columns.JOIN operations on encrypted columns using deterministic encryption are supported.

  • Anonymous
    September 21, 2016
    If the application is developed/hosted in different machine, how will the application acquire access to the certificate store of the SQL server?

    • Anonymous
      December 12, 2016
      Hi Mia,Let me first clarify that when you provision a certificate using a tool, such as the New Column Master Key dialog (used in the above article), the certificate gets provisioned on the machine hosting SSMS, not SQL Server. The database in SQL Server only contains metadata, referencing the certificate (the column master key).If the application is hosted on a different machine from the machine, you provisioned the certificate on, you need to export the certificate (with the private key) and import it on the machine hosting your app. Of course, if you have many machines hosting apps that need to access plaintext data, managing certificates on all of them can be challenging. In such cases, you may want to store column master keys in a central key store, such as Azure Key Vault or a networked hardware security module.Thanks,Jakub