Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PWDENCRYPT and DataType of column

Posted on 2005-04-19
7
Medium Priority
?
1,458 Views
Last Modified: 2008-01-09
Hello,

I was wondering what the data type needs to be for the password column in order to use the PWDENCRYPT and PWDCOMPARE functions of sql server?

Also, wondering whether I am on the right right track her with the following syntax:

SELECT     *
FROM       Users
WHERE     (Login = 'Charlie') AND (1 = pwdcompare('mySecretPassword', Password))

Thanks

0
Comment
Question by:brdrok
7 Comments
 
LVL 7

Author Comment

by:brdrok
ID: 13816069
currently the DataType for the column "Password" is VARCHAR(50).  Want to make sure that this ist he right column before harassing the dba to change the column type.

0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13816149
Based on this link:

http://www.windowsitpro.com/Article/ArticleID/9809/9809.html

the encrypted password will be varbinary(255)
0
 
LVL 5

Accepted Solution

by:
obahat earned 1400 total points
ID: 13816298
Actually, the password is stored as varbinary(256) and not 255.

The passwords are stored on the master..sysxlogins table.
Simply run the following query to get the data type of the password column:

select * from syscolumns where id = object_id('sysxlogins')

You'd see that the xtype = 165 (varbinary), and length = 256.

Regarding your query- it seems fine, although you haven't really specified what it is that you are trying to do.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Author Comment

by:brdrok
ID: 13816556
thanks for your reply obahat,

SELECT     *
FROM       Users
WHERE     (Login = 'Charlie') AND (1 = pwdcompare('mySecretPassword', Password))

is suppose to return a row if the login and the password matches.  nothing terribly fancy.

i execute this statement:
select * from syscolumns where id = object_id('sysxlogins')

but only got a blank row.  Do not have Enterprise Manager nor Query Analzyer, thus, I am kinda working blind.  Maybe also because I do have restrictive access to my database.  


"You'd see that the xtype = 165 (varbinary), and length = 256"
can you please explain what"xtype" is.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 13817952
This works for me:

Declare @Users table (
            Login nvarchar(50),
            [Password] nvarchar(128))
Insert      @Users (Login, [Password]) Values ('Charlie', PWDEncrypt('mySecretPassword'))

SELECT  *
FROM      @Users
WHERE      Login = 'Charlie'
      AND PwdCompare('mySecretPassword', [Password]) = 1
0
 
LVL 7

Author Comment

by:brdrok
ID: 13817995
heya,

thanks...sorry...should have closed this question earlier.i asked the dba to change the datatype from varchar to varBinary and it works like a charm.  

0
 

Expert Comment

by:SECdev
ID: 27610612
Why is it so particular about data types?... I wonder...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question