SQL Server Q&A

Symptoms
When you create a new maintenance plan that runs on a schedule in Microsoft SQL Server 2005, you may receive the following error message:

Create maintenance plan failed.
Additional information:
Create failed for JobStep ‘Subplan’. (Microsoft.SqlServer.MaintenancePlanTasks)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The specified ‘@subsystem‘ is invalid (valid values are returned by sp_enum_sqlagent_subsystems). (Microsoft SQL Server, Error 14234)This behavior may occur when you create the maintenance plan manually or by using the Maintenance Plan Wizard.
Resolution
This behavior occurs when you install SQL Server 2005 without installing the SQL Server 2005 Integration Services (SSIS) service. When you create the maintenance plan, the following code is run.

EXEC msdb.dbo.sp_update_jobstep @job_name=N’System DB Maintenance Plan’, @step_id=1 , @subsystem=N’SSIS’, @command=N’/SQL “\Maintenance Plans\System DB Maintenance Plan” /SERVER ASHBSQL701 /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF’This code tries to access the Integration Services service.

Symptoms
When you create a trusted connection from Microsoft ASP.NET to Microsoft SQL Server, you may receive the following error message:

Login failed for user ‘MachineName\ASPNETFor computers that run Internet Information Services (IIS) 6.0, you may receive the following error message:

Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’Note You receive either of these error messages specifically when you use integrated security (when you include the integrated security=sspi attribute in a connection string).
Resolution
When you use ASP.NET, the default security context is the ASPNET account (or NetworkService account, for an application that runs on IIS 6.0) for both Aspnet_wp.exe (or W3wp.exe, for an application that runs on IIS 6.0) and the request to SQL Server. By default, the ASPNET account (or NetworkService account, for an application that runs on IIS 6.0) does not have any permissions in SQL Server, and therefore it cannot access the database.

Symptoms
When you create a parameterized command against Microsoft SQL Server 7.0, if you call the Prepare method before you add parameters to the command, you receive the following error message:

An unhandled exception of type ‘System.Data.SqlClient.SqlException’ occurred in system.data.dll.
Additional information: System error. This problem does not occur in SQL Server 2000.
Resolution
This problem occurs in SQL Server 7.0 because, by design, you cannot run the Prepare method before you add parameters. This applies to most database systems.
SQL Server 2000 does not generate the above-mentioned exception because it does not run Prepare until the first command is executed. This optimization prevents the overhead of Prepare if no commands are subsequently executed.

Symptoms
On a server that is running Windows Server 2003, you upgrade from Microsoft SQL Server 2000 to Microsoft SQL Server 2005. However, an access violation occurs during the startup of the SQL Server service. Additionally, the following error message is logged in the SQL Server Errorlog file:

DateTime spid4s * Exception Address = 78144D3A Module(MSVCR80+00014D3A)
DateTime spid4s * Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
DateTime spid4s * Access Violation occurred reading address 00000006
Unable to update password policyMessages that resemble the following are logged in the SQLSetup0005_ServerName_SQL.log file:

Service MSSQL$InstanceName with parameters ‘-m SqlSetup -r -T4022 -T4010′ is being started at DateTime
MSI (s) (80:A4) [12:46:36:153]: Running as a service.
Failed to start service MSSQL$InstanceName. Error code 17185
Service failed unexpectedly (17185)
Error Code: 0×80074321 (17185)
Windows Error Text: Source File Name: sqlsetuplib\service.cpp
Compiler Timestamp: DateTime
Function Name: sqls::Service::Start
Source Line Number: 301This problem occurs when SQL Server 2005 is trying to recover the resource database.
This problem occurs if the following conditions are true: The syslogins table in SQL Server 2000 has a SQL Server login.The password of the SQL Server login does not comply with the password policy in Windows Server 2003.
Resolution
To work around this problem, use one of the following methods.
Note We recommend that you use Method 1. Use Method 2 only as a temporary workaround for migration purposes.
Method 1: Update the password of the SQL Server login to comply with the password policy in Windows Server 2003Note You may not know the specific SQL Server login whose password does not comply with the password policy. In this case, you must repeat step 1 through step 4 to examine each SQL Server login until you find the problematic SQL Server login. Start the SQL Server service by using trace flag 4606. Open SQL Server Configuration Manager.Click SQL Server 2005 Services, and then double-click SQL Server (InstanceName).In the SQL Server (InstanceName) Properties dialog box, click the Advanced tab.On the Advanced tab, add the following text at the end of the existing string in the Startup Parameters box:
-T4606Click OK.Right-click SQL Server (InstanceName), and then click Start.Update the password of the SQL Server login.Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.Click New Query.In the query window, run the following statement:

