• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1886
  • Last Modified:

Can not connect to a sql 2005 server HELP

I was able to access my SQL 2005 server until recently then all of the sudden I was not able to assess the server getting the below message.   I have tried to start the server and received the second message below.  
I enabled the protocols and that didn’t help I went into the service area configuration section and set the remote connections to remote and local.  Non of this helped any help in this area would be appreciated.

Thanks in advanced.
TAR
"Message 1
An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)"


"Message 2
Unable to start service MSSQLSERVER on server DJ9QF3B1."
0
TAR1325
Asked:
TAR1325
  • 8
  • 5
  • 3
2 Solutions
 
Einstine98Commented:
what error messages do you have in your application event log? (on the server side)
 and are you able to connect to the server locally?
0
 
Eugene ZCommented:
try to create alias for the remote sql server on the client and use TCP\IP protocol instead of  Named Pipes

But before (maybe instead): login as local admin on the server DJ9QF3B1 and check the
 MSSQLSERVER  service status - make sure the login account and it's password  is good
and try to start the service again- if there are problem to start: please post all related event viewer errors
0
 
TAR1325Author Commented:
Answer1
what error messages do you have in your application event log? (on the server side)
 and are you able to connect to the server locally?
>>I am working on the PC I have my server loaded on so everything I do is local, I am not able to connect.  The application event log only says Error MSSQLSERVER with a catigory or (2)  

Answer 2,
try to create alias for the remote sql server on the client and use TCP\IP protocol instead of  Named Pipes

But before (maybe instead): login as local admin on the server DJ9QF3B1 and check the
 MSSQLSERVER  service status - make sure the login account and it's password  is good
and try to start the service again- if there are problem to start: please post all related event viewer errors

>>I am not sure what you mean by create an ALIAS I am very new to this and am not familiar with that term

And how do I log as admin.  This software is on my home PC and I do not have to login at all.   When I do start up the server I login using windows authentication not SQL server so I do not need a pass word.  This is when I encounter the problem below.

Sorry if my responses seem amateurish but this is new to me…

Thanks for your response
And thanks in advance for your follow up help.
 

An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476

------------------------------
Error Number: 2
Severity: 20
State: 0


------------------------------
Program Location:

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()



But before (maybe instead): login as local admin on the server DJ9QF3B1 and check the
 MSSQLSERVER  service status - make sure the login account and it's password  is good
and try to start the service again- if there are problem to start: please post all related event viewer errors








0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Eugene ZCommented:
ok,
step1:
check the MSSSQL service logon account:
start-> run -> [copy\paste ] ->  %SystemRoot%\system32\services.msc /s
It will open services there -> right click  the SQL Server (your SqlServer2005 inst name) service -> properties ->Tab 'LOG ON'
tell me what you see -> is it local system account -
if not - some NT account - make sure the password is ok and the nt account is member of Local admins group


---
MSSQLserver Service belongs to sql server 2000
So: do you have sql server 2000 as default instance and 2005 as named instance  or you have just sql server 2005 ?
if named you need see  what is you SQL Server (your SqlServer2005 inst name) service name....

0
 
TAR1325Author Commented:
Answer to step 1
SQL SERVER AGENT (MSSQLSERVER) Lonon is set to = Local System Account

Question 2 do you have sql server 2000
No I never had 2000 SQL server on this machine this is a new machine.  
But there is an entry for SQL SERVER SQLEXPRESS

Thanks for your response.
0
 
TAR1325Author Commented:
Hello Is there anyone who can help me with this issue

Thanks in advance

TAR1325
0
 
Einstine98Commented:
The error message says that you are trying to use named pipes... are you doing this on purpose? did you try connecting using TCP-IP?
0
 
TAR1325Author Commented:
No I did not do this on purpose I did not try connecting using TCP-IP.  I am getting this message when I start up Microsoft Server SQL Management Studio.

Thanks For your response any help is appreciated.
0
 
TAR1325Author Commented:
I also Get the below message when I try to manually start the MSSQLSERVER in The SQL Management Studio:

Unable to start service MSSQLSERVER on server DJ9QF3B1.

===================================

The MSSQLSERVER service on DJ9QF3B1 started and then stopped. (ObjectExplorer)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start()

0
 
Einstine98Commented:
there is your problem, you need to fix this part first....

Check application event log for errors that can tell why is it stopping right after starting
0
 
