And foreign keys must match encryption types:
Msg 33281, Level 16 Column 'dbo.tbl1.col' has a different encryption scheme than referencing column 'tbl2.col' in foreign key 'FKName'.
Client libraries need to be updated to support encryption and decryption of columns and parameters. Not all drivers will support this functionality, and some may never. Currently the only technology that supports this is .NET 4.6; ODBC and JDBC updates should come soon.
The connection string needs to be updated with the following additional attribute:
Column Encryption Setting=enabled;
Management Studio itself will require each client to have a copy of the column encryption key, or direct access to it. I will go over a more elaborate scenario in a future post, but for now you can assume that you will need to grant application users (including those of SSMS) the ability to see the master/column key metadata, with the following code:
GRANT VIEW ANY COLUMN MASTER KEY DEFINITION TO database_user; GRANT VIEW ANY COLUMN ENCRYPTION KEY DEFINITION TO database_user;
As long as the Column Encryption Setting is added as an additional connection parameter, SSMS users will be able to see the decrypted values in the result of a query. But they will not be able to pass parameters to a stored procedure that inserts or updates encrypted values, because - unlike with an application, which handles the encryption - there is no round-trip between declaring the parameters and passing them to the stored procedure. Result:
Msg 33299, Level 16 Encryption scheme mismatch for columns/variables '@param'. 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 = 'ColumnKey', column_encryption_key_database_name = 'AE') (or weaker).
There may be some way to work around this using SQLCMD mode, but I haven't explored too deeply yet.
For the client applications on machines other than where SQL Server 2016 is installed, they'll need an updated version of .NET Framework (not necessary on Windows 10 or if you have Visual Studio 2015), and the certificate installed (as I describe here). If the framework isn't modern enough, the connection string setting simply won't work (though I haven't confirmed the exact error message because all of my VMs running SQL Server 2016 are Windows 10). If the certificate used for the keys isn't there, the application will crash with errors like this, depending on the type of keys you generated on the database server:
Unhandled Exception: System.Data.SqlClient.SqlException: Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are: '60-8E-2E-D3-09-8E-1D-18-10-E3'. Certificate with thumbprint '8D2106DABD34DD557D208DD5BE9DFCD1D23CE1BF' 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 —> System.ArgumentException: Certificate with thumbprint '8D2106DABD34DD557D208DD5BE9DFCD1D23CE1BF' 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.
(It knows where to look for that certificate on the local machine because it gets the metadata from SQL Server. For some other information about master/column keys and particularly about key rotation, see this SQL Server Security Blog post.)
Encrypted parameters need to be passed as properly typed parameters. This type of ad hoc SQL will break, even when passed from your C# 4.6 application with the proper column encryption setting in the connection string:
SELECT cols FROM dbo.tbl WHERE EncryptedColumn = 'some value';
Msg 206, Level 16 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 = 'ColumnKey', column_encryption_key_database_name = 'AE') collation_name = 'SQL_Latin1_General_CP1_CI_AS'
You'll need to use SqlCommand and SqlParameter, with types and lengths that match exactly, in order to perform any of the supported operations against encrypted columns.
Finally, there is additional overhead required to negotiate the encryption handshakes, so if your application is really chatty, you'll definitely want to baseline your network performance and then compare once your columns are encrypted. There is also the consideration that encrypted data can take a lot more space than its unencrypted equivalent, whether or not you are using compression. As a companion to this post, I took a look at these two impacts in a separate post over on SQLPerformance.com.
There are some additional considerations for client code development, and there is a dedicated topic on that in Books Online.
With all of the data breaches happening these days, I am quite happy that there are advances being made in the protection of data. But understanding the limitations before jumping into it can be a valuable learning exercise. I'll repeat, because it can't be stressed enough, that these are the limitations I am observing in current builds today, and the status of any of these issues can change at any time. I'll also admit that this isn't necessarily an exhaustive list in terms of features; just the ones I thought most obvious to check.
Follow the #TSQL2sDay hash tag and Ken's post for more posts on encryption and SQL Server.