473,890 Members | 1,381 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How do I get encrypted data from SQL Server?

Seth Schrock
2,965 Recognized Expert Specialist
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.
Mar 18 '13 #1
13 9696
zmbd
5,501 Recognized Expert Moderator Expert
How did you encrypt the fields?
Mar 18 '13 #2
Seth Schrock
2,965 Recognized Expert Specialist
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.
Mar 18 '13 #3
zmbd
5,501 Recognized Expert Moderator Expert
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(...)
Mar 18 '13 #4
Seth Schrock
2,965 Recognized Expert Specialist
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.
Mar 18 '13 #5
mshmyob
904 Recognized Expert Contributor
@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,
Mar 18 '13 #6
zmbd
5,501 Recognized Expert Moderator Expert
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.
Mar 19 '13 #7
mshmyob
904 Recognized Expert Contributor
@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,
Mar 19 '13 #8
Seth Schrock
2,965 Recognized Expert Specialist
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?
Mar 19 '13 #9
zmbd
5,501 Recognized Expert Moderator Expert
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.
Mar 19 '13 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

1
2868
by: Bruno | last post by:
Hi, Does anybody have any experience with Oracle Data Server (the Progress product)? I would love to exchange experiences and findings :-) Thanks, Bruno
0
1310
by: rparimi | last post by:
Hi, I have encrypted data stored in a table on a DB2 database. The encryption algorithm used to store data in the table is not important. Using a perl script and the DBI module, I was able to read the values stored in the column into an array. However, I do not know what transformation needs to be applied to this data (with pack or unpack for e.g) so I can use this variable for other tasks. When I run a select on the table from a db2...
5
2270
by: Nico | last post by:
My database have 20 tables and many users. I wish to store encrypted data in 3 tables and have only 3 users have access to them, walking into tables or using forms. Can someone point me a direct help or something to read? Tnx Nico
4
4595
by: Burke Atilla | last post by:
While encrypting data with DES through CryptoStream makes encrypted data bigger than original string. if we have 8 byte key and 8 byte of data then the mode is ECB. output encrypted data is 16 bytes long. first 8 bytes is out encrypted key but last 8 byte unknown. and while decrypting if we couldn't supply this 8 bytes we couldnt decrypt data. and get exception "Bad Data" What is this 8 bytes and how can i supply this data if i have only the...
3
4481
by: Todd Gruben | last post by:
I am trying to send some encrypted data from a php application to be decoded in a .Net application. Both apps encode/decode a given string but generate different encrypted results. Anyone have any idea? Code to follow: php====> <?php // Designate string to be encrypted $string = "This is a test";
6
1664
by: Microsoft | last post by:
Im trying to do a new app where i can store most of my passwords, basically another "Password Keeper" style tool.... Question about this is what is the best way to store the data, as a plain XML or MDB is totally insecure im wondering what other choices i do have and how do i can implement them.. Forgot to mention that im using VB.net 2005... Thanks
0
2191
by: smart.bug | last post by:
hi, i have SQLserver properly running on my machine, when i open a project in VB.Net and add a SQLConnection object, while setting its connection string (when i click on combo box to choose data server) it gives this error " error enumerating data server. enumerator reports 'Unexpected error' " i need help on this very urjent please. thanx WaC
9
2651
by: Suresh | last post by:
Hi All I have one column which has data type of varchar only. in this column i want to store some secured data (I want to store this data by encrpting using db2 encryption function).But along with this encrypted data this column contains normal data also.. But in DB2 while storing encrypted data column should be defined FOR BIT DATA. In my case how should I go as my column contains both encrypted and
1
2377
by: diSangro | last post by:
I need to transfer data from one machine to another , both have HP UX B.10.20 with same usr settings. Data to be transferred are used by Unify data server r.6.1 , installed on both these machines. In other words I have to copy one DB from the first machine to the second one . My idea is to copy two data files from the 1st machine to the 2nd : ...../bin/file.db -> this the application database file and
14
6861
by: wassssup | last post by:
hi guys..im new to datagridview and im wondering is there a way to convert an encrypted data in access and show it in a datagridview? im using C# and microsoft access normally this is how to show data in datagridview: OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""); OleDbCommand command = conn.CreateCommand(); string strSQL = "SELECT * FROM Employee"; ...
0
11236
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10836
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10926
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9643
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8018
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7172
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5856
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6064
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3287
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.