Multiple Instances, SQL Server 2000 & 2005; Install order

Bob Admin — Mon, 21 Jan 2008 17:04:00 GMT

The fun of supporting 1000's of installations of SQL Server 2000 and 2005, is you get too see some fun stuff; 12 hours of research shows that when you have SQL Server 2000 and 2005 installed together and 2000 is installed after 2005, the SQL Server browser service does not work properly due to permissions issue in the registry.

For this particular problem, we are dealing with two instances of MSDE and an instance of SQL Express, installed as: MSDE, SQL Express, MSDE.

After this install the following message is seen for the sql server browser service: “The configuration of the instance {} is invalid” ,substitute your instance name in {}. pic:

Local connections using the shared libraries still work, but remote connections do not. There is a MS KB Article that documents how installing SQL 2000 after 2005 affects the registry permissions causing the SQL Server browser service to be unable to read the registry.

Microsoft KB Article: http://support.microsoft.com/kb/905618

SYMPTOMS

You may receive a connection error message if the following conditions are true:• You install Microsoft SQL Server 2000 or SQL Server 7.0 after you install SQL Server 2005.

• You try to connect to an instance that was created by SQL Server 2000 or by SQL Server 7.0.

This problem occurs if the connection string does not include the protocol-specific information, even if the SQL Server Browser service is still running.

In this scenario, the protocol-specific information depends on the information that is returned by the server. For example, you may receive the following error message when you connect to an instance by using the Sqlcmd utility (Sqlcmd.exe):

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

Sqlcmd: Error: Microsoft SQL Native Client : Client unable to establish connection.

Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Back to the top

CAUSE

When you try to connect to an instance of SQL Server 2000 or of SQL Server 7.0, the SQL Server Browser service (Sqlbrowser) will try to obtain the required protocol information from the Microsoft Windows registry to relay to the client. Therefore, the SQL Server Browser service must have sufficient permissions to read the appropriate Windows registry for the instance.

When you set up SQL Server 2005, the registry keys for all existing instances are modified to enable the SQL Server Browser service to read the required protocol information. However, if you install an instance of SQL Server 2000 or of SQL Server 7.0 after you set up SQL Server 2005, the correct registry permissions are not set. The permissions are only modified when the SQL Server Browser service is installed. Specifically, if the startup account of the SQL Server Browser service is not an Administrators account, this account may not have the required access permissions to read the relevant keys of the instance.

Note In SQL Server 2000, the SQL Server service identifies the server connection endpoints. SQL Server 2005 replaces that function with the SQL Server Browser service. If you install SQL Server 2005 on a computer that is also running SQL Server 2000, you must install SQL Server 2000 Service Pack 3 (SP3), SQL Server 2000 SP3a, or SQL Server 2000 SP4 on that computer.

Back to the top

WORKAROUND

Warnings• Serious problems might occur if you modify the registry incorrectly by using Registry Editor or by using another method. These problems might require that you reinstall your operating system. Microsoft cannot guarantee that these problems can be solved. Modify the registry at your own risk.

• This workaround does not apply to SQL Server failover cluster instances when you are performing a new installation or a maintenance installation.

You can use one of the following methods to work around this problem.

Back to the top

Modify the registry to grant read permissions to specific keys

For each instance of SQL Server 2000 that you installed after you installed SQL Server 2005, follow these steps: 1. Click Start, click Run, type regedit, and then click OK.

2. Locate the following registry subkey:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName

In this subkey, InstanceName is the name of the instance of SQL Server 2000.

Note If you installed SQL Server 2000 as the default instance, the registry subkey is the following:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

3. On the Edit menu, click Permissions.

4. Click Add, type SQLServer2005SQLBrowserUser or type SQLServer2005SQLBrowserUser$ComputerName, and then click OK.

Note The account group name SQLServer2005SQLBrowserUser may be SQLServer2005SQLBrowserUser$ComputerName on your computer. You can find this group name in Local Users and Groups in Computer Management. In this step, ComputerName in SQLServer2005SQLBrowserUser$ComputerName is the name of your computer.

