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
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.
---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 ('firstname.lastname@example.org', pwdencrypt('test'))
--Compare the encrypted value and the value you give newly
Select Pwdcompare('test',Pwd ) from tstSQLEncryption where EmailId = 'email@example.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!