次の方法で共有

Parameterization for Always Encrypted - Using SSMS to Insert into, Update and Filter by Encrypted Columns

  • [アーティクル]

SQL Server  Management Studio 17.o (the next major update of SSMS, currently available as a Release Candidate) introduces two important capabilities for Always Encrypted:

  • Ability to insert into, update and filter by values stored in encrypted columns from a Query Editor window.
  • The new online encryption algorithm, exposed in the Set-SqlColumnEncryption PowerShell cmdlet, which makes tables available for both reads and writes during the initial encryption and column encryption key rotation.

This article addresses the first of the two enhancements.

Prerequisites

To try the examples in this article, you need:

Introducing Parameterization for Always Encrypted

In SSMS 16.x, queries that insert, update or select data (in the WHERE clause) in encrypted columns  are not supported. For example, if you try to execute the following statement, it will fail, assuming the SSN column is encrypted.

DECLARE @SSN CHAR(11) = '795-73-9838' SELECT * FROM [dbo].[Patients] WHERE [SSN] = @SSN

SSMS sends the query verbatim as a batch to SQL Server, including the plaintext value of the @SSN variable. As a result, the query fails with the below encryption scheme mismatch error, because the SQL Server expects the value targeting the SSN column to be encrypted, not in plaintext.

Msg 33299, Level 16, State 6, Line 2 Encryption scheme mismatch for columns/variables '@SSN'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '2' 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 = 'Clinic') (or weaker).

SSMS 17.0 introduces the new feature, called Parameterization for Always Encrypted, which, when enabled, maps Transact-SQL variables to query parameters ( SqlParameter objects, in .NET - SSMS uses .NET Framework Data Provider for SQL Server) and it refactors queries, using Transact-SQL variables, into parameterized statements.

For example, if you run the above query in SSMS over a connection with column encryption settings=enabled and with parameterization turned on, a SQL Server profiler log will capture the following two RPC calls, instead of the single batch statement, on the server side:

exec sp_describe_parameter_encryption N'DECLARE @SSN AS CHAR (11) = @pdf9f37d6e63c46879555e4ba44741aa6; SELECT * FROM [dbo].[Patients] WHERE [SSN] = @SSN; ',N'@pdf9f37d6e63c46879555e4ba44741aa6 char(11)' goexec sp_executesql N'DECLARE @SSN AS CHAR (11) = @pdf9f37d6e63c46879555e4ba44741aa6; SELECT * FROM [dbo].[Patients] WHERE [SSN] = @SSN; ',N'@pdf9f37d6e63c46879555e4ba44741aa6 char(11)',@pdf9f37d6e63c46879555e4ba44741aa6=0x01A01201846E5E924FC73155B7CC71CD05153DD09E95663F8DB34885B048E58C2D2DDDB15A6144A9CD7E6A46310590788F398CA1C216F9215992A0CF77990C9F6B go

First thing to note is that SSMS has rewritten the query as a parameterized statement. The literal, used to initialize the @SSN variable in the original query, is being passed inside a parameter, with an auto-generated name (@pdf9f37d6e63c46879555e4ba44741aa6). This allows the .NET Framework Data Provider for SQL Server to automatically detect that the parameter needs to be encrypted. The driver achieves that by calling sp_describe_parameter_encryption that prompts SQL Server to analyze the query statement and determine which parameters should be encrypted and how. Then, the driver, transparently encrypts the parameter value, before submitting the query to SQL Server for execution via sp_executesql. SQL Server can now successfully execute the query.

Why Use It?

One of the benefits of Parameterization of Always Encrypted is that it makes it possible to try and test Always Encrypted in SSMS end-to-end. Before, you needed to write a custom app to insert or update data into encrypted columns, or to test point lookups against encrypted columns - you can now issue such queries in SSMS. The new capability also simplifies populating encrypted columns in a test or development database in your development environment.

Parameterization of Always Encrypted also allows you to use SSMS for management and development tasks that require access to plaintext values stored in encrypted columns inside production databases. For example, you can now perform tasks, such as manually cleansing/fixing data in encrypted columns or developing reporting queries against encrypted columns in SSMS. Please note the following important security considerations that apply to this scenario.

  • SSMS users accessing sensitive data in your production environment must be trusted. For example, if your organization stores sensitive data in the cloud (Azure SQL Database or SQL VMs), and the organization uses Always Encrypted to protect data from data breaches in the cloud (including data theft by malicious Microsoft Operators or malware), and the organization fully trusts their own DBAs, it might make perfect sense to enable the DBAs to manage sensitive data stored in encrypted columns.
  • On the other hand,  if the organization uses Always Encrypted to protect sensitive data from insider attacks by high privilege users, allowing those users to access sensitive data in plaintext may defeat the purpose of using the feature. To insert, update or read plaintext data from encrypted column, a user must be granted access to the keys protecting the data. Once a user is granted access to the keys, the only reliable method to revoke access is by rotating both the column encryption key and the column master key, which involves re-encrypting all data (with a new column encryption key).
  • When you use SSMS to access plaintext data in a production environment, always run it on a trusted/secure machine, which is different from a computer hosting your SQL Server instance.

Getting Started with Parameterization for Always Encrypted

To issue parameterized queries targeting encrypted columns n SSMS:

  1. First, you need to make sure you can access a column master key, protecting a column you want to insert data to, update or filter by. For example, if the column master key is a certificate, you need to make sure, it is imported to Windows Certificate Store on your machine and you can access it.
  2. Make sure SSMS is connected to the database with column encryption setting=enabled in the database connection string, which instructs the .NET Framework Data Provider for SQL Server to encrypt query parameters (and decrypt the results). Here is how you can set the above setting for an existing Query Editor window:
    1. Right-click anywhere in the Query Editor window
    2. Select Connection > Change Connection … .
    3. Click Options >>.
    4. Select the Additional Properties tab,  type Column Encryption Setting=Enabled .
    5. Click Connect.
  3. Parameterization is disabled by default. To enable it:
    1. Select Query from the main menu.
    2. Select Query Options… .
    3. Navigate to Execution > Advanced.
    4. Select or unselect Enable Parameterization for Always Encrypted.
    5. Click OK.
  4. Now, you are ready to author your to-be-parameterized query. Note that SSMS underlines each Transact-SQL variable that is going to be mapped to a parameter. If you hover on a declaration statement that got marked with a warning underline, you will see the results of the parameterization process, including the values of the key properties of the resulting SqlParameter object (the variable is mapped to): SqlDbType, Size, Precision, Scale, SqlValue.

Which Transact-SQL Variables Get Parameterized?

Not all Transact-SQL variables get parameterized. To be converted to a parameter, a variable must be:

  • Declared and initialized in the same statement (inline initialization). SSMS will not parameterize variables declared using separate SET statements.
  • Initialized using a single literal. Variables initialized using expressions including any operators or functions will not be parameterized.

Again, SSMS informs you which variables it parameterizes via warning underlines in the Query Editor window. You can also see the complete list of all variables that have been successfully parameterized in the Warning tab of the Error List view. To open the Error List view, select View from the main menu and then select Error List.

The below screenshot shows a Transact-SQL script with 6 variables. The first 3 variables (@SSN, @BirthDate and @Salary) get successfully parameterized as they meet the above two conditions. The following variables do not get parameterized.

  • @Name - is initialized using a separate SET statement.
  • @BirthDate - is initialized using a function.
  • @NewSalary - is initialized using an expression.


If a variable targets an encrypted column and it does not get parameterized, you need to change the way it is declared or/and initialized, otherwise, your query will fail with an encryption schema mismatch error.

Note that SSMS attempts to parameterize any variable, meeting the above two conditions, regardless if the variable is used in a query targeting an encrypted column.

Requirements for Initialization Literals

A declaration of a variable must meet the above condition for SSMS to attempt to parameterize the variable. In addition, the declaration must satisfy the following two requirements for the parameterization to succeed:

  • The type of the literal used for the initialization of the variable must the type in the variable declaration.
  • If the declared type of the variable is a date type or a time type, the variable must be initialized with a string using one of the ISO 8601-compliant formats (e.g. yyyy-mm-ddThh:mm:ss[.mmm], which are independent from local culture and language settings. SSMS imposes this restriction for the following reason. If SSMS allowed non-ISO formats, the date or date time values would be interpreted based on the culture/language settings of the machine SSMS is running on, which can be different than the settings of the target database. Consequently, running the same query from different machines or with vs. without parameterization, would lead to ambiguities, as it would produce different results.

The below screen shot shows two variables SSMS fails to parameterize:

  • @BirthDate - is initialized using a non-ISO format.
  • @Number- is declared as int, but it is initialized using a literal of an incompatible type (float).

You can see the details about the parameterization errors by hovering on the declaration of the variable or in the Error List view.

Troubleshooting Server-side Type Conversion Errors

The below screenshot shows an example of a successfully parameterized variable and a query. Yet, the execution of the query fails.

Here is the complete error message:

Msg 206, Level 16, State 2, Line 3 Operand type clash: nchar(50) 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 = 'Clinic') is incompatible with char(11) 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 = 'Clinic') collation_name = 'Latin1_General_BIN2' Msg 8180, Level 16, State 1, Procedure sp_describe_parameter_encryption, Line 1 [Batch Start Line 0] Statement(s) could not be prepared.

The reason for the failure is the type of the target SSN column is CHAR(11), but the variable uses NCHAR(50), which, when encrypted, is not compatible with CHAR(11). SQL Server supports few conversions for encrypted data types. In particular, conversions between Unicode and ANSI strings are not supported. To avoid such errors, make sure types of the variables and the types of the columns those variables target are the same.

Conclusion

Parameterization for Always Encrypted enables updating and filtering encrypted columns from SSMS. This new capability enables you to try Always Encrypted end-to-end using SSMS in a test/development database. It also aims at enabling trusted users within your organization to manage sensitive information protected with Always Encrypted in your production environment, or to develop reporting queries against sensitive data in production.

Parameterization for Always Encrypted is included in SSMS 17.0, which is currently available for a preview - please, see: Download SQL Server Management Studio (SSMS).

As always, we are looking forward to hearing your feedback. Please, post your comments below.

Comments

  • Anonymous
    August 24, 2017
    Interesting approach. How would I do this with insert selects. In other words I may have one table that has some values that I want to encrypt. What I would want to do is select all the values from the table then insert them into another table where there is column level encryption. How could I achieve that?
    • Anonymous
      October 03, 2017
      @brianmrush1 - currently, SSMS only supports parameterizing T-SQL variables with inline declarations, preventing you from setting a variable separately from the declaration, e.g. in a loop, so you cannot do select/insert. One alternative is be to use the SQL Server Export/Import Wizard (which comes with SSMS). You would need to point the wizard to the source (plaintext) table and to the target (encrypted, initially empty) table, making sure that Always Encrypted is encrypted in the connection string for the target table. Please, see this article for information: https://blogs.msdn.microsoft.com/sqlsecurity/2015/07/28/encrypting-existing-data-with-always-encrypted/.
  • Anonymous
    October 12, 2017
    How can I filter always encrypted column with multiple values? For eg: column1 is always encypted column in table t then I want to select * from t where column1 in ('value1','value2')
  • Anonymous
    March 10, 2019
    The comment has been removed