SQL Server 2005 Remote Connectivity Issue TroubleShoot

아티클
09/30/2006

With shipping SQL Server 2005, we heard from customer feedback about suffering make successful remote connection against SQL 2k5, plus, if running SQL Server on XP/WIN2K3/VISTA behind firewall, such problem occurs more frequently and harder for customer to get clue of behind reason.

I. Background of remote connectivity issue.

The reason we saw the issue comes out w/ SQL 2k5 instead of SQL 2k is because two breaking change in SQL 2k5 compared to SQL 2000.

1) Network Protocols Off by Default: In order to make system secure, we limit connectivity when a user install SQL 2k5, thereby reducing surface area for attack. By default, SQL server (SQLEXPR, SQLDEV and EVAL SKUs), on installation will listen only on Shared memory and local-only Named Pipe. TCP and remote Named Pipe will be off-by-default. VIA will also be off-by-default; SQL server (SQLENT, STANDARD and Workgroup SKUs), on installation will listen only on Shared memory, local-only Named Pipe and TCP. Remote Named Pipe will be off-by-default. VIA will also be off-by-default.

Therefore, when you mae remote connection, you should make sure at least either Named Pipe or TCP are enabled in your remote SQL instance.

2) SQL Browser service: which is a replacement of SSRP system in SQL Server 2000, run as a Windows Service on installation of SQL 2k5. Upon startup, SQL Server Browser starts and claims UDP port 1434. When SQL Server 2000 and SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance.

Therefore, you need to make sure SQL Browser is enabled and started when remote client ask for which tcp port or pipe name SQL Server is listening on. If your remote SQL Instance is a default instance, you do not necessary enable sql browser since client would always try default TCP port 1433 and pipe name \<remoterserver>pipesqlquery. But, if you have SQL 2k5 named instance installed or SQL 2000 and SQL 2k5 side by side installed, you must enable and start sqlbrowser.

II. Problem list:

By understanding background 1) and 2), I belive you can imagine issues you may face when make remote connection against SQL 2k5:

1) Fail to connect over TCP/IP or Named Pipe if the request protocol was not enabled.

2) Fail to connect over TCP/IP or Named Pipe if Firewall enabled on the remote server and tcp port or "File and Printer Sharing" is not added to the Firewall exception list.

3) Fail to connect to remote sql named instance if SQL Browser was not enabled or UDP port 1434 that browser listening on is added to the Firewall exception list.

III. Troubleshooting Tips:

Assumption: your SQL Server was installed on remote server and behind firewall; SQL Instance was started; and your client app specify correct remote sql instance name.

On your server side:

[2] Sql Instance was restarted successfully, check Server ErrorLog, find which tcp port or pipe name server is listening on.

[3] netstat -ano | findstr <portnumber> if server enable TCP, and make sure server is listening on the correct port.

[4] go to services.msc, find service "SQL Server Browser", enable it and restarted, also, go to SQL Server Configuration Manager, check properties for SQL Browser service, in Advanced tab, make sure it is active.

[5] Enable "Fire and Printer Sharing" in Firewall exception list.

[6] Add TCP port or sqlservr.exe to Firewall exception list, either add "..Binnsqlsevr.exe" or add port.

If your server was not started successfully by any reason, it is very helpful to collect info from server logs; also, you can get clear picture of protocols that server is listening on, for eg, if TCP was enabled, you should be able to see which port server is listening on, and if Np was enabled, you can make connection throgh the pipe name.

[7] Add Sql Browser service to Firewall exception list, you can either add program " C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe" or add UDP port 1434.

[8] Make sure if your remote sql Server Instance is a default instance, it must listen on tcp port 1433 and pipe \.pipesqlquery.

On your Client Side:

[1] ping <remote server> return correct IP address of your remote server.

[2] telnet <remoteserver> <portnumber> works, <portnumber> is the port that your remote sql instance is listening on.

telnet < ipaddress> <port> works

[3] \<remoteserver>, make sure you can access share of remote server.

[4] Go to SQL Server Configuration Manager | SQL Native Client Configuration | Client Protocols, make sure Named pipe or tcp is enabled; Click properties of Client Protocols, make Sure you see at least Np or tcp is in enabled protocols, plus, recommend put TCP on the top of the order.

