Monday, October 31, 2005

Encryption in SQL Server 7.0

After a long time I visited CNUG (Chennai .NET User Group) . When I was going through the questions I saw a question posted by "Nitin" asking Is it possible to encrypt data within data server (SQL Server 7.0)

URL of that post can be seen here >> http://groups.msn.com/ChennaiNetUserGroup/general.msnw?action=get_message&mview=0&ID_Message=9327&all_topics=0

My response to that post:

There are two undocumented functions in SQL Server (since SQL Server 6.5). They are:

1. Pwdencrypt and
2. Pwdcompare.

Pwdencrypt -- It uses one way encryption. That is, it takes a string and returns a encrypted version of that string. Pls note that in one way encryption you can't get back the actual string (i.e., you can decrypt the encrypted data).

Pwdcompare -- It compares an unencrypted string to its encrypted representation to see whether they match.

Since it is undocumented functions there is a possibility that MS can remove or change it at anytime without prior notice. So use it at your own risk.

Code snippet:

---Sample table to hold the encrypted password
Create Table tstSQLEncryption(EmailId varchar(50),Pwd varbinary(255))

--Encrypt password and save it into the table
Insert Into tstSQLEncryption (EmailId, Pwd) Values ('vmvadivel@yahoo.com', pwdencrypt('test'))

--Compare the encrypted value and the value you give newly
Select Pwdcompare('test',Pwd ) from tstSQLEncryption where EmailId = 'vmvadivel@yahoo.com'

If the above select statement returns 1 then it means the value you entered and the value already encrypted are one and the same. Hope this is helps!

1 comment:

Param said...

PwdCompare function is not case-sensitive hence, i can trace password with either case given and vice-versa.