December 21, 2008 at 10:27 pm
Hello,
This is related to SQL SERVER 2000
I have to store encrypted data in the tables and descypt it only when a user with proper authentication and autherization role will view the data.
It will be nice if some one can provide me leads on the following:
#1. How can I encrypt the data.
#2. I want the encrypted data to be descypted based on a key concept and the key will be generated based on the user name
#3. How will be sql queries be executed on the encrypted data.
Thanks,
Pavas
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
December 22, 2008 at 12:25 am
SQL 2000 doesn't have any native encryption functions. If upgrading to 2005 is not an option, you'll have to do the encryption and decryption in the front end app.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2008 at 12:48 am
You can do as Gail suggested OR you can build a DLL with your encryption/decryption logic and add it to ur SQL Server as extended stored proc and use that DLL.
To simplyfy things you can add two functions db_encrypt() and db_decrypt() with one column as a parameter which in turn calls ur dll.
I think u'll shud write seperately for user rights management. may be create a seperate roles and add users to that role. The role should have execute rights on your DLL.
December 22, 2008 at 1:35 am
ps (12/22/2008)
You can do as Gail suggested OR you can build a DLL with your encryption/decryption logic and add it to ur SQL Server as extended stored proc and use that DLL.
If you go that route, be very, very careful. Extended stored procs are tough to write and mistakes can do anything, from corrupting SQL's memory to crashing the service.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2008 at 10:47 pm
Thank you for all the information...I think I need to use the extended proc
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
December 22, 2008 at 11:09 pm
All the best. Be careful!! test that dll from an application multiple times with varied inputs and then attach the DLL(s) to the master database on a test server preferably and then to production.
December 23, 2008 at 12:44 am
Pavas Malviya (12/22/2008)
Thank you for all the information...I think I need to use the extended proc
How's your C++ skills?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 26, 2008 at 6:52 am
c++ is bad. But good at c#
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
December 26, 2008 at 7:49 am
December 26, 2008 at 8:46 pm
Incidently we've implemented the same algorithm as mentioned in the article mentioned by Steve. 128 bit blowfish algorithm.
Would suggest Pavas to download the toolkit and implement it. you can implement encryption in just few minutes 😉
December 26, 2008 at 8:53 pm
Just a follow up query on this!
If i'm writing DLLs for extended stored proc, do i need to use C++/C# or Can i use the DLL written in VB 6.0 as well?
Is it important to select a particular source language OR is it same after compilation no matter what language it is written in?
December 27, 2008 at 2:53 am
Pavas Malviya (12/26/2008)
c++ is bad. But good at c#
But you can't write extended procs (as far as I know) in C#.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2009 at 1:54 am
Sorry for the late response, Thank you very much. I will use the blow fish implementation.
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply