To totally unlock this section you need to Log-in
Login
Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it. (Microsoft SQL Server, Error: 18486)
SQL server has local password policies. If policy is enabled which locks down the account after X number of failed attempts then the account is automatically locked down.This error with 'sa' account is very common. sa is default administartor login available with SQL server. So there are chances that an ousider has tried to bruteforce your system.
You can unlock the account with the following options (use another admin account or connect via Windows Authentication):
ALTER LOGIN sa WITH PASSWORD='password' UNLOCK
When creating SQL user you can set CHECK_POLICY=on which will enforce the windows password policy on the account. The following policies will be applied:
- Define the Enforce password history policy setting so that several previous passwords are remembered. With this policy setting, users cannot use the same password when their password expires.
- Define the Maximum password age policy setting so that passwords expire as often as necessary for your environment, typically, every 30 to 90 days. With this policy setting, if an attacker cracks a password, the attacker only has access to the network until the password expires.
- Define the Minimum password age policy setting so that passwords cannot be changed until they are more than a certain number of days old. This policy setting works in combination with the Enforce password historypolicy setting. If a minimum password age is defined, users cannot repeatedly change their passwords to get around the Enforce password history policy setting and then use their original password. Users must wait the specified number of days to change their passwords.
- Define a Minimum password length policy setting so that passwords must consist of at least a specified number of characters. Long passwords--seven or more characters--are usually stronger than short ones. With this policy setting, users cannot use blank passwords, and they have to create passwords that are a certain number of characters long.
- Enable the Password must meet complexity requirements policy setting. This policy setting checks all new passwords to ensure that they meet basic strong password requirements.
Use another account
Almost everyone is aware of the sa account. This can be the potential security risk. Even if you provide strong password hackers can lock the account by providing the wrong password. ( You can provide extra security by installing firewall or changing the default port but these measures are not always practical). As a best practice you can disable the sa account and use another account with same privileges.
Using Single-User Mode and SQLCMD
Start the SQL Server instance using single-user mode from the command prompt by launching the command prompt as an administrator. You can also start SQL Server 2012 using minimal configuration, which will also put SQL Server in single-user mode.
Since SQL Server 2012, if you have configuration problems that prevent the server from starting, you can start an instance of Microsoft SQL Server by using the minimal configuration startup option.
This is the startup option -f. Starting an instance of SQL Server with minimal configuration automatically puts the server in single-user mode. When you start an instance of SQL Server in minimal configuration mode, note the following:
- Only a single user can connect, and the CHECKPOINT process is not executed.
- Remote access and read-ahead are disabled.
- Startup stored procedures do not run.
After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
From the command prompt (Run as Administrator), type: SQLServr.Exe –m (or SQLServr.exe –f, for Minimal Configuration mode) and start the SQL Server database engine.
Make sure you don’t close this command prompt window. You can locate SQLServr.exe in the Binn folder of your environmental path. If you don’t have a SQL Server's Binn folder in your environmental path, you can always navigate to the Binn folder of your SQL Server machine. Usually the Binn folder is located at C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Binn\.
Once the SQL Server service has started in single-user mode or with minimal configuration, you can now open up another command-line window as an administrator and use the SQLCMD command from the command prompt to connect to the SQL Server instance:
SQLCMD –S-E
If successful, the window should simply display a "1>". You will now be logged in to SQL Server. Keep in mind that you’re logged in as an Admin .
Once you’re logged in to SQL Server using SQLCMD from the command prompt, you have the option of creating a new account and granting it any server-level permission. Create a new login in SQL Server called “admin_SQL” and then add this account to the SA server role. To create a new login from the command prompt after performing the last step, use the following:
1> CREATE LOGIN '' with PASSWORD=' ' 2> GO
Once you’ve created the new login "admin_SQL", add this login to the SA server role on the SQL Server instance. From the same command-prompt window, execute the following statement:
1> SP_ADDSRVROLEMEMBER '','SYSADMIN' 2> GO
If this is a one-time issue, enable “sa” login with changing password of “sa” login, use the following command:
1> ALTER LOGIN sa WITH PASSWORD = 'new_password' UNLOCK 2> GO
Once you’ve successfully performed these steps, the next step is to stop and start SQL Server services using regular startup options. This time you won’t need –f or –m.
Log in to the SQL Server Management Studio. You could also log in from the command prompt using the “SQL_SALEEM” account and its respective password. You now have SA access to your SQL Server instance. You may now reset the SA password and take control of your production SQL Server boxes.
Quick Note
We can star SQL Server instance in Single-User mode, after we have stopped the respective services (in services.msc), by using the following command, as Administrator:
net start MSSQLSERVER /m
Unlock SA Account in Management Studio
If the mixed mode authentication is enabled in SQL Server, you can log into SQL Server Management Studio with Windows Authentication, and then unlock any SQL Server user account easily. Login into SQL Server using Windows Authentication. In Object Explorer, open Security folder, open Logins folder. Right click on SA account and go to Properties.
In the Login Properties window, uncheck the box beside Login is locked out and click OK.