none
MS SQL Server 2008 sa account locked out and Windows Authentication login is greyed out

    Question

  • Hi,

    The sa account got locked out and even though mixed mode authentication is enabled. When I select "Windows Authentication" the Username and Password fields are greyed out. The username field contains my current windows logon and the password field is blank. I am logged in with a windows administrator account though I hadnt explicitly added this account to the SQL server sysadmin role.

    I have also tried "Run as Administrator" with the SQL server management studio but the problem persists

    Please note that SQL server was installed with another windows administrator account. Could this be the reason ? Will it work if I login with the account with which SQL server was installed ?

    Thursday, May 12, 2011 4:57 PM

Answers

All replies

  • Hello,

    When selecting to connect through SSMS using the windows authentication option, the current logged in user (or the user specified using /runas) will be used to authenticate to SQL Server. Those boxes should be greyed out when this option is chosen.

    If it is SQL Server 2005 that you are attempting to gain access to the Builtin\Administrators group was added by default whereas in 2008 it is not, so being a member of the local admins groupin this case may not help you.

    If you can't access SQL Server you have a few options:

    1. If SQL Server is running under a domain or local account, attempting to login and connect through that using windows authentication should give you access.

    2. If SQL Server is running under a local service account and you have local admin to that server you can follow these directions to start SQL server in sungle user mode which will give the local administrators access to the instance. From there you can add/remove/unlock logins. http://msdn.microsoft.com/en-us/library/ms188236.aspx

     

    Hope this helps,
    Sean

    Thursday, May 12, 2011 5:40 PM
  • In addition to the link provided by Sean, there is a more specific topic Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out http://msdn.microsoft.com/en-us/library/dd207004.aspx

    My guess is that SQL Server was installed with only Windows Authentication, and then was later changed to mixed mode. That leaves the sa account disabled, unless you actualy go and enable it. For steps to enable the sa account, see How to: Change Server Authentication Mode http://msdn.microsoft.com/en-us/library/ms188670.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Thursday, May 12, 2011 5:59 PM
  • Hello Vortexmak,

    I will try to answer your queries individually:

    • When I select "Windows Authentication" the Username and Password fields are greyed out. The username field contains my current windows logon and the password field is blank. - This is the behaviour of login dialog box.This option is only editable if you have selected to connect using SQL Server Authentication.
    • Please note that SQL server was installed with another windows administrator account. Could this be the reason ? Will it work if I login with the account with which SQL server was installed ? - You would have to login to windows with same login with which you installed SQL Server. Then you could give the sysadmin/desired permission to the user/id that you would want to.
    • The sa account got locked out and even though mixed mode authentication is enabled. - ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK

    I hope that helps. If you have more queries then please keep us posted but if it is answered then please mark it resolved.

     


    Friday, May 13, 2011 1:21 PM