SQL Server 2005 Locked Logins

Question

SQL Server 2005 Locked Logins

David S.

SSC Veteran

Points: 228
More actions
November 9, 2006 at 4:15 pm

#170961

I'm very new to SQL Server 2k5, but I'm a little surprised with the implementation of the UNLOCK statement for a SQL Server standard login. It seems that the only way a sysadmin can unlock a sql server account that has been locked out because of invalid attempts is to perform an alter login statement specifying a new password for that login, in a format similar to this:
ALTER LOGIN <login_name> WITH PASSWORD = 'THE_New_Pwd' UNLOCK
What surprises me is that sysadmins are being required to provide a new password to unlock an account, when they may not be allowed to know that password in the first place (even though they can change it)
It's this the only way SQL Server allows sysadmins to unlock a sql server login? Or is there a better way? I'm hoping for something like this:
exec master..sp_unlock '<login_name>'
I'd appreciate anybody that can shed some light into this concern
The truth is just a point of view accepted by majority
David Stohlmann

Viewing 8 posts - 1 through 7 (of 7 total)

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

Venu Dukka Old Hand Points: 351 More actions · Answer 1

I think I can answer your question. The reason a login is locked is because there were three consecutive invalid login attempts right? Say a hacker is trying to do that and failed. So the user who is not aware of this goes to sysadmin and asks him to unlock the account. If the hacker is close to cracking the password for that login and the sysadmin doesn't for a change even if it is locked means that the hacker has more changes of breaking in. In essence this behavior is by design for added security.

In practice a sysadmin should do something like this:

ALTER LOGIN <login_name> WITH PASSWORD = 'THE_New_Pwd' MUST_CHANGE, UNLOCK

so that the user is forced to change it. This way the sysadmin won't know the new password of that user and also the hacker has to start all over again to guess the login's password.

For some reason SQL Server2005 is moving away from using the master stored procedures for doing routine tasks. Instead the preference is using T-SQL for these. I suggest don't use the sp_.. things if Microsoft says they are deprecated.

Venu

David S. SSC Veteran Points: 228 More actions · Answer 2

Thanks for that fast response, this really brings some meaning to that design decision. However, I'm confused as to why SQL Server will handle the management of locked accounts different than Active Directory, even though this policy for locked accounts is coming from the Active Directory security policy.

Please correct me if I'm wrong, but with Active Directory, if you get an account locked out, you can simply unlocked it without changing the password. I'm wondering if this is a change coming in the way Active Directory works, if domain admins will have to change passwords in order to unlock accounts in the future.

Thanks for the explanation, we'll make sure to keep this behavior in mind when we need to handle this kind of situations.

The truth is just a point of view accepted by majority

David Stohlmann

Venu Dukka Old Hand Points: 351 More actions · Answer 3

I did not try this. Instead of using T-SQL to unlock an account, what if we use the SSMS to unlock it? Does it still ask for a new password?

I am not familiar with Active Directory security much. It will be interesting to know the answer to your question from a domain admin.

David S. SSC Veteran Points: 228 More actions · Answer 4

Initially I tried it from SSMS and it didn't ask for a password, but it didn't unlocked the account either. It simply let me close the properties window by saying ok, and when I looked at the properties afterwards, the account was still locked.

I just tried to recreate it and if I select the script option from the properties window after I uncheck the "Locked" marked for that login, the statement that get's generated it's simply:

The truth is just a point of view accepted by majority

David Stohlmann

David S. SSC Veteran Points: 228 More actions · Answer 5

I just wanted to offer a follow up on this issue. It turns out that if you disable the password policy enforcement and then re-enable it, you can unlocked an account without changing the password. You can do this with SSMS or with the following set of commands:

USE

[master];

ALTER LOGIN <login_name> WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

ALTER

LOGIN <login_name> WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;

I hope this helps compliments this post.

The truth is just a point of view accepted by majority

David Stohlmann

Venu Dukka Old Hand Points: 351 More actions · Answer 6

Venu Dukka

Old Hand

Points: 351

December 13, 2006 at 10:24 am

#677853

That's interesting!

Thanks.

Venu

MohammedU SSCoach Points: 19644 More actions · Answer 7

This is one of the new security feature offered in sql 2005...

Until sql 2000, even sql passwords are not case sensitive...

SQL Server 2005 Security Features at a Glance

http://www.microsoft.com/sql/technologies/security/security-at-a-glance.mspx

Administrators are able to specify Microsoft Windows–style policies on standard logins so that a consistent policy is applied across all accounts in the domain.

This is on by default when you create a new login..

MohammedU
Microsoft SQL Server MVP