TAR1325Author Commented:
When I check the event viewer I get the below message
But I do not know hao to check the SQL Server error log or the Windows event logs
Thanks again for your response any further help would be appreciated!

TAR1325

Event Type:      Error
Event Source:      MSSQLSERVER
Event Category:      (2)
Event ID:      17120
Date:            7/10/2006
Time:            1:48:09 AM
User:            N/A
Computer:      DJ9QF3B1
Description:
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: e0 42 00 00 10 00 00 00   àB......
0008: 09 00 00 00 44 00 4a 00   ....D.J.
0010: 39 00 51 00 46 00 33 00   9.Q.F.3.
0018: 42 00 31 00 00 00 00 00   B.1.....
0020: 00 00                     ..      
0
 
Einstine98Commented:
I can't remember where SQL 2005 puts the error logs, but it should be in the main folder under program files... can you check there? I would say you have a faulty installation can you reinstall?
0
 
TAR1325Author Commented:
Below is the error log from my last attempt at loging on.    
Looks like one thing I might have to do is clear out my windows event log I do not know where that is. do you?

Some other things I do not understand:
1)The Database Mirroring protocol transport is disabled or not configured.
   THis is something I never set up is there a reason why this would stop working.

2)The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. 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.
  Is this something that might be causing problems?

3) Also It seems loke the SQLEXPRESS server starts with out a problem every time I wonder why this would start and not SQLSERVER?

Any suggestions would be appreciated.

Thanks Again TAR1325


2006-07-08 00:09:26.17 Server      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
      Oct 14 2005 00:33:37
      Copyright (c) 1988-2005 Microsoft Corporation
      Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-07-08 00:09:26.18 Server      (c) 2005 Microsoft Corporation.
2006-07-08 00:09:26.18 Server      All rights reserved.
2006-07-08 00:09:26.18 Server      Server process ID is 392.
2006-07-08 00:09:26.18 Server      Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2006-07-08 00:09:26.18 Server      This instance of SQL Server last reported using a process ID of 384 at 7/7/2006 11:20:29 PM (local) 7/8/2006 3:20:29 AM (UTC). This is an informational message only; no user action is required.
2006-07-08 00:09:26.20 Server      Registry startup parameters:
2006-07-08 00:09:26.21 Server             -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-07-08 00:09:26.21 Server             -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-07-08 00:09:26.21 Server             -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-07-08 00:09:26.26 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-07-08 00:09:26.26 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2006-07-08 00:09:32.46 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2006-07-08 00:09:34.65 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2006-07-08 00:09:35.29 spid5s      Starting up database 'master'.
2006-07-08 00:09:35.76 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-07-08 00:09:35.99 spid5s      SQL Trace ID 1 was started by login "sa".
2006-07-08 00:09:36.04 spid5s      Starting up database 'mssqlsystemresource'.
2006-07-08 00:09:36.82 spid8s      Starting up database 'model'.
2006-07-08 00:09:36.82 Server      Virtual Interface Architecture protocol is not supported for this particular edition of SQL Server.
2006-07-08 00:09:36.84 spid5s      Server name is 'DJ9QF3B1\SQLEXPRESS'. This is an informational message only. No user action is required.
2006-07-08 00:09:36.87 spid5s      Starting up database 'msdb'.
2006-07-08 00:09:37.31 Server      A self-generated certificate was successfully loaded for encryption.
2006-07-08 00:09:37.84 spid8s      Clearing tempdb database.
2006-07-08 00:09:39.85 Server      Server is listening on [ 'any' <ipv4> 1038].
2006-07-08 00:09:39.85 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2006-07-08 00:09:39.85 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2006-07-08 00:09:39.85 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.
2006-07-08 00:09:39.87 Server      The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. 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.
2006-07-08 00:09:39.87 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-07-08 00:09:40.81 spid8s      Starting up database 'tempdb'.
2006-07-08 00:09:41.07 spid5s      Recovery is complete. This is an informational message only. No user action is required.
2006-07-08 00:09:41.12 spid11s     The Service Broker protocol transport is disabled or not configured.
2006-07-08 00:09:41.12 spid11s     The Database Mirroring protocol transport is disabled or not configured.
2006-07-08 00:09:41.37 spid11s     Service Broker manager has started.
2006-07-08 01:00:42.00 spid1s      Server resumed execution after being idle 3021 seconds. Reason: timer event.
2006-07-08 03:57:15.51 Server      SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2006-07-08 03:57:19.37 spid11s     Service Broker manager has shut down.
2006-07-08 03:57:19.37 spid11s     Error: 17054, Severity: 16, State: 1.
2006-07-08 03:57:19.37 spid11s     The current event was not reported to the Windows Events log. Operating system error = 1717(The interface is unknown.). You may need to clear the Windows Events log if it is full.
2006-07-08 03:57:19.90 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2006-07-08 03:57:20.03 Server      The SQL Network Interface library could not deregister the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b. Administrator should deregister this SPN manually to avoid client authentication errors.
0
 
