SQLServerWiki

“The Only Thing That Is Constant Is Change”

MSSQLSERVER services hangs on bootup.

Posted by database-wiki on March 6, 2011

Look at the event log:

Event Type: Warning

Event Source: MSSQLServer

Event Category: (8)

Event ID: 19011

Date:  8/3/2009

Time:  10:49:20 AM

User:  N/A

Computer: PC4258VM2

Description:

SuperSocket info: (SpnRegister) : Error 8344.

It’s a SPN issue.

Followed the plan to register SPN.

SQL Server service was running under domain account domain\user. Checked the SPNs for this account by running command “setspn -l  domain\user” but didn’t find any.

We then had to download the setspn utility to setspn for the SQL server instance.

Registering the Service Principle Name (SPN) To register the SPN do the following:

1. Download and install the SETSPN utility from Microsoft to a Domain Controller. a. For Windows 2000 download it from the link below or search for setspn download at

http://www.microsoft.com/downloads/details.aspx?familyid=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46

For Windows 2003 download it from the link below or search for setspn download at

http://www.microsoft.com/downloads/details.aspx?FamilyId=6EC50B78-8BE1-4E81-B3BE-4E7AC4F0912D.

2. Click Start > Programs > Accessories > Command Prompt and run SETSPN using the following syntax:

• Added MSSQLSvc SPNs using below commands for the cluster name with and without the port no.

Setspn -a MSSQLSvc/servername-with-fully-qualified-domain-name domain\user (did not exist)

Setspn -a MSSQLSvc/servername-with-fully-qualified-domain-name:1433 domain\user (existed in our case)

In our case, our default instance was using 1433 port.

Then we went to the domain controller and since we have multiple domains we had to force replication so that the changes get into effect.

• After this, we were no longer getting the SSPI error and the issue got resolved.

In order to see the changes we installed ADSI edit in the domain controller server. We navigated to the appropriate location and saw that two spn’s were created.

Installing ADSI Edit – To add read and write SPN privilege to the service account.

Download Windows Server 2003 Service Pack 2 32-bit Support Tools from http://www.microsoft.com/downloads/details.aspx?FamilyID=96a35011-fd83-419d-939b-9a772ea2df90&DisplayLang=en

Note: if you copy adsiedit.dll manually then you need to paste into the ‘path’ for example C: \windows\adsiedit.dll.  Then you need to register the dll with:

regsvr32 adsiedit.  (If you install from supptools.msi there is no need for this extra step).

Related Articles: (These KB are very good to understand SPN’s connection with sql server)

How to troubleshoot the “Cannot generate SSPI context” error message==>

http://support.microsoft.com/kb/811889

How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005==>

http://support.microsoft.com/kb/909801

rebooted to see if this fixes the issue but no.

We check the SQL Server error log:

2009-08-05 18:42:15.97 server    Microsoft SQL Server  2000 – 8.00.2039 (Intel X86)

 May  3 2005 23:18:38

 Copyright (c) 1988-2003 Microsoft Corporation

 Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

2009-08-05 18:42:15.99 server    Copyright (C) 1988-2002 Microsoft Corporation.

2009-08-05 18:42:15.99 server    All rights reserved.

2009-08-05 18:42:15.99 server    Server Process ID is 868.

2009-08-05 18:42:15.99 server    Logging SQL Server messages in file ‘d:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG’.

2009-08-05 18:42:16.07 server    SQL Server is starting at priority class ‘normal'(1 CPU detected).

2009-08-05 18:42:18.87 server    SQL Server configured for thread mode processing.

2009-08-05 18:42:19.01 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.

2009-08-05 18:42:19.30 server    Attempting to initialize Distributed Transaction Coordinator.

Microsoft SQL Server  2000 – 8.00.2039 (Intel X86)

 May  3 2005 23:18:38

 Copyright (c) 1988-2003 Microsoft Corporation

 Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

 2009-08-05 19:19:51.59 server    Copyright (C) 1988-2002 Microsoft Corporation.

2009-08-05 19:19:51.59 server    All rights reserved.

2009-08-05 19:19:51.59 server    Server Process ID is 872.

2009-08-05 19:19:51.59 server    Logging SQL Server messages in file ‘d:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG’.

2009-08-05 19:19:51.63 server    SQL Server is starting at priority class ‘normal'(1 CPU detected).

2009-08-05 19:19:52.30 server    SQL Server configured for thread mode processing.

2009-08-05 19:19:52.33 server    Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.

2009-08-05 19:19:53.62 server    Attempting to initialize Distributed Transaction Coordinator.

This a dependency issue with MSDTC,RPCSS.

EXPLANATION:

During start-up SQL Server needs to register itself with MSDTC. MSDTC in turn is dependent on RPC. If the RPC service was not started when SQL was trying to start, the MSDTC service tries to issue a manual start of RPC. But this request is not honored by the Service Control Manager until all the AutoStart services have completed. The problem is that SQL Server itself is also part of the Autostart sequence. This results in a deadlock situation. In other words, SQL Server has initiated the manual start of RPC as part of its autostart but the Service Control Manager cannot honor this until SQL Server has started.

Another part of the issue is that the boot sequence is never guaranteed to be in any particular order so it’s possible to run into this issue under any situation that delays the normal start-up of the RPC and DTC services. The problem is very machine specific and dependent on what services are being started on boot-up. From past cases, it has been tied to too many services auto-starting. To resolve the problem create a dependency for SQL on the RPC server. In this manner SQL will not attempt to start until it detects that the RPC service is up and running.

RESOLUTION:

To create this dependency, add the “DependOnService” data item (Multi-String Value) under HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSSQLSERVER registry key and
set its value to RPCSS and MSDTC(all caps).

we did not get an option to create DependOnService of type REG_MULTI_SZ key in HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSSQLSERVER using regedit of windows 2000.

So we used regedt32 and after some RnD found a way to add this DependOnService of type REG_MULTI_SZ key in HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\MSSQLSERVER using add value in the edit tab of regedt32. Then we added the dependencies of MSDTC and RPCSS.

We rebooted and it worked perfectly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: