SA account is locked in SQL server
- What is SA account in SQL server.?
The “SA” (system administrator) account in SQL Server is a highly privileged, built-in account with full administrative access to the server. Its primary purpose is to provide a powerful account for performing administrative tasks, such as managing databases, users, permissions, and server configurations. It’s a member of the sysadmin fixed server role, which grants the account the highest level of authority.
Disaster Recovery:
In situations requiring immediate and unrestricted access to the server, the “sa” account can be used to perform critical tasks like restoring databases or modifying settings without being blocked by permission restrictions.
2. Why SA account in SQL server will lock.?
The SA account will used by different applications for especially for monitoring for DBA end, there are chances to frequent lock since monitoring application will try to hit the servers multiple times as per scheduled, specifically when encountering a “password mismatch” and indicating the sa account is locked, signifies a failed login attempt for the sa user due to incorrect credentials or account lockout. We have to update the passwords if changed in monitoring applications else will face this issues
3. How to unlock SA account.?
=>In Object Explorer, navigate to Security > Logins. Find the SQL Server login that is locked out
=>In the Login Properties dialog box, go to the Status page.
=>Uncheck the Login is locked out checkbox.
=>Click OK to apply the changes.
Alternatively, using Transact-SQL (T-SQL):
SQL Code to unlock account
ALTER LOGIN [SA] WITH CHECK_POLICY = OFF;
ALTER LOGIN [SA] WITH CHECK_POLICY = ON;
GO
Conclusion:
To avoid SA account lock frequently should maintain proper schedules to change the password policies and update new passwords with respective applications
