SQL Server 2005 Remote Connectivity Issue TroubleShoot

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Santosh — Wed, 03 Jan 2007 07:07:37 GMT

Dear All,

I am having problems in the connectivity from branch office to head office. I have a sql server 2000 installed on a Windows 2000 Enterprise server in head office.

There is client server application sitting in branch office and i have correctly setup the connection parameters from the app itself. But it always fails to connect to this server.

I believe this has something to do with SQL server port or something???

Pls help

Rgds

Santosh

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

SQL Protocols — Wed, 03 Jan 2007 10:45:26 GMT

Hi, Santosh

Please provide your connection string and error message you came across, plus, follow up section III in the blog to see whether that resolve your issue. Double check whether you can telnet your sql port on header office, whether the port is blocked by the firewall.

Good Luck!

Ming.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Named Pipes Provider, error 40 Could not open a connection to SQL — Tue, 23 Jan 2007 20:47:01 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

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

SQL Protocols — Mon, 29 Jan 2007 09:28:48 GMT

Hi, Miguel

Can you provide more detail info by answering following questions?

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

Thanks!

Ming.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

maks — Sat, 10 Feb 2007 12:20:54 GMT

guys can't you just make programs without bugs?? i did everything to allow remote connections and tomorrow it did not work. today i restarted server again and i did connected but after 15 minutes of work in management studio express i got disconnected and more than hour can't connect again. is it some kind of a joke? if (onlinetime > 15) { disconnect(); blockallconnections(); } ??

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Matt Neerincx (MSFT) — Mon, 12 Feb 2007 06:13:02 GMT

If you got disconnected mid-connection this is very unusual for SQL Server. Check your ERRORLOG and see if someone else is kicking you off (look for KILL commands in ERRORLOG).

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

David — Fri, 23 Feb 2007 22:15:49 GMT

When I try to use telnet to monitor sql2005 connectivity I'm getting an error says 17836 severity 20, state 1, Length specified in network packet payload did not match number of bytes read; the connection has been closed. Plase contact the vendor of the client library. In sql 2000, the same test is ok.

Thanks,

David

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Matt Neerincx (MSFT) — Fri, 23 Feb 2007 22:31:34 GMT

Yes, this is SQL Server 2005 reporting that you sent some invalid bytes to it in a request. SQL Server 2000 did not have this level of error reporting.

When you telnet to a SQL port, you are effectively acting like a client driver and starting the login process. If you type some things into the telnet session and this is sent to the server, then SQL Server will read these bytes and attempt to process the bytes as an incoming login or prelogin packet and this will fail and report the error to the errorlog.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

SQL Protocols — Fri, 23 Feb 2007 22:33:43 GMT

Hi, David

1) Is your SQL 2000 and SQL 2005 on the same box? If not, can you install SQL 2k on the sql 2k5 box, and do the same testing from the same client box? what happens?

2) What is your client request? namely, what data operation you are doing?

3) It'd better to provide more detail info about what is your testing? and Your testing environment, such as OS of client and server box, version, sku, protocol setting of your SQL 2005.

Thanks!

Ming.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

David — Sun, 25 Feb 2007 05:33:13 GMT

Thanks Matt Neerincx, is there anyway to get around it?

Ming,

1)No, not on same box, and this can be reproduced anytime anywhere with any machine.

2)the request is the cmd below:

telnet machinename 1433

quit

Basically this will login to sql on port 1433, and exit out.

3)I tested this on any sp level on sql 2000 ok, and sp1 and sp2 of sql 2005 getting an error.

I know it's probably better to use odbcping to monitor it, but the corp standard monitor server is an UNIX box, and I can not run odbcping on it.

Thanks,

David

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

SQL Protocols — Sun, 01 Apr 2007 03:08:43 GMT

This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting

re: SQL Server 2005 Remote Connectivity & non-default port

Oleksiy — Thu, 12 Apr 2007 00:04:39 GMT

I just want to add additional part here - issues with a non-default port 1433.

If your SQL 2005 server has to listen on something different than 1433 which can be a security requirement in large corporations, then it becomes additional problem to get it to work properly.

The sequence of the performed changes that I discovered:

- Install SQL 2005

- Run Management Studio for the first time, make sure all services are running

- Install SQL 2005 SP1

- Run Management Studio again, make sure everything still works

- Change the default port in SQL Server Configuration Manager (Server Protocols, Native Client Protocols)

- Add an alias to itself under Native Client with your non-default protocol:

Alias Name: MYSERVERNAME

Port No: your_port_number

Protocol: TCP/IP

Server: MYSERVERNAME

- Restart all SQL services

- Run Management Studio again, make sure it works this time and you can connect to the server.

The similar alias need to be configured under the Native Client configuration to connect to remote SQL 2005 server which is listenning on non-1433 port.

It looks like the SQL Browser service is responsible for the named instances but not for the non-default port configuraion. Each server running on non-1433 port need to have an alisas defining port number. This applies to the scenario with database replication between servers running on non-1433 port.

Oleksiy

Sql server 2000 engine getting disconnected automaticaly frequently

Yogesh — Thu, 12 Apr 2007 11:25:24 GMT

I am using MS SQL server 2000 with Widnows 2000 server, problem is sql engine is automaticaly disconnected where ever an event is generated.

pls help

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

abraham — Wed, 16 May 2007 00:49:27 GMT

I found an alternative, you can use the following sintax,

You can se the por number on sql server log in the sql studio manager or the sql configuration manager.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Carl — Sun, 20 May 2007 12:21:25 GMT

Hi,

I have a problem with a connection to database engine. Evert time when I connect it, it always gives me this 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

For the server name, I put in <my servername>\<my instance>. I checked sqlbrowser and it's running. I changed the remote connection in database engine to local and remote conncection, but it still doesn't work.

Please help me out. Give me a solution to this problem. Thanks so much for your help.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

MING LU — Mon, 21 May 2007 21:16:33 GMT

Hi, Carl

1) What is the error message in server ERRORLOG? You can also see it from system application event log. If there is problem that indicate sql server terminate client connection, that means your client request has problem, and it is out of connectivity scope, you need to check your client application.

2) Are you connecting Express? If so, please doublec check the instance name is "sqlexpress" in your connection string.If not, make sure you specifiy the correct instance name.

3) Open SQL Server Configuration Manager, click client protocols, check whether TCP/NP protocols are allowed for remote connection?

4) Look at your server ERRORLOG, see whether your sql instance is listening on the NP and TCP port.

5) On your client machine,do "new view \\<remoteserver>" and "telnet <remoteserver> <tcpportthatserver listing on>" See whether that works?

Good Luck!

Ming.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Carl — Mon, 21 May 2007 23:25:18 GMT

Hi Ming,

I checked the Errorlog and it shows:

Server is listening on [ ::1 <ipv6> 49299].

2007-05-20 18:09:28.92 Server Server is listening on [ 127.0.0.1 <ipv4> 49299].

2007-05-20 18:09:28.92 Server Dedicated admin connection support was established for listening locally on port 49299.

I also checked TCP/NP protocols are allowed for remote connection. I tried to telnet my remote server and it gies me this message:

Could not open connection to the host, on port 23: Connection failed.

I'm using SQL2005 Enterprise eval version. When I connect to database engine using <computer name>\MSSQLSERVER, it gives me this 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: SQL Network Interfaces, error: 25 - Connection string is not valid) (.Net SqlClient Data Provider)

I tried to uninstall the program and reinstall it again, but still the same. Could you please help me correct it? Thanks so much.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

MING LU — Tue, 22 May 2007 01:24:33 GMT

Hi, Carl

You connection string seems incorrect. You are connecting to the default instance, so, you do not need to specify "MSSQLSERVER", just the <computer name> in your connection string.

Good Luck!

Ming.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Carl — Tue, 22 May 2007 02:37:59 GMT

Hi Ming,

I tried it with just <computer name> and it gives me this 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: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)

How do I fix my connection string? Thanks for your help.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Stony — Mon, 28 May 2007 15:57:22 GMT

I have some problems with this remote connection thing. May be I should start with this question.

Do you have to have sql server installed on the client machine before can make a remote connection to server?

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Saif — Fri, 01 Jun 2007 21:22:04 GMT

Hi,

I am having hard time with my VS-2005. Please get me out of this. I dont want to use MSSQL-2005 with VS-2005 so i uninstalled it and in place of this i installed MSSQL-2000.VS-2005 is not showing it in server explorer. Also When i provide the connection string an error message come like this

"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: 25 - Connection string is not valid)".

I have done lots of R&D but not successful.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

MING LU — Fri, 01 Jun 2007 21:45:52 GMT

Can you provide the connection string to us for taking a look at.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Radu — Wed, 06 Jun 2007 15:02:27 GMT

In my situation, the things are pretty clear...I have installed on my machine SQL Server, and I try to connect to it, from my machine...and I get that error with "...does not support remote connections...". Well, I wonder why Microsoft, a multi-bilion dollars company, has this connectivity issue, when MYSQL, which is free, doesn't have it...I only need SQL Server for school (we are forced to use Microsoft software :( ), I don't need security and stuff...but of course, 90 % of the people that use computers have Microsoft software installed on them - yet. So please, use Linux, BSD, Solaris or any other open-source software...you won't have these issue...NEVER!

SQL Server 2005 Remote Connectivity Issue using an alias

Nancy — Thu, 14 Jun 2007 02:32:34 GMT

I have a remote connectivity issue using an alias.

I can connect from a remote computer using the named instance (OPERACIONESDC\DELFOS). This instance is using the 1435 port. I configured an alias for this instance. It is named OPERACIONESDC\ALIAS:

Alias Name: OPERACIONESDC\ALIAS

Port No: 1435

Protocol: TCP/IP

Server: OPERACIONESDC\DELFOS

The connection using the alias can be established locally, but it doesn´t work from the remote computer. The error is:

I checked the network configuration and every ports are open in the server, and the remote computer can listen the 1435 port, because it can resolve the OPERACIONES/DELFOS connection and a telnet connection to the 1435 port.

What should I check? Any Ideas? Please help me!

Thank you.

Regards,

Nancy

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

SQL Protocols — Thu, 14 Jun 2007 04:17:58 GMT

Nancy,

It might due to remote connection was not enabled. TODO:

1) Start sqlbrowser service on remote machine.

2) Add sqlbrowser.exe to Firewall exception list

3) Double check "File and Printer Sharing" was enabled on remote server.

4) Whether you typed correct remote machine name in your connection string?

5) Whether your remote machine has the same instance installed?

Good Luck!

Ming.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Nancy — Thu, 14 Jun 2007 19:23:15 GMT

Hi Ming

I still have problems.

I installed a sniffer in the client computer and I noticed that, when I call the named instance with the original name, the client make a requisition to the 1434 UDP Port, and the 1434 UDP port answer saying that the conextion must be established using the 1435 TCP port. The conexion using the 1435 port is estabilished and everything works fine.

Otherwise, when I tried to connect using the alias, the client make the call to the 1434 UDP port but there is not answer from the server, I expected an error or something, but it just doesn´t answer and the timeout present the error locating the server.

What can be happening? the sql browser server is active and started. I don´t have a firewall in the server, and in the client it is disabled.

I think the problem is with the sql browser services. When I connect to the named instance, is it working? is the 1434 UDP conection always answered by the sql browser service? why it answer when I use the original name, but not when I use the alias?

I understand that you suggest me to add the sqlbrowser.exe in the client firewall. I mean to use the conection using an ODBC, so I guess that I only have to install the conectivity tools in the client and I don´t have a sql browser service.

Thank you for your help... I hope you have some ideas.

Regards,

Nancy

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Nancy — Fri, 15 Jun 2007 22:12:22 GMT

I explain what I want to do, because I´m thinking that it is not possible:

I´m migrating SQL Server 2000 to SQL Server 2005 and I want to use an alias because there is an application in Visual Basic 6.0 that uses the SQL 2000 database, connected by an ODBC. I want to define an alias in SQL 2005 with the name of the instance in SQL 2000. I have done some test, and I found that is necessary to define the alias in the client too, instaling the SQL Server Native Connectivity. Is it true? is necessary to define the alias in the client?

What I have in mind is to define the alias in the server, and every computer using the VB application could connect to SQL 2005 without making any change in the computer.

Thank you for your help.

Regards,

Nancy

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

MING LU — Sat, 16 Jun 2007 20:34:28 GMT

Hi, Nancy

1) SqlBrowser service is with SQL Server, not on the client. Make sure if you do not use alias, just "osql /S <remoteserver>\<instancename> /E" succeeds.

2) Did you configure the alias on the client or on the server, you need to run "SQL Server Client Network Utility" on your client box, then specify the remote serve name(it should be like <remoteserver>\<instance> if you connect to a named instance(here is and tcp port(1435), alias name.

3) Try first "telnet <remoteservername> 1435" see whether it succeeds.

Then retry connection.

Good Luck!

Ming.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

SSharma — Fri, 22 Jun 2007 23:39:16 GMT

I get an error message when I try to connect a database to my web application :

(I am running both SQL server and the web application on a local machine)

I get the same error when I try to add a new SQL database in the server explorer.

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 doesn't allow remote connections (Provider : Named Pipes Provider,error : 40 - Could not open a connection to SQL Server )

Please help!

How do I configure SQL Server 2005 to connect the web application I am building using VS 2005 to the database?

Why does this error occur even when I am not making a remote connection ?

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Balaji — Sat, 30 Jun 2007 13:52:00 GMT

Hi,

I get no error with establishing connection with remote server by the port 5508. My local one is 2000. I have changed this port from 1434 local.

But, when i try to connect the databse, i find "No ITEM".

Could you please let me know, where I have the blunder stuck.

Thanks

Balaji

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

SQL Protocols — Sat, 30 Jun 2007 22:23:00 GMT

Hi, Balaji

What is the exact error message when you connect locally to SQL 2000?

Thanks!

Ming.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

simon — Thu, 05 Jul 2007 12:48:56 GMT

Just wondering if you can help me. I have Cisco CSA picking up the following message.The process 'C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe' (as user NT AUTHORITY\NETWORK SERVICE) attempted to accept a connection as a server on UDP port 1434 from 172.16.71.132 using interface Wired\Broadcom NetXtreme 57xx Gigabit Controller. The operation would have been denied.

I get this every 7 seconds from the same pc to another pc. Both used by our development team. I could open the firewall and stop the error but i would like to know why it trys every 7 seconds.

Thanks

Conection from Sql server 2005 to sql server 2000

Conection from Sql server 2005 to sql server 2000 — Fri, 06 Jul 2007 10:18:31 GMT

Hi,

I using SQL SERVER 2005 on my computer.

I want to conection to DATABASE on other computer. That Computer using SQL SERVER 2000.

I don't conection. What I do something? Help me, please.

Thank's

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Helen Steve — Tue, 10 Jul 2007 11:24:17 GMT

I am getting the same kind of error. I am stuck

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Agbons David — Sun, 12 Aug 2007 23:12:47 GMT

We resolved remote connectivity issue by runnning a name instance of sql 2005 on port 1420 and the browser service on port 1434. The Challenge now is to implement replication. Replication request actual name of server - it does not allow name pipe, ip or "<server>,port\instance" format when trying to connect to the publisher. Please hhhhheeeeeelllllppppppp me!!!

I have had long hours on this challenge.

Regards.

sql remote connection

Keith — Thu, 16 Aug 2007 17:23:01 GMT

Hi Ming

i feel like i am going around in circles but the end is in sight.

the problem is with remote connections. i have found a work around but it is not ideal. i am getting the network protocol: 26 error i am connecting to a named instance which is running on port 2005.

if i run the connection string slightly differently so that is <server>,<port>\<named instance> the remote computers can connect, so the question is what have i got to change so that the connection string is <server>\<named instance>

Now i have sqlbrowser running. i did notice that in the log i had the following

2007-08-16 18:50:38.22 Server Server is listening on [ 'any' <ipv4> 2005].

2007-08-16 18:50:38.22 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MS2005 ].

2007-08-16 18:50:38.22 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$MS2005\sql\query ].

2007-08-16 18:50:38.24 Server Server is listening on [ 127.0.0.1 <ipv4> 1631].

2007-08-16 18:50:38.24 Server Dedicated admin connection support was established for listening locally on port 1631.

2007-08-16 18:50:38.28 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.

which seems i think to say that the server maybe listening on different ports and get confused or am i missreading this

anyway any help would be appreciated.

keith

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Matt Neerincx (MSFT) — Thu, 16 Aug 2007 19:27:57 GMT

This looks normal to me. You main port is 2005 and your admin port is 1631. I suspect your firewall is blocking UDP port 1434 and this causes dynamic lookup of the port to fail. Open up UDP port 1434 and it should work.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Matt Neerincx (MSFT) — Thu, 16 Aug 2007 19:28:01 GMT

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

keith — Fri, 17 Aug 2007 05:29:38 GMT

hi matt

We have the firewall switched off on the server so it cant be the firewall blocking anything.

i have looked at all that. as i said if we can connect but we have to explicitly state the port even though sqlbrowser is working'

keith

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Matt Neerincx (MSFT) — Fri, 17 Aug 2007 20:08:41 GMT

Try starting sqlbrowser from command prompt like so:

net stop sqlbrowser

cd "C:\Program Files\Microsoft SQL Server\90\Shared"

sqlbrowser -c

This will show you in real time when incoming requests hit the browser.

Next, open notepad on the same machine where SQLBrowser resides and add following script code:

' START SCRIPT

set conn = createobject("adodb.connection")

conn.open "provider=sqlncli;data source=.\test;integrated security=sspi;"

' END SCRIPT

Save this to c:\testbrowser.vbs, then run:

cscript c:\testbrowser.vbs

You should see the following in browser console mode:

C:\Program Files\Microsoft SQL Server\90\Shared>sqlbrowser -c

SQLBrowser: starting up in console mode

SQLBrowser: starting up SSRP redirection service

SQLBrowser is successfully listening on 0.0.0.0[1434]

[3480]: Waiting for next request...

[3480]: Received request...

[3480]: Waiting for next request...

[3480]: Received request...

[3480]: Waiting for next request...

[5092]: Waiting for next request...

[4304]: Waiting for next request...

[4144]: Waiting for next request...

[3480]: Received request...

CLNT_UCAST_INST w/refresh from 123.123.123.123[4234]

CLNT_UCAST_INST from 123.123.123.123[4234]

[3480]: Waiting for next request...

[3480]: Received request...

CLNT_UCAST_INST w/refresh from 123.123.123.123[4235]

CLNT_UCAST_INST from 123.123.123.123[4235]

[3480]: Waiting for next request...

[3480]: Received request...

CLNT_UCAST_INST w/refresh from 123.123.123.123[4236]

CLNT_UCAST_INST from 123.123.123.123[4236]

[3480]: Waiting for next request...

[3480]: Received request...

CLNT_UCAST_INST w/refresh from 123.123.123.123[4237]

