View source for MS SQL - How to unlock SA account

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.

# Open SQL Server Management Studio
# In the '''Object Explorer''', expand '''Security''' > '''Logins'''
# Right-click on the 'SA' user and choose '''Properties'''
# Enter a new password (can be the same as the existing password but is still required)
# Select '''Status''' page
# Uncheck "Login is locked out"
# 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.

# Open SQL Server Management Studio
# In the '''Object Explorer''', expand '''Management''' > '''SQL Server Logs'''
# Right-click on the "Current" log and select '''View SQL Server Log'''
# Click '''Filter...''' at the top of the window
# Set the '''Source''' to: Logon
# Check "Apply Filter"
# Click '''OK'''
# 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.


----
[[User:Davidd|Davidd]] ([[User talk:Davidd|talk]]) 17:10, 5 July 2013 (CDT)

[[Category:Databases-MSSQL]]
[[Category:VPS]]
[[Category:Windows-VPS]]