none
SA account Locked out

    Domanda

  • I have been using SQL2000 for a number of years and the company I work for needs a new system so I decided to install SQL2005.

    I installed SQL2005 without any problems and set a complex password for SA. I tried to log on and had to make several atempts as I remmebered the password after the SA account got locked out with the following message:

    Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.  (Microsoft SQL Server, Error: 18486).

    The problem I have is I cannot log onto SQL at all as it is not logging on with windows authentication either!

    How do I unlock the SA account or do I have to reinstall SQL?

    Thanks

    Brian

    lunedì 20 marzo 2006 09:49

Risposte

  • For unlocking SA, you just have to connect as a machine administrator to the server, as you observed. The machine administrators are by default granted access to SQL Server. Once you connect as an administrator, you can issue:

    ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK

    Thanks
    Laurentiu

    lunedì 20 marzo 2006 19:16
    Moderatore
  • Hi,

    nice message for you: No you don´t have to reinstall the database. :-)

    You can set the mixed mode of SQL Server (if you only activated SQL Server authentication) via the registry:

    Default instance:
    "HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode"

    to 2 for mixed-mode or 1 for integrated.

    After restarting the service you should be able to logon with a local administrator and reactivate the sa account again.

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    lunedì 20 marzo 2006 11:35
    Moderatore
  • I have managed to logon with windows authentication!!(Add the Logon Account to the Adminstrator group)

    So I have managed to sort out the SA account.

    Brian

    lunedì 20 marzo 2006 11:25
  • You can also use Management Studio to change this setting: open Object Explorer, right click on the Server and select Properties from the menu. On the resulting dialog select the Security page. Use the radio button under Server Authentication to select either Windows or mixed authentication mode.

    Thanks
    Laurentiu

    lunedì 20 marzo 2006 19:06
    Moderatore
  • I guess you are qoeking on a Windows 2003 Server system. Changes of SQL 2k5 made us able to use local or domain password policies on SQL Server Accounts. So if you have defined a password policy on the local machine or on the domain controller (of the machine is within a domain) that any accounts have to be disabled / locked down after X failed attemps to login, then probably thats your problem.

    What do do ?

    -Apparantly someone is trying to bruteforce your system (or is just guessing the wrong password). Try to log these attempts, as also unsuccessfull attemptscan be logged in SQL Server, just navigate to the securities tab on the server properties in Managment Studio and change the audit flag for logons.

    -Meanwhile you have to prevent that the account is locked out every time (although I got used not to use the sa account due to security reasons, everyone know the Account "sa", so that just have to hack the password). You can either disabled the policy on machine / domain level /which is applied to all user accounts in SQL Server), or you disabled the policy inheritance on the User properties pane:

    http://www.sqlserver2005.de/SharedFiles/Passwordpolicy.jpg

    Anyway, for my best practices, I would suggest to create another user, which has sa right but has another name, why is not stating clearly that it has the appropiate permissions and then to use this one.

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    martedì 18 aprile 2006 07:08
    Moderatore
  • You can also rename sa to prevent attackers from knowing what account to try:

    ALTER LOGIN sa WITH NAME = 'new_name_for_sa'

    To make sure that sa is locked out because of attempts to break the password, you can check the server error log. If you see a lot of messages about invalid connection attempts for sa, then you know someone is attempting to break in using that account.

    If you want to use a different account than sa (you don't want to rename sa for some reason), then you can disable the sa account using:

    ALTER LOGIN sa DISABLE

    This basically marks the login as unusable for authentication. Attempts to logon using the account will still show up in the error log, but they cannot succeed.

    Thanks
    Laurentiu

    martedì 18 aprile 2006 18:08
    Moderatore

Tutte le risposte

  • I have managed to logon with windows authentication!!(Add the Logon Account to the Adminstrator group)

    So I have managed to sort out the SA account.

    Brian

    lunedì 20 marzo 2006 11:25
  • Hi,

    nice message for you: No you don´t have to reinstall the database. :-)

    You can set the mixed mode of SQL Server (if you only activated SQL Server authentication) via the registry:

    Default instance:
    "HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode"

    to 2 for mixed-mode or 1 for integrated.

    After restarting the service you should be able to logon with a local administrator and reactivate the sa account again.

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    lunedì 20 marzo 2006 11:35
    Moderatore
  • You can also use Management Studio to change this setting: open Object Explorer, right click on the Server and select Properties from the menu. On the resulting dialog select the Security page. Use the radio button under Server Authentication to select either Windows or mixed authentication mode.

    Thanks
    Laurentiu

    lunedì 20 marzo 2006 19:06
    Moderatore
  • For unlocking SA, you just have to connect as a machine administrator to the server, as you observed. The machine administrators are by default granted access to SQL Server. Once you connect as an administrator, you can issue:

    ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK

    Thanks
    Laurentiu

    lunedì 20 marzo 2006 19:16
    Moderatore
  • I have a repeating problem - my sa account is locked out quite regularly. It seems to occur when I restore a database (but I can't be sure).

    I found the checkbox for "Account is Locked out" under Status on properties of sa login, but when I un-check it, then click ok then go back to see if it worked, it is always still checked!!!

    Thanks to Laurentiu for the SQL to unlock it - that worked.

    What I really want to know is: WHY does my sa account get locked out?

    Thanks in advance for any info.

    martedì 18 aprile 2006 03:54
  • I guess you are qoeking on a Windows 2003 Server system. Changes of SQL 2k5 made us able to use local or domain password policies on SQL Server Accounts. So if you have defined a password policy on the local machine or on the domain controller (of the machine is within a domain) that any accounts have to be disabled / locked down after X failed attemps to login, then probably thats your problem.

    What do do ?

    -Apparantly someone is trying to bruteforce your system (or is just guessing the wrong password). Try to log these attempts, as also unsuccessfull attemptscan be logged in SQL Server, just navigate to the securities tab on the server properties in Managment Studio and change the audit flag for logons.

    -Meanwhile you have to prevent that the account is locked out every time (although I got used not to use the sa account due to security reasons, everyone know the Account "sa", so that just have to hack the password). You can either disabled the policy on machine / domain level /which is applied to all user accounts in SQL Server), or you disabled the policy inheritance on the User properties pane:

    http://www.sqlserver2005.de/SharedFiles/Passwordpolicy.jpg

    Anyway, for my best practices, I would suggest to create another user, which has sa right but has another name, why is not stating clearly that it has the appropiate permissions and then to use this one.

    HTH, Jens Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---

    martedì 18 aprile 2006 07:08
    Moderatore
  • You can also rename sa to prevent attackers from knowing what account to try:

    ALTER LOGIN sa WITH NAME = 'new_name_for_sa'

    To make sure that sa is locked out because of attempts to break the password, you can check the server error log. If you see a lot of messages about invalid connection attempts for sa, then you know someone is attempting to break in using that account.

    If you want to use a different account than sa (you don't want to rename sa for some reason), then you can disable the sa account using:

    ALTER LOGIN sa DISABLE

    This basically marks the login as unusable for authentication. Attempts to logon using the account will still show up in the error log, but they cannot succeed.

    Thanks
    Laurentiu

    martedì 18 aprile 2006 18:08
    Moderatore
  • Thanks for the info people. I have worked out why my sa account is locked out. It was all down to using SQL Server authentication with SQL Server Integration Services!.

    What happens is that SSIS attempts to validate each and every data flow (not just each connection). Unfortunately SSIS has gone a bit overboard on security and despite checking "Save my password" it doesn't remember the password next time the project is opened. When a login fails it just continues to validate all objects in the .dtsx file. 

    I think in this case it would lock out whichever SQL Server Login I used, not just sa, because we are using Windows Server 2003. The solution for me would be to use windows authentication. BUT, I had to deploy my project on the customer's system which did not have a domain, hence no Windows Authentication.

    I think I will take Jens' advice and create another login with sa rights. That way if it does get locked out, it is easier to unlock than sa. Even better would be to use windows authentication I suppose, since no saving of passwords is required.

    giovedì 20 aprile 2006 01:10
  • HELP....HELP

    I am missing "MSSqlServer\LoginMode" from

    HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

     

    All part of Vs2005 and SqlExpress2005 uninstalled.

    No visible parts.

    Install Express(auto)no options for "Mixed Mode"

    "Connect" caused ERROR message-

     

     

    Cannot connect to NOTEBOOKVLL\SQLEXPRESS.

    ===================================

    Cannot open user default database. Login failed.
    Login failed for user 'NOTEBOOKVLL\Valerie Ladd'. (.Net SqlClient Data Provider)

    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

    ------------------------------
    Server Name: NOTEBOOKVLL\SQLEXPRESS
    Error Number: 4064
    Severity: 11
    State: 1
    Line Number: 65536


    ------------------------------
    Program Location:

       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectorThread()

     

    lunedì 8 maggio 2006 03:21
  • I am loced out of Management ALSO.

    See previous message/response.

     

    HELP.....HELP

    lunedì 8 maggio 2006 03:28
  • worked like a charm.  thanks laurentiu!
    martedì 20 gennaio 2009 15:39
  • Thanks Laurentiu, this worked.
    mercoledì 16 dicembre 2009 00:33
  • It worked for me.

    Thank You Laurentiu!

    lunedì 6 dicembre 2010 16:14
  • Can Anyone guide it for sql server 2008 as Link :

     HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode"

    is old.


    "SQLSERVER DBA" "INDIA"
    lunedì 25 aprile 2011 09:32
  • This worked for me in 2008 R3:

    - I put the server in single user mode by adding a ";-m" to the end of the start-up parameters.

    - Restarted the server.  This allowed me to login using windows administrator authentication.

    - I then used SQL Server Management Studio to change the password and reset it for the sa user.

    - I also create me a new sysadmin account just in case I have problems again in the future.


    giovedì 1 settembre 2011 16:33
  • Its worked for me thank u so much @ Palani.

    martedì 15 novembre 2011 16:05
  • Very Good!
    venerdì 20 aprile 2012 18:55
  • hi Brain i know this is an old issue but... this happened to me once and what i did was repair the sql with the cd and then you will be able to log in with the SA acc
    martedì 24 aprile 2012 17:51
  • The primary method to unlock is well described by Christofor , but if you wish you can take a short –cut and should go for a software which is designed for such purpose. The software will reboot the password after that the SQL server will not prompt you for any password whatsoever.  You can check out the demo version and see if that works for you http://www.mssqlserverrecovery.net/sql-password-recovery

    giovedì 5 settembre 2013 17:25