After updating a server from SQL 2000 to 2005 yesterday everything was working fine, but today after windows updates were dropped it lost connection and it wouldn't come back on line. I set the TCP/IP config listen all to No this allowed the services to come on line, initially but not when SP 2 was added. This setting stopped remote connections anyway, so wasn’t what I wanted, but it proved that the port was the issue. The network protocol TCP/IP was configured to the default port 1433.
Log:
2007-10-19 15:53:10.61 Server Error: 17182, Severity: 16, State: 1.
2007-10-19 15:53:10.61 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x80.
2007-10-19 15:53:10.61 Server Error: 17182, Severity: 16, State: 1.
2007-10-19 15:53:10.61 Server TDSSNIClient initialization failed with error 0x80092004, status code 0x1.
2007-10-19 15:53:10.61 Server Error: 17826, Severity: 18, State: 3.
2007-10-19 15:53:10.61 Server 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.
2007-10-19 15:53:10.61 Server Error: 17120, Severity: 16, State: 1.
2007-10-19 15:53:10.61 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
I installed a new instance of SQL Server which worked fine, connections no issue, this confused me a bit, as it proved the port could be listened to, but seemed to indicate something else on the server was using the initial port. I reinstalled SQL Server 2005 on the default instance, errors in log same but new one indicating something already listening on port, why didn't I get that message yesterday? Though this confirmed my suspicion swapped to port 1434 yippee up and running, BUT my client could not connect.
Stranger and stranger I had a old ODBC connection on my machine legacy of my predecessor, the app shouldn't use that connection but it was and wouldn't fire up, once I dropped the ODBC connection it all worked fine....the client can now connect, just need to ensure that no one has an old ODBC connection. Now adding SP2.
Typical SP2 install now had issues:-
2007-10-19 16:06:24.40 Server A self-generated certificate was successfully loaded for encryption.
2007-10-19 16:06:24.41 Server Server is listening on [ 'any' <ipv4> 1434].
2007-10-19 16:06:24.41 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2007-10-19 16:06:24.41 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2007-10-19 16:06:24.41 Server Server is listening on [ 127.0.0.1 <ipv4> 2032].
2007-10-19 16:06:24.41 Server Dedicated admin connection support was established for listening locally on port 2032.
2007-10-19 16:06:24.44 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x5, state: 4. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2007-10-19 16:06:24.44 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2007-10-19 16:06:24.47 spid12s Starting up database 'msdb'.
2007-10-19 16:06:24.77 spid9s Starting up database 'tempdb'.
2007-10-19 16:06:24.94 spid14s The Service Broker protocol transport is disabled or not configured.
2007-10-19 16:06:24.94 spid14s The Database Mirroring protocol transport is disabled or not configured.
2007-10-19 16:06:25.10 spid14s Service Broker manager has started.
2007-10-19 16:06:26.68 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2007-10-19 16:06:26.68 spid5s Recovery is complete. This is an informational message only. No user action is required.
2007-10-19 16:06:32.13 spid51 DBCC TRACEON 4606, server process ID (SPID) 51. This is an informational message only; no user action is required.
2007-10-19 16:06:32.21 spid51 DBCC TRACEOFF 4606, server process ID (SPID) 51. This is an informational message only; no user action is required.
2007-10-19 16:06:36.21 spid51 Starting up database 'temp_MS_AgentSigningCertificate_database'.
2007-10-19 16:06:36.55 spid51 Using 'xpstar90.dll' version '2005.90.3042' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2007-10-19 16:06:36.58 spid51 Using 'xplog70.dll' version '2005.90.3042' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
2007-10-19 16:06:39.36 spid51 Using 'xprepl.dll' version '2005.90.1399' to execute extended stored procedure 'xp_repl_encrypt'. This is an informational message only; no user action is required.
2007-10-19 16:06:39.68 spid14s Service Broker manager has shut down.
2007-10-19 16:06:40.19 spid5s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
2007-10-19 16:06:40.19 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
This was solved by not using the domain admin account to run SQL Server Services, I just created a local administration account and running the services using the new account.
Now I worked out what was the solution, decided to completely rebuild the server, as there was no reason to keep any of the old files, and I was still concerned that something on the server was still using the default port. This time the install had no issues.