MS SQL - How to unlock SA account

From Wiki
Jump to: navigation, search

If MSSQL detects too many failed login attempts for a particular user, it will lock that user as a security precaution. For MSSQL servers with public access, the most common user to get locked is the built-in 'SA' (Server Administrator) account. This article shows how to unlock the 'SA' account and track down the IP with the failed login attempts.

Unlocking the 'SA' user

These are the steps required to unlock the 'SA' account immediately without waiting for the lock to expire.

  1. Open SQL Server Management Studio
  2. In the Object Explorer, expand Security > Logins
  3. Right-click on the 'SA' user and choose Properties
  4. Enter a new password (can be the same as the existing password but is still required)
  5. Select Status page
  6. Uncheck "Login is locked out"
  7. Click OK

Track down IP address with failed login attempts

These are the steps used to track down the source IP of the failed login attempts.

  1. Open SQL Server Management Studio
  2. In the Object Explorer, expand Management > SQL Server Logs
  3. Right-click on the "Current" log and select View SQL Server Log
  4. Click Filter... at the top of the window
  5. Set the Source to: Logon
  6. Check "Apply Filter"
  7. Click OK
  8. Each Message field that starts out with "Login failed for user 'SA'" will contain IP for the client attempting to login

After finding the IP address(es) of the client(s) with the failed login attempts, you can either inform the client to update their login credentials if they are a valid user of the database system or block the IP in the Windows Firewall if the client is not authorized to use the database system.

Davidd (talk) 17:10, 5 July 2013 (CDT)