To encrypt the data/data files in SQL Server 2000 environment we had to use the 3rd party tools such as Encryptionizer from NetLib which provides database encryption and column-level encryption.
The second option is to go for certification based authentication and SSL encryption which are provided by Certification Authority such as VeriSign etc and at SQL Server client end we need to enable the Force Protocol encryption.
The third option is to developing manual ENCRYPT LOGIC for source data and DECRYPT LOGIC for target data. First run the encryption logic on the source data to encrypt the data in tables. Later to decrypt the data run the decryption logic.
Recently, I had a challenge to encrypt or decrypt the data in some specific tables stored in SQL Server 2008 and Ms-Access 2003 databases. The challenge was that Ms-Access database had link tables and these link tables were pointing to a tables and database which was in SQL Server 2008.
The requirement was to encrypt the data in some specific columns at SQL Server 2008 end but the same data was needed in Ms-Access in decrypted format. Essentially the data at SQL Server end will always remain decrypted.
This is achievable through many mechanisms. One of the solutions is SQL Pass-Through Queries in Ms-Access
What is Pass-Through Query?
A Pass-through query is jus like any other query in ms-access with the exception that it executes SQL statements against the tables in an external database (ex:- SQL Sever, Oracle etc.). It uses ODBC connection to access the data in the external database (ex:- SQL Sever, Oracle etc.). So if you have some UDF at SQL Server end you can use it with Pass-through query because the SQL statements will get executed at database level and not a ms-access level.
Encrypt data in SQL Server 2008 and Decrypt in Ms-Access using Pass-Through Query
In order to demonstrate the solution using Pass-Through query I created a database “EncryptTest” at sql server level. I created a table “Employee”. This table has two columns EmpID and EmpName.
I inserted some employee data into Employee table.
I encrypted the column EmpName using ENCRYPTBYPASSPHRASE function which is available with SQL Server 2008. After encryption the column data looks like following.
I created a Pass-through SQL Query in Ms-Access with the name SQLPassQuery. This query was to decrypt the encrypted Colum (Empname) and returns the decrypted value. To create a Pass-Through Query we need to setup the ODBC connection string in the query properties.
The next thing I did was to create a Make Table query “CreateLocalTable”. This query creates a local table “Decrypted_Local_Table”.
After running the make table query it created a local ms-access table named “Decrypted_Local_Table”. The table had EmpName column values in decrypted format.
While the SQL-Pass query returns the decrypted values at the same time sql server table had encrypted values.
I hope you enjoy the reading the post. Thanks for reading!!!
Related Article:
- SSIS: Read public data of Twitter Users?
- SSIS: How to pull Currency Rates from European Central Bank
- SSIS: How to pull Tweets from Twitter?
- SSIS: How to read Excel Meta Data?
- SSIS: How to resolve Excel Import 255 character Truncation issue?
- SSIS: Read and Export Excel data from nth Row
- SSIS: How to pull Stock Quotes from Google Finance
- SSIS: How to load data into SQL table from Google Spreadsheet?
- SSIS: How to Download images from Picasa album?
- SSIS: How to Load images into Picasa web album?
- SSIS: How to Load XML search result from Search engines like Bing, Google etc?
- SSIS: How to generate HyperLink in Excel output?
- SSIS: How to Compress/Zip your file using 7-Zip?
- SSIS: What is CheckPoints?
- SSIS: Checkpoints implementation
- SSIS: Event Handlers
- SSIS: Data Flow v/s Control Flow
