Save Your Password

Question

Save Your Password

Dinesh Asanka

SSChampion

Points: 11058
More actions
June 25, 2004 at 3:54 pm

#85310

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/dasanka/saveyou

My Blog: http://dineshasanka.spaces.live.com/

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Arunasg Grasshopper Points: 13 More actions · Answer 1

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

NPeeters SSChampion Points: 12502 More actions · Answer 2

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).

CDJorg Old Hand Points: 342 More actions · Answer 3

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.

Kenneth Lee Ten Centuries Points: 1293 More actions · Answer 4

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);

KEN M SSC Rookie Points: 42 More actions · Answer 5

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

mrdenny SSCrazy Points: 2898 More actions · Answer 6

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.

M.A.Wolf SSC Rookie Points: 39 More actions · Answer 7

Hi,

I moved an SQL Server 2000 database to another machine using backup/restore. On the new machine it was attached to SQL Server 2008.

The database contains an encrypted password which was encrypted on SQL Server 2000 using PWDEncrypt function. Everything seems to work fine on SQL Server 2008, apart from the fact that PWDCompare is not working correctly any more.

My guess is that the algorithm used in encryption has changed or that the key is different, but from what I can tell, SQL Server 2000 doesn't have a MASTER KEY I can backup and restore.

Does anyone know what I could do?

Many thanks,

Markus

mrdenny SSCrazy Points: 2898 More actions · Answer 8

You would need to research the encryption algorithm that Microsoft used in SQL 2000 and build your own decryption process and have it decrypt the values then reencrypt the values using a normal supported encryption method. Given that SQL 2000 is 14 years old I'm sure that this information is out there somewhere.

Another option would to force all your users to reset their passwords. I would highly recommend changing the encryption that you use to one of the supported encryption options as you do this.

Your only other option is to continue to run the application in SQL Server 2000.