CLNT_UCAST_INST from 123.123.123.123[4237]

[3480]: Waiting for next request...

[3480]: Received request...

CLNT_UCAST_INST w/refresh from 123.123.123.123[4238]

CLNT_UCAST_INST from 123.123.123.123[4238]

[3480]: Waiting for next request...

[3480]: Received request...

CLNT_UCAST_INST w/refresh from 123.123.123.123[4239]

CLNT_UCAST_INST from 123.123.123.123[4239]

[3480]: Waiting for next request...

[3480]: Received request...

CLNT_UCAST_INST w/refresh from 123.123.123.123[4240]

CLNT_UCAST_INST from 123.123.123.123[4240]

[3480]: Waiting for next request...

[3480]: Received request...

CLNT_UCAST_INST w/refresh from 123.123.123.123[4241]

CLNT_UCAST_INST from 123.123.123.123[4241]

[3480]: Waiting for next request...

[3480]: Received request...

CLNT_UCAST_INST w/refresh from 123.123.123.123[4242]

CLNT_UCAST_INST from 123.123.123.123[4242]

[3480]: Waiting for next request...

This will confirm that sqlbrowser service is listening at least locally.

Next on a remote machine, run the following script (change MySQLServer to your actual target server).

' START SCRIPT

dim conn, serverName

serverName = "MySQLServer"

wscript.echo "Attempting to use SNAC driver first, which will send browser request even locally..."

set conn = createobject("adodb.connection")

conn.ConnectionTimeout = 5

on error resume next

conn.open "provider=sqlncli;data source=" & serverName & "\test;integrated security=sspi;"

wscript.echo err.description

on error goto 0

set conn = nothing

wscript.echo "Attempting to use SQLOLEDB driver next, but it will not send browser request locally..."

set conn = createobject("adodb.connection")

conn.ConnectionTimeout = 5

on error resume next

conn.open "provider=sqloledb;data source=" & serverName & "\test;integrated security=sspi;"

on error goto 0

wscript.echo err.description

set conn = nothing

' END SCRIPT

Examine console output from browser to see if it makes it through.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Michael — Wed, 22 Aug 2007 18:47:28 GMT

Hi All, connecting to a server running Windows Server 2003 Enterprise. Have SQL Server 2005 running nicely. Can connect from an asp page (connecting string below) from one server, but not another... thoughts?

String:

mydb="PROVIDER=MSDASQL;DRIVER={SQL Server}; SERVER=<server>.<domain>.xyz;DATABASE=DemoSolomonApp60; UID=****;PWD=******;"

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Ramy — Sun, 26 Aug 2007 17:21:18 GMT

i'm using sql server 2005 developer edition with VS2005 team suite when i'm trying to add connectiong using sql server database file it gives me error

although i allowed all remote connection but this error still appear

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

mike skramstad — Wed, 29 Aug 2007 00:53:34 GMT

Try using connection string like this (set the port number, uid, pwd, and database name appropriately):

strConnect = "driver={SQL Server}; server=x.x.x.x:1433; uid=usernamehere; pwd=guessme; database=databasenamehere"

To nicely handle occasional SQL connectivity problems that can break this auto maker's web applications and a backend leads daemon, I wrote a Visual Basic script to monitor processes and their SQL connectivity, and to restart processes, thereby increasing the systems' fault tolerance.

Here is a short description of what the function, below, does. It checks an output file for "SQLexception" errors generated by the Leads daemon. When the SQLexception text is found it then tests the SQL server for signs of life, and then restarts the Leads daemon.

Code snippet from the script:

Function TestLog(logfile)

DIM myCounter, myLog

myCounter = 0

myLog = Slurp(logfile)

TestLog = -1

if instr(1, myLog, "SQLException", 1) Then

' kill all Leads daemon PIDs

Call KillPIDs()

' copy log file to new name and delete old log file

Set fso = CreateObject("Scripting.FileSystemObject")

Set f2 = fso.GetFile(logfile)

fs.WriteLine Now & " exception found in log file "

MyNow = Replace(FormatDateTime(Date, vbShortDate), "/", "-") & "_" & Replace(FormatDateTime(Time,vbShortTime), ":",".")

f2.Copy (logfile & "-" & MyNow & ".err")

f2.Delete

why = "'SQLException' was encountered in file " & logfile & ". I will test the SQL connection every minute and when it is alive I will restart the leads daemon and I will send another email notification stating that the leads daemon was restarted. Otherwise, I'll send a failure reminder every six hours."

subj = "SQL Connection Error. Javamon3 killed Leads Daemon"

Call SendMail(strToAddr, strFromAddr, why, subj)

' test SQL connection

Set conn = CreateObject ("ADODB.Connection")

conn.ConnectionTimeout = 20 'wait seconds

myCounter = 0

On Error Resume Next

Err.Raise = 6 ' raise any error

Err.Clear

Conn.Open strConnect

' MsgBox "inside loop Error # " & CStr(Err.Number) & " " & Err.Description

wscript.sleep (1000 * 60 * 1) ' wait 1 minute

IF Err.Number = 0 Then

Exit Do

End If

myCounter = myCounter + 1

If myCounter MOD (60 * 6) = 0 Then ' (6 * n) hours have passed

why = "Leads Daemon has been dead for " & (myCounter/360)*6 & " hours."

subj = "SQL Connection Error. Leads Daemon has been dead for " & (myCounter/360)*6 & " hours."

Call SendMail(strToAddr, strFromAddr, why, subj)

End If

Loop Until Err.Number = 0

Conn.Close

subj = ""

why = "The SQL connection was re-established. I will now restart the Leads daemon."

Start(strCommand) ' start leads daemon

TestLog = 1

End IF

END Function

To debug and test this piece of code, I created an IPSEC policy, on my local computer, to block and unblock access to the SQL server. You can either use the command line version described here http://support.microsoft.com/kb/813878 or the GUI MMC version whose setup (for XP) is described here

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/ipsec_start_snap-in.mspx.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Moff58 — Mon, 24 Sep 2007 21:59:49 GMT

I have worked 3 days trying to connect a remote computer on my workgroup to SQL Server 2005 Express. I have tried dozens of things. I am desesperated. Is SQL Server 2005 Express working at all with workgroups ?

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Simon — Mon, 08 Oct 2007 20:11:57 GMT

I just spent an hour trying to get my SQL 2005 Mgmt Studio to connect to the SQL 2000 box sat right across the desk... turned out to be the (on by default) XP Firewall running on the remote box. Thanks for this issue being mentioned somewhere above...

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Keith — Tue, 09 Oct 2007 01:20:48 GMT

Hello,

Could someone explain why I have to turn on File and Printer Sharing to allow a TCP connection to a named sql instance through the XP firewall? I know it opens more ports in the firewall but why is this necessary?

There are MS products that use SQL Express (Microsoft Office Accounting 2007)that do NOT require Sharing to be on in order to use the SQL instance remotely. What are they doing differently? How are they configuring their instance to permit remote TCP connections through the firewall? Are Named Pipes required because of connecting to a named Instance?

Exasperated.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

MING LU — Fri, 12 Oct 2007 09:15:06 GMT

Hi, keith

You do not need to turn on *File and Printer Sharing* if making TCP connection. Can you share your experience that without the sharing, you can not make remote tcp connection against a named instance?

Thanks!

Ming.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Keith — Thu, 18 Oct 2007 02:19:15 GMT

Ming,

After seeing "Turn on File and Printer Sharing" in most of the troubleshooting guides in this blog, combined with my own issues connecting to SQL Server I thought I needed to use F&P Sharing.

It turns out (I think) that all my connection problems (errors 0, 26, and 40) were due to Active Directory/Domain Controller timeouts when trying to locate and log in to a SQL instance.

I work remotely, and the machine hosting the SQL instance is on a virtual machine on my end of our VPN, but the domain controller is on the other end. The roundtrip required for windows authentication seems to exceed the default login timeout, so I upped the timeout and my problems went away.

Thanks for the interest in my problem, though!

A different question: why don't you need to use a port# when using ODBC to connect to a SQL instance with a static port behind a firewall?

Keith

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Melvin Krom — Thu, 18 Oct 2007 23:10:52 GMT

I have installed several instances on two SQL 2005 servers. Whenever I remote connect between the servers or remote connect from my 2k desktop express client, it fails ( time out the first time), if I try again the connection is made. What do I need to do to eliminate this time out error the first time I connect?

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

ZUlfi — Tue, 23 Oct 2007 19:27:26 GMT

Dear all,

I am using the sql server 2005 in remote server, in which the surface are configuration is enabled for tcp/ip, named pipes. I cannot connect the remote server from one of the client machine but i can pin the server from that client. The firewall of the client is disabled.

I also able to connect remote server from another client in different physical location and network.

Please through your thoughts....

Thanks in advance.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Venerando Villarino — Thu, 08 Nov 2007 08:04:59 GMT

IN MY CASE ........

TCP/IP or Named Pipe are enabled.

SQL Server Browser is active running

Fire and Printer Sharing,sqlsevr.exe and sqlbrowser.exe are in Firewall exception list

browser using port 1433

from error log:

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

2007-11-07 19:42:53.20 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].

2007-11-07 19:42:53.20 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].

AND STILL GETTIN THIS STUPID ERROR !!!!!

This is the error I'm getting......

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 invalid argument was supplied.)

This is my connection String

Server=(local);

Initial Catalog=myDatabaseName;

User ID=sa;

PWD=MyPassword;

I'm thinking to go back to SQL 2000 !!!!!

Vene

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Venerando Villarino — Thu, 08 Nov 2007 08:09:04 GMT

sorry, This is the full 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 invalid argument was supplied.)

Note that the connection was successfully established with the server

more ERRORLOG

2007-11-07 19:42:53.20 Server Server is listening on [ 'any' <ipv4> 1027].

2007-11-07 19:42:53.20 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].

2007-11-07 19:42:53.20 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].

2007-11-07 19:42:53.20 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.

2007-11-07 19:42:53.21 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

2007-11-07 19:42:53.21 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.

2007-11-07 19:42:53.51 spid8s Clearing tempdb database.

2007-11-07 19:42:54.06 spid8s Starting up database 'tempdb'.

2007-11-07 19:42:54.11 spid5s Recovery is complete. This is an informational message only. No user action is required.

2007-11-07 19:42:54.11 spid11s The Service Broker protocol transport is disabled or not configured.

2007-11-07 19:42:54.11 spid11s The Database Mirroring protocol transport is disabled or not configured.

2007-11-07 19:42:54.20 spid11s Service Broker manager has started.

2007-11-07 19:50:51.81 Server The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.

2007-11-07 19:58:00.03 Server Server resumed execution after being idle 872 seconds: user activity awakened the server. This is an informational message only. No user action is required.

2007-11-07 19:58:00.28 Logon Error: 18456, Severity: 14, State: 16.

2007-11-07 19:58:00.28 Logon Login failed for user 'sa'. [CLIENT: 192.168.11.5]

2007-11-07 19:58:30.93 Logon Error: 18456, Severity: 14, State: 16.

2007-11-07 19:58:30.93 Logon Login failed for user 'sa'. [CLIENT: 192.168.11.5]

Vene

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Matt Neerincx (MSFT) — Thu, 08 Nov 2007 20:47:45 GMT

From the state code of 16, this means there were issues opening/using the default database for the incoming user. Try logging into a different database like master or try another user (try integrated login and local admin of machine for example).

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Jared — Mon, 21 Jan 2008 17:53:01 GMT

This is a piece of gold bros, it saves may days out of trouble and keep me alive in the business, I thank you very big for the help I got, as I said piece of gold.

This is greate place to get noble ideas.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Ratish — Wed, 12 Mar 2008 13:57:24 GMT

Dear Sir,

I am facing this above problem to connect the SQL SERVER 2005 it pops up a error saying Remote Connection not allowed.

It is listening 127.0.0.1 <ipv4> 3344 port

Everything Is Enabled

TCP/IP or Named Pipe are enabled.

SQL Server Browser is active running

Fire and Printer Sharing,sqlsevr.exe and sqlbrowser.exe are in Firewall exception list

browser using port 1433

Kindly advice.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Tres London — Thu, 13 Mar 2008 02:53:14 GMT

How are you trying to connect? Are you able to connect with Management Studio? Have you tried connecting with osql? Have you checked your error log for any clues? If you are able to connect with Management Studio and unable to connect through ASP.NET, it may be a problem with your connection string or something like that.

HTH

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Blair Rorani — Mon, 31 Mar 2008 05:25:18 GMT

*Here is a copy of my error message. I have a default instance installed, login using sa and password*

TITLE: Connect to Server

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

Cannot connect to [my server].

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

ADDITIONAL INFORMATION:

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

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Zayn — Fri, 25 Apr 2008 21:24:35 GMT

Hello,

I am running SQL 2005 NAMED INSTANCE on windows 2003 cluster on PORT 1433 with SP2.

