Blank password check

Is there any way to check whether the password for existing sql authentication login is blank? (except trying to connect)

Where can I find information about how the passwords are stored internally in SQL Server?

which version r u talking about. if its 2005 you can do that i believe.

credentials are stored in sysxlogins system table

Madhu

|||Only hashes are stored, you won′t find them in clear text. For SQL Server 2005 use password policies to ensure a certain password complexity.

Jens K. Suessmeyer:

http://www.sqlserver2005.de

|||Yes I understand it, I just wonder how some tools discover logins with blank passwords (http://www.ngssoftware.com/products/database-security/ngs-squirrel-sql.php)|||

Oh, actually it seems to be fairly simple,

I just call ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N'' and if it succeed -- login myLogin has a blank password. Or am I missing something?

|||

sir..... you are resetting the password to null. it will change the password, it will not tell u whether its blank or not. But it will make the password blank evenif its not blank. Not atall the correct method

Madhu

|||No, trying to change the password seems to be a bit offensive :-)

I wrote this short code snippet for you, tell me if it works for you, it scans all reachable /discoverable servers and test all SQL Server logins:

Code Snippet

foreach (DataRow dr in SmoApplication.EnumAvailableSqlServers().Rows)

{

Server srv = new Server(dr["Name"].ToString());

foreach (Login l in srv.Logins)

{

if (l.LoginType == LoginType.SqlLogin)

{

SqlConnection conn = new SqlConnection(string.Format("Data Source={0};Initial Catalog=master;User Id={1},Password={2}",

srv.InstanceName,

l.Name,

string.Empty));

try

{

conn.Open();

Console.WriteLine(string.Format("The userid {0} has an empty password.", l.Name));

}

catch (SqlException ex)

{ }

finally

{

if (conn.State == ConnectionState.Open)

conn.Close();

}

}

}

}

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thanks Jens,

the way you suggest works fine I think.

|||

Madhu,

I cannot agree with you.

See the following script:

Code Snippet

CREATE LOGIN myLogin WITH PASSWORD = N'IT IS NOT A BLANK PASSWORD!', CHECK_POLICY = OFF

BEGIN TRY

-- this statement will FAIL because we specified invalid old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

END TRY

BEGIN CATCH

PRINT 'Password is NOT blank!'

END CATCH

-- this statement will actually change the password

ALTER LOGIN myLogin WITH PASSWORD = N''

PRINT '//Password was changed to blank//'

-- so now password is blank

BEGIN TRY

-- this statement will SUCCEED because we specified right old (current) password

ALTER LOGIN myLogin WITH PASSWORD = N'' OLD_PASSWORD = N''

-- but actually nothing will happen because new password is the same as old one.

PRINT 'Password is BLANK!'

END TRY

BEGIN CATCH

END CATCH

DROP LOGIN myLogin

The idea is that ALTER LOGIN fails when called with OLD_PASSWORD option and invalid old (current) password given.

I think that it is even more effective way to check than trying to logon on server with <loginName, blankPassword> credentials.