SQL Server Table Data Encryption in SQL 2K

SQL Server Table Data Encryption in SQL 2K

Post by Michael Go » Sat, 11 Oct 2003 06:51:30


Is it possible for SQL Server 2000 to encrypt data for a
particular column in a table. I would like to prevent
someone who has select rights on a table from seeing the
actual contents of a particular column. Similar to
selecting from the sysxlogins table and seeing an
encrypted password.
 
 
 

SQL Server Table Data Encryption in SQL 2K

Post by Vern Rab » Sat, 11 Oct 2003 07:02:34

Why not remove all permissions on the table and create a
view that doesn't include the column?

HTH
Vern

 
 
 

SQL Server Table Data Encryption in SQL 2K

Post by Vishal Par » Sat, 11 Oct 2003 07:03:20


There aren't any documented functions in SQL server.
But there are 2 undocumented password encryption function pwdencrypt/pwdcompare by which you can
encrypt and decrypt a string.

Following is the example.

declare @x varbinary(500)
declare @ret int
select @x=convert(varbinary(500),pwdencrypt('check'))
select pwdcompare('check',@x, 0)

You can use pwdencrypt in a update/insert trigger to
encrypt the values of a table's column. While decrypting
you'll have to use pwdcompare which will return 0 or 1 on
the basis of failed or successfull comparison respectively.

for more information visit
http://www.yqcomputer.com/

since these functions are undocumented i wont suggest you using them. you may go for third party
encryption, visit following url

http://www.yqcomputer.com/
 
 
 

SQL Server Table Data Encryption in SQL 2K

Post by Michael Go » Sat, 11 Oct 2003 07:21:29

Vishal,

Thank you for this solution, I think it might work for me.

pwdencrypt/pwdcompare by which you can
respectively.
using them. you may go for third party
 
 
 

SQL Server Table Data Encryption in SQL 2K

Post by Vern Rab » Sat, 11 Oct 2003 07:36:22

Not sure how you plan on using it, but Vishal said
something that may be a little misleading. The pwdcompare
function does NOT decrypt. It simply returns a 1/0 (for
true/false) if you pass in the correct decrypted string.
Once encrypted with pwdencrypt, there is no function in
SQL Server to decrypt it.

HTH
Vern

function
you
 
 
 

SQL Server Table Data Encryption in SQL 2K

Post by Tom » Sat, 11 Oct 2003 08:23:15

Another option for you would be to use a third party tool. We use wiCrypt and
wiHash from www.whamware.com. You may want to search some of the groups for
'encrypt' to find other solutions.