SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 validate a SQL Users password
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/30/2001 :  18:12:07  Show Profile  Visit AskSQLTeam's Homepage
JJ writes "This question is regarding a question answered by Scott Mitchell (http://www.4guysfromrolla.com/webtech/sqlguru/q051500-3.shtml ). Please refer to the above url for that article.

As Scott pointed out the whole point of the SQL Users password is to allow/disallow access to the server. But he also said "The password in syslogins is encrypted, so there really is no way you can get to it". The two can not be both correct at the same time. To allow/disallow access, you have to have a way to get the passwords from the sysxlogin table and compare it to the password entered by a user to validate the login.

The passwords store in sysxlogin table is encrypted using a query
convert(binary(256), pwdencrypt(@pwd))

you can find the detail of this if you open stored procedure sp_password. There must be a decrypt fuction to work with pwddecrypt() to decrypt the password, because it is not reliable if you compare values at encrypted format in sql. In another words, if you encrypt a password using

update... set password=convert(binary(256), pwdencrypt('password'))

then you want to view the result using

select * from ... where password=convert(binary(256), pwdencrypt('password'))

you will not get what you expected, I tested this on SQL Server 2000.

In one table I have a column store sensitive information. I made the column data type as varbinary and encrypted the data with pwdencrypt(), but can not have it decrypted. Did any one know the decrypt function for pwdencrypt()? Thanks.
"

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 10/30/2001 :  18:38:16  Show Profile  Visit robvolk's Homepage
Actually, you don't have to perform the comparison that way. This would be the way to do it:

CREATE PROCEDURE CheckPassword @user sysname, @password varchar(30) AS
SELECT pwdcompare(@password,password) FROM sysxlogins WHERE name=@user


Pass the user name and password as clear text and it will use the pwdcompare function to return a 1 if they match or a 0 if they don't.

It completely defeats the purpose of encrypting something if it can be easily decrypted. That's why there is no pwddecrypt() function.

BTW, I got the pwdcompare function from.....you guessed it:

The Guru's Guide To Transact-SQL



Now that I think about your question, it doesn't even make sense to validate someone's password this way, if they are using their SQL login to access your server. Simply try to connect to the server with the supplied login and password; you'll get an error message saying it couldn't connect because the password is invalid, or the connection will succeed.

Edited by - robvolk on 10/30/2001 18:41:08
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/30/2001 :  18:41:44  Show Profile  Visit AjarnMark's Homepage
quote:
The Guru's Guide To Transact-SQL



Damn, Rob, I was just about to reference that when you snuck in just ahead of me.... That's why YOU are the Pimp...

----------------------------------------------------
21 Years after Cancer... Lovin' Every Minute of it!
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 10/30/2001 :  18:53:25  Show Profile
JJ,

Here is a simple UserLogin Table using the pwdencrypt
--<TSQL>
create table UserLogin(UserID varchar(50) not null Primary Key, PWD varbinary(256) not null)
insert UserLogin (UserID, PWD) values ('DavidM',pwdencrypt('byrmol'))
insert UserLogin (UserID, PWD) values ('SimonJ',pwdencrypt('hello'))
insert UserLogin (UserID, PWD) values ('GaryM',pwdencrypt('My Cat'))
insert UserLogin (UserID, PWD) values ('PaulI',pwdencrypt('Jester'))

Select * from UserLogin where UserID = 'DavidM' and pwdcompare('byrmol',PWD) = 1
--</TSQL>

By looking at the text for sp_password you can see that SQL Server uses the pwdcompare function. That's where I got it and saved me $100.00+

DavidM
It gets windy at a thousand feet...."Tutorial D"
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/30/2001 :  18:57:00  Show Profile  Visit AjarnMark's Homepage
quote:
That's where I got it and saved me $100.00+



I don't know where you guys buy your books, but it must be because you're "down under" that they are so expensive. Here in the U.S., if you go to the link at http://www.sqlteam.com/store.asp, The Guru's Guide to Transact-SQL is only $34.96. You must be paying like $60.00 in shipping or something...

----------------------------------------------------
21 Years after Cancer... Lovin' Every Minute of it!
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 10/30/2001 :  19:03:45  Show Profile
Current $US/$AUS exchange rate (market not retail) is 0.505 US$:

Roughly $AUS 70.00 and 30 $AUS for it to travel thousands of miles.

DavidM
It gets windy at a thousand feet...."Tutorial D"
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000