Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
This article describes how to query columns, encrypted with Always Encrypted using SQL Server Management Studio (SSMS). With SSMS, you can:
INSERT
or UPDATE
statements and as a lookup parameter of the WHERE
clauses in SELECT
statements).Note
Using column master keys stored in a managed HSM in Azure Key Vault requires SSMS 18.9 or a later version.
Running SELECT queries that retrieve ciphertext of data stored in encrypted columns (without decrypting the data) doesn't require you to have access to column master keys protecting the data. To retrieve values from an encrypted column as ciphertext in SSMS:
SELECT
query retrieving ciphertext values. See Enabling and disabling Always Encrypted for a database connection below.SELECT
query. Any data retrieved from encrypted columns will be returned as binary (encrypted) values.Assuming SSN
is an encrypted column in the Patients
table, the query shown below will retrieve binary ciphertext values, if Always Encrypted is disabled for the database connection.
To retrieve values from an encrypted column as plaintext (to decrypt the values):
SELECT
query retrieving and decrypting your data. This will instruct the .NET Framework Data Provider for SQL Server (used by SSMS) to decrypt the encrypted columns in the query result set. See Enabling and disabling Always Encrypted for a database connection below.SELECT
query. Any data retrieved from encrypted columns will be returned as plaintext values of the original data types.Assuming SSN is an encrypted char(11)
column in the Patients
table, the query shown below will return plaintext values, if Always Encrypted is enabled for the database connection and if you have access to the column master key configured for the SSN
column.
To execute a query that sends a value that targets an encrypted column, for example a query that inserts, updates or filters by a value stored in an encrypted column:
Make sure you can access the column master keys and the metadata for the keys protecting the columns that your query runs against. For more information, see Permissions for querying encrypted columns below.
Make sure you've enabled Always Encrypted for the database connection for the Query Editor window, from which you'll run a SELECT
query retrieving and decrypting your data. This will instruct the .NET Framework Data Provider for SQL Server (used by SSMS) to decrypt the encrypted columns in the query result set. See Enabling and disabling Always Encrypted for a database connection below.
Ensure Parameterization for Always Encrypted is enabled for the Query Editor window. (Requires at least SSMS version 17.0.) Declare a Transact-SQL variable and initialize it with a value, you want to send (insert, update, or filter by) to the database. See Parameterization for Always Encrypted below for details.
Run your query sending the value of the Transact-SQL variable to the database. SSMS will convert the variable to a query parameter and it will encrypt its value before sending it to the database.
Assuming SSN
is an encrypted char(11)
column in the Patients
table, the below script will attempt to find a row containing '795-73-9838'
in the SSN column and return the value of the LastName
column, providing Always Encrypted is enabled for the database connection, Parameterization for Always Encrypted is enabled for the Query Editor window, and you have access to the column master key configured for the SSN
column.
To run any queries against encrypted columns, including queries that retrieve data in ciphertext, you need the VIEW ANY COLUMN MASTER KEY DEFINITION
and VIEW ANY COLUMN ENCRYPTION KEY DEFINITION
permissions in the database.
In addition to the above permissions, to decrypt any query results or to encrypt any query parameters (produced by parameterizing Transact-SQL variables), you also need key store permissions to access and use to the column master key protecting the target columns. For detailed information on key store permissions, go to Create and store column master keys for Always Encrypted and find a section relevant for your key store.
When you connect to a database in SSMS, you can either enable or disable Always Encrypted for the database connection. By default, Always Encrypted is disabled.
Enabling Always Encrypted for a database connection instructs the .NET Framework Data Provider for SQL Server, used by SQL Server Management Studio, to attempt to transparently:
If you don't enable Always Encrypted for a connection, the .NET Framework Data Provider for SQL Server, SSMS uses, won't try to encrypt query parameters or decrypt results.
You can enable or disable Always Encrypted when you create a new connection or you change an existing connection using the Connect to Server dialog.
To enable (disable) Always Encrypted:
Tip
To toggle between Always Encrypted being enabled and disabled for an existing Query Editor window:
Note
To run statements that leverage a server-side secure enclave when you're using Always Encrypted with secure enclaves, see Run Transact-SQL statements using secure enclaves.
Parameterization for Always Encrypted is a feature in SQL Server Management Studio that automatically converts Transact-SQL variables into query parameters (instances of SqlParameter Class). (Requires at least SSMS version 17.0.) This allows the underlying .NET Framework Data Provider for SQL Server to detect data targeting encrypted columns, and to encrypt such data before sending it to the database.
Without parameterization, the .NET Framework Data Provider passes each statement, you author in the Query Editor, as a non-parameterized query. If the query contains literals or Transact-SQL variables that target encrypted columns, the .NET Framework Data Provider for SQL Server won't be able to detect and encrypt them, before sending the query to the database. As a result, the query will fail due to type mismatch (between the plaintext literal Transact-SQL variable and the encrypted column). For example, the following query will fail without parameterization, assuming the SSN
column is encrypted.
DECLARE @SSN NCHAR(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN
Parameterization for Always Encrypted is disabled by default.
To enable/disable Parameterization for Always Encrypted for the current Query Editor window:
To enable/disable Parameterization for Always Encrypted for future Query Editor windows:
If you execute a query in a Query Editor window that uses a database connection with Always Encrypted enabled, but parameterization isn't enabled for the Query Editor window, you'll be prompted to enable it.
Note
Parameterization for Always Encrypted works only in Query Editor windows that use database connections with Always Encrypted enabled (see Enabling and disabling Parameterization for Always Encrypted). No Transact-SQL variables will be parameterized if the Query Editor window uses a database connection without Always Encrypted enabled.
If both Parameterization for Always Encrypted and the Always Encrypted behavior in the database connection are enabled for a Query Editor window, SQL Server Management Studio will attempt parameterize Transact-SQL variables that meet the following pre-requisite conditions:
SET
statements won't be parameterized.Below are examples of variables, SQL Server Management Studio will parameterize.
DECLARE @SSN char(11) = '795-73-9838';
DECLARE @BirthDate date = '19990104';
DECLARE @Salary money = $30000;
And, here are a few examples of variables SQL Server Management Studio won't attempt to parameterize:
DECLARE @Name nvarchar(50); --Initialization separate from declaration
SET @Name = 'Abel';
DECLARE @StartDate date = GETDATE(); -- a function used instead of a literal
DECLARE @NewSalary money = @Salary * 1.1; -- an expression used instead of a literal
For an attempted parameterization to succeed:
Here are the examples of Transact-SQL variable declarations that will result in parameterization errors:
DECLARE @BirthDate date = '01/04/1999' -- unsupported date format
DECLARE @Number int = 1.1 -- the type of the literal does not match the type of the variable
SQL Server Management Studio uses Intellisense to inform you which variables can be successfully parameterized and which parameterization attempts fail (and why).
A declaration of a variable that can be successfully parameterized is marked with a warning underline in the Query Editor. If you hover on a declaration statement that got marked with a warning underline, you'll 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. 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.
If SQL Server Management Studio has attempted to parameterize a variable, but the parameterization has failed, the declaration of the variable will be marked with an error underline. If you hover on the declaration statement that has been marked with an error underline, you'll get the results about the error. You can also see the complete list of parameterization errors for all variables in the Error 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 an example of six variable declarations. SQL Server Management Studio successfully parameterized the first three variables. The last three variables didn't meet the pre-requisite conditions for parameterization, and therefore, SQL Server Management Studio didn't attempt to parameterize them (their declarations aren't marked in any way).
Another example below, shows two variables that meet pre-requisite conditions for parameterization, but the parameterization attempt has failed because the variables are incorrectly initialized.
Note
As Always Encrypted supports a limited subset of type conversions, in many cases it is required that the data type of a Transact-SQL variable is the same as the type of the target database column, it targets. For example, assuming type of the SSN
column in the Patients
table is char(11)
, the below query will fail, as the type of the @SSN
variable, which is nchar(11)
, does not match the type of the column.
DECLARE @SSN nchar(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN;
Msg 402, Level 16, State 2, Line 5
The data types 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'
and nchar(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') are incompatible in the equal to operator.
Note
Without parameterization, the entire query, including type conversions, is processed inside SQL Server/Azure SQL Database. With parameterization enabled, some type conversions are performed by .NET Framework inside SQL Server Management Studio. Due to differences between the .NET Framework type system and the SQL Server type system (e.g. different precision of some types, such as float), a query executed with parameterization enabled can produce different results than the query executed without parameterization enabled.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The biggest SQL, Fabric and Power BI learning event. March 31 – April 2. Use code FABINSIDER to save $400.
Register todayTraining
Certification
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.