[5] If you are using MDAC ( Driver={SQL Server} or SQLOLEDB.x) in your client application, in command line, tyep "cliconfg.exe", also enable NP and TCP and put TCP on top of order.

[6] Use osql or sqlcmd to try " osql /S<remoteserver> /E" or "osql /S<remoteserver><Instancename> /E" see whether connection works.

[7] Check your client connection string syntax: a. do you specify correct remote server name? b. do you specify correct instance name? if remote sql is default instance, you just need to specify remote server machine name, otherwise, you need to specify " <remoteserver><instancename>" c. do you spcify tcp port or pipe name in your connection string, if so, double check the port and pipe name are the one server is listening on, otherwise, remove it.

[8] If your client app connect to remote server using server alias name, a. if you are using MDAC, try "cliconfg.exe", in "Alias" Tab, check whether you specify any alias in which the tcp port or the pipe name should be the one remote server is listening on; b. if you are using SQL Native Client, go to SQL Server Configuration Manager, check "Aliases" configuration.

If you are sure the network protocol configuration for remote connection are all correct by going through above checklist, and still face specific problem, pleas refer following blog that give troubleshooting tips based on concrete error message:

https://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

https://blogs.msdn.com/sql_protocols/archive/2005/10/29/486861.aspx

https://blogs.msdn.com/sql_protocols/archive/2005/12/22/506607.aspx

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

MING LU

SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

Anonymous
January 02, 2007
The comment has been removed
Anonymous
January 02, 2007
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.
Anonymous
January 23, 2007
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
Anonymous
January 28, 2007
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.
Anonymous
February 09, 2007
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(); } ??
Anonymous
February 11, 2007
The comment has been removed
Anonymous
February 23, 2007
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
Anonymous
February 23, 2007
The comment has been removed
Anonymous
February 23, 2007
The comment has been removed
Anonymous
February 24, 2007
The comment has been removed
Anonymous
March 31, 2007
This error was most frequently hitted by our customers, and in this post, give a brief summary of troubleshooting
Anonymous
April 11, 2007
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

Anonymous
April 11, 2007
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
Anonymous
May 15, 2007
I found an alternative, you can use the following sintax, <server name>,<port number><instance name> You can se the por number on sql server log in the sql studio manager or the sql configuration manager.
Anonymous
May 19, 2007
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.
Anonymous
May 21, 2007
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.
Anonymous
May 21, 2007
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.
Anonymous
May 21, 2007
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.
Anonymous
May 21, 2007
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.
Anonymous
May 28, 2007
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?
Anonymous
June 01, 2007
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.
Anonymous
June 01, 2007
Can you provide the connection string to us for taking a look at.
Anonymous
June 06, 2007
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!
Anonymous
June 13, 2007
The comment has been removed
Anonymous
June 13, 2007
The comment has been removed
Anonymous
June 14, 2007
The comment has been removed
Anonymous
June 15, 2007
The comment has been removed
Anonymous
June 16, 2007
The comment has been removed
Anonymous
June 22, 2007
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 ?
Anonymous
June 29, 2007
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
Anonymous
June 30, 2007
Hi, Balaji What is the exact error message when you connect locally to SQL 2000? Thanks! Ming.
Anonymous
July 04, 2007
Hi Just wondering if you can help me. I have Cisco CSA picking up the following message.The process 'C:Program FilesMicrosoft SQL Server90Sharedsqlbrowser.exe' (as user NT AUTHORITYNETWORK SERVICE) attempted to accept a connection as a server on UDP port 1434 from 172.16.71.132 using interface WiredBroadcom 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
Anonymous
July 05, 2007
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
Anonymous
July 09, 2007
I am getting the same kind of error. I am stuck
Anonymous
August 12, 2007
The comment has been removed
Anonymous
August 16, 2007
The comment has been removed
Anonymous
August 16, 2007
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.
Anonymous
August 16, 2007
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.
Anonymous
August 16, 2007
The comment has been removed
Anonymous
August 17, 2007
Try starting sqlbrowser from command prompt like so: net stop sqlbrowser cd "C:Program FilesMicrosoft SQL Server90Shared" 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 FilesMicrosoft SQL Server90Shared>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.
Anonymous
August 22, 2007
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=**;"
Anonymous
August 26, 2007
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 "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)". although i allowed all remote connection but this error still appear
Anonymous
August 28, 2007
The comment has been removed
Anonymous
September 24, 2007
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 ?
Anonymous
October 08, 2007
The comment has been removed
Anonymous
October 08, 2007
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.
Anonymous
October 11, 2007
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.
Anonymous
October 17, 2007
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
Anonymous
October 18, 2007
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?
Anonymous
October 23, 2007
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.
Anonymous
November 07, 2007
The comment has been removed
Anonymous
November 07, 2007
The comment has been removed
Anonymous
November 08, 2007
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).
Anonymous
January 21, 2008
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.
Anonymous
March 11, 2008
The comment has been removed
Anonymous
March 12, 2008
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
Anonymous
March 30, 2008
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: 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) (Microsoft SQL Server, Error: -1) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476

Anonymous
April 25, 2008
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 (ServerNameinstanceName), 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 (ServerNameinstanceName, 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
Anonymous
May 07, 2008
Very helpful article, solved my problems with remote connectivity. Thanks!
Anonymous
May 29, 2008
Good Comments and usful blog. It gaves us all kinds of SQL error messages while connection issues. Thanks Everyone again for your inputs
Anonymous
July 13, 2008
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
Anonymous
August 24, 2008
3pThank's.8y I compleatly disagree with last post . bqj <a href="http://skuper.ru">ламинат и паркет</a> 9y
Anonymous
September 07, 2008
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 WindowsSystem32driversetc 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.
Anonymous
September 27, 2008
The comment has been removed
Anonymous
September 30, 2008
The comment has been removed
Anonymous
March 06, 2009
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
Anonymous
April 01, 2009
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
Anonymous
April 01, 2009
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
Anonymous
July 09, 2009
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....
Anonymous
August 11, 2009
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
Anonymous
August 11, 2009
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.
Anonymous
November 25, 2009
Hi, My SQL Server Browser Service is disabled, and i use Vista Home Basic, pls how can i Enable it.
Anonymous
December 03, 2009
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.
Anonymous
February 09, 2010
hi i have big problem and i can't solve it. My applications (vb6) connected to (sql server 2005 sp1 and windows 2003 server sp1) with msdaSQL (oledb). And i downloaded windows 2003 sp2 and sql server 2005 sp3. then apllications cannot connect to sql server 2005. (sql state 01000 and sql error : 14). Only sql native client connect to database. what happened and how to solve it.
Anonymous
February 10, 2010
i found a solution. Network card driver uptaded and it solved. Waavv.Machine hp ml570 and driver 1 gb nic ethernet driver from hp sites. Be carefull updateing from microsoft. i hope i helped many person.
Anonymous
March 17, 2010
There is no such command as 'netstate' on windows.
Anonymous
March 17, 2010
Indeed, that is a typo in the article. It should read "netstat". Thanks for catching it!
Anonymous
April 11, 2010
The comment has been removed
Anonymous
May 06, 2010
Hi, thanks, it helped me. Martin
Anonymous
August 26, 2010
Thank you very much for the concise background and comprehensive step-by-step troubleshooting advice. Much appreciated!
Anonymous
March 12, 2011
i have sql server express 2008 on windows server 2008 the port which server listen to is 1433 and the server name lets say is server2893. i made a database and i'm using plesk 9.5 the information of the data base is DBName : test_DB user : dbtestUser pass : visitpassw00rD

now tell me wht i should put as username, password and server name =/ whtever i try is nt working

Anonymous
June 24, 2011
Hi, My problem trying to connect to a SQL Server 2008 remotely. I made a connection to the same server in other networks but in the one at home I recieve "Error: 26 – Error Locating Server/Instance" from the management studio.

다음을 통해 공유

SQL Server 2005 Remote Connectivity Issue TroubleShoot

Comments

Cannot connect to [my server].

추가 리소스