Solved

Can not connect to a sql 2005 server HELP

Posted on 2006-07-05
16
1,831 Views
Last Modified: 2008-01-09
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
Comment
Question by:TAR1325
  • 8
  • 5
  • 3
16 Comments
 
LVL 12

Assisted Solution

by:Einstine98
Einstine98 earned 250 total points
ID: 17047461
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
 
LVL 42

Accepted Solution

by:
EugeneZ earned 250 total points
ID: 17047486
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
 

Author Comment

by:TAR1325
ID: 17054459
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17055052
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
 

Author Comment

by:TAR1325
ID: 17055532
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
 

Author Comment

by:TAR1325
ID: 17068484
Hello Is there anyone who can help me with this issue

Thanks in advance

TAR1325
0
 
LVL 12

Expert Comment

by:Einstine98
ID: 17069098
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
 

Author Comment

by:TAR1325
ID: 17070367
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:TAR1325
ID: 17070627
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17070641
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
 

Author Comment

by:TAR1325
ID: 17070765
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17070781
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
 

Author Comment

by:TAR1325
ID: 17078489
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
 

Author Comment

by:TAR1325
ID: 17078670
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17078786
if you have both SQL Express and 2005... then can you make sure SQL Express is stopped completely and then start 2005?
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 17080541
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now