How do I get encrypted data from SQL Server?

[Seth Schrock]

I have created a test database in SQL Server and I have encrypted a few fields to test the connection between Access and SQL Server with encryption. Just viewing the table, there is no data in the encrypted fields. Not even encrypted mumbo jumbo data. So my problem is that I can't get the encrypted data into Access in order to decrypt it. I have spent the last few days searching online, but I haven't been able to find anything.

[zmbd]

How did you encrypt the fields?

[Seth Schrock]

I guess that would be an important detail :) I'm not exactly sure the correct technical terms, so if I'm unclear, here is the link that I followed to do it: SQL Server 2005 Database encryption step by step. I created a master key and a certificate. I then created a symmetric key (using 3DES as AES isn't available on XP, which is what my test laptop is running). I then ran an UPDATE query to encrypt the existing field into a new field.

I hope this helps.

[zmbd]

Honestly, I've not used this part of the SQL server.
I've found this: Encrypt a Column of Data SQL Server 2012

This also looks interesting; however, I've not finished reading thru it yet: Database Encryption in SQL Server 2008 Enterprise Edition SQL Server 2008

Finally theres this article as related to V2003
SQL Server 2008: Encrypt/Decrypt data through SQL Pass-Through Query

So it appears that you may end up either haveing to create a stored procedure on the SQL Server to push the encypted data between the front end and back ends or you will need to create a passthru query which basically sends the "raw" SQL to the server bypassing the Jet/ACE database engine.


[Z:{Edit: Found another article that reads fairly well}]
SQL SERVER – Introduction to SQL Server Encryption(...)

[Seth Schrock]

This might sound like a stupid question, but I'm currently connected to the SQL Server via ODBC driver. Does this exclude the option of using passthru queries? In my research for how to connect Access to SQL server, it seemed like those were the two options, so I didn't know if they could be combined.

[mshmyob]

@seth

I have not read all the links posted and I quickly looked at your link.

The encryption that is used by SQL Server is called TDE (Transparent Data Encryption). All the encryption and decryption is actually done by the SQL Server Database Engine and the client should be irrelevant (hence Transparent).

The users who access the Server Instance should automatically see the decrypted data if they have the proper security rights. I am assuming you are using the Windows Authentication mode.

I have never tried using Access to access (no pun intended) a TDE enabled SQL database but I don't see that it should be a problem since the encryption/decryption is internal to the SQL Server and access is granted by the end user's access rights set up in Windows server (Windows Authentication mode). You may have Mixed mode but I still don't think that would be a problem either.

I would have to try it myself to be sure of what I am saying but don't have the time for a few days. You could try Googling around with the keywords TDE and see if anything pops up.

PS: I just thought of something... if you are trying to load the encrypted data into your Access database that may be the problem there. The Server engine should not allow you to copy the encrypted data from the SQL server as that would defeat the purpose of encryption. You should only be able to view or modify on the SQL server using your client app.

cheers,

[zmbd]

mshmyob
You really should have read the articles.
The type of encryption being used can be either TDE, columnar, or hybrid and can be setup in a number of ways - some of which require the password and/or session certificate to be entered/invoked.

There was a thread in a related forum talking about how the Access front end is initially linked to only a read only table wherein the encrypted connections strings were stored requiring a user name and password to decrypt the connection string used to connect to the TDE back end whereupon a stored procedure was used to invoke the session key against the database. I would liked to have posted that link here too as it was from one of the SQL forums; however, it is a competitor to Bytes so that would have been against the TOU.

[mshmyob]

@z

I have never heard that a TDE enabled database needs an encrypted connection string. I am also assuming his database is just encrypted and not the transmission link using something like IPSec or some other transmission encryption/decryption protocol.

So I still stand behind my previous post unless you can show otherwise.

cheers,

[Seth Schrock]

The pass through query worked to get the data, but I can't edit or add records to it. Do I have to use UPDATE and APPEND queries for this?

[zmbd]

mshmyob:
If you would have followed Seth's link in post #3 (... SQL Server 2005 Database encryption step by step ...), you would have discovered that he didn't implement TDE. Instead the article implemented column/field level encryption.

Also, TDE was introduced in SQL Server 2008 and is NOT available in SQL Server 2005 which Seth's link clearly states.

So, you may very well stand by your prior post, and as far as SQLS2008 it may very well be correct; however, in this case even if Seth is using SQLS2008, TDE was not implemented and therefore not available for Seth.

The next thing is that even if Seth is using SQLS2008 with TDE enabled, there is still a need for the cell/column level encryption for sensitive information. For example, say the DBA had a bad day and didn't change the defaults (I know... just work with it ;-) ) Mr. Blackhat comes along (or young Mstr. SkrptKddy) and finds this weak link, authenticates to the server and voila - here's the sensitive information in plain text served on a platter.

As for:

z: Access front end is initially linked to only a read only table wherein the encrypted connections strings were stored requiring a user name and password to decrypt the connection

m: I have never heard that a TDE enabled database needs an encrypted connection string

I think you missed the decrypt. Actually, I'm not sure you followed what was being done... so let me try again:
In the forum I was refering to in my post (#7) the person posting had an issue wherein the connection strings for the queries are available in plain text. Those strings have the password required for the server connection.
In order to provide a higher level of security, the individual contrived a scheme wherein the user's authenticated to a database that contained only the user name and the connection string; however, they encrypted the string so as to prevent the user from seeing the database password.
--- see where this is going ? ---
the connection string is encrypted at the field/cell level using the user name and password. Once the string was/is decrypted it is pushed into the query via code; thus, never available to the end user.

[zmbd]

Seth:
Just in case you can move to SQLS2008: SQL Server 2008 Transparent Data Encryption getting started

Notice the comments/feedback: Scroll down... there is a question wherein the poster queries how to prevent users from seeing the plain data. Obviously missing the point that with TDE the data is (as mshmyob points out and so does the author) is only encrypted on disk. The DBA/Developer has to ensure that user access is setup correctly AND if a particular data field should not be accessible to everyone that the field is held in a data table that is 1:1 with the related table and is restricted by user/group AND/OR has implemented the column/field level encryption.

You might find this worth a watch too:
MSDN Blogs > Microsoft SQL Server Tips & Tricks > Transparent Data Encryption (TDE)

[Seth Schrock]

SQL Server 2005 is what we already have, so I was just using it. Moving to SQL Server 2012 has been discussed, but my job is to use what is provided, not make suggestions to buy new software. We have several other commercial databases running on the SQL Server and those take precedence over my databases.

Anyway, I did get the data to be unencrypted in the query. However it doesn't allow me to edit it or add new records. Does this mean that I would have to use UPDATE and APPEND queries to edit and add records?

[zmbd]

Something of a pain with this... woefully, I haven't used the encrypted field/cell in SQLS so other than the links I've provided you, and the fact that you will have to use the pass thru queries to handle the encrypted information, sadly, I'll not be of much more help.
Hopefully Rabbit, NeoPa or one of the others will be able to provide more help.

[Seth Schrock]

I got it to work. What I ended up doing is to have the form based on a regular query. This allows me to add and edit records like normal. I then use unbound controls for the fields that I have encrypted. In the form's OnCurrent event, I use the DLookup function to get the values out of my Passthrough query for the current record. In each control's AfterUpdate event, I have it do an UPDATE passthrough query to set the new values in the table. Not the cleanest method probably, but it does work.