今天遇到的问题比较有意思。首先是很久没有打开测试数据库了,今天打开,使用service程序测试的时候出现下面的错误提示:
Message: System.Data.SqlClient.SqlException: Login failed for user 'dcp_prod'. Reason: The password of the account has expired.
很明显,这个是因为密码失效了,dcp_prod这个账户使用了密码失效策略,打开数据库查看这个账户的属性,果然,Enforce password policy和Enforce password expiration这两个属性被选中了。
现在只需要重新设置一下密码就可以了。但是之后又出现了一个错误,提示如下:
System.Data.SqlClient.SqlException: Login failed for user 'dcp_prod' because the account is
currently locked out. The system administrator can unlock it.
这是什么原因呢,locked out是锁住了,然后使用administrator站好去解锁,字面意思是这样的,照着提示区做,再次打开账户属性。
(这里是回复正常之后的状态,默认是灰色不可用的)
点击Status标签,果然Login is locked out属性被选中,取消选中,点击OK,回到程序中。奇怪了,还是上面的locked out提示,打开属性查看,这个属性又一次被选中了。这样连续来回了好几次都是这样,开始纳闷了。后来在网上查,网上说有其他用户尝试连接数据库,我恍然大悟,是那个service程序在跟我争夺数据库。这个service程序每3秒钟会连接一次数据库,进行相应的操作,在这中间如果尝试修改密码,账户会被锁定的。我Administrator tool->service中关掉程序,修改密码,重新打开程序,这个错误就消失了。
PS:如果Sql server账号因为密码输错,尝试过多次登录,也会导致Sql server账号locked out,原文如下:
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. (This can cause even if a legitimate tries to access the account with wrong password.Sometimes a user would have changed the password without informing others. So the other users would try to lo)
You can unlock the account with the following options (use another admin account or connect via windows authentication)
1.Alter account & unlock
ALTER LOGIN sa WITH PASSWORD='password' UNLOCK
2.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.
ALTER LOGIN sa DISABLE
You can edit the lock-ot options using gpedit.msc( in command prompt type gpedit.msc and press enter). Navigate to Account Lokout policy as shown in the figure(这说明sql server账号会被锁定的最大失败登录次数,是在windows操作系统的组管理策略中设定的)