Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Save Your Password Expand / Collapse
Author
Message
Posted Friday, June 25, 2004 3:54 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 2:48 AM
Points: 912, Visits: 210
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/
Post #123118
Posted Tuesday, July 13, 2004 10:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 22, 2004 2:41 PM
Points: 3, Visits: 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




Post #126110
Posted Wednesday, July 14, 2004 3:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:01 AM
Points: 2,721, Visits: 33

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




Post #126218
Posted Tuesday, July 12, 2005 8:50 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, November 20, 2006 10:12 AM
Points: 78, Visits: 1

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.

Post #199906
Posted Tuesday, July 12, 2005 2:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, Visits: 1

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

Post #200209
Posted Tuesday, July 12, 2005 2:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 18, 2007 9:59 AM
Points: 12, Visits: 1

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

Post #200211
Posted Tuesday, July 12, 2005 11:51 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:18 AM
Points: 77, Visits: 217

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.

 

Post #200333
Posted Thursday, May 29, 2014 9:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2014 3:58 PM
Points: 1, Visits: 3
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
Post #1575644
Posted Thursday, May 29, 2014 10:38 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:18 AM
Points: 77, Visits: 217
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.

Post #1575687
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse