Comparing Passwords

Question

Comparing Passwords

Ed Mlynar

Mr or Mrs. 500

Points: 593
More actions
April 8, 2011 at 1:31 pm

#234679

I have an interesting situation with a third party application used by multiple sites. The application uses a hardcoded database name and login, but the vendor is able to change the password.
To prevent one site from looking at another site's database, we create a SQL instance for each site. So now I have a server with six SQL instances. All instances have five databases and the application login.
The problem is that I have no trust in the vendor. I would like to audit the passwords for the application login on each instance.
I wrote the following query (named have been changed to protect the innocent):
selectINST1.password, INST2.password, INST3.password
from[SERVER\INST1].master.dbo.syslogins INST1
left join [SERVER\INST2].master.dbo.syslogins INST2 on INST1.name = INST2.name
left join [SERVER\INST3].master.dbo.syslogins INST3 on INST1.name = INST3.name
whereINST1.name = 'applogin'
and(INST1.password = INST2.password
OR INST2.password = INST3.password
OR INST3.password = INST1.password)
The problem is that when I tested it on a login I created on each instance that has the same password, it doesn't return anything.
Does anyone know how to compare one encrypted password with another?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Patrick_Fiedler SSCommitted Points: 1904 More actions · Answer 1

Hi,

when I correctly remember, part of the encryption mechanism is to include the instance specific guid which is created after setup the instance, so it is not possible to directly compare the passwords between the instances.

But you can try to analyse the application that logs in, perhaps you can read the hard coded password in the binary code.

Or you can track the tcp packets of the application and try to get the password from this.

Greets

Patrick Fiedler

[font="Arial"]Kind regards,

Patrick Fiedler
Consultant

Fiedler SQL Consulting

www.fiedler-sql-consulting.de
[/font]

Lowell SSC Guru Points: 323518 More actions · Answer 2

I'm assuming we are talking about SQL server login passwords, and not an encrypted value in a table that is used by the app.

Ed you can compare if the hash of the password is the same as the hash of a known password, if that helps.

so if you know the password on one of the logins, you can compare it to the others.

this is from a post to compare known, weak passwords to the current logins to see if anyone is using 'asdf' or whatever.

DECLARE @WeakPwdList TABLE(WeakPwd NVARCHAR(255))

INSERT INTO @WeakPwdList(WeakPwd)

SELECT ''

UNION ALL SELECT '123'

UNION ALL SELECT '1234'

UNION ALL SELECT '12345'

UNION ALL SELECT 'abc'

UNION ALL SELECT 'default'

UNION ALL SELECT 'guest'

UNION ALL SELECT '123456'

UNION ALL SELECT '@@Name123'

UNION ALL SELECT '@@Name'

UNION ALL SELECT '@@Name@@Name'

UNION ALL SELECT 'admin'

UNION ALL SELECT 'Administrator'

UNION ALL SELECT 'admin123'

UNION ALL SELECT 'asdf'

UNION ALL SELECT 'asdfasdf'

UNION ALL SELECT 'sa'

UNION ALL SELECT 'biteme'

UNION ALL SELECT 'hds'

UNION ALL SELECT 'hdssa'

UNION ALL SELECT 'password'

UNION ALL SELECT 'pass'

SELECT t1.*, REPLACE(t2.WeakPwd,'@@Name',t1.name) As [Password]

FROM sys.sql_logins t1

INNER JOIN @WeakPwdList t2 ON (PWDCOMPARE(t2.WeakPwd, password_hash) = 1

OR PWDCOMPARE(REPLACE(t2.WeakPwd,'@@Name',t1.name),password_hash) = 1)

WHERE t1.is_policy_checked = 0

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!