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: Friday, March 14, 2014 2:21 AM
Points: 912, Visits: 205
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: Monday, December 02, 2013 4:20 PM
Points: 2,608, Visits: 29

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


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 24, 2014 6:23 PM
Points: 69, Visits: 194

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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse