Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Randy Martin — Tue, 24 Jan 2006 00:17:22 GMT

This is a great list of troubleshooting tips. I would like to add one for your inspection and (if possible) resolution. This has been a stumper for me. Any help would be appreciated.
Randy Martin
randy.martin@ds-iq.com

OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message "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.".
Msg 782, Level 16, State 1, Line 0
SQL Network Interfaces: No credentials are available in the security package
OLE DB provider "SQLNCLI" for linked server "linkedserver" returned message "Client unable to establish connection".
Msg 782, Level 16, State 1, Line 0
SQL Network Interfaces: No credentials are available in the security package

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Julian — Tue, 24 Jan 2006 05:16:49 GMT

i need help with this.

I try to connect my project in visual studio with my database sql server express 2005 and...

An error has occurred while established a connection to 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

please any help to:
juliangrijalba@gmail.com

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 24 Jan 2006 21:30:46 GMT

Julian: If you’re trying to connect from a remote machine you will have to enable network protocols to enable remote connectivity.

This may help.
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Wed, 25 Jan 2006 21:12:18 GMT

Hi, randy

Here is our suggestion:

This might caused by the kerberos ticket in the session cache expires in the middle tier server.The situation could be first, you configure a linked server C on your machine B, and you connect to B from machine A by using SSMS and executing query of C; Secondly, you connected from A to B as your domain credential, and B connect to C using same credential based on your configuration. But if you kept the connection from A to B for a long time, your domain credential catche on B probably expires after a period of continuous use.

Possible resolution:

1) If your OS is WIN2K and XP, please refer follow KB artical

818173 Authentication May Intermittently Fail
http://support.microsoft.com/default.aspx?scid=kb;EN-US;818173

2) If not 1), on your machine B, please configure a new linked server by specifying the DSN equals C, and explicitly specify a sql login name and pwd in security instead of using current login credential.

If you still face the problem, please let us know your configuration and how you make connection, if possible, attach any info in the server log.

Thanks for your comments!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Steve — Thu, 26 Jan 2006 19:44:44 GMT

Hi, my SQL Agent 2005 service won't start. I cannot connect to the Profiler (2005) either. But both the SQl Server 2005 and sqlbrowser services are running and active. I have Sql Server 2000 on the same machine as the default instance and the SQL Server 2005 as the named instance wiht name "SQL2005".
The error log from starting sqlagent is the following:
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, 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. [SQLSTATE 08001]
[000] Unable to connect to server '<servername>\SQL2005'; SQLServerAgent cannot start
[298] SQLServer Error: 21, Encryption not supported on the client. [SQLSTATE 08001]
[298] SQLServer Error: 21, Client unable to establish connection [SQLSTATE 08001]
[165] ODBC Error: 0, 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. [SQLSTATE 08001]
[382] Logon to server '<servername>\SQL2005' failed (DisableAgentXPs)
[098] SQLServerAgent terminated (normally)

If I run sqlcmd -S instancename, I got the following error:
HResult 0x15, Level 16, State 1
Encryption not supported on the client.
Sqlcmd: Error: Microsoft SQL Native Client : Client unable to establish connecti
on.
Sqlcmd: Error: Microsoft SQL Native Client : 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.

I have both remote connection and tcp/ip enabled.
Any help is greatly appreciated!

Steve

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Sat, 28 Jan 2006 02:58:10 GMT

Hi, Steve

First, thanks for posting detail info to help us identify possible cause. Secondly, your problem might be either corruption of encryption setting or schannel corruption on your client side box.

1)Your client side setting probably was messed up. Please go to SQL Server Configuration Manager and click properties for "SQL Native Client Configuration", if correct, you should be able to see two flags, one is "force client encryption", the other one is "trust server certificate" and value were set "yes" or "no". If you see "No properties are available", that means your client registry key was messed up and leads to the connection error . To fix this, suggest reinstall SNAC(SQL Native Client).

2)If you are sure 1) was not the case, that might be schannel library somehow corrupt on your client box or no encryption support in you OS, and we need to collect more info, eg, see whether there is warning or error in the eventlog 'security'.

3)Please double check whether there is any error which indicates certificate or encryption in the server error log, and if there is, please let us know what kind of error displayed.

Most likely, your case is due to 1), to fix the messed up setting on your client, you'd better reinstall SNAC which is in tools part of Yukon installation.

Wish this help you!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Steve — Fri, 03 Feb 2006 18:10:56 GMT

Hi, Ming,
Sorry it took a little while. I wanted to make sure I tried your solutions before responding. Suggestion 1) helped. I did not see those two flags so went right ahead uninstalling and reinstalling the SQL Native Client.
Both the agent and profiler are now working.
Thanks very much for the help.
Steve

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Rajesh — Thu, 09 Feb 2006 22:41:46 GMT

Hi,
I am having a SQLNCLI problem similar to Randy's issue above, but slightly different.

I have configured a linked server C (SQL2000) on machine B (SQL2005), and I connect to B from machine A by using terminal Server and executing a simple query

INSERT INTO mydb..mytable -- this is on Machine B
Select * from C.mydb..mytable where ....

This is dealing with a data set of about 13 Million and it fails with a timeout error (details at the end of this message) and it fails exactly after 10 minutes.

Summary - Scenario 1:
A ->termServ->B->Run distributed query involving C (in management studio) or execute SSIS package. -- Failure either way after exactly 10 minutes.

I have this same operation going on on a different machine D, where this is part of an "Exec SQL task" inside a SSIS package.
And the package is schedule to run nightly and it does this part in about 30 minutes.
Last week this package started failing after 10 minutes of trying - before last week it was always successful and finished in roughly 30 minutes.

Summary - Scenario 2:
D -> Scheduled task involving a pkg doing a distributed query involving C -- Suddenly Started Failing after exactly 10 minutes and continued failing consistently.

So, I looked around and found a Remote Query timeout setting of 10 minutes on ServerC. I set this to 0 (unlimited), and this fixed scenario 2.
Scenario 1 continues to fail.

======Error===========================
OLE DB provider "SQLNCLI" for linked server "ServerC" returned message "Query timeout expired".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "ServerC" reported an error. Execution terminated by the provider because a resource limit was reached.
Msg 7421, Level 16, State 2, Line 1
Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "ServerC". .
======================================

Any thoughts or help is greatly appreciated.

Thanks,
Rajesh

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Rajesh — Thu, 09 Feb 2006 22:48:35 GMT

Forgot to include my email.
rajesh_vee@donotspam.hotmail.com

- email modified to prevent automated spam engines. Please cut out the donotspam part. Thanks.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Fri, 10 Feb 2006 06:02:59 GMT

Try setting the Remote Query timeout on server/machine B, or possibly on all servers involved, and let us know whether it makes any difference.

FYI, setting the value to 0 (infinity) is not recommended. Prfeerably set to to a value that is high enough for your purpose.

Thanks,
Peter Gvozdjak

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

skiblue — Mon, 20 Feb 2006 13:49:09 GMT

I try to connect to SQL Server 2005 using ADO:

(1)connect to a named instance(mynamedinstance),using SQL Native Client as the driver.

CString strConn =_T("Provider=SQLNCLI;Data Source=mypcname\\mynamedinstance;Initial Catalog=mydb;User Id=myuser;Password=mypw;");
_ConnectionPtr conn("ADODB.Connection");
_bstr_t bstrConn(strConn);
if( FAILED(conn->Open(bstrConn, _T(""), _T(""), 0)) )
{...}

Connect successfully!

(2)connect to the default instance, using SQL Native Client as the driver, but use DSN in the connection string.

CString strConn =_T("Provider=SQLNCLI;DSN=myDSN;Uid=myuser;Pwd=mypw;");
...
...

Connect successfuly!

(3)Conncet to a named instance(mynamedinstance), using SQL Native Client as the driver, using DSN in the connection string.

CString strConn =_T("Provider=SQLNCLI;DSN=myDSN;Uid=myuser;Pwd=mypw;");
...
...

Connect error!!!

Why (3) failed? Please help me.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Nan Tu — Tue, 21 Feb 2006 04:21:22 GMT

Provider SQLNCLI does not recognize DSN. So, in the second case, the DSN=myDSN is ignored and, possibly, connection successfully connect to local default instance. In the third case, the DSN=myDSN is ignored as well and connection cannot be established for named instance.

So the short answer is “Do not use DSN when using SQLNCLI provider”.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Francois — Thu, 02 Mar 2006 10:45:31 GMT

Hi guys, Im getting this connection error

[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)]

, but only when i move the application from my local machine (which connects fine) to a IIS server. What can the error be?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Agron Bauta — Fri, 03 Mar 2006 03:54:40 GMT

Hi,

the problem that is driving me nuts is this:

Error Message 5:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

1) You might have named pipe or tcp enabled and connect to a named instance, but SQL Browser service was not started or enabled. To enable browser, First, Use net start or go to sql configuration manager(SSCM), check whether sqlbrowser service is running, if not, start it; Secondly,You still need to make sure SqlBrowser is active. Go to SSCM, click properties of sqlbrowser service -> Advanced-> Active “Yes” or “No”, if sqlbrowser is running but is not active, the service would not serve you correct pipe name and Tcp port info on which your connection depends.

=====

I have enabbled and activated sqlbrowser but still get the error: 26 as above.

When I am running the >sqlcmd utility, connecting and quering the DB work fine.

Is there any other cause and solution to the error: 26 other than the above explanation. All the resources I found online say the same thing, which obviously is not good enough for me.

Regards,

Agron

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Agron Bauta — Fri, 03 Mar 2006 17:36:27 GMT

Sorry, I forgot to mention that my Application is not trying to access the DB remotely. The app and the DB are located in the same computer.

The connection string is the following:

<add key="connStr" value="data source=AGRON\SQLEXPRESS;initial catalog=INVENTORY;integrated security=SSPI;persist security info=False;workstation id=AGRON;packet size=4096;" />

Regards

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Sat, 04 Mar 2006 05:27:15 GMT

Hi, Agron

It seems you installed SQLEXPRESS. Normally, it is installed as a named instance and with TCP and Named Pipe are disabled by default.

To resolve this:

1)If you have shared momery disabled, either enable it or enable tcp,named pipe and restart SQL Server.

2)If 1) is not the case, and you still fail to connect even with TCP and Named Pipe enabled, please check which port or pipe name server is listening on( go to SQL Server Configuration Manager, click properties for *Protocols for MSSQLSERVER*), then specify exact port in your connection string:
data source=AGRON\SQLEXPRESS,<PortNumber> ..; if that works, that might be sqlbrowser issue. But remember double check whether server is listening on the configured port.

Here is useful info for SqlExpress

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Let me know if you have further questions.

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Agron Bauta — Sun, 05 Mar 2006 01:17:56 GMT

Thank you Ming,

I just uninstalled and reinstalled SQL Server Express, and now everything seems to be working fine.

Thank you for your help,

Agron

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Joe THomas — Tue, 14 Mar 2006 00:22:30 GMT

A connection I used for SQL 2000 doesn't work for 2005. App servers are in DOMAIN1 and SQL servers are in DOMAIN2. There is no trust between the domains.

Create DOMAIN1\User1 and DOMAIN2\User1 with identical passwords.

From App servers (DOMAIN1) I can create a DSN that uses Windows NT Authentication to SQL 2000 (DOMAIN2). Connection works fine.

From App servers (DOMAIN1) if I create the same DSN that uses Windows NT Authentication to SQL 2005 (DOMAIN2) - it fails.

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''. The user is not associated with a trusted SQL Server connection.

The domains aren't trusted because a firewall separates them. The app I am using requires NT Authentication -- I can't do SQL Server Authentication to get around this.

Any suggestions?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 14 Mar 2006 20:01:25 GMT

Hi, Joe

To identify your problem, we need further information:
1) Does your SQL 2000 run on the same box as SQL 2005?
2) What OS are they installed? Was SQL 2000 installed on WIN2K or WIN2K3?
3) How do you make connection to SQL 2000 and SQL 2005? Could you provide the connection string, which protocol you use to connect different server, TCP or Named Pipe?
4) Could you attach any info inside the Server Log file? The error you saw was returned by server response, so we believe helpful info should be logged.
5) Since the domain aren't trusted, the SQL Server 2005 behavior seems right. Could you provide more detail info about your domain credential configuration?

Thanks!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Joe THomas — Tue, 14 Mar 2006 21:49:42 GMT

1) Does your SQL 2000 run on the same box as SQL 2005?
No - separate servers.
2) What OS are they installed? Was SQL 2000 installed on WIN2K or WIN2K3?
SQL 2000 - Windows 2000
SQL 2005 - Windows 2003
3) How do you make connection to SQL 2000 and SQL 2005? Could you provide the connection string, which protocol you use to connect different server, TCP or Named Pipe?
TCP -- I am using the System DSN (under Admin tools, Data Sources).
4) Could you attach any info inside the Server Log file? The error you saw was returned by server response, so we believe helpful info should be logged.

Date 3/13/2006 5:23:47 PM
Log SQL Server (Current - 3/14/2006 2:00:00 AM)

Source Logon

Message
Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: 10.3.1.73]
-----------------
Date 3/13/2006 5:23:47 PM
Log SQL Server (Current - 3/14/2006 2:00:00 AM)

Source Logon

Message
Error: 18452, Severity: 14, State: 1.
---------------------
Date 3/13/2006 5:23:47 PM
Log SQL Server (Current - 3/14/2006 2:00:00 AM)

Source Logon

Message
SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 10.3.1.73]

5) Since the domain aren't trusted, the SQL Server 2005 behavior seems right. Could you provide more detail info about your domain credential configuration?
Sorry - I don't understand the question.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Nan Tu — Thu, 16 Mar 2006 01:28:31 GMT

Joe,

The solution to hop over untrusted domain are. Basically the authentication uses NTLM.

1) create local account with identical password on both machine. Say, create "testuser" on both machineA and machineB with password "testuserpass".

