Login failed for user '' because the account is currently locked out. The system administrator can unlock it.

Issue: There are multiple entries in the SQL Server Error log. Also Applications started throwing errors.

Login failed for user 'TEST' because the account is currently locked out. The system administrator can unlock it.

First thing to confirm if the account actually got locked or not, verified properties of the SQL login and under the tab ‘Status’ and noticed “Login is locked out” check box was in ticked state. This confirms account got locked.

Here you can unlock the account by simply unchecking the “Login is locked out” check box  and it will solve the problem. But the catch is if you have ‘Enforce Password Policy’ already checked in ‘General’ tab of SQL login properties  then it will force you to reset the password while unchecking the “Login is locked out” check box .

If you want to UNLOCK the SQL login without resetting the password of SQL login we have 2 options

1.    One way is running below 2 commands:

ALTER LOGIN [TEST] WITH CHECK_POLICY = OFF;

ALTER LOGIN [TEST] WITH CHECK_POLICY = ON;

2.    Un-check the ‘Enforce Password Policy’ option in ‘General’ tab of login properties and click on ‘OK’. Once you do that login gets unlocked. Once the account gets unlocked you can check the ‘Enforce Password Policy’ option in ‘General’ tab again.

Thanks!!

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 200, state 7, severity 25.

We face this error during below scenario’s:

           1 .    When you have done a Service Pack upgrade.

           2 .    When you have changed collation of the instance.

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

As with many errors even this error comes up due to many reasons. So there are few things you need to verify.

As a first step, I recommend to verify below paths in the REGISTRY of the server where SQL Server installed and make sure those paths exists.

Type regedit in Run.

Once registry opens, go to below path: (Below underlined MSSQL10.MSSQLSERVER, here MSSQLSERVER is your instance name. So keep in mind this will change as per your instance name.)

After clicking on MSSQLServer, you will see ‘BackupDirectory’, ‘DefaultData’ and ‘DefaultLog’ fields along with many other fields which will be on right side.  For all these 3  fields verify the paths mentioned in ‘Data’ column exists in the server.

Next go to below location in registry and click on Setup

Then on right side you will notice ‘SQLDataRoot’. Verify here as well the path given is correct.

For some reasons during few Service Pack installations or instance upgrades the locations of above fields becomes invalid in registry and we start receiving above error.

So if you correct the paths in above fields then it will fix the issue in most of the cases.

As a second step, try to start the SQL services in single user mode by using the switch ‘-m’ and REBUILD the master database.

As a last step,  I would verify if there is any conflict of collation between system databases.

Recently I had same issue and after spending several hours on this, it turned out to be a collation difference between master and msdb databases.

How this happened? We had to upgrade an instance and change collation as well after upgrade. So after performing the upgrade and changing collation, DBA have performed master database restore(in order to get back logins) and unfortunately the backup used was of old collation. So this has messed up the instance.

If you are very lucky instance will start and will be stable until you perform the collation change of the database, you can do it either performing a fresh restore or master database REBUILD(I HAVE NOT FOUND A DBA WITH LUCK TILL NOW J). So what’s happening in my case was, as there was collation difference in system databases, instance was starting but it was going down immediately. During the time it was in started state when we tried to connect to instance, it was throwing above script upgrade error.

So the only option we had was to re-install the instance and this time, no, we didn't changed the collation of master database.

Please feel free to share any other easy way to fix this issue.

Thanks!!

How to backup Encryption Key in SQL Server Reporting Services

Connect to SQL Server “Reporting Services Configuration Manager” from “All Programs”, provide the instance name and click on “Connect”.

Now click on “Encryption Keys” tab, which is second tab from bottom on left side of the window.

Here click on “Backup”

In the pop up window, we need to choose the file location and provide password for the file. Backup file will be of “.snk” extenstion.

Note: We need to know the password of the encryption key backup file to restore it. Without password encryption Key restore is not possible.

After clicking on “OK” and once backup completes you will see below message in the window:

Thanks!!