TAR1325Author Commented:
Also Found this in another error log

SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-07-10 01:48:08.53 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2006-07-10 01:48:09.18 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2006-07-10 01:48:09.20 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2006-07-10 01:48:09.21 Server      The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.  If you would like distributed transaction functionality, please start this service.
2006-07-10 01:48:09.21 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2006-07-10 01:48:09.23 spid5s      Starting up database 'master'.
2006-07-10 01:48:09.32 spid5s      SQL Trace ID 1 was started by login "sa".
2006-07-10 01:48:09.34 spid5s      Starting up database 'mssqlsystemresource'.
2006-07-10 01:48:09.53 spid5s      Server name is 'DJ9QF3B1'. This is an informational message only. No user action is required.
2006-07-10 01:48:09.53 spid9s      Starting up database 'model'.
2006-07-10 01:48:09.60 spid9s      Clearing tempdb database.
2006-07-10 01:48:09.71 Server      A self-generated certificate was successfully loaded for encryption.
2006-07-10 01:48:09.71 Server      Error: 17182, Severity: 16, State: 1.
2006-07-10 01:48:09.71 Server      TDSSNIClient initialization failed with error 0x7e, status code 0x60.
2006-07-10 01:48:09.71 Server      Error: 17182, Severity: 16, State: 1.
2006-07-10 01:48:09.71 Server      TDSSNIClient initialization failed with error 0x7e, status code 0x1.
2006-07-10 01:48:09.71 Server      Error: 17826, Severity: 18, State: 3.
2006-07-10 01:48:09.71 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.
2006-07-10 01:48:09.71 Server      Error: 17120, Severity: 16, State: 1.
2006-07-10 01:48:09.71 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.
0
 
Einstine98Commented:
if you have both SQL Express and 2005... then can you make sure SQL Express is stopped completely and then start 2005?
0
 
Eugene ZCommented:
try to disable Virtual Interface Adapter (VIA) protocol first
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=83593&SiteID=1

0. <SQL Server could not spawn FRunCM thread>
If the Force Protocol Encryption option is turned on on the server by using the Server Network Utility, communication between all clients and SQL Server is encrypted. Therefore, if a certificate is not installed on the computer that is running SQL Server, or if SQL Server cannot validate the certificate, SQL Server fails to start.

more:
How SQL Server uses a certificate when the Force Protocol Encryption option is turned on
http://support.microsoft.com/kb/318605

1. According posts above
your sql server 2005 name is

'DJ9QF3B1\SQLEXPRESS' - it means that it is named instance vs. default DJ9QF3B1
sql server service name that you need to start  -- SQL SERVER (SQLEXPRESS)
---
2. if you have service
SQL SERVER AGENT (MSSQLSERVER)
-It means you have another sql server 2005 deafult (main)
'DJ9QF3B1'
But
if you can not find service:
SQL SERVER (MSSQLSERVER)  -it is not good -so check again
-----
3.
Operating system error = 1717(The interface is unknown.). You may need to clear the Windows Events log if it is full.

---
4.
The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b

---
To use Kerberos authentication, you must make sure that all the following conditions are true: • Both the server and the client computers must be members of the same Windows domain or members of trusted domains.
• The server's service principal name (SPN) must be registered in the Active Directory directory service.
• The instance of SQL Server 2005 must enable the TCP/IP protocol.
• The client must connect to the instance of SQL Server 2005 by using the TCP/IP protocol. For example, you can put the TCP/IP protocol at the top of the client's protocol order. Or you can add the prefix "tcp:" in the connection string to specify that the connection will use the TCP/IP protocol.



--
more:
see article:


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/
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 8
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now