2) grant testuser permission to access SS2k5 on machine A.

3) run your client app as testuser on machine B.

In your case, eventhough the username are same, but the difference between domains make the access token SID actually different. By using machine local account, NTLM do not check domain credentials.

Hope this helps.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Thu, 16 Mar 2006 01:41:38 GMT

Joe,
In both cases, ss2k and ss2k5, you can check the login use name using "select suser_name()"

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Jeremy Holt — Thu, 16 Mar 2006 03:36:12 GMT

Hi, I'm in a bit of a panic.

I have successfully been running SQL Server 2005 (Integrated Security) on Windows Server 2003 SP1 for the last few months.

Ever since I installed the latest Critical Updates from Windows Updates this afternoon, all remote connections to SQL Server are being refused. The updates installed were:
Update for Windows Server 2003 (KB904942)
Update for Windows Server 2003 (KB912945)
Windows Malicious Software removal Tools - March 2006 (KB890830).

I am unable to connect to my remote server using SQL Server Management Studio on my local machine. None of my applications using the same connection string: "Data Source=my.server.com;Initial Catalog=myCatalog;Integrated Security=True" are working - each attempt to connect to the server returns this error message:

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) (Microsoft SQL Server, Error: 1214)

If I access the server through an internet application (i.e. one running on the same machine as SQL Server) - I have no problems, in that case the connection string is: "Data Source=(local);Initial Catalog=myCatalog;Integrated Security=True".

The server was under a sustained attack from some prick this morning trying the usual username = root, admin, sa, sql etc.

Please let me know if there is any more information I can provide to help in working out what's happened.

I am absolutely desperate ......

Regards
Jeremy Holt
jholt@nospam.amberwoodtrading.com (please remove the nospam bit).
Tel: +5585 3273 3444

The error log is

2006-03-15 16:52:58.84 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

2006-03-15 16:52:58.84 Server (c) 2005 Microsoft Corporation.
2006-03-15 16:52:58.84 Server All rights reserved.
2006-03-15 16:52:58.84 Server Server process ID is 236.
2006-03-15 16:52:58.84 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2006-03-15 16:52:58.84 Server This instance of SQL Server last reported using a process ID of 3948 at 3/15/2006 4:52:20 PM (local) 3/16/2006 12:52:20 AM (UTC). This is an informational message only; no user action is required.
2006-03-15 16:52:58.84 Server Registry startup parameters:
2006-03-15 16:52:58.84 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-03-15 16:52:58.84 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-03-15 16:52:58.84 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-03-15 16:52:58.85 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-03-15 16:52:58.85 Server Detected 1 CPUs. This is an informational message; no user action is required.
2006-03-15 16:52:58.95 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-03-15 16:52:59.01 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2006-03-15 16:52:59.04 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-03-15 16:52:59.04 Server Database Mirroring Transport is disabled in the endpoint configuration.
2006-03-15 16:52:59.07 spid5s Starting up database 'master'.
2006-03-15 16:52:59.22 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-03-15 16:52:59.50 spid5s SQL Trace ID 1 was started by login "sa".
2006-03-15 16:52:59.57 spid5s Starting up database 'mssqlsystemresource'.
2006-03-15 16:53:00.33 spid9s Starting up database 'model'.
2006-03-15 16:53:00.39 spid5s Server name is 'STAR'. This is an informational message only. No user action is required.
2006-03-15 16:53:00.69 Server A self-generated certificate was successfully loaded for encryption.
2006-03-15 16:53:00.70 Server Server is listening on [ 'any' <ipv4> 1433].
2006-03-15 16:53:00.70 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2006-03-15 16:53:00.71 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
2006-03-15 16:53:00.71 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2006-03-15 16:53:00.71 Server Dedicated admin connection support was established for listening locally on port 1434.
2006-03-15 16:53:00.72 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-03-15 16:53:00.72 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-03-15 16:53:00.79 spid12s Starting up database 'msdb'.
2006-03-15 16:53:00.79 spid13s Starting up database 'ReportServer'.
2006-03-15 16:53:00.79 spid14s Starting up database 'ReportServerTempDB'.
2006-03-15 16:53:00.81 spid15s Starting up database 'AdventureWorksDW'.
2006-03-15 16:53:03.95 spid9s Clearing tempdb database.
2006-03-15 16:53:04.22 spid12s Starting up database 'AdventureWorks'.
2006-03-15 16:53:04.31 spid15s Starting up database 'ATRBE'.
2006-03-15 16:53:04.40 spid14s Starting up database 'Florenzano'.
2006-03-15 16:53:04.45 spid13s Starting up database 'Iracema'.
2006-03-15 16:53:08.98 spid14s Starting up database 'StockControlSQL2001'.
2006-03-15 16:53:09.21 spid12s Starting up database 'StockControlSQL2002'.
2006-03-15 16:53:13.26 spid13s Starting up database 'aspnetdb'.
2006-03-15 16:53:16.75 spid9s Starting up database 'tempdb'.
2006-03-15 16:53:19.16 spid13s The Service Broker protocol transport is disabled or not configured.
2006-03-15 16:53:19.16 spid13s The Database Mirroring protocol transport is disabled or not configured.
2006-03-15 16:53:20.07 spid13s Service Broker manager has started.
2006-03-15 16:53:20.25 spid52 Using 'xpstar90.dll' version '2005.90.1399' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
2006-03-15 16:53:20.68 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-03-15 16:53:22.14 spid52 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2006-03-15 16:53:22.36 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2006-03-15 16:53:22.56 spid52 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2006-03-15 16:53:24.33 spid52 Using 'xpsqlbot.dll' version '2005.90.1399' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2006-03-15 16:53:25.07 spid52 Using 'xplog70.dll' version '2005.90.1399' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Neerincx [MSFT] — Thu, 16 Mar 2006 04:48:34 GMT

Hi Jeremy,

Sounds like someone perhaps tweaked the settings on Windows Firewall on your SQL Server machine (perhaps in response to someone trying to bust in as sa? Not sure).

Check Windows Firewall on the machine and make sure that there is a way for external applications to use tcp port 1433 to your SQL Server.

Take a look at this article for details on setting this up properly:

841249 How to configure Windows XP Service Pack 2 (SP2) for use with SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;841249

Note that this article applies equally well to Windows 2003 server as far as the Firewall settings go.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Jeremy Holt — Thu, 16 Mar 2006 14:25:50 GMT

Yes - you're absolutely correct. Our ISP had put a complete block on incoming packets to 1433 after I reported the attacks (but forgot to mention this to me).
By the time I'd sent this message to you, I had started smoking again - and I only gave up a week ago!!

By the way, what is the recomended way to protect SQL Server from attacks? The database is really an intranet - limited access to a very small group of users - however the users are located in various parts of the world, and for the most part use dynamic ip addresses provided by their local ISP.

Regards
Jeremy

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Joe THomas — Thu, 16 Mar 2006 19:21:45 GMT

Thanks for the help. I broke down and setup the domain trust. It is working. I just didn't want to poke those holes through the firewall, but I guess I have to.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Julius P. — Fri, 17 Mar 2006 11:31:54 GMT

Hi,

I am having a problem in regards to "Cannot generate SSPI context". Initially if the firewall was disabled (using windows2k3 and sql server 2005).. I am able to connect to SQL Server without having problem at all. Setting the suggested parameters in the firewall and enabling it, I am now stuck with this problem. I have tried following the troubleshooting procedures set to diagnose this but to no avail. Before trying to do anything else is there any other way to tweak the firewall settings? In addition I noticed if I initially disabled the firewall and connected to SQL server and disconnected the firewall again. Connecting to SQL server will not result with the same error? why is that?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Nan Tu — Tue, 28 Mar 2006 21:02:23 GMT

Julius,

"Cannot generate SSPI context" in your case indicates that your client machine can't access domain controller. Might be blocked by enabled firewall. Your second scenario is because Windows caches server credential when connect succussfully with domain controller and use that credential in subsequent connections.

To further understand your problem,
(1) What OS is on your client/server machine?
(2) Does your client/server machine part of a domain?
(3) Is your client login a domain account?
(4) Where the firewall seat at, network topologically?

Thanks,

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Flynn — Mon, 03 Apr 2006 18:46:29 GMT

Hi,

We recently upgraded from SQL 2K to SQL 2005 on WINDOWS 2003 Server. The entire upgrade process has run pretty smoothly aside from encryption.

We have ASP.NET 1.x apps that connect to an internal server but fail when encrypted. We have tried all of the steps available on the MS KB and still have not resolved the issue.

On http://msdn2.microsoft.com/en-us/library/ms191192.aspx, we find the following:
To configure the server to accept encrypted connections

1. In SQL Server Configuration Manager, expand SQL Server 2005 Network Configuration, right-click Protocols for <server instance>, and then selectProperties.
2. In the Protocols for<instance name> Properties dialog box, on the Certificate tab, select the desired certificate from the drop down for the Certificate box, and then click OK.
3. On the Flags tab, in the ForceEncryption box, select Yes, and then click OK to close the dialog box.
4. Restart the SQL Server service.

The problem is when we get to step 2, there are NO certificates available in the dropdown box.

This problem is solely the result of installing SQL Server 2005. The apps were successfully encrypting data connections prior to the install. All internal and non-encrypted connections work correctly and no other changes were made to the box aside from the SQL 2005 upgrade.

What can I do to get certificates to appear in the Certificate tab of the Properties dialog box?

Many thanks.

-Matt

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Neerincx [MSFT] — Mon, 03 Apr 2006 22:55:58 GMT

Hi Matt,

The certificates dialog in SQL Server 2005 is actually trying to help you. It will only display certificates that are valid for usage with SQL 2005 encryption. So it verifies the following things:

1. Enhanced Key Usage section of cert contains:

Server Authentication (1.3.6.1.5.5.7.3.1)

2. Certificate has private key.

3. Certificate is in correct store (should be in Local Computer store under Personal Certificates for SQL running under localsystem or network service). If your SQL is running under domain account, then it will look in the store for the domain account.

So take a look at your cert and verify the above 3 items and it should show up in the dialog.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Flynn — Mon, 03 Apr 2006 23:51:25 GMT

Matt,

Thank you for your response. I know we have #3 set up as you mention, and we're looking into #1 and #2. Unfortunately my knowledge of certificates is limited so I don't know how to set or even check on these properties without doing some research first.

We were able to open mmc and add the certificates snap-in, but we couldn't find any EKU or private key properties on the cert itself.

Thanks again.

-Matt F.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Neerincx [MSFT] — Tue, 04 Apr 2006 00:19:03 GMT

Open MMC snapin and locate your certificate and double click on it to open the "Certificate" dialog box.

On the General tab you should see something like:

Issued to: xyz
Issued by: xyz
Valid from <start date> to <end date>

Then below the Valid from... you should see:

"You have a private key that corresponds to this certificate"

If you don't see this, then the cert does not contain the private key.

For Enhanced Key Usage go to the Details tab and scroll down until you see "Enhanced Key Usage" section. If you select this you should see:

Server Authentication (1.3.6.1.5.5.7.3.1)

You may see more things in here, this is fine, but Server Authentication is required.

I know I ran into difficulty getting #2 to work with some methods of obtaining the certificate. Let me know how you are obtaining the certificate.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Nan Tu — Tue, 04 Apr 2006 00:38:32 GMT

Also pay attention to
(1) whether the issue to: xyz match the FQDN of your machine name,

(2) the expriation date of your certificate.

These two checks are added for sql servr 2005 among others.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Flynn — Tue, 04 Apr 2006 18:20:50 GMT

Speaking with my sys admins, they do see "You have a private key that corresponds to this certificate" as you mentioned and the cert does have server authentication checked in the EKU section.

Interestingly, when the cert is imported onto my machine I do not see "You have a private key that corresponds to this certificate" listed on the same cert. Also, I'm importing a cert with a p7b file extension and a FQDN, but it installs not with the FQDN. I know I'm missing s step here.

They are creating the cert using CA. We are currently trying different combinations of where the cert lives to try to resolve.

-Matt

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Flynn — Tue, 04 Apr 2006 18:26:38 GMT

One other note.... Just tried to install a .cer with the correct FQDN and it tells me it installed correctly, but it does not appear in the cert list on ie.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Neerincx [MSFT] — Tue, 04 Apr 2006 20:38:53 GMT

This sounds like when your admins are generating the certs, they are not enabling the following options when generating the cert. These options are under the "Key Generation Options" section ->

#1. You must check "Use local machine store".
#2. You must check "Mark keys as exportable".

Sorry this is so confusing, the cert folks seem to never make life easy for us mere mortals. (G)

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 04 Apr 2006 20:48:53 GMT

Matt,
You can try to post more info on
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1
the SQL Server Data Access Forum.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

MING LU [MSFT] — Tue, 04 Apr 2006 21:19:23 GMT

Hi,Matt

Beside the suggestions from Matt Neerincx, I would add following:

1) Your sql service should run under admin account, otherwise it has no permissing to the private key and thus the cert would not show up in the drop down list of SSCM, visit KB article for more detail:
http://support.microsoft.com/?kbid=900495.

2) SQL Server 2005 has a new kind cert called self-signed certificate, which means when server can not find a good cert to load in the cert store, it will load such a self-signed cert. In another word, you always can make encryption connection if you force server encryption, but, if you force client encryption, you will get error"certificate was not trusted" unless you specify trusting server cert through configuration,search more information in Books online.

3)Before upgrad to 2005, if you set any cert in the registry key, you should be careful whether it already expired or match condition of valid cert for 2005, otherwise, after installing 2005, you might not be able to see them in the cert list and server would load a self-signed cert.

Hope this helps!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Flynn — Fri, 07 Apr 2006 20:10:55 GMT

Ming,

The link you provided under #1 is clearly incorrect. Goes to a celebrities page of some sort.

Anyway, as soon as we upgraded, all of our ASP.NET apps that were using Encrypt=True in their connection strings simply stopped working. Nothing else on either box, the server or the client, was changed. We changed the connection string to Encrypt=False and the apps work like they should, aside from not being encrypted. So my question is, what could cause this?

We have tried just about every MS KB article and recommendation we can find. We created new certificates using a CA server. We made sure they have had private keys and allowed server authentication. Force protocol encryption is set to YES on the server.

W still have never been able to get the certificate we create to appear in the list. We are logging on the server as the domain admin and starting the service under the same account.

Any ideas why we can't see the cert in the list or why the encryption failed after a SQL upgrade?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

MING LU [MSFT] — Fri, 07 Apr 2006 22:07:22 GMT

Hi, Matt

First, Sorry about the wrong link, I meant to point to our KB article, it is http://support.microsoft.com/?kbid=900495.

Secondly, the encryption failure you saw is due to the reason that I desicribed in #2 of my last answer. Your server might load a self-signed cert(To verify this, you can go to server errorlog and see there is keyword "self-signed certificate"), and you forced client encryption through setting "Encrypt=True". If you already forced server encryption, the connection should be encrypted. And if you require forcing client encryption, you need to have server loading a certificate issued by a trusted CA.

Thirdly, please run "certutil.exe -v -store my", the tool would verify whether the certs you installed are valid, and send us info, that would help us to identify why cert not appear in the list; and try change sql service running under LocalSystem account, see whether any cert appear in the list.

Finally, we have questions about your scenario, whether you installed a cert in SQL 2000 before you upgrading and specifying server using such a cert? what is the reason you trying to install a new cert?

Let us know how things going.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Flynn — Fri, 07 Apr 2006 22:52:20 GMT

Thank you for your quick responses Ming, I really appreciate it!

Looking at the errorlog, I see the following:
A self-generated certificate was successfully loaded for encryption.

We need to force client encryption. How do we force the server to load a specific certificate for sql 2005? We have valid certificate installed.

We ran certutil.exe -v -store my and got the output. Do you want that displayed as well? I'm concerned that might pose a security risk, only because I don't know the details.

In terms of our scenario, we had a certificate installed for SQL 2K but as soon as we upgraded and the encrypted connections didn't work, we thought it was a certificate issue so we recreated hoping that would solve the problem.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Flynn — Tue, 18 Apr 2006 17:41:54 GMT

Here is the error we recieve from asp.net 2.0 when Encrypt=True...

"System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
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 System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at Security.GetSystemData() in C:\Inetpub\screen\App_Code\Security.vb:line 109
at Security.SecurityCheck() in C:\Inetpub\screen\App_Code\Security.vb:line 94"

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 18 Apr 2006 21:05:46 GMT

Check Server ERRORLOG and see if server load a self-signed cert? If you need force client encryption, you really need install a good certificate that valid for SQL Server 2005, otherwise, you can force server encryption to make encrypt connection if you are fine with the self-signed cert.

Thanks!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 18 Apr 2006 21:12:20 GMT

Since you are using ASP.NET 2.0, if you force client encryption and server load a self-signed cert, you can add one more connection string property "TrustServerCertificate=True" to make connection through. See detail in http://msdn2.microsoft.com/en-US/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx

Thanks!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 18 Apr 2006 21:47:12 GMT

One more thing might be related to the error is that your server is not running under an admin account if you think you installed a valid certificate but server still load a self-signed cert. To fix that,change the service account to localsystem or an admin account, but this might not you want, recommend keep sql service running under low priviliage accoun and use the suggestion that I posted in last comments.

Thanks!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Express Guy — Tue, 25 Apr 2006 07:47:33 GMT

Help!

I have recently installed SQL Express on my Web Server. I am running .Net 1.1. I am using the ADO.NET SqlClient. The SQL Server and the Web Server are on the same machine. Only local connections and shared memory are enabled. I have no trouble connecting to the server via Management Studio or sqlcmd. When I attempt to connect via ADO.NET, I receive a SQLException which says simply "Invalid Connection". This is the connection string:

Server=.\SQLEXPRESS;Database=SomeDb;trusted_connection=sspi;

What am I doing wrong? I would prefer not to enable remote connections or other protocols if possible.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 25 Apr 2006 21:38:29 GMT

Hi,

The error you saw indicates problem inside your client app,since you can connect through Whidbey and SNAC.
If possible, could you provide your application? Or could you try follow script and see what happens?
***********************************
using System;
using System.Net;
using System.Data.SqlClient;

namespace Sqlclient
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class LPCPrefixFallback
{

/// <summary>
/// The main entry point for the application.
/// </summary>
[STAThread]
static void Main(string[] args)
{
string strConn = @"Server=.\SQLEXPRESS;Trusted_Connection=TRUE;";

SqlConnection myConn = null;
//
// TODO: Add code to start application here
//

Console.WriteLine("Conn WIHOUT PRIFIX...");

try
{
Console.WriteLine("connection {0}",strConn);
myConn = new SqlConnection(strConn);
myConn.Open();
SqlCommand curComm = new SqlCommand("select net_library from sysprocesses where spid=@@spid", myConn);
string netLib = (string) curComm.ExecuteScalar();
Console.WriteLine("Net library: " + netLib);
}
catch (Exception ex)
{
myConn.Close();
Console.WriteLine("ex->" + ex.Message );
return;
}
}
}
}
*************************************

Thanks!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 25 Apr 2006 21:51:43 GMT

Also, please try "odbcad32.exe" through command line, and type ".\SQLExpress" as server name, if connection works, that means your .NET app has problem.

Thanks!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Alistair — Thu, 27 Apr 2006 19:19:27 GMT

Hi,
I've got a connectivity issue, on a server that is running both SS2000 and SS2005.

Each has a named instance.

* The client application can not connect to the SS2000 named instance. This is the problem.
* I can access the instance remotely, but only if I create an alias on the client (in IP, Ispecifying the port of the instance)
* The client application can not connect reliably using an alias. (sometimes it works...)

The problem seems to be SS2000's SQL Server Browser service, which keeps "terminating unexpectedly" (messages in the System Log) and gets re-started every 60 seconds.

I have no idea why this service is crashing. I guess it is needed to ensure visibility of the named instances, in this co-existence scenario.

So, I guess I need some clues on how to troubleshoot the browser service, or else a workaround for the instance visibility. ...???

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

wewew — Sat, 13 May 2006 08:42:06 GMT

wewe

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

bdotjones — Sat, 13 May 2006 09:39:33 GMT

Hi,

I've installed SQL Server 2005 Exprees on my PC and I keep getting this error when trying to connect to a database:

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: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server)

I went through the steps to try and correct it, including others, and I still am unable to connect
.
I am able to connect to the sever using the Management studio express

Any help would be greatly appreciated

Here's the error log:

2006-05-13 02:22:40.69 Server Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Express Edition with Advanced Services on Windows NT 5.1 (Build 2600: Service Pack 2)

2006-05-13 02:22:40.69 Server Error: 17054, Severity: 16, State: 1.
2006-05-13 02:22:40.69 Server The current event was not reported to the Windows Events log. Operating system error = 1502(The event log file is full.). You may need to clear the Windows Events log if it is full.
2006-05-13 02:22:40.69 Server (c) 2005 Microsoft Corporation.
2006-05-13 02:22:40.69 Server All rights reserved.
2006-05-13 02:22:40.69 Server Server process ID is 3780.
2006-05-13 02:22:40.69 Server Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2006-05-13 02:22:40.69 Server This instance of SQL Server last reported using a process ID of 2928 at 5/13/2006 2:22:33 AM (local) 5/13/2006 6:22:33 AM (UTC). This is an informational message only; no user action is required.
2006-05-13 02:22:40.69 Server Registry startup parameters:
2006-05-13 02:22:40.69 Server -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2006-05-13 02:22:40.69 Server -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2006-05-13 02:22:40.69 Server -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2006-05-13 02:22:40.69 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2006-05-13 02:22:40.69 Server Detected 2 CPUs. This is an informational message; no user action is required.
2006-05-13 02:22:40.88 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-05-13 02:22:40.90 Server Database mirroring has been enabled on this instance of SQL Server.
2006-05-13 02:22:40.90 spid5s Starting up database 'master'.
2006-05-13 02:22:41.02 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2006-05-13 02:22:41.15 spid5s SQL Trace ID 1 was started by login "sa".
2006-05-13 02:22:41.16 spid5s Starting up database 'mssqlsystemresource'.
2006-05-13 02:22:41.19 spid5s The resource database build version is 9.00.2047. This is an informational message only. No user action is required.
2006-05-13 02:22:41.38 spid5s Server name is 'BLEU-13\SQLEXPRESS'. This is an informational message only. No user action is required.
2006-05-13 02:22:41.38 Server Virtual Interface Architecture protocol is not supported for this particular edition of SQL Server.
2006-05-13 02:22:41.38 spid8s Starting up database 'model'.
2006-05-13 02:22:41.52 Server A self-generated certificate was successfully loaded for encryption.
2006-05-13 02:22:41.54 Server Server is listening on [ 'any' <ipv6> 1027].
2006-05-13 02:22:41.60 spid8s Clearing tempdb database.
2006-05-13 02:22:41.82 spid8s Starting up database 'tempdb'.
2006-05-13 02:22:41.88 spid11s The Service Broker protocol transport is disabled or not configured.
2006-05-13 02:22:41.88 spid11s The Database Mirroring protocol transport is disabled or not configured.
2006-05-13 02:22:41.88 spid11s Service Broker manager has started.
2006-05-13 02:22:42.32 Server Server is listening on [ 'any' <ipv4> 1027].
2006-05-13 02:22:42.32 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
2006-05-13 02:22:42.32 Server Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
2006-05-13 02:22:42.32 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-05-13 02:22:42.32 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-05-13 02:22:42.32 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2006-05-13 02:22:42.33 spid5s Starting up database 'msdb'.
2006-05-13 02:22:42.57 spid5s Recovery is complete. This is an informational message only. No user action is required.
2006-05-13 02:24:02.45 spid51 Starting up database 'ReportServer$SQLExpress'.
2006-05-13 02:25:02.40 spid51 Starting up database 'ReportServer$SQLExpress'.
2006-05-13 02:26:02.40 spid51 Starting up database 'ReportServer$SQLExpress'.
2006-05-13 02:26:02.72 spid52 Starting up database 'ReportServer$SQLExpressTempDB'.
2006-05-13 02:30:22.82 spid11s Service Broker manager has shut down.
2006-05-13 02:30:22.83 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.
2006-05-13 02:30:22.83 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

MING LU [MSFT] — Mon, 15 May 2006 20:35:54 GMT

Hi,

1) You were making local connection to SQLExpress, noticed it is a named instance, hence you should specify instance name in your connection string, eg:
"Provider=SQLNCLI;Server=<MachineName>\SQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI "; What does your connection string look like?

2) You were connecting through SqlClient provider,please do:
"sc query sqlbrowser", make sure sqlbrowser is running.

3) go to "..\programe files\microsoft sql server\90\tools\binn", run "osql /S \\.\pipe\SQLLocal\SQLEXPRESS /E", can you connect?

If you still face problem, please follow the guidline and provide more detail info, especially how you make connection in your case:

http://blogs.msdn.com/sql_protocols/archive/2006/04/21/581035.aspx

Thanks!

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Ricky — Thu, 25 May 2006 12:47:53 GMT

Hi, I am getting the following error when ever I am trying to connect to sql server 2005

"An error has occured 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)"

I tried to ping the server "ping xx.xxx.xx.x" and it works fine, but however when I try to "telnet xx.xxx.xx.x 1433" this however fails. When I log on into the physical box where SQL server is installed then I can connect, however if I try to connect from another machine, this error happen, Can any one please shed some light on this error, I am sure its some setting being incorrectly set, Arrrrrrrrrrrrrrrrhh

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Fri, 26 May 2006 07:50:52 GMT

Hi, Ricky

1) Did you install SQL Express 2005? if so, please how to enable remote connection according to below blog:

http://blogs.msdn.com/sql_protocols/archive/2006/04/21/581035.aspx

2) You can not telnet indicates that either you did not enalbe TCP or server did not listen on port 1433, it depends on whether you install sqlserver as a default instance and named instance, if it is named instance, please first, enable sqlbrowser services; secondly, enalbe named pipe and tcp and restart sql server make sure it started, check which port sql server is listening on, then try telnet by specify the port.

3) double check your connection string, see whether it specify the correct instance, if it is default instance, you can specify the <machinename>, if it is named instance, you need to specify <machinename>\<instancename>.

Good Luck!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Ranga — Mon, 12 Jun 2006 15:22:05 GMT

Hi...!

I need to log remotely to the SQL EXPRESS 2005. lets say i dont have any tools like SQL SERVER MANAGMENT STUDIO in da machine which is SQL EXPRESS installed. how can i access it from another machine which has SQL SERVER MANAGMENT STUDIO ?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 13 Jun 2006 05:51:43 GMT

Hi, Ranga

Please check following blog

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

There is a section called "Configure Express if you want to mak remote connection."

Good Luck!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

JBurgess — Fri, 16 Jun 2006 23:38:07 GMT

Okay, we have a similar problem that I haven't seen specifically mentioned here, so if there's another blog or resource I should be using, please let me know. Our problem:

Customers and DB Admin staff can connect to SOME 2005 instances, but not others. The problem instance will be different for each person having a problem, but others can connect fine to that same instance. In other words, it appears to be a random thing. Everybody but X can connect to Server1, everybody but person Y can connect to Server2. X can connect to all other servers but 1, and Y can connect to all others but 2. All our SQL instances are set for TCP/IP over a specified port, not the default. Named Pipes is disabled.

Any help would be appreciated!

Jack Burgess
State of Ohio
jack.burgess@ohio.gov

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Madrona — Sat, 24 Jun 2006 17:29:23 GMT

This is helpful but I still can't get it working. Here's the scenario:

Server A runnign SQL 2005 Standard (Two instances - 1 and 2).

Instance 1 hosts many customer databases
Instance 2 hosts an application db

I am writing sp's and functions to eventually be called from the application.

In order for most of them to work I need to have the application pass me the name of the customer database among other parameters.

THen I want to run a sp (or funtion) on the specified client Db. Example:

Declare @ExString varchar
Declare @ClientDb nvarchar(50)
Declare @Id int
Declare @TimeZoneOffset varchar

set @ClientDb = 'CLIENT_DB1'
set @Id = 123

set @ExString = 'SELECT Time_Zone_offset FROM ' + @ClientDb + '.dbo.TimeZone_tb WHERE Id =' + @Id

Execute (@ExString, @TimeZoneOffset OUTPUT)
AT [Instance1];

I am trying to execute this from SSMS either on my workstation or on the server itself.

I have setup Instance1 as a linked server and security is set to "Being Made using this security context:" with a valid SQL user account for the database on Instance1.

The message returned is:

Msg 7411, Level 16, State 1, Line 12
Server 'INSTANCE1' is not configured for RPC.

My questions are:

A. Am I approaching this the best way (using execute to pass the client database name? Almost everything I have to write needs to be database non-specific because of the number of client databases. Is there another, better way?

B. How do I configure RPC so that this will work?

Thanks for the prompt reply!

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Madrona — Sun, 25 Jun 2006 08:58:56 GMT

Got the RPC thing working. It also looks like sp_executesql will solve this issue.

But one question remains, is this the best way?

Login failed for user '' (Error: 18452)

Migue — Mon, 03 Jul 2006 02:05:23 GMT

Hello,

I have a problem that is disturbing to me.

I have installed SQL Server 2005 in a W2K3 joined to a workgroup. I'm using a local user account to execute the service, the Windows Authentication mode and local accounts for users.

Everything is working, unless I cannot connect from the same machine using FQDN or IP address. ie: from the server I can connect, without problems, using any of this:

tcp:localhost,1433
tcp:machine,1433

but I cannot be connected like

tcp:machine.domain.com,1433
tcp:x.y.z.t,1433

From other computers (using the same user + password) it works using anyone of the options (IP address, short name and FQDN).

Although I do not understand which is the problem, did not worry to me until I have seen that I cannot setup the Database mirroring, since it gives that error to me.

Any help would be appreciated

0) [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.

...

1) [Logon] Error: 17806, Severity: 20, State: 2
2) [Logon] SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 158.42.250.74]
3) [Logon] Error: 18452, Severity: 14, State: 1
4) [Logon] Login failed for user ''. The user is not associated with a trusted SQL Server connection. [CLIENT: x.y.z.t]

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Mon, 03 Jul 2006 21:18:07 GMT

Hi, Migue

The two quick workaround here is:

1) Enable Named Pipe, and connect through "np:machine.domain.com/
np:x.y.z.t"

2) Use SQL Authentication.

To identify the root cause, I have question for you:

a. Did your machine join domain before?
b. Is your machine connected to a network?
c. Can you also try "tcp:127.0.0.1,1433", see what happens?

Thanks!
Ming.

re: Login failed for user '' (Error: 18452)

Migue — Tue, 04 Jul 2006 00:58:33 GMT

Hi, Ming

thanks for your quick answer.

Answering your questions:

a. Did your machine join domain before?

No, from the beginning it is in a workgroup and it has never joined a domain

b. Is your machine connected to a network?

Yes. It's in a network with connectivity to the other computers and servers (DHCP, DNS, ...).

c. Can you also try "tcp:127.0.0.1,1433", see what happens?

It works well. When I noticed the problem I checked the connectivity and everything seems correct (telnet x.y.z.t 1433 also works)

And ... do you mean that Database mirroring works with named pipes? I had understood that the protocol was necessary tcp.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 04 Jul 2006 02:50:29 GMT

Hi, Migue

1)Thanks a lot for your response. Your scenario seems very interesting, it sounds like you try "tcp:127.0.0.1,1433" works, only "tcp:<FQDN>,1433" and "tcp:<IP>,1433" not work, right? We saw such issue on WINXP and WIN2K due to OS design, but not on WIN2K3.
So, sounds for you the current workaround is either use "tcp:127.0.0.1,1433" or SQL Authentication.

2)Yes. In DB-Mirroring scenario, you are required using TCP.

Basically, I will do some investigation and send you reply about the possible cause.

Thanks!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

suyog — Tue, 11 Jul 2006 09:22:22 GMT

OLE DB provider "SQLNCLI" for linked server "LinkerServerName" returned message "Communication link failure".
Msg 233, Level 16, State 1, Line 0
Named Pipes Provider: No process is on the other end of the pipe.
OLE DB provider "SQLNCLI" for linked server "LinkerServerName" returned message "Invalid connection string attribute".

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Fri, 14 Jul 2006 08:35:12 GMT

Hi, suyog

The error indicated that either you might have not name pipe protocol enabled on your server or your connection string is not correct.

To resolve the issue, could you
1) provide your connection string? how do you make connection? Is your server a default or named instance? did you add the instance name to your connection string? Did you make local or remote connection?

2) Can you go to SQL Server Configuration Manager to find your sql instance, and enable Named Pipe and then restart sever, and retry?

Good Luck!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Faisal — Tue, 25 Jul 2006 05:38:12 GMT

Hi,

I get following the errors, these are rare and random. The application is written C++ uses MS ODBC on SQL 7 with Windows NT4:

SQLError Info
SqlState 01000fNativeError 233
Error Msg [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionTransact (TransactNamedPipe()).
SqlState 08S01f NativeError 0
Error Msg [Microsoft][ODBC SQL Server Driver]Communication link failure

Thanks.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Wed, 26 Jul 2006 07:00:51 GMT

Hi,Faisal

We need more info to identify your problem, could you provide:

1) What is your connection string?, namely how do you make connection? Connect through server name or ipaddress?
2) Were you making local or remote connection? If remote, do you have firewall on your server? Did you have "file and printer sharing" added in the exception list? Or can you try whether you can access a share folder of your remote server?
3) Can you check your server errorlog to see whether there is any error info, the error indicates that server might close connection or you can open sql trace file to see which client operation cause server closing the connection.

Good Luck!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Van-Loc Doran — Sat, 05 Aug 2006 14:35:18 GMT

Hello Ming,
I'm unable to run my webapp (written in NET 2.0 and C#) from home. Could you please help?
Thank you.

The error is:
[SqlException (0x80131904): 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)]

Here is my connection setup:
<appSettings>
<add key="ConnectionStringXML" value="Server=sof2;Provider=SQLOLEDB;Database=myDB;UID=myUID;PWD=myPWD" />
<add key="ConnectionString" value="Server=sof2; Database=myDB;UID=myUID;PWD=myPWD" " />
</appSettings>

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Sun, 06 Aug 2006 04:19:28 GMT

Hi, Van
Can you answer following question:

1) Were you making local or remote connection?
2) Is your sql server a default or named instance?
3) Do you have sqlbrowser on?

Here is checklist of troubleshooting tips:

1) If remote connection, double sheck whether you have firewall on your server, if so, please add "File and Printer Sharing" to exception list; plus add sqlservr.exe to exception list; and add sqlbrowser.exe to exception list.

2) If you tried to connect to a named instance, change your connection string to set "<servername>\<instancename>" as the value of *Server* field.

3) If you were connecting to a named instance and it is remote connection, on your remote server, please do "net start sqlbrowser".

Also, see the section "Error Message 1:" in this blog, there are some other potential cause and resolution described.

Good Luck!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Dexter — Wed, 09 Aug 2006 18:11:12 GMT

Hello Ming -

Thanks for the great t-shooting list.
Despite this, I'm still having trouble getting a new linked server to work.
Server Mfr is 2005 sp1 and is the 'local' server.
Server Krypton is 2000 sp3 and is the target server I need to link to.
I have the Windows login on both systems and I'm in the sysadmin group on both servers.

After linking the servers, I ran this:
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'KRYPTON', @useself = 'true'

When I try running this query:
SELECT * FROM OPENQUERY(KRYPTON, 'SELECT * FROM CRICUST WHERE CustName = ''Acme''')

I get these errors:
OLE DB provider "SQLNCLI" for linked server "KRYPTON" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Can you see what I'm missing?
Thanks!
dj

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Wed, 09 Aug 2006 21:58:46 GMT

DJ,
I believe your case is single hop in that the client is collocated with the middle server.
Are you be able to login as the windows login on the local server and make successful direct SQL connection to the target linked server?
If you can, could you describe more about your setting?
(1) The version of Windows OS.
(2) The version of SQL.
(3) The user account is local account or domain account?
(4) The SQL service running account is local account or a domain account?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Jeorge Lukasing — Sun, 27 Aug 2006 13:01:13 GMT

Very many thanks for a good work. Nice and useful. Like it!

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

kihh — Sat, 09 Sep 2006 05:58:02 GMT

Hi,

I'm getting the same error as suyog (233 Named Pipes Provider: No process is on the other end of the pipe). I'm sure that named pipes is enabled, however.

I get this error when trying to log in with the SQL Management Studio.

Thanks for any help you can provide.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

MING LU [MSFT] — Sun, 10 Sep 2006 01:48:14 GMT

Hi, kihh

Assume you have named pipe enabled on your server:

1) Can you check the server errorlog the pipe name that server is listening on, try your connectiong string by specifying the pipe name, eg: "osql /Snp:\\<machinename>\pipe\sql\query" or "osql /Snp:\\<machinename>\pipe\mssql$<instancename>\sql\query" see whether you can connect?

2)Are you making remote connection? if so, could you check whether you turn on "Files and printer sharing" on your remote server firewall?

3)Can you check following blog, whether it is a potential cause?

http://blogs.msdn.com/sql_protocols/archive/2006/07/26/678596.aspx

4)The error you saw is intermitentely or consistently?

5)Is your sql server a default or named instance? If named instance, please check your connection string, whether you specify the correct server pipe?

Finally, when you were saying you have named pipe enabled, is it client side or server side, if you do not have named pipe enabled on server side, the cause might be

a. go to Sql server configuration manager,-> client protocols -> enabled protocol, put tcp on the top of np.

b. or turn off shared memory on your server side and restart sql server.

Good Luck!
Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

kihh — Sat, 16 Sep 2006 05:58:40 GMT

Hi,

Currently this is local.

Most advice I've read deals with point 5b. However, I can't figure out to sort them--I'm only using SQL Express if that has an effect--it seems like they just sort by name or status. Right clicking should have a move up or down option, but I can't find it.

It appears to be caused whenever more than one connection exists, though Max connections is set to 0. I could be wrong though. However, now, after a lot of trouble shooting, it seems to have subsided a bit. Sorry for the vague answer, but I don't know.

Is this something that will go away when I move to a host and SQL Server Standard?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 19 Sep 2006 22:25:40 GMT

Hi, kihh

Could you describe more specific about your problem? What if you set max connections to non 0? Are you able to connect to Express successfully? Or you faced some data operation issue? You can try to use same client app against SQL Server Standard or Enterprise or Dev sku, if the problem disappear, that may be caused by Express.

Following Forum can help you w/ that:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1

Good Luck!
Ming

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

riaan — Thu, 21 Sep 2006 09:29:56 GMT

Hi,
I get the above mentioned: "Login failed for user ''. The user is not associated with a trusted SQL Server connection." error.

But I get this error when connecting through a website on an IIS webserver. When connecting through a normal app the connection works fine.
So the problem seems to be with IIS.

I have a local account on both machines with the same password. The SQL server is set to mixed mode authentication. I am sure that the connection string is correct.

The SQL server 2000 is running on a windows 2003 server. I am running win xp and IIS 5.1.

Any help would be greatly appreciated.

thanx
riaan.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

page — Fri, 22 Sep 2006 13:25:05 GMT

We testig SQL server 2005 with 20 GB database for check limit of users who can login to SQL server. But we recive an error:
"A connection was successfully established with the server, but then an error occurred during the pre-login handshake. 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: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

We change SQL settings (max worker threads from 0 to 2048 (default was 0 - for 64 bit platform and 8 processors is value=576)

Is this action ok (we use long query)

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Mon, 25 Sep 2006 22:39:24 GMT

Hi,

1) Is the error you saw consistently or intermittently? If not consistently, could you reduce the CPU assumption when the problem occured? and see whether that helps?

2) Can you open SQL Server Profile and trace which client operation trigger the connection closed, did you see any error info in the server error log or system eventlog when problem occured?

3) Did the problem occure after your adjusting the "max worker thread"? or not? From books online, your configuration of that seems OK based on your system configuration, the problem probably during data operation, client or server close the connection. To open sql trace file and monitor SQL Server error log will help you get clue.

Good Luck!
Ming.

SQL Server 20005 Remote Connectivity Issue TroubleShooting

SQL Protocols — Sat, 30 Sep 2006 23:22:30 GMT

With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote...

SQL Server 2005 Remote Connectivity Issue TroubleShooting

SQL Protocols — Wed, 04 Oct 2006 06:26:37 GMT

With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote

Connectivity Issues with sql sERVER 2002

Vidya — Thu, 05 Oct 2006 13:15:45 GMT

Can you please help me with this problem

.Following is the problem I am facing when trying to remotely log into SQL Server 2005

· Got an error when logging in with SA::St0pLand0 – There was no process at the end of the pipe (or some such)

o Followed KB info to activate named pipe and TCP/IP access, restarted, getting same error

· After a couple attempts, SA account gets locked out

o Run script: Alter Login SA With Password = ‘St0pLand0’ unlock

o Indicates success

o Access sa login properties under Security\Logins – status area, shows “Login is locked out” checkbox checked.

· Also server seems VERY slow to respond at some points

Connectivity Issues with Sql Server 2005

Vidya — Thu, 05 Oct 2006 13:17:27 GMT

Can you please help me with this problem

.Following is the problem I am facing when trying to remotely log into SQL Server 2005

