It is very interesting article.

I ran into trouble couple times where I set a password to a DTS package and forgot it so I had to redo the package completely. I may make use of this suggestion but my only question is: after encrypting the password, to retrieve it, you have to do a comparison for which you have to use a plain text password value. So, somewhere this password should always be stored in plain text. Or did I not read the article correctly?

Thank you,

Aruna

Nice article. Certainly something that is forgotten all too often.

Two things to bare in mind :

1. PwdEncrypt is not a very strong encryption method. So to store very sensitive passwords, I don't believe it is the right choice.

2. You still have the issue that the password is sent to the server in the clear over the dataline. So, to have a truly secure system, it is best to encrypt the password client-side or use some sort of encrypted transport (SSL).

Although it is 1) undocumented, and 2) less secure than other libraries, it is the only method that SQL Server provides, and is certainly better than storing passwords in clear text.

Although the password must be transmitted in clear text, it would need to be whether or not you encrypt the password in the database.  What these functions do, however, is eliminate or reduce a point-of-failure: an unauthorized database user reading the passwords from the database (or a backup, which happens too often for comfort).

A better method for an enterprise-level solution would be to handle all the encryption and comparisons in the application itself, using more secure libraries, and only store and retrieve the encrypted value in the database.

Aruna, I hoped you picked up from the other comments that the clear text password is given by the person trying to get access.

The Improvements section isn't clear.  Are you asking SQL Server to improve their undocumented software, or are you saying that the clear text password given by the person is appended or prepended with a mask in the application layer?

That still doesn't stop a sniffer from seeing the password if you aren't using SSL.

I also wondered why you would use pwdcompare, so I ran the following tests and found out what it does.  Still not sure why you would use it:

if (pwdencrypt('pwdencrypt')=pwdencrypt('pwdencrypt')) print 'exact match found'
else print 'exact match not found'
if (pwdencrypt('pwdencrypt')=pwdencrypt('Pwdencrypt')) print 'case match found'
else print 'case match not found'
PRINT pwdcompare('Pwdencrypt', pwdencrypt('pwdencrypt'), 0);

While we are on the subject of passwords, what would be a good solution for running dts packages that have a password in a AS400 ODBC connection that change every 30 days? Is there another way that I can do that without manually opening the all the packages and putting in the new password? Thanks for the help.

Ken

kennyskasa@yahoo.com

The problem with using the pwencrypt function is that Microsoft can and does change the encryption algorithm at will.  It was changed from SQL 6.5 to SQL 7, and again from SQL 7 to SQL 2000, and again from SQL 2000 to SQL 2005.  It can also be changed at service pack revisions.

This means that if you start encrypting your data within SQL 2000, and you upgrade to SQL 2005, your stored encrypted version will no longer match the value that was encrypted.