sa account locked out

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

J. Cairn - 26 Oct 2007 22:41 GMT

I am getting a message that my sa account is locked out, and I can't
connect with SQL Server Management Studio.

Other posts say to log in using Windows auth, but that is not letting
me in either. I am logged into the machine as the administrator and
SQL is set to mixed mode authentication. For security, I previously
removed the Windows accounts from SQL, including builtin
\adminstrators.

Any ideas? Thanks.

Reply to this Message

Erland Sommarskog - 26 Oct 2007 22:53 GMT

> I am getting a message that my sa account is locked out, and I can't
> connect with SQL Server Management Studio.
[quoted text clipped - 6 lines]
>
> Any ideas? Thanks.

Looks like you are in dire straits. You need sysadmin to unlock sa, but
you don't have an sysadmin access available.

If you really must need access to this master database, you could open a
case with Microsoft to see if they have any tricks up their sleeve.

Else, it seems that you need to reinstall. You can attach the user
databases later on.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

thejamie - 27 Oct 2007 12:54 GMT

Did you try using the service account (if you have one)?

Signature

Regards,
Jamie

> I am getting a message that my sa account is locked out, and I can't
> connect with SQL Server Management Studio.
[quoted text clipped - 6 lines]
>
> Any ideas? Thanks.

Reply to this Message

J. Cairn - 28 Oct 2007 21:13 GMT

I was just about to try the service account (thanks for the
suggestion) but it appears that the account is now unlocked. Not sure
if it automatically unlocks after a certain period if it is locked due
to multiple password failures(?)

Reply to this Message

Erland Sommarskog - 28 Oct 2007 23:50 GMT

> I was just about to try the service account (thanks for the
> suggestion) but it appears that the account is now unlocked. Not sure
> if it automatically unlocks after a certain period if it is locked due
> to multiple password failures(?)

Probably. The policies are taken from Windows, so the same policy applies
to SQL logins.

Signature

Reply to this Message

thejamie - 27 Nov 2007 21:08 GMT

Probably a little late for this, but given the circumstance this might work
as well. Assuming the mode for login is SQL Authentication:
Change the SQL Authentication mode in the registry to Windows Authentication.

Find the LOGINMODE and change it from a 0 or a 2 to a value of 1 which is
Windows Authentication. At that point you should be able to login using the
Administrative login (I think).
Default instance:
"HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode"

Signature

Regards,
Jamie

Reply to this Message