· Got an error when logging in with SA::St0pLand0 – There was no process at the end of the pipe (or some such)

o Followed KB info to activate named pipe and TCP/IP access, restarted, getting same error

· After a couple attempts, SA account gets locked out

o Run script: Alter Login SA With Password = ‘St0pLand0’ unlock

o Indicates success

o Access sa login properties under Security\Logins – status area, shows “Login is locked out” checkbox checked.

· Also server seems VERY slow to respond at some points

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Jay Larbes — Wed, 18 Oct 2006 17:40:00 GMT

Hi, on a web application that I develop, I just upgraded to a new database server running SQL Server 2005 and Windows Server 2003 Enterprise Edition. The previous database server ran SQL Server 2000 and Windows Server 2003 Standard Edition. Since this upgrade, the application has been causing "timeout" errors all over the application. I've been stumped. Here's the error mssage:

Microsoft OLE DB Provider for ODBC Drivers error '80040e31'

[Microsoft][ODBC SQL Server Driver]Timeout expired

I'm hoping there is some sort of a setting that can be modified to increase the maximum number of connections between the web server and database server. This is a fresh version of Windows Server 2003 and SQL Server 2005. Any support that can be provided will be greatly appreciated.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Goose — Sat, 21 Oct 2006 00:21:38 GMT

I have the same problem that many of the others where i'm getting the error about connecting remotely not allow. I tried all the solutions, but still have the problem. My issue is just a little different than the others. The program runs fine if I were calling it directly, however, i added a login page and change authentication to "forms". That's when i get the error. Any ideas?

Thanks

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Ajith — Fri, 27 Oct 2006 20:05:12 GMT

App: Classic ASP

Migrated from SQL2K -> SQL2K5

Legacy ConnString

conn.open

"Provider=sqloledb;Data Source=CLUSTERSQL,1433;Network Library=DBMSSOCN;Initial Catalog=AjithsDB", "uid", "pswd"

Still works for SQL2K5. DataShape gives me the following error

MSDataShape error '80040e14'

Provider command for child rowset does not produce a rowset.

So I tried ConnString as follows but it fails

conn.open "Provider=SQLNCLI;Server=CLUSTERSQL;Database=AjithsDB;", "uid", "pswd"

Also tried

conn.open "Provider=SQLNCLI;Server=CLUSTERSQL,1433;Database=AjithsDB;", "uid", "pswd"

I am not finding any examples of how to specify a port for SQLNCLI provider.

Please let me know if there is a workaround.

Thanks

Ajith

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

gsmani — Thu, 09 Nov 2006 05:39:52 GMT

Hi Ming,

I am having a peculier problem when using Site Manager for MCMS 2002 and i am using SQL Server 2005(standard) sitting on other side of firewall. I am able to access data sizes of 30 MB and below. When it exceeds, i am getting the following error message:

Import Preview

--------------------------------------------------------------------------------

Report Information

Package file: C:\PROGRA~1\MIB84C~1\Server\Temp\sdupload1.sdo

Generated on: 11/8/2006 6:33:21 PM

Report generated by: WinNT://NYCEDC-BAT-WEB1/MCMSAdmin

Client Machine: NYCEDC-BAT-WEB1

Client Version: 5.0.5054.0

Server URL: http://192.168.71.100:80/NR/System/Marshalling/

Server Version: 5.0.5054.0

--------------------------------------------------------------------------------

Error Information

An error has occurred that has interfered with the generation of this report. Please try the operation again, and contact your system administrator should this error persist.

Error ID: -2147352567

Source File:

Source Method: .Net SqlClient Data Provider

Source Line: 0

Description: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Recommended Action:

-------------------------------

Can you help in resolving this error?

Thanks

Mani

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Fri, 10 Nov 2006 10:37:04 GMT

Hi, Mani

Does this happen consistently or intermittently? The problem looks like either you lost network access to the server temporarily or sql server reject new connection.

You need first identify whether it is SQL Server issue or pure network issue.

1) You can check event log SQL Server to see whether transaction log full or server ran out of disk place.

2) Open SQL Server Profile, start a new trace, redo your client operation, watch what were server doing?

Or if you think this problem can only occured when you access data size larger than 30MB, please post your question to this forum, provide your detail data operation info.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=93&SiteID=1

3) If the error just happen intermittently, it might be due to bad connection, and you can see following KB article to troubleshoot:

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

Good Luck!

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

gsmani — Fri, 10 Nov 2006 17:49:39 GMT

Thanks Ming for your suggestion. Do you think the firewall plays a part in this? Is it invalidating the connection?

Thanks

Mani

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Mon, 13 Nov 2006 08:11:03 GMT

Hi, Mani

Did you enable your firewall during running your client application? What if you turn off firewall, see whether the problem repro?

BTW, for remote connection troubleshooting, you can see the following blog:

http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

Good Luck!

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Mark Skrotzki — Thu, 16 Nov 2006 19:10:36 GMT

I am trying to connect to a internet based webserver [sql14.aquesthosting.com] using SqlServer 2005 on a Windows XP 64 bit version.

I have activated remote connections, configured TCP protocol, opened the windows firewall {even turned it off for some tests]. I have spent the better part of a day reading Google and the archives of this [most helpful] blog.

I originally had the 64 bit version of Sql Server 2005 on this machine, but got the impression that Report Server does not work properly on this version. I uninstalled SS 2k5 64 bit version, and installed the 32 bit version of SS2k5

I have a 32 bit tablet computer connected to the same DSL line, with the {nearly as I can tell) same configuration and it connects perfectly -- and quickly.

Are there any special considerations for connecting with a 64 bit operating system?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Sun, 19 Nov 2006 10:09:22 GMT

Hi, Mark

Could you give more specific error when you connect from 64bit client? It should not make difference compared to 32bit if you

were making remote connection.

Good Luck!

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Sundar — Wed, 22 Nov 2006 23:42:54 GMT

We are getting the below error in a production environment. What would be the main cause of this issue? The message “Not enough storage is available to complete this operation” does mean the disk space on the server?

Exception Message: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - Not enough storage is available to complete this operation.) (type SqlException)

SQL Exception Class: 20

SQL Exception Number: 14

SQL Exception Procedure:

SQL Exception Line Number: 0

SQL Exception Server:

SQL Exception State: 0

SQL Error(s):

Thanks

-Sundar

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Fri, 24 Nov 2006 01:07:41 GMT

Hi, Sundar

1) Can you check any info in server errorlog when the problem repros?

2) Can you open sql profile to see whether large or incorrect data operation caused the problem? As far as I know, the error does not necessarily mean run out of memeory.

3) Can you collect ETW trace and send to us to further investigate?

Here is a guide to collect ETW trace.

http://blogs.msdn.com/sql_protocols/archive/2006/08/04/688396.aspx

Thanks!

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Marnee — Thu, 30 Nov 2006 02:02:21 GMT

Hello,

I am having a somewhat odd problem.

I was unable to create a connection to a SQL 2005 instance on a win2K3 server using an ASP.Net Framework 1.1 app running on IIS/Visual Studio 2003 on XP machine.

There are both a SQL 2005 and 2000 instance on the server. The SQL 2005 instance is a named instance. I decided to try configuring the two instances to listen on different ip addresses and ports as follows.

SQL 2005

Server is listening on [ 'any' <ipv4> 2602].

Server is listening on [ 192.168.2.99 <ipv4> 1312].

SQL 2000

SQL server listening on 192.168.2.9: 1433.

SQL server listening on 192.168.2.99: 1433.

SQL server listening on 127.0.0.1: 1433.

By specifying the ip address and port for the server instance in my connection string I can connect to the 2005 instance but not the 2000 instance.

Connection String:

Data Source=192.168.2.9,1433;Initial Catalog=DB;Persist Security Info=True;User ID=USER;Password=PASSWORD;Network Library=dbmssocn

However, I can connect (same username and password) to either instance without error using the Management Studio or the Visual Studio 2005 data connection browser but only if I specify the ip address and port of the desired instance. If I use the instance enumerated in the drop down, the connection is active refused.

I am imagining that it must be a kind of tcp/ip conflict. But I do not know how to resolve this.

Please advise.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Marnee — Thu, 30 Nov 2006 02:03:46 GMT

One more thing: I cannot connect to the 2000 instance using Enterprise Manager.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Marnee — Thu, 30 Nov 2006 02:38:53 GMT

No, actually I can register the 2000 instance in Enterprise Manager if I specify the ip address, not the name.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Jeff — Thu, 14 Dec 2006 19:52:29 GMT

I think your problem is the same one I'm having. Using SQL 2005 Mgmt Studio & trying to connect to a 2k DB using the .Net SqlClient Data Provider. Ent. Manager uses a different data provider & I was hunting for a way to set which data provider to use when connecting to various SQL servers. If the host allows the .net data provider you could probably connect to your db, just fyi

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Phil — Fri, 15 Dec 2006 18:30:30 GMT

I am having the same problem as Mani above. I can connect to my remote SQL Server 2005 just fine, but when I try to add a new table in Management Studio I get a (TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64).

It doesn't seem like it could really be a connectivity issue as I can access all other parts of the DB both before and after attempting this. (It does it constantly, not intermittently)

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Jeff — Thu, 21 Dec 2006 22:54:23 GMT

I recently upgraded sql express 05 to developer edition (win xp pro laptop). After struggling to get the upgrade to work, i finally unistalled all 2005 stuff and reinstalled developer edition from scratch using mixed authentication. Everything was great and i could connect to my databases no problem. When i started up my pc this morning I cannt start any of the services in the surface area configuration manager (MSSQLServer, SQL Agent, SQL Browser etc). I have been stuck on this for 4 hours, do you have any advice? Thanks! Do I need to change the login info for those services? How do I do that?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Fri, 22 Dec 2006 00:57:46 GMT

Jeff,

It would be helpful to know if the Windows Event Log has any error events for this problem and what the error message is.

Were the SQL services configured to run under some user account or a service account (like "Local System", "Local Service", or "Network Service")? If they were configured to run under a user account, did that user account password expire?

If you would like to view/modify the account in which these services run under, I recommend using the "Configuration Manager" tool and look under the properties for each of these services.

HTH,

Jimmy

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Fri, 22 Dec 2006 09:12:54 GMT

Hi, Phil

To resolve your problem, please take a look at following forum post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=984492&SiteID=1

Good Luck!

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Johan — Wed, 27 Dec 2006 18:24:28 GMT

Hi all,

I've got a little problem.

I'm working on a ASP.NET project. The applications has 4-layers. When I look in the Data Access Layer and test the typed dataset, then the "Preview Data" works fine. I see the right records from SQL Server 2005.

But when I run the app (default.aspx) in debug-mode, then the system gives the following error:

"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)"

Please help.....

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Thu, 28 Dec 2006 09:18:43 GMT

Hi, Johan

Did this happen intermittently or consistently?

1) If consistently, have you tried the tips in "Error Message 1" section of this blog? Plus, did you enable sql port in firewall if you were making remote connection?

2) If intermittently, can you open sql profile and see when the connection failed, is it during data operation? then you need check server errorlog to trace more detail.

Good Luck!

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Johan — Mon, 01 Jan 2007 15:49:47 GMT

ASP.NET application, Visual Studio 2005 and SQL Server 2005 are on the same development machine (WinXP Pro SP2).

Does the application use "remote connections" in this configuration?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Wed, 03 Jan 2007 10:48:16 GMT

Hi, Johan

I am not sure about your question. You can check following blog to know about configuration in remote connections to SQL 2k5.

http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

Good Luck!

Ming.

Troubleshoot Connectivity Issue in SQL Server 2005 - Part III - new message?

Regan — Fri, 12 Jan 2007 15:41:28 GMT

I seem to have come across a NEW error message not described in the 8 listed in the article.

Using SQLServer 2005, Enterprise Edition, SP 1 (9.0.2047).

I have a remote connection to a box (our DialBox), where we can remotely access our servers for that domain. Up until 2 days ago, I had no issues connection from DialBox, using SSMS, to a server. Since yesterday, I've been seeing this error message when I try and connection to the Registered Server:

--------------------------------------

TITLE: Microsoft SQL Server Management Studio

------------------------------

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The Local Security Authority cannot be contacted) (Microsoft SQL Server, Error: -2146893052)

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

--------------------------------------

I can connect to other servers, in that domain, from the DialBox.

If I remote through to the actual DB server from the DialBox, I can connect successfully.

Both connections are made using the same domain user (domainname\user).

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Sun, 14 Jan 2007 08:34:07 GMT

Hi, Regan

This is OS Error, which indicates that unreachable Domain Controller. It might due to the DC temparily shutdown. Try "nslookup" to find the DC, then troubleshoot the network between your computer and DC, such as

a. ping <DCname>

b. telnet <ipofDC> 445

c. telnet <ipofDC> 135

If you fail to do above steps, try to disjoin your computer and rejoin your computer to the domain.

Also, there are several articles talking about this problem and potential cuase, hope them helps.

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

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=129237&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=578673&SiteID=1

Good Luck!

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Regan — Mon, 15 Jan 2007 15:12:25 GMT

Ming, thanks for the repsonse - I'll have a look! If it is a DC issue, it's a bit surprising that other users can use the DialBox, and connect successfully. Might this be related to a specific profile (i.e. mine) and an expired kerebos ticket, or some such?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Regan — Mon, 15 Jan 2007 21:35:59 GMT

... and, given a weekend, the problem went away, so I may never know what the issue was. At least I can work on the DialBox again ...

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Shane G Eckel — Thu, 18 Jan 2007 22:57:52 GMT

Ming, thank you for your helpful instruction. I followed everything you mentioned but, for some reason, I still can't connect to SQL Server Express.

I have two computers on the office network, the Host with SQL Express (DELLDESKTOP1) and the Remote computer I'm trying to connect from.

On the Remote, if I go to Windows Explorer and enter "\\DELLDESKTOP1" in the Address, I'm able to 'see' the Host computer. Just the printers and shared docs.

