Upgrade of SQL Server 2000 instance to SQL Server 2005 fails with error similar to "Enforce password policy"

During the upgrade for the SQL Server 2000 to SQL Server 2005, the upgrade completes but with an error message related to the "Enforce Password Policy". The error message can be found in the setup log files but the server upgrade completes and you cans see that the instance is upgraded to the SQL Server 2005.

- But you will not be able to start the SQL Server services and will create a exception at times in the log folder.

The message in the errorlog could be something similar to the below,

Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATIONAccess Violation occurred reading address 00000004

- Also after this we are unable to upgrade the SQL Server 2005 to SP2 as the Service Pack installation will fail stating that the previous installation has not succeeded. But you will find the services are there

- This is an expected behaviour when there are SQL logins on the SQL Server 2000 instance whose passwords doesn't satisfy the "Password Policy" that is enforced in case of SQL Server 2005.

- Also as the service is not starting, so we willbe unable to modify any settings on the SQL Server level.

- So the steps which will resolve the issue here are below,

1. In SQL Server Configuration Manager, set the traceflag -T4606 as startup parameter for the SQL Server.

2. After setting this trace Flag, SQL Server will ignore the Enforce Password Policy during startup and the services will come online.

3. Once the server is online we will need to uncheck the "Enforce Password Policy" for the SQL Logins that are there on the server. We will need to remove this for the SQL Server logins whose password is not satisfying the Password Policy in SQL Server 2005, but as it is difficult to find the specific logins and their passwords, hence it is better to uncheck this one for all the SQL logins.

Also note that after the SQL Server service is started and SQL Server is online, still the SQL Server agent can fail with the below error message,

SQLServer Error: 208, Invalid object name 'msdb.dbo.syssessions'.

After the upgrade is completed by following the steps, this issue will also be resolved as the SQL Server agent configuration will be completed successfully.

4. Then we will need to remove the -T4606 startup parameter and then start the SQL Server service. Once this comes online, we can try to bring the S Server agent service online. But it is possible that the agent will fail to come online. That will be fixed later in this article.

5. Then we will go ahead and run the setup in the below method to fix the setup of the SQL Server 2005
  a. To fix your problem go to Add/Remove Programs and: Select Microsoft SQL Server 2005 -> Change
  b. Select SQL instance that failed (MSSQLSERVER) -> Next
  c. Select Database Engine -> Next
  d. Pass Welcome and System Configuration Check
  e. Select Changed Installed Component
  f. Setup will offer continuing suspended installation
  g. After this, the installation completed fine. So the setup of the SQL Server 2005 has been fixed now.

6. As the RTM setup for SQL Server 2005 is fixed now, so we can go ahead and start the SP2 installation for the instance. The installation will be successful and now all the services will run successfully.

Also you will find the SQL Server 2005 instance running fine on the server.

Sanjaya Padhi
SE, Microsoft SQL Server