ALTER LOGIN [LoginName] WITH PASSWORD = ‘NewPWD’NotesLoginName represents the name of the SQL Server login that causes the problem.NewPWD represents the new password that complies with the password policy in Windows Server 2003.Remove trace flag 4606 from the startup parameters for the SQL Server service. Open SQL Server Configuration Manager.Click SQL Server 2005 Services, and then double-click SQL Server (InstanceName).In the SQL Server (InstanceName) Properties dialog box, click the Advanced tab.On the Advanced tab, remove the following text from the existing string in the Startup Parameters box:
-T4606Click OK.Right-click SQL Server (InstanceName), and then click Restart.
Method 2: Disable the password policy enforcement feature for the SQL Server loginNote Use Method 2 only as a temporary workaround for migration purposes until you update the password.
Important These steps may increase your security risk. These steps may also make the computer or the network more vulnerable to attack by malicious users or by malicious software such as viruses. We recommend the process that this article describes to enable programs to operate as they are designed to or to implement specific program capabilities. Before you make these changes, we recommend that you evaluate the risks that are associated with implementing this process in your particular environment. If you decide to implement this process, take any appropriate additional steps to help protect the system. We recommend that you use this process only if you really require this process. Start the SQL Server service by using trace flag 4606. Open SQL Server Configuration Manager.Click SQL Server 2005 Services, and then double-click SQL Server (InstanceName).In the SQL Server (InstanceName) Properties dialog box, click the Advanced tab.On the Advanced tab, add the following text at the end of the existing string in the Startup Parameters box:
-T4606Click OK.Right-click SQL Server (InstanceName), and then click Start.Open SQL Server Management Studio, and then connect to the instance of SQL Server 2005.Click New Query.In the query window, run the following statement:

ALTER LOGIN [LoginName] WITH CHECK_POLICY = OFFRemove trace flag 4606 from the startup parameters for the SQL Server service. Open SQL Server Configuration Manager.Click SQL Server 2005 Services, and then double-click SQL Server (InstanceName).In the SQL Server (InstanceName) Properties dialog box, click the Advanced tab.On the Advanced tab, remove the following text from the existing string in the Startup Parameters box:
-T4606Click OK.Right-click SQL Server (InstanceName), and then click Restart.

Symptoms
When creating a new SQL Server Data Source Name (DSN) using the ODBC API SQLConfigDataSource function, a network library must be specified. If the network library name is in lowercase letters, the Client Configuration dialog box may show it as “Other”.
Resolution
In Control Panel, the Client Configuration dialog box in ODBC Data Source Administrator is case-sensitive. It compares the network library name from the registry to uppercase network library names. See the “More Information” section for information on the registry entries that are affected by SQLConfigDataSource.

Symptoms
This article discusses two alternative approaches to a disaster recovery plan for transactional replication. This article contains more information about the following:Problems in the approach for the disaster recovery plan that is mentioned in SQL Server Books Online.Two alternative approaches to a disaster recovery plan.Step-by-step procedure to set up a disaster recovery plan for the two alternative approaches.Step-by-step procedure to recover the replication databases for the two alternative approaches.
Resolution
In SQL Server Books Online, the “Strategies for backing up and restoring transactional replication” topic discusses the disaster recovery plan for transactional replication. However, the approach that is mentioned in SQL Server Books Online may not satisfy the requirement of minimum latency. This article discusses two alternative approaches that can be used to set up a disaster recovery plan for databases that are involved in transactional replication. The approaches that are discussed in this article can be used to recover the replication databases from a failure situation, such as a hardware failure.