Thursday, January 8, 2009

SQL Server service not starting

I had a strange experience recently. I tried to connect to a SQL Server instance and Management Studio stopped working and finally Windows Vista crashed. I couldn't start SQL Server after restart. I got the following messages in Windows Application Error Log:

  1. TDSSNIClient initialization failed with error 0xffffffff, status code 0x80.
  2. TDSSNIClient initialization failed with error 0xffffffff, status code 0x1.
  3. Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
  4. SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Solution:

  1. Set SQL instance to run as Local System Account
  2. Stop SQL Server
  3. Open "SQL Server Surface Area Configuration" and disable remote connection
  4. Start SQL Server

Now it should work.

Resolution:

There are very useful blog entries in SQL Protocols blog: Error Messages of SQL Server 2005 Start Up Failure, and Understanding server-side protocol initialization error codes

Messages from 2 to 4 are generic start up failure messages, you can ignore them. The only meaningful message is:

TDSSNIClient initialization failed with error 0xffffffff, status code 0x80.

Error code 0xffffffff means nothing. If error code was 0x7e, we could do the following:

  1. 0x7e = 126
  2. c:\> net helpmsg 126

    The specified module could not be found.

We have the reason now. Let's see what is status code 0x80.

Status Code (Hexa) Status Code (Dec) Protocol Area
0x03
0x40-0x4F
3 (0x03)
64-79 (0x40-0x4F)
Shared Memory
0x09-0x1E

6-30 (0x09-0x1E)

TCP/IP

0x1F-0x23

31-35 (0x1F-0x23)

DAC

0x35
0x50-0x5F

53 (0x35)
80-95 (0x50-0x5F)

Named Pipes

0x36
0x60-0x6F

54 (0x36)
96-111 (0x60-0x6F)

VIA

0x70-0x7F

112-127 (0x70-0x7F)

HTTP

0x38
0x80-0x8F

56 (0x38)
128-143 (0x80-0x8F)

SSL

0x90-0x9F

144-159 (0x90-0x9F)

General

So it means I have a problem with SSL. Some typical, more specific status codes:

Status Code Description
0x03 Error starting shared memory support
0x04 All protocols disabled
0x0A Unable to initialize the TCP/IP listener
0x1C Server configured to listen on a specific IP address in a cluster environment
0x1E Duplicate IP address detected in network
0x35 Error starting named pipe support
0x36

Error starting VIA support

0x38 Error obtaining or using the Certificate for SSL
0x3A Unable to initialize the communication listeners
0x40 Unable to initialize the Shared Memory listener
0x50 Unable to initialize the Named Pipe listener
0x60 Unable to initialize the VIA listener
0x70 Unable to initialize the HTTP listener
0x80 Unable to initialize SSL support

3 comments:

  1. Nice article. Quick question--how did you know that 0x7e = 126, and how does one convert those types of error codes to a number.

    Jon

    ReplyDelete
  2. 0x7e means 7e in hexadecimal. Hexadecimal indicator is 0x before the number. You can convert hexadecimal numbers to decimal numbers e.g. with calc.exe.

    See http://en.wikipedia.org/wiki/Hexadecimal article about hexadecimal numbers for details. It has a section about hexadecial-decimal conversion with examples.

    ReplyDelete
  3. Thanks for the info. Unfortunately I've tried that several times. In the systems I'm seeing the setting reverts to Network Service after a reboot. Can't figure out how to stop that yet. Tried on XP sp2, XP sp3 and Vista sp1. Maybe some kind of SQL issue? I'm now testing SQL Server sp3 to see if that fixes it.

    ReplyDelete