When i connect from Server's Management studio (ServerName\instanceName), i am able to connect BUT when i try to connect to that remote Server from a client's Management Studio, i am not able to connect unless i put the port number in front (ServerName\instanceName, port#). I know there is a work around by using the alias BUT IS THERE ANY WAY I CAN FIX THIS WITHOUT USING THE ALIAS AND WITHOUT CHANGING THE PORT NUMBER.

I will really appreciate your help.

Thanks,

Zayn

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Bas Wanders — Wed, 07 May 2008 18:13:18 GMT

Very helpful article, solved my problems with remote connectivity.

Thanks!

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Manoj — Thu, 29 May 2008 17:08:27 GMT

Good Comments and usful blog. It gaves us all kinds of SQL error messages while connection issues.

Thanks Everyone again for your inputs

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Anuj — Mon, 14 Jul 2008 14:41:00 GMT

Hi,

I am trying to connect to a OLAP Remote Server and I am getting the error message that you are not authenticated User.

Please Give me some Idea

Regards,

Anuj

Ламинат цены

ламинат — Sun, 24 Aug 2008 15:26:41 GMT

3pThank's.8y I compleatly disagree with last post . bqj

<a href="http://skuper.ru">ламинат и паркет</a> 9y

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Dr. Debug — Mon, 08 Sep 2008 07:29:41 GMT

I had figured out the solution to this last year but only made it on my main dev system. My laptop had the same problem and after hours of searching I finally recalled the 'trick': In SQL Server Configuration Manager under define an Alias under the Client Configuration, add a value in the Alias section that references the target server you are trying to access and the port, along with TCP as the protocol. In my case the port for SQLExpress 2005 on the target is 3306. Insure your Hosts file in Windows\System32\drivers\etc has the same name of the server you specify and then every thing will work.

This is so frustrating - but this is a good blog to help in trouble shooting the problem.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Allan — Sat, 27 Sep 2008 20:20:40 GMT

Hello,

I have 2K3 Server and Sql Server 2005.

I can connect from a machine witch is not on the domain to the database engine on the server. But, when I join the machine to the domain (now the machine is on the domain) I cannot connect?

Any ideas?

Thank you,

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Vijay Chandra — Wed, 01 Oct 2008 13:08:44 GMT

I have developed an windows form application using visual basic 2005 (professional) and sql server 2005 (express) database. This is working fine ( single user ). Now the client has got in for expansion within the city limits and would like to add another 5 client systems. As the application is already created is it possible by just changing the connection string to point to the server at the head office. Report are created using reportviewer.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

jajal.ketan — Sat, 07 Mar 2009 01:52:59 GMT

Hi,

I have installed SQL Server and not able to access it from the local area network

I tried to telnet the sql server but it failed

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

ebailey — Wed, 01 Apr 2009 21:36:06 GMT

SQL 2005

Cannot connect from external point of sale station in one of our stores.

SQL server is behind ASA firewall with appropriate port opened.

Verified settings (server and remote client) as per SQL Server 2005 Remote Connectivity Issue troubleshoot doc (by the way thanks for the info)

Test data sent from remote client and receiving the following error

2009-04-01 12:45:17.25 Logon Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. Followed by IP address of remote client

Event App log displays:

MSSQLSERVER

Category:4

Event ID: 17836

Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library

Any help would be appreciated

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Brad Sarsfield - MSFT — Wed, 01 Apr 2009 22:43:37 GMT

ebailey: Can you contact us thorugh the contact form at the top of the blog?

http://blogs.msdn.com/sql_protocols/contact.aspx

Thanks,

Brad Sarsfield

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Maggie B — Thu, 09 Jul 2009 20:45:42 GMT

I am moving a sql 2000 database to a new windows 2003 server running sql 2005. My issue is with a remote SQL 2000 server that I connect to via tcp/ip. I have no problem connecting and viewing the databases on my old sql2000 server. On the new 2005 server I can connect to the remote server, and see and expand all the objects - but cannot expand the databases in Management Studio. I get the following error:

Failed to retrieve data for this request. (Microsoft SQLServer.SmoEnum)

Additional Information: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.) (Microsoft SQL Server, Error 121)

I'm stumped....

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

jm — Wed, 12 Aug 2009 10:53:02 GMT

A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

Can anybody help me in troubleshooting the error mentioned above. It's already running for few months and been searching for few months also but no possible solution.

This SQL Server 2005 running on Win Server 2003 remotely. What's the worse part is that connectivity is very unstable. Once it is normal my web application and remote access using SQL Management Studio runs very smoothly and if the issue starts, everything fails.

It's very, very frustating on my part because we are not able to solve the issue.

Any suggestion is highly appreciated. TIA

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

jm — Wed, 12 Aug 2009 10:57:03 GMT

Oh and by the way, we've already tried replacing the Server NIC and Network Switch on the Remote Network where this Server is Located.

Thanks again.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

allen — Thu, 26 Nov 2009 00:01:14 GMT

Hi, My SQL Server Browser Service is disabled, and i use Vista Home Basic, pls how can i Enable it.

re: SQL Server 2005 Remote Connectivity Issue TroubleShoot

Frank I. — Fri, 04 Dec 2009 14:59:42 GMT

We have just recently upgraded to Sql2005 from 2000.

Our main application uses services which are written in Ms Java 6.0. One service in particular is used very frequently to receive data over a port and enter it into the application database. We use java and com classes in com.ms.wfc.data.*. In particular we use com.ms.wfc.data.Connection to create a Connection to the database. We are experiencing com error "Failed remote creation of coclass com/ms/wfc/data/adodb/Connection" We are using a connection string like "Driver={SQL Server};SERVER=FINFANTE;DATABASE=MegaCare;UID=MegaCareUser;PWD=pwd;APP=Infinity MegaCare;"

What is happening is that after creating and using several Connections successfully, and storing data in the db successfully, then after a short period like a minute or 2, the next attempt to create a Connection fails with the com error.

Nothing like this had ever happened with sql 2000.

We have tried using different drivers and/or providers and are still getting the same error.

We have enabled all network protocols except VIS. We have exceptions in Windows firewall for SQlBRowser.exe and File and Printer Sharing.

Any clues about how to proceed debugging would be greatly appreciated.