Database sa account is locked

Problem Description:
After a Web site, the main page can not log on, access to another site on the same server, a sudden system report 18456 error, the message is "Unable to connect to XXX server", other information for "user ' sa ' login failed." (Microsoft SQL Server, Error: 18456) ".

Problem Reason:
When the Web site, the database connection file password is not correct, so multiple access to the main page after the problem.

The reason for this is that the SA user for SQL Server 2005 is enabled by default to enforce password policy. The general default Windows account password policy or the ad (domain account) password policy is 3-6 times bad password logon failure, the system temporarily freezes the user. The time to freeze depends on how long the system is set. The problem is that the site database connection file attempts to access the system multiple times with the wrong sa password, and the SA's "Enforce Password policy" works.

Graphic:
the SA account is locked because the account for the SA has "enforce secret policy" enabled, or "force expires." In the Logon Properties dialog box for the logged-on user SA, you can see whether the selection status is selected. As shown in the following figure (1) (after Windows is logged on to the database--security--right-click the sa--property in the login name):

figure (1 )

When the enforce secret policy is selected, SQL server2005 invokes the Windows or domain account management policy. If this is the account management policy for Windows, you can see the account policy situation in the local Security settings in Control Panel, where the password policy can set the user's secret expiration time, length, and so on, and the Account lockout policy can set the account lockout threshold. That is, the account is automatically locked by the system by attempting to log on several times with the wrong password. You can refer to the figure (2).


figure (2 )

If the account for SQL Server is locked, the login attribute for that account is shown in Figure (3).

figure (3)

This is an account that uses integrated Windows to log on to SQL Server and cancel the check (3) "Login locked". A 18456 error also occurs when you re logged on to SQL Server with the correct password for the SA. As shown in figure (4). This is because the password for the SA needs to be reset. You can resolve the problem by logging on to SQL Server for the SA password reset with an account that is logged on to SQL Server with the Integrated Windows account.

Figure (4)


Summary: First, if the database security does not have special requirements, remove the "enforced secret policy" as shown in figure (2) to avoid the occurrence of such problems. Second, the website of the department must pay attention to the database connection file changes.