5. Click to select the Read check box in the permission list for this user account, and then click OK.

6. Quit Registry Editor, and then restart the SQL Server Browser service.

Note The permissions should be inherited by the child keys. If the permissions are not inherited, you have to explicitly grant the read permission to SQLServer2005SQLBrowserUser or to SQLServer2005SQLBrowserUser$ComputerName for the following keys, if they are present:• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\CurrentVersion

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\SuperSocketNetLib

• HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\Cluster

Note In this subkey, InstanceName is the name of the instance of SQL Server 2000.

If the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceName>\SuperSocketNetLib registry subkey is not inherited, you must manually modify the underlying keys for this registry subkey.

Note SQL Server 7.0 did not have named instances. Therefore, the relevant registry key should have inherited the appropriate permission from the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer registry subkey. However, if the permission has not been inherited, you must manually set the permission.

Back to the top

Explicitly specify the connection information in the connection string

When you connect to an instance of SQL Server 2000 or of SQL Server 7.0 from the client, you can explicitly specify the connection information in the connection string. You specify this information so that the connection information is not dependent on the information being returned by the server. For example, you can connect to the instance by using the command that similar to the following command when you use the Sqlcmd utility. sqlcmd –S tcp:yourhost,1500

Back to the top

DTS package, and knowing your error codes !

Bob Admin — Sun, 23 Dec 2007 21:36:47 GMT

First off it's always important to have lots of logging on for your dts packages. These packages are difficult to troubleshoot, even with the logs. So SET LOGGING ON, in the data pumps, in sql jobs, everywhere (don't forget you may need to have some clean up scripts to keep your log files from growing out of control).

Recently ran across a package, failing in production, ran fine of course in development and test. Now this wasn't the greatest package, but unfortunately the DBA doesn't always get consulted on the design of things, but of course, it became the DBA Groups problem to fix!

The package has quite a few tasks, data pumps from Oracle to SQL and even runs an external c# program to pull data from Active Directory \ Exchange.

Nothing obvious for the error (it seems obvious to you realize it's bogus !), the log file is below. Finally after researching and research could find nothing for why the "incorrect syntax error happenned". To this day I beleive that to be a "bogus" error.

I focused on that provider error of 170 (AA), remembering back to my ancient days of programming, I looked the error up: AA 170 BUSY: The requested resource is in use . I began to throttle the parrallell processes to lower the amount of resources simultaneously in use by the package, ultimately I had to set it to 1. It's run fine every day since. I'm not sure if the Oracle provider interface on this machine is outdated and caused the error, or if the "requested resource in use" was some other item !

Don't forget those error codes... http://www.lifeasbob.com/Code/ErrorCodes.aspx

DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005)
   Error string: Error at Destination for Row number 1. Errors encountered so far in this task: 1.
   Error source: DTS Data Pump
   Help file:
   Help context: 0

Error Detail Records:

Error: -2147467259 (80004005); Provider Error: 170 (AA)
   Error string: Line 1: Incorrect syntax near 'OFFICE_ID'.
   Error source: Microsoft OLE DB Provider for SQL Server
   Help file:
   Help context: 0

DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147213206 (8004206A)
   Error string: The number of failing rows exceeds the maximum specified.
   Error source: Microsoft Data Transformation Services (DTS) Data Pump
   Help file: sqldts80.hlp
   Help context: 0

Error Detail Records:

Error: -2147213206 (8004206A); Provider Error: 0 (0)
   Error string: The number of failing rows exceeds the maximum specified.
   Error source: Microsoft Data Transformation Services (DTS) Data Pump
   Help file: sqldts80.hlp
   Help context: 0

Error: -2147467259 (80004005); Provider Error: 170 (AA)
   Error string: Line 1: Incorrect syntax near 'OFFICE_ID'.
   Error source: Microsoft OLE DB Provider for SQL Server
   Help file:
   Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_2
DTSRun: Package execution complete.

Return to LifeAsBob - SQL Server | DTS

Multiple Instances, SQL Server 2000 & 2005; Install order

DTS package, and knowing your error codes !