making pwdencrypt/pwdcompare case sensitive

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

making pwdencrypt/pwdcompare case sensitive

Author

Topic

stevep
Starting Member

17 Posts

Posted - 2003-10-23 : 18:52:17

Whilst I know PWDCOMPARE/PWDENCRYPT are undocumented features their cost makes them attractive. However in testing I've noticed that they
are not case-sensitive:
---------------------------
DECLARE @pass varbinary(50)
DECLARE @result INT
SELECT @pass = CONVERT(varbinary(50), pwdencrypt('test'))

SELECT @result = PWDCOMPARE('test', @pass)
IF (@result = 1) PRINT 'matches lowercase'

SELECT @result = PWDCOMPARE('TEST', @pass)
IF (@result = 1) PRINT 'matches uppercase'
----------------------------

The only code I could find for testing case involved converting strings to varbinaries. I thought this was doing that?

SO is there anyway to use these undocumented features but make
passwords case-sensitive also?

TIA

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-23 : 18:56:35

You could change the collation of that column. If you've selected the default collation when you installed SQL Server, then the default collation is case-insensitive. You can specify this on a per column basis. If you don't specify it, then it uses the default.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-23 : 18:59:28

DECLARE @pass varbinary(50)
DECLARE @result INT
SELECT @pass = CONVERT(varbinary(50), pwdencrypt('test'))

SELECT @result = case when CONVERT(varbinary(50), pwdencrypt('test')) = @pass then 1 else 0 end
IF (@result = 1) PRINT 'matches lowercase'

SELECT @result = case when CONVERT(varbinary(50), pwdencrypt('TEST')) = @pass then 1 else 0 end
IF (@result = 1) PRINT 'matches uppercase'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

stevep
Starting Member

17 Posts

Posted - 2003-10-23 : 19:15:06

Ta for ideas.

Not sure how to change collation, or if that will affect a varbinary field?

Adding the CASE statements made neither of my examples match ??

Steve

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-23 : 19:22:11

You can change the collation of a column using ALTER TABLE ALTER COLUMN if:

ALTER COLUMN cannot have a collation change if any of the following conditions apply:

If a check constraint, foreign key constraint, or computed columns reference the column changed.

If any index, statistics, or full-text index are created on the column. Statistics created automatically on the column changed will be dropped if the column collation is altered.

If a SCHEMABOUND view or function references the column.

Info taken from SQL Server Books Online.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-23 : 19:26:15

The lowercase one should match.
In fact it must because it is comparing the two results from the same call.

try
DECLARE @pass varbinary(50)
DECLARE @result INT
SELECT @pass = CONVERT(varbinary(50), pwdencrypt('test'))
select @pass
select CONVERT(varbinary(50), pwdencrypt('test'))
SELECT case when CONVERT(varbinary(50), pwdencrypt('test')) = @pass then 1 else 0 end

To see what is happening

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-23 : 19:29:17

What happens if the password is sOmEtEsT?

Wouldn't you want to handle this using the collation option?

Tara

stevep
Starting Member

17 Posts

Posted - 2003-10-23 : 19:31:05

Then the plot thickens...

The results I get with the latest code:

first select = 0x21312132234E264A60274B225E4F353C
second select = 0x2131223E2C594C2D5346254B53323C39

result = 0

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-23 : 20:21:09

SQL Server's password generation code is case-insensitive by design, you will not be able to make PwdCompare case-sensitive. It also salts the password with the current time during encryption, that's why you get two different results for the same password.

You're better off not using PwdEncrypt if you really need to keep something secure, it's relatively weak as encryption goes. There are some third-party encryption tools available for SQL Server, you can try searching this site for "encryption", but I'd recommend you look more closely at restricting access via permissions. Encryption is no substitute for solid security.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-10-24 : 12:46:06

Oops guess I should have tried that.
I thought at the time this would be a trivial encryption to break if it worked as I thought.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

surajc
Starting Member

1 Post

Posted - 2007-11-23 : 01:51:19

Here is a solution that works:

SELECT PWDCOMPARE( CAST('PASSWORD' AS varbinary(100)) ,PWDENCRYPT( CAST('PaSSWORD' As VARBINARY(100) )))
Returns:0

SELECT PWDCOMPARE( CAST('PASSWORD' AS varbinary(100)) ,PWDENCRYPT( CAST('PASSWORD' As VARBINARY(100) )))
Returns:1

Explanation:

Type cast the input to BINARY before passing it to PWDENCRYPT(),and while using PWDCOMPARE().
Type casting to BINARY ensures that values of 'a' & 'A' passed to PWDECRYPT()/PWDCOMPARE() are different.

Suraj C.(SCH)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources