There may be multiple situations which end up with SA password locked.
Brute force attack: attempt to guess a password using different possible combination,if you have password policy setting enable which locked the account after certain attempts.
Multiple unsuccessful attempts to guess ‘SA’ password. Etc...
Fix:
1.
Login with other SQL user which has sysadmin role.
Unlocked ‘SA’ login WITH ALTER command
ALTER LOGIN sa WITH PASSWORD = 'currentpassword' UNLOCK ;
GO
2. Unlocked with SSMS.
Microsoft SQL Server management studio-->Logins-->sa-->Properties-->Status-->unchecked the Login is locked out check box
3. Sometime because of Enforce password policy enable , SQL server not giving you specific message when you trying to unlocked the account. In this case first you need to unchecked the ‘Enforced password policy’ , unchecked the Login is locked press OK and again the set the the ‘Enforced Password policy’ option once account is unlocked.
It is good practice to disable the sa user to prevent SQL server from hacking. 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. As a best practice you can disable the sa account and use another account with same privileges.in this case you have different user should have same access as ‘SA’
it is also possible that to not have a user with sytem admin rights in your database and you only have activated SQL Server authentication.
It is possible to change the Server authentication with change value of the registry key "HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode" to 2 for Mixed mode. Then restart the SQL Server service. Now you can login to SQL server with windows authentication and change the password with the procedure discuss in above steps.
No comments:
Post a Comment