On the Host, I've made sure that Shared Memory, Named Pipes and TCP/IP are all enabled. I've Enabled IP1/IP2 and added TCP Port 1434 for IP1/IP2/IPALL. the TCP Dynamic Ports under IPALL is blank.

The error I'm getting is the server timed out when I enter, "SQLCMD -E -S DELLDESKTOP1\SQLEXPRESS, 1434"

The error message I get is "Login timeout expired."

I have disabled the Windows Firewall on the Host just to take that out of the equation.

Again, I have followed all of you suggestions above and have run out of options. Thank you, thank you for any help you can provide. (if you can't tell already, I'm not a dba or a developer for that matter, just a newbie)

Thanks, Shane

shane.eckel@seattlesoftware.com

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Mon, 22 Jan 2007 09:00:15 GMT

Hi, shane

When you were using "sqlcmd -E -S DELLDESKTOP1\SQLEXPRESS, 1434", are you sure sqlexpress was listening on this tcp port? Please check:

1) telnet DELLDESTOP1 1434, see whether it succeeds?

2) go to SQL express ERRORLOG, check server has TCP enabled and listening on port 1434?

3) If 1) & 2) has not any problem, try enlarge the connection timeout, such as "SQLCMD -E -S DELLDESKTOP1\SQLEXPRESS, 1434 -l 120"

Note:Go to SQL server configuration manager, click properties of "Protocols for SQLExpress", check tcp was enabled and click properties, see the configuration of ip1, ip2, ipall, normally, you do not need enable ip1 or ipv2, suggest you disable ipv1 and ip2, let server listen on ipall, leave the dynamic port of ipall blank, restart the sqlserver and retry your connection.

Good Luck!

Ming

Named Pipes Provider, error 40 Could not open a connection to SQL

Named Pipes Provider, error 40 Could not open a connection to SQL — Tue, 23 Jan 2007 14:02:10 GMT

Hi,

I have a big problem, I have a website in a Windows 2003 and SQL Server

2005, and the website usually goes weel, but some times and error appears,

the error is "Named Pipes Provider, error: 40 - Could not open a connection

to SQL Server".

There is not installed a firewall and TCP and Pipes connection are enabled.

Any ideas?

Thanks in advance,

Miguel

SQL Server 2005 Connectivity Issue Troubleshoot - Part I

SQL Protocols — Mon, 29 Jan 2007 09:44:01 GMT

This post provides some tips to troubleshoot Sql Server connection problems based on various displayed

SQl Server 2005 Linked Server to SQL server 2000

Sankar — Tue, 30 Jan 2007 07:35:54 GMT

I am running a query from Server A (Sql Server 2005) which fetches 15 Million rows from a remote server B (SQL server 2000) This fails runs sometimes and fails some times

the error stmt as below

The OLE DB provider "SQLNCLI" for linked server reported an error. Execution terminated by the provider because a resource limit was reached.

Msg 7421, Level 16, State 2, Line 1

Any Suggestions what will be the potential cause

thanks

sankar

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Jim Colley — Mon, 05 Feb 2007 08:49:21 GMT

I have a really strange problem on our new Windows 2003 SQL 2005 active/active cluster where after approximately 48 hours the server is unable to create or accept socket connections. Windows authentication fails, clustered file shares fail, some applications recieve WSAENO_BUF errors, Windows is unable to create a secure connection to a domain controller, and many other socket related errors. Stopping and restarting the SQL instance resovles the issue for another 48 hours. The server has lots of memory available and MS Server Performance Advisor reports that the server is healthy.

I am stumped...The other instance running on the second node does not have this problem. The server ran without issues until we moved production databases to it from SQL 2000

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt — Mon, 05 Feb 2007 20:22:59 GMT

Sounds like SQL is leaking resources, most likely socket handles. In the past the few times I've seen this was with 3rd-party items running inside SQL, for example 3rd-party extended stored procedures (xprocs). You can verify this by looking at Task Manager and selecting View | Select Columns... check Handle Count, then monitor the Handles column for sqlservr.exe process. If you see the handles keep climbing over time then you have a handle leak inside SQL.

Check if you have any 3rd-party items running inside SQL (for example SQL Lightspeed, etc...) and contact these vendors to ensure you have the latest patches. You may also have your own extended stored procedure dlls verify that these are not doing things with socket handles or review the code to ensure you are not leaking handles.

Debugging what is leaking handles inside a process is not an easy to do unfortunately if you are not familiar with debugging tools if the above does not work I would open a support incident with Microsoft for assistance.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Jim Colley — Tue, 06 Feb 2007 19:46:12 GMT

I think you be right, if I watch the handle count for the sqlservr.exe it climbs constantly at a rate of around 240 hanles per hour. It seems to climb faster when a particular database and related XProc is being accessed. I also notice the system process hanldes climb relative to the handle count for the sqlservr.exe process, is that normal? I will investigate some more..Is there an actual limit to the number of handles a Windows 2003 server can manage before it runs into problems?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Neerincx (MSFT) — Tue, 06 Feb 2007 21:12:14 GMT

The limit is related to resources on the machine, there is not a hard limit to all handles. So if you have more RAM for example or are running the machine in /3GB mode it could affect the resource limits. /3GB mode means OS has 1GB and rest of apps has 3GB (versus normal 2GB each). In /3GB mode you can run out of system resources faster in most cases because you are taking away resources from kernel.

You could use process explorer to look at what specific handle types are leaking this might give you a clue as to the root cause (for example if you see 10000 file handles to c:\fooapp.dat file you know foo application is leaking handles).

You can get process explorer here:

http://www.microsoft.com/technet/sysinternals/ProcessesAndThreads/ProcessExplorer.mspx

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Sheetal — Wed, 14 Feb 2007 17:17:37 GMT

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Sheetal — Wed, 14 Feb 2007 17:32:15 GMT

Hi,

I am getting the below error while my ASPX page trying to connect with SQL Server 2005. Web application is running under the IIS.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Thu, 15 Feb 2007 05:01:44 GMT

Hi, Sheetal

Did you make local connection or remote connection?

If it is remote, you need to identify whether it is because of network issue or sql server close the connection.

To identify network issue, please check out the following blog "On client side" section:

http://blogs.msdn.com/sql_protocols/archive/2006/09/30/SQL-Server-2005-Remote-Connectivity-Issue-TroubleShooting.aspx

After making sure it is not network issue, please check out server ERRORLOG or open sQL profile to see which client operation caused the server closing connection.

Finally, whether this error occured intermittently or consistently from the beginning.

LMK if you have further questions.

Good Luck!

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Shum — Wed, 21 Feb 2007 09:10:06 GMT

Hi everybody!!

I'm trying to connect sql server 2005 on two remote computers and the error that i get is:

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

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) (Microsoft SQL Server, Error: 10054)

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

Any idea how it could be resolved.

Thanks

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

rob — Fri, 23 Feb 2007 18:23:27 GMT

hey, im getting this error

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)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: 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)

my connection string is

ive enabled the sql browser on the server and tc/ip is open, but i cant get rid of the error. maybe its a connection string problem?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Mon, 26 Feb 2007 20:55:43 GMT

Hi, rob

Please check out the following blog regarding connection to sqlexpress, you probably did not enable remote connection for sqlexpress.

http://blogs.msdn.com/sql_protocols/archive/2006/03/23/558651.aspx

Good Luck!

Ming

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

JeffT — Wed, 28 Feb 2007 23:47:30 GMT

Hi all.

While closely related, I am not using linked servers. Instead I am trying to use an open rowset command:

SELECT * FROM OPENROWSET(

'SQLOLEDB',

'Server=server-alias;Trusted_Connection=YES;Database=MyDb;Integrated Security=SSPI',

'Select * From SomeTable')

This returns the following message:

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Communication link failure".

Msg 10054, Level 16, State 1, Line 0

TCP Provider: An existing connection was forcibly closed by the remote host.

Msg 18452, Level 14, State 1, Line 0

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute".

There are some caveats to this.

1) I am going from 64 bit 2005 to 32 bit 2000

2) We are using groups and delegation via AD. There are no SQL logins and no direct access to the DB - only access via a group.

3) This occurs when people try to run the statement above from thier desktop.

4) I can fix the problem - for my login only - if I go to the server then run the query from there. After that, when I go back to my desktop it will run fine for that server only.

5) We are using TCP - unfortunately we can not switch from that.

6) We are using server aliases.

I think this is related to the 'double hop' problem, but I am unsure just how to fix it.

Any help would be GREATLY appreciated!

Thanks

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Josh Coady — Mon, 23 Apr 2007 22:59:54 GMT

I have an ASP.Net 2.0 app that is using sql express. Every couple days I get a "Timeout Expired" error message when creating a connection (not when running a query). This never happens on dev machine, just on production server. I am using a named instance of sqlexpress, sample connection string: "Data Source=.\sqlexpress;Database=MyDBName;Trusted_Connection=true;". This is all running on Win2K3 server under NETWORK SERVICE account. Any ideas?

Stack trace follows:

[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734867 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) +556 System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) +164 System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) +34 System.Data.SqlClient.TdsParserStateObject.ReadBuffer() +30 System.Data.SqlClient.TdsParserStateObject.ReadByte() +17 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +59 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111

free weight loss

Papidusik — Thu, 10 May 2007 20:21:23 GMT

<a href=http://idisk.mac.com/tolsto/Public/weight-loss-cure.html>weight loss cure</a>

<a href=http://idisk.mac.com/tolsto/Public/weight-loss-tips.html>weight loss tips</a>

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Tanu — Fri, 25 May 2007 22:13:53 GMT

Windows updates are scheduled for the server every 15 days. After windows update the machine reboots on its own but sql server does not start. If I reboot the mchine manually sql server starts without any problem. SQL services set to start automatically.

Can somone tell me what do I need to do to fix this problem.

Appication Log

Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.

FallBack certificate initialization failed with error code: 1.

Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.

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

TDSSNIClient initialization failed with error 0x80092004, status code 0x1.

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.

SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

But when I reboot it manually I got this messages:

Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.

A self-generated certificate was successfully loaded for encryption.

Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Mon, 11 Jun 2007 20:35:59 GMT

Hi, Tanu

This is interesting scenario. Which service account your SQL Server was running under? I suggest you post your question to our security forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

Good Luck!

Ming.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

John F - jbflowersATcomcast.net — Tue, 26 Jun 2007 16:45:07 GMT

Hi Ming, this blog is great info, thanks for all your hard work!

I am getting a very weird error and it is not two machines talking to each other, its a SSIS package trying to load data on the same box, the SSIS pkg uses Win auth and we are TCP - has anyone found a fix for this? its very sporadic but seems to occur when we are trying to load larger amounts of data - any help is greatly appreciated - thanks - Win2K3 64 bit, SQL2005 dev 64 bit SP2,

here is my error -

TCP Provider: The specified network name is no longer available.

Executing the query "execute [usp_NPRInsertFromNPRStagePrev]" failed with the following error:

"Communication link failure". Possible failure reasons: Problems with the query,

"ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Kev — Wed, 15 Aug 2007 03:07:31 GMT

There's a lot of great information here so I thought I'd try my luck.

I have a SQL Server 2005 db on machine 'B' which has a linked server to machine 'C'. I have credentials to access 'C' directly and I am able to successfully run queries to the linked server from B. Under the security settings for the linked server I have it set up to impersonate my local login.

Here's my problem. I have created an ASP application on 'A' that will connect to B and includes a query involving the linked server.

My connection string is built as follows:

Server=servername;Initial Catalog=database;Trusted_Connection = Yes";

I get the following error message:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. OLE DB provider "SQLNCLI" for linked server "ldrollup" returned message "Invalid connection string attribute".

Since I have mapped my login for the linked server to my login name, why is it using NT AUTHORITY\ANONYMOUS and how do I fix that? It might be worth noting that I have had similar issues using SQL Agent which I have only temporarily resolved by hard coding my user name and password as the owner of SQL Agent.

Any help would be greatly appreciated!

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Tushar Patel — Wed, 15 Aug 2007 20:32:11 GMT

Go to the SQL Server Network Configuration and make sure the protocols are enabled.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Kev — Thu, 16 Aug 2007 00:06:33 GMT

The only one that is not enable is 'VIA'. Should that be enabled? What exactly does that protocol do?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Gianfranco — Mon, 20 Aug 2007 12:25:46 GMT

Hi all,

here it is another connection issue.

I am trying to connect a SQL Server 2005 server from an XP machine. From OSQL and a ASPX application, everything goes well; but when I try to connect with SQL Server Management Studio, the program stops responding and seems waiting indefinitely for the connection: no error message is displayed. The same happens trying to connect the SQL Server on the local machine.

I am using Windows authentication, all protocols are enabled, both the server and the local machine allow remote connections, Windows Firewall is stopped and other collegues have no problem.

Anyone can help me?

Thanks in advance,

Gianfranco

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Aftab — Tue, 28 Aug 2007 12:51:59 GMT

Hi there,

I didn't have much problem connecting to the server and database but I did however have problem with starting up the Sql Server Agent Service. If you have the same problem you can try getting SQL Server SSIS and install it. Hopefully it will help you out!

Cheers!

Aftab

http://www.ComputerVideos.110mb.com/

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Aftab — Tue, 28 Aug 2007 12:52:19 GMT

Hi there,

Cheers!

Aftab

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Garick — Fri, 31 Aug 2007 16:18:24 GMT

Thanks to this article, I was able to fix a service broker problem.

Dot Net Tips and Tricks » Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Fri, 31 Aug 2007 16:27:20 GMT

PingBack from http://www.nootz.net/index.php/troubleshoot-connectivity-issue-in-sql-server-2005-part-iii.html

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Vedran — Thu, 27 Sep 2007 16:37:18 GMT

Hi there,

