Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Learning is a never ending thing and just one life is not enough to learn all the things you want to learn. The ocean of knowledge is very, very deep. This blog has been created for the same objective. I write on things which I come across in my daily life and feel to share it with the people across the world. The blog publish articles on SQL Server, SSIS 2005, SSIS 2008 alongwith other useful stuff which are good to know for the professional life.
Browse by Tags · View All
Google SpreadSheet API 12
YouTube API with .NET 7
PEGA Tutorials 6
PRPC Tutorials 6
Google BigQuery 6
Google Analytics API in .NET 6
Google Cloud Services 5
Core Reporting API with C# 4
Good Data API with Picasa 4
Ms-Excel function 4

Archive · View All
June 2012 17
October 2012 12
May 2012 12
August 2012 11
March 2013 10
July 2012 10
December 2011 9
January 2012 7
September 2012 6
February 2012 6

SinghVikash Blog

SQL Server 2008: Encrypt/Decrypt data through SQL Pass-Through Query

Oct 22 2011 12:00AM by Vikash Kumar Singh   

All of us coming from SQL Sever background knows that grand father (SQL Server 6.5, 7.0 or 2000) of current SQL Server 2008R2 had no built-in mechanism for encryption/decryption of data/files/login. Built-In mechanisms for encryption/decryption are available from SQL Server 2005 onwards.

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!!!

Republished from Blog by Vikash Kumar Singh [46 clicks].  Read the original version here [3 clicks].

Vikash Kumar Singh
282 · 0% · 155


Your Comment

Sign Up or Login to post a comment.

"SQL Server 2008: Encrypt/Decrypt data through SQL Pass-Through Query" rated 5 out of 5 by 1 readers
SQL Server 2008: Encrypt/Decrypt data through SQL Pass-Through Query , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]