I have a SQL Server database with 'Always Encrypted' columns. I have the certificate to decrypt the columns installed on my local computer and can see them unencrypted via MS Access/ODBC. But if I run a query in TDP that selects any of the encrypted columns, they appear as {Excluded}. Can TDP unencrypt columns using the certificate?
Thanks
Doesn't appear that TDP's connection pane allows for choosing such a cert, unless that's done on the back end of the database for certain user IDs.
If we don't support it directly, Quest R&D first of all, should note this as an enhancement suggestion.
Second, you may have a couple of options:
-
Specify in the TDP Editor the "Open key" and "Decrypt by cert" commands logic to execute before issuing your SELECT statement to see the data.
-
Use the same ODBC driver (that's already configured with the cert) in TDP to connect to SQL Server. In other words, don't choose the "SQL Server" connection type, but rather choose ODBC, and point to your cert'd driver.
-
As a remote third possibility (run this one by your admin to see if it might work?) there is a way to specify an initial script execution as part of a connection process. See the screen snap below. There may be a way to take care of keys and certs first thing as part of the connection process.
Thanks much Gary. I tried your option 2 to use the same driver that I am using with Access but it did not work. What did work, partially anyway, is to create a Generic ODBC connection and then select the DSN that I am using with Access. This allows me to see the encrypted columns when I SELECT them.
I cannot include these columns in the WHERE clause however. But this doesn't work in Access either. So it's probably a driver issue. Interestingly I can query on these columns using Java and SQL Server's jdbc driver.
Hi,
You should add additional parameter to Toad connection Column Encryption Setting=Enabled in Connection dialog
Same, as in SSMS 17
You can read about querying to encrypted columns here. After quick look on this article, try declare variable for where clause like bellow:
DECLARE @SSN nchar(11) = '795-73-9838'
SELECT * FROM [dbo].[Patients]
WHERE [SSN] = @SSN;
SSN is encrypted column
I hope it helps,
Vasiliy
Thanks Vasiliy. I get the following error after adding the 'Column Encryption Setting':
Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'.
I have the certificate installed under the local user and local machine accounts.
When I try to DECLARE a variable and use it in a search on the working Generic ODBC connection that I have I get this error:
*[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Encryption scheme mismatch for columns/variables '@FN'. The encryption scheme for the columns/variables is (encryption_type = 'PLAINTEXT') and the expression near line '2' expects it to be (encryption_type = 'DETERMINISTIC', *
I wonder if this will only work in SSMS.
Could you try standard Toad Sql Server connection?
Due article bellow it will work because of Toad Sql Server connection uses standard .NET MSSQL driver for connect to db
Vasiliy, still getting an encryption error. Here is my set up:
And my SELECT:
DECLARE @FN NCHAR(3)='KEV';
SELECT first_name_3 from dbo.customers WHERE first_name_3 =@FN;
The error:
*Encryption scheme mismatch for columns/variables '@FN'. 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 = *
Peg
Hi peg.flick,
QQ if you don't mind -- could you briefly explain how you were able to decrypt column via MS Access and ODBC. I have an Azure SQL Server Back End/MS Access Front End, and don't seem to be able to decrypt the always encrypted columns on the MS Access side.
Any tips you have, or perhaps a resource you can guide me to would be greatly appreciated. Thank You!