I've have a strange connection problem. The application is ASP.NET (1.1) and it is connection with no problem on SQL 2005 server (SSPI). Recently we have implemented threading and the problem is that when thread tries to access SQL it fails. It doesn't use correct credentials - the error in SQL is following "SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed". Do you have any ideas ? We now that it's works when we use SQL user and password is used which has a credentials on database.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

nicks — Fri, 28 Sep 2007 09:10:35 GMT

Hi,

I am getting the following error can you suggest?

SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 208.38.82.114

Nicks

Troubleshoot Connectivity Issue in SQL Server 2005 Express

Mary Jo — Fri, 12 Oct 2007 20:48:18 GMT

I am trying to connect to SQL Server 2005 Express through a command prompt. I have gone through the sites and followed the directions to change my settings to connect remotely but still having issues. Below is my error message:

SQLState = 08001, NativeError = 2

Error = [Microsoft][SQL Native ClientNamed Pipes Provider: Could not open a connection to SQL Server [2].

SQlState = HYT00, NativeError = 0

Error = [Microsoft][SQL Native Client]Login timed expired

SQLState = 08001, NativeError = 2

Error = [Microsoft][SQL Native Client]An error has occured 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.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

seif — Sat, 08 Dec 2007 17:50:54 GMT

I'm trying to connect to a local database on the same machine where i run my website.

when i specify (local) in the datasource i get the target machine actively refused error. When i use my ip address in the datasource i get the "connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond" error.

By the way, i enabled tcp and named pipes, i had tcp listenning to 1433 port., basically i did everything that could be done here.

Any help please?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Neerincx (MSFT) — Sun, 09 Dec 2007 03:12:27 GMT

Run netstat -aon on the machine to see what IP is bound to port 1433, you could have multiple IPs on the box. Also, the SQL Server ERRORLOG file should list exactly what IPs the server is bound to.

I recall from vague memories of debugging this code in the past that the server will just bind to all ips available on the machine.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Shreyas — Sat, 05 Jan 2008 11:20:05 GMT

Hi,

I have hosted a website on a hosting provider.

The SQL server data fetching works fine on a page when the page is in the root directory.

But when I add the page to a subdirectory and access the database, it gives error 26. I am using the same connection string as defined in the web.config..

Please advise.

Thanks

Shreyas

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Tintin — Thu, 31 Jan 2008 17:04:08 GMT

Hi SQL Team:

I am developping a website, I can connect to the production database when I debug the website from Visual Studio 2008, But after I deploy the website to my IIS server, I get the error message 1 above(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)); I try as below:

1. use the database on internet, in debug mode from Visual Studio 2008, it works fine.

2. deploy the website, change the database to use an intranet database server, the website works fine.

3. deploy the website, use the database on internet, it get the error message.

How can i figure out the error and resolve it?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

shawn — Wed, 06 Feb 2008 02:11:10 GMT

Changing the service "SQL Server (SQLEXPRESS)" from local service to network service fixed the problem for me.

can't get sqlsrv_ts.dll to connect from IIS server to remote sql srv

Paul.Richardson@TTU.edu — Tue, 04 Mar 2008 02:10:11 GMT

I installed SQL client connectivity tools, and now I got the more traditional (and php team supported 'official') extension called mssql_connect to work, and select/print from tables works ok now with EITHER the odbc_connect or mssql_connect. But I'm still not able to get sqlsrv_connect to work.

Here is the code I'm trying to use:

===========//BEGIN QUOTE//==========

<?php

/* Specify the server and connection string attributes. */

$serverName = "(subname.subname2.parent.topname)";

/* Get UID and PWD from application-specific files. */

$uid = ("myuserid");

$pwd = ("mypw");

$connectionInfo = array( "UID"=>$uid,

"PWD"=>$pwd,

"Database"=>"testdb");

/* Connect using SQL Server Authentication. */

if( !( $conn = sqlsrv_connect( $serverName, $connectionInfo)))

{

echo "Unable to connect.</br>";

die( print_r( sqlsrv_errors(), true));

}

/* Free statement and connection resources. */

sqlsrv_free_stmt( $stmt);

sqlsrv_close( $conn);

===========//END QUOTE//==========

Here is the error I get:

===========//BEGIN QUOTE//==========

Unable to connect.

Array ( [0] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 53 [code] => 53 [2] => [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53]. [message] => [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53]. ) [1] => Array ( [0] => HYT00 [SQLSTATE] => HYT00 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Login timeout expired [message] => [Microsoft][SQL Native Client]Login timeout expired ) [2] => Array ( [0] => 08001 [SQLSTATE] => 08001 [1] => 53 [code] => 53 [2] => [Microsoft][SQL Native Client]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. [message] => [Microsoft][SQL Native Client]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. ) )

===========//END QUOTE//==========

I have version 8.00.194 of ntwdblib.dll in sys32 dir and in c:\php\ and IUSR has full control over it.

At this point, I'm not worried about security, so much as getting this thing to work. I can tighten down later if need be. So I also gave IUSR read/write over the sql server subkey in the registry odbc hive

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Mugunthan — Tue, 04 Mar 2008 09:38:18 GMT

This does not look like an issue particular to php. This looks like a db connectivity issue.

See this post for troubleshooting connectivity issues:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Vivek — Tue, 22 Apr 2008 13:23:12 GMT

[MICROSOFT][ODBC SQL SERVER DRIVER][TCP/IP SOCKET]CONNECTIONOPEN (CONNECT()).CONNECTION FAILED

I'm facing this problem when I configure ODBC in Windows Vista for SQL Server from Windows Sever 2003, where the SQL Server 2000 is installed.

Please resolve this problem.

Thanks

ViveK

vivek@bsquareinternational.com

vivek_l2k@yahoo.com

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Sanjay — Mon, 28 Apr 2008 20:38:28 GMT

hello,

i have this connection string in web.config file

<add name="mycon" connectionString="Server=localost; Database=\db\test.mdf;uid="sanjay"; pwd="rpg007in""

providerName="System.Data.SqlClient" />

but in the above connection string i get error

Cannot open database "\db\test.mdf" requested by the login. The login failed.

everything in my code is same and its working properly on y local machine but on shard hosting service i am getting this error.

only change is above conenxcion string.

my databse was created on my local machine i used ftp to copy it on srver created a db folder under wwwroot while my code behind file is in subdomain

i am desperately looking for help

after two days of search i came across ur blog and i finally feel that this is where i will get solution.

email candychip [at ]gmail.com

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Somnath Dey — Thu, 19 Jun 2008 10:01:01 GMT

Objective: My source database is internet database server which is located by a Ip address. My

objective is to insert some data from internet database to local machine. Internet database has Windows

2003 standard Os, and sqlserver 2005 database server and local machine has Windows 2000 standard OS and

sqlserver 2000 database server. So I write a store procedure in internet database as follows

-----------------------------------------------------

exec sp_configure 'remote admin connections',1

reconfigure

INSERT INTO [dba\somnath].Webpatentfiling.dbo.patentInventor1

(INV_NAM,INV_ADD1,INV_ADD2,INV_ADD3,NATIONALITY )

select INVENTOR_NAME,INVENTOR_ADDRESS,

INVENTOR_STATE,INVENTOR_COUNTRY,INVENTOR_NATIONALITY

from OPENDATASOURCE(

'SQLOLEDB',

'Data Source=123.456.78.555;User ID=sa;Password=mfklj4Y'

).Efiling.dbo.TBL_APP_INVENTOR

where APP_ID='7'

At the time of execution the following error msg come

OLE DB provider "SQLNCLI" for linked server "dba\somnath" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "dba\somnath" returned message

"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.".

Msg 65535, Level 16, State 1, Line 0

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

So give me a solution

- Somnath Dey

error 17054 severity 16 state 1 sql server 2005

error 17054 severity 16 state 1 sql server 2005 — Mon, 07 Jul 2008 10:43:30 GMT

PingBack from http://nayeli.linkmediavideo.info/error17054severity16state1sqlserver2005.html

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

AlexWCY — Thu, 24 Jul 2008 12:12:04 GMT

Can anyone tell me what is the caused of below. I am facing this error, not always....sometime only...

My production is running Windows Server 2003 and SQL Server 2005.

Source = .Net SqlClient Data Provider

Error = A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - Not enough storage is available to complete this operation.) | at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

cosmosvert — Thu, 21 Aug 2008 19:22:58 GMT

Hello,guys,

I create a package SSIS locally, and there's a SQL task that executes a stocked procedure in ServerA, whoes domain name is AA, and the procedure selects items from a table in ServerB, whoes domain name is BB. The package SSIS failed to execute, and the error message goes like this:

[Execute SQL Task] Error: Executing the query "exec Procedure_Test" failed with the following error: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Can anyone tell me how to solve it, please? By the way, I used integrated authentication for the oledb connection.

Thank you in advance.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

feliks — Thu, 21 Aug 2008 23:54:59 GMT

i have problems connection to my SQL on the server, but not from my remote PC. The error is:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server, Error: -2146893019)

I can pay $100 if you can fix it

trakhtenberg@stanford.edu

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Mon, 25 Aug 2008 19:52:03 GMT

The problem seems to be with the SSL certificate being used by the server. My guess is:

1) Your server is loading a self-signed certificate

2) Your server is not enforcing encryption on inbound connections.

3) Your remote client machine is not enforcing encryption on outbound connections.

4) Your server IS enforcing encryption on outbound connections. (this is the source of your problem)

Since neither side enforces encryption with the remote client, it doesn't need the server to have a trustworthy SSL certificate. But, since the server, when opening an outbound connecting to itself, demands encryption, the server needs to have a trustworthy certificate - it tries to use the self-signed cert, and you get this error.

If that is the problem, here is a simple solution: disable client-side Force Encryption on the server. On the machine that runs the SQL Server instance, open up the SQL Server Configuration Manager, right-click SQL Native Client Configuration, and set Force Protocol Encryption to No. Then try connecting locally.

Hope that helps,

Dan

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Ray — Fri, 29 Aug 2008 10:22:52 GMT

on attempt to login to SQL Server 2005 sp2, standard edition in SSMS OR start in services, resulted in the following:

"TITLE: Connect to Server

------------------------------

Cannot connect to IBMSERVER.

------------------------------

ADDITIONAL INFORMATION:

I'm fairly new at this and have spent over 12 hours wading through technet articles that don't apply or don't resolve the issue. SS is configured to listen at port 1433, but if the service won't start, it can't listen, right?

More details:

System was working until a couple of days ago, when my external app that access SQL couldn't connect to the db. Attemted to login to SSMS under user sa (SQL authentication), but got the above error message. Attempts to login w/ Windows authentication also failed.

SQL Server 2005 is running under Windows Server 2008 on Virtual PC 2007 with all latest updates.

My busines depends on this working, & I don't know where to go from here. Can some bright guru provide some direction on what to check next?

raymillr[AT(change to @)]gate.net

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Fri, 29 Aug 2008 21:12:23 GMT

Hi Ray,

From the error message and the scenario you describe, I expect that your SQL Server service isn't started (you mentioned something about it not being started as well). You can start the service from services.msc. It will be named something like "SQL Server (MSSQLSERVER)" (if it is a named instance, replace MSSQLSERVER with the name of your instance).

If your service won't start, post the error message you receive when you try to start it, and if there is a new ERRORLOG file generated at %ProgramFiles%\Microsoft SQL Server\INSTANCE\MSSQL\Log\, get that and post the errors from the ERRORLOG file.

Also, just FYI, you'll generally get a better/faster response on the SQL Server Data Access forum (http://forums.microsoft.com/msdn/ShowForum.aspx?siteid=1&ForumID=87) than on our blog.

Hope this helps,

Dan

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Prasad — Tue, 09 Sep 2008 17:57:37 GMT

I am developing an application in VB 2008 which uses SQL Server 2005 database. The application is running perfectly well on the development server and I can execute my options and update tables like Product Master, etc. When I build the project and install it on another machine, I get the following error:

"SQL Server does not allow reote connections. (provider SQL Network Interfaces. error 26 - Error locating server / instance specified)"

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 09 Sep 2008 21:30:31 GMT

Prasad,

Can you make sure your connection string is updated correctly on the new machine? Please refer to

http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

for the specific error.

Thanks,

Xinwei

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Prasad — Wed, 10 Sep 2008 19:47:44 GMT

I am a newbie. How do I update the connection string.

I am well and truly stuck.

Thanks.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Sidney Benetti — Wed, 17 Sep 2008 00:05:35 GMT

Thanks, it solved my "error 26"

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Mahesh K — Fri, 19 Sep 2008 12:44:33 GMT

Hi,

I have installed SQL Server 2005 on a Vista Business. I am able to connect to SQL Server instance on Vista machine from Management Studio on other machine (Running on Xp).

But I am not able to connect to Analysis Services default instance on Vista Business machine from Management Studio on other machine (Running on Xp).

I did all settings with Vista firewall exceptions, SQL Surface area configuration, Security, everything.

Please tell me do I need to modify something else on 'Vista Business'.

Thank you.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

NB — Sun, 28 Sep 2008 17:04:30 GMT

I have problem in connecting to remote SQL Express instance, when Windows firewall is enabled:

1. It's a Workgroup environment

2. I can connect through remote desktop when firewall is enabled.

3. I can connect when I disable the Windows firewall

4. Port 1433 is added in exceptions

5. SQlserver.exe and SQLbroswers are added in execeptions

What am I missing?

Thank you

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

NB — Sun, 28 Sep 2008 17:06:49 GMT

Correction:

2. I can connect through remote desktop when firewall is DISabled.

Therefore its only a forewall problem.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Chris — Thu, 09 Oct 2008 02:16:27 GMT

its win 2003 64bit cluster with sql 2005 sp2 std ed 64 bit. SQL is running fine on one node but failing on another.. while checking agent seems to have problems.. i have disabled shared memory now but still problem is there. I did enable AgentXps but no luck..

2008-10-09 00:23:40 - ? [393] Waiting for SQL Server to recover databases...

2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Shared Memory Provider: Shared Memory is not supported for clustered server connectivity [50]. [SQLSTATE 08001]

2008-10-09 00:23:40 - ! [165] ODBC Error: 0, Protocol error in TDS stream [SQLSTATE HY000]

2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Client unable to establish connection due to prelogin failure [SQLSTATE 08001]

2008-10-09 00:23:40 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start

2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Shared Memory Provider: Shared Memory is not supported for clustered server connectivity [50]. [SQLSTATE 08001]

2008-10-09 00:23:40 - ! [165] ODBC Error: 0, Protocol error in TDS stream [SQLSTATE HY000]

2008-10-09 00:23:40 - ! [298] SQLServer Error: 50, Client unable to establish connection due to prelogin failure [SQLSTATE 08001]

2008-10-09 00:23:40 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)

2008-10-09 00:23:41 - ? [098] SQLServerAgent terminated (normally)

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Gary Tsiang — Fri, 05 Dec 2008 18:10:05 GMT

My new SQL Server 2005 instance shuts down every day at 3:00am but I don't recall ever setting anything to do so. I would prefer not having to manually restart this every morning. How do I correct this?

All I see in the error log is this:

2006-05-13 02:30:22.82 spid11s Service Broker manager has shut down.

2006-05-13 02:30:22.83 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.

2006-05-13 02:30:22.83 spid5s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Matt Neerincx (MSFT) — Fri, 05 Dec 2008 21:06:17 GMT

SQL does not have an "auto shutdown" feature.

I suspect you have automatic Windows Updates turned on any Windows is rebooting. You can verify by scanning event log (Start| Run -> eventvwr) If you see a Windows Update event prior to your shutdown that's probably the cause.

Recommendation is turn off automatic Windows Updates and use download automatically feature (but do not apply updates). This way you can precisely schedule when to apply updates and reboot.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Gary Tsiang — Fri, 05 Dec 2008 22:10:21 GMT

Thanks -- I just checked the Windows Event Log and found that, right after SQL Server shuts down, MSInstaller kicks off and tries to install/update SQL Server automatically (I think). Here are the log file messages at this point:

* Product: Microsoft SQL Server 2005 -- Install started.

* Product: Microsoft SQL Server 2005 -- Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.

* Product: Microsoft SQL Server 2005 - Update 'GDR 3068 for SQL Server Database Services 2005 ENU (KB948109)' could not be installed. Error code 1603. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\SQL9_Hotfix_KB948109_sqlrun_sql.msp.log.

* Product: Microsoft SQL Server 2005 -- Configuration failed.

* Product: Microsoft SQL Server 2005 Analysis Services -- Install started.

* Product: Microsoft SQL Server 2005 Analysis Services -- Error 29528. The setup has encountered an unexpected error while Setting Internal Properties. The error is: Fatal error during installation.

* Product: Microsoft SQL Server 2005 Analysis Services - Update 'GDR 3068 for SQL Server Analysis Services 2005 ENU (KB948109)' could not be installed. Error code 1603. Additional information is available in the log file C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\OLAP9_Hotfix_KB948109_sqlrun_as.msp.log.

* Product: Microsoft SQL Server 2005 Analysis Services -- Configuration failed.

* .NET Runtime Optimization Service (clr_optimization_v2.0.50727_32) - Completed all work. Shutting down.

Does this mean an auto-update flag for SQL Server is set somewhere? If so, why would it be failing every night? Again, I don't remember setting anything to do this but I guess I could have.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Gary Tsiang — Fri, 05 Dec 2008 23:32:02 GMT

Ok, I did find the Windows Update section and it was indeed set to download and install every night at 3:00am. I reset it to just download and prompt me. I'm thinking that this still doesn't explain why the downloads fail though. I'll let this run through first and see what happens when I manually intervene going forward. Thanks again!

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

MP — Tue, 30 Dec 2008 00:24:01 GMT

I see the following error in the event viewer of the SQL 2005 server. This is happening every day at between certain time interval. Please help. Thanks in advance

Server local connection provider has stopped listening on [ \\.\pipe\SQLLocal\MSSQLSERVER ] due to a failure. Error: 0xe8, state: 4. The server will automatically attempt to re-establish listening.

It might be the firewall

Blogger — Tue, 30 Dec 2008 17:31:19 GMT

It might be the firewall:

http://dotnetnotforcompletedummies.blogspot.com/2008/12/its-firewall.html

Error during connecting MSSQL 2005

bolivar1985 — Mon, 05 Jan 2009 12:05:39 GMT

Good Day to all,

I really need your advice guys,

anyway my problem is that i installed PHP4, MSSQL2005, and using IIS6 in Windows 2003 Server

I have no problems in the installation, but when i tried to connect to MSSQL an error would appear, error below:

mssql_connect(): message: Login failed for user 'sa'. The user is not associated with a trusted SQL Server Connection.

and also this

mssql_connect(): message: unable to connect to server: KPSWEB1

KPSWEB1 is the name of the MSSQL Server.

Please help...

Thanks

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 06 Jan 2009 02:27:04 GMT

MP,

Do you see any issue with this behavior? It looks like a client made a NP connection to the server after the Pipe is created, but before it's ready to accept connection. When we try to setup things to accept connection, we could re-active the existing client connection and thing would be OK. However, looks like in your case, client closed the connection before everything is ready and we logged this error. The server is robust to create another pipe to accept new connections after this. You can verify if this is the case by using Netmon to see how the NP connection is opened/closed.

Thanks,

Xinwei

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Tue, 06 Jan 2009 02:45:07 GMT

bolivar1985,

Looks like you have only Windows Auth. Can make sure SQL Authentication is enabled?

http://msdn.microsoft.com/en-us/library/ms188670.aspx

Thanks,

Xinwei

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

bolivar1985 — Tue, 06 Jan 2009 07:52:49 GMT

Xinwei,

Thanks, i will try your suggestion...

Thanks,

Bolivar1985

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Bolivar1985 — Fri, 09 Jan 2009 04:58:55 GMT

Xinwei,

It works, thank you, i just reinstalled MSSQL2005 Standard edition, then chose the mixed mode, and it run, using mssql_connect(),

Thanks,

Bolivar1985

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Tasleem — Fri, 09 Jan 2009 19:51:07 GMT

I have installed SQL server 2005 on my system (Window Vista Home Premium). However, I am getting error messgae when I try to connect. The message is "Cannot connect to ... Login failed for user ... error:18456". I have selected Windows Authentication mode during installation. Can any body help?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Enzo — Thu, 15 Jan 2009 20:59:07 GMT

Hello,

I'm experiencing an issue trying to setup an ODBC connection on a Windowns 2003 Server(64 bit)using SQL server 2005(64 bit).

It is a 32 bit application that will be using the ODBC datasource, so I am using the ODBC administrator located in C:\Windows\SysWOW64\odbcad32.exe.

However, I am receiving the Microsoft SQL Server Login errors indicating:

Connection failed:

SQLState: '08001'

SQL Server Error: 64

[Microsoft][SQL Native Client]TCP Provider: The specified network name is no longer available.

Connection failed:

SQLState: '08001'

SQL Server Error: 64

[Microsoft][SQL Native Client]Client unable to establish connection

Any assistance you can provide would be greatly appreciated.

Error al querer conectar una base de datos | hilpers

Error al querer conectar una base de datos | hilpers — Tue, 20 Jan 2009 22:58:24 GMT

PingBack from http://www.hilpers-esp.com/404764-error-al-querer-conectar-una

error 40 | keyongtech

error 40 | keyongtech — Thu, 22 Jan 2009 04:12:44 GMT

PingBack from http://www.keyongtech.com/2079472-error-40-a

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Karthik Vijayakumar — Sat, 31 Jan 2009 05:03:58 GMT

Out of nowhere, we got this error and are trying to figure out what the issue is.

OLE DB provider "SQLNCLI" for linked server "<server name>" returned message "Communication link failure".

Msg 64, Level 16, State 1, Line 0

TCP Provider: The specified network name is no longer available.

Any help would be greatly appreciated.

Thanks,

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Anand — Sun, 01 Feb 2009 20:09:43 GMT

Bad public user name or password. Database Server Error: Named Pipes Provider: Could not open a connection to SQL Server [53].

I am getting this message on installing Primavera

Can anybody help me...?

anandc1@gmail.com

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Paul — Thu, 26 Feb 2009 05:49:32 GMT

Hello,

I am building a SQL 2008 cluster. On the initial node that I ran the install on, SQL is running fine. However, whenever I try to fail it over, I am receiving these messages in the event viewer:

FallBack certificate initialization failed with error code: 1.

I do not want to use certificates. Is there any way I can bypass this?

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Barbara Johnson — Tue, 17 Mar 2009 20:49:14 GMT

Help!!! We are getting the following message on a SQL Server 2005 box. I could find anyone else who has had this problem.

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The client and server cannot communicate, because they do not possess a common algorithm.)

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Barbara Johnson — Tue, 17 Mar 2009 22:46:53 GMT

Our problem has been resolved. Someone had made some SSL registry changes which affected SQL Server and other applications.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Andrew Fiebert — Thu, 19 Mar 2009 17:16:13 GMT

I have a very unique issue related to Error: 26 - Error Locating Server/Instance Specified, I am hoping that your expertise may help.

I have a cluster running SQL Server with two instances, listening on ports 2300 and 2305. Connecting to the instances within the network works fine by either using VSPHOST1A\PHOST1A or VSPHOST1A,2300. I can also from within the network use VSPHOST1A.name1.name2.name3.com\PHOST1A or VSPHOST1A.name1.name2.name3.com,2300.

The problem is I can not access the SQL Server from another domain/network (within the same company) using the fully qualified name\PHOST1A, it only works with the port. I am however able to get both methods under the fully qualified name to work with non clustered instances. It is definitely cluster-related, any thoughts?

Thanks!!

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

SQL Protocols — Mon, 23 Mar 2009 21:21:26 GMT

Hi Andrew,

It sounds like you very likely have the problem discussed on this blog entry: http://blogs.msdn.com/sql_protocols/archive/2006/02/27/unable-to-connect-to-a-sql-server-named-instance-on-a-cluster.aspx

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

shahrbano — Mon, 11 May 2009 08:43:54 GMT

MMC cannot open the file c:\program files\microsoft sql server \80\toolse\binn\sql srver enterprise manager.msc

SQL Protocols Troubleshoot Connectivity Issue in SQL Server 2005 | Shed Kits

SQL Protocols Troubleshoot Connectivity Issue in SQL Server 2005 | Shed Kits — Wed, 27 May 2009 06:50:36 GMT

PingBack from http://backyardshed.info/story.php?title=sql-protocols-troubleshoot-connectivity-issue-in-sql-server-2005

SQL Protocols Troubleshoot Connectivity Issue in SQL Server 2005 | Green Tea Fat Burner

Mon, 08 Jun 2009 05:34:03 GMT

PingBack from http://greenteafatburner.info/story.php?id=1584

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Monichan — Mon, 20 Jul 2009 15:19:01 GMT

If you wants to know how to enable remote connections

in sql server 2005 pls visit this

http://aspnetmembershipprovider.blogspot.com/2009/02/eanble-remote-connections-on-instance.html

Regards

Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Rui Silva - Portugal — Wed, 22 Jul 2009 19:29:13 GMT

Solution:

After many tries in diferent ways we found the solution in simply change the connection string in the web config file. We take out:

"Persisty security Info=True; User ID=theuser; Password=thepass"

and simply keep:

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Tejas software — Wed, 02 Sep 2009 07:46:59 GMT

TITLE: Microsoft SQL Server Management Studio

------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

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

------------------------------

ADDITIONAL INFORMATION:

Failed to connect to server 192.168.0.1. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)

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

------------------------------

BUTTONS:

------------------------------

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Helen — Thu, 03 Sep 2009 16:51:49 GMT

Hi Ming,

I am getting this error when trying to connect to SQL server 2008 server

OLEDB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver]Timeoutexpired

Any suggestions/ideas, please?

Thanks

Helen

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Reader_ManZZ2@yahoo.co.uk — Tue, 29 Sep 2009 21:49:58 GMT

Guss what, i tried everything i saw for errors 26, 28, 40 when they come to me but as if magically they get solved until i suspected in my ADSL modem that i use for DSL internet and that has in it a switch of 4 ports, so i connected my development computer to a new switch and connected to it also the SQL Server workstation, and voila everything worked perfect from the start, the strange thing that when i was on the old ADSL modem, i can make file sharing and copy files but i can't connect to SQL Server.

So i suggest that the last step after the above is to check if changing the switch make the connection work or not.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

agnainx@msn.com — Thu, 01 Oct 2009 13:25:20 GMT

Hi,

I have the Error Message 2. SQL Server was working since about 2 years and this morning it just stopped to work.

I tried everything, even to uninstall then reinstall but now it can't be reinstalled because I have the same error when I tries to connect to configure the server.

I really really need help.

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

Benjamin — Thu, 12 Nov 2009 23:18:54 GMT

Hi, I can access my sql server from my laptop, but can not do it from desktop. Following is the error message. I turned off firewall on sqlserver and sqlbrower is running.

Thanks,

-Benjamin

TITLE: Connect to Server

------------------------------

Cannot connect to XYCOMVMEPC\SQLEXPRESS.

------------------------------

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

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

------------------------------

BUTTONS:

------------------------------

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

RANJITH — Tue, 24 Nov 2009 08:48:09 GMT

hi ming.. plz help me i am getting this error.. i am using sql server management express studio.. i was occupied to clear this error for 2 days.. plz help me..

Cannot open database "cms" requested by the login. The login failed.

thanks

ranjith

re: Troubleshoot Connectivity Issue in SQL Server 2005 - Part III

BAO — Tue, 01 Dec 2009 01:51:33 GMT

TITLE: Connect to Server

------------------------------

Cannot connect to BAO\SQLEXPRESS.

------------------------------

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol) (Microsoft SQL Server, Error: -1)

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

------------------------------

BUTTONS:

------------------------------