Problem connecting to SQL Server 2005 remote

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Problem connecting to SQL Server 2005 remote

Author

Topic

svakula
Starting Member

4 Posts

Posted - 2008-08-28 : 06:35:54

I'm trying to connect to a remote SQL Server (2005) located in US, from SQLEXPRESS on my pc in UK. However i keep getting 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: SQL Network Interfaces, Error: 26 - Error locating server/instance specified)
-------------------------------------

Actually i bought web server space from a US company to host my web application. To create the database and data restore i need to connect to their SQL Server, but it keeps showing up the error message whenever i try. And i learned from the US company that there are no firewalls from their end and everything is configured perfect so that remote clients can connect to thier server. They also say that many people who are hosting web applications on their servers could connect to thier SQL server from different parts of the world with out any problems. Why i can't coonnect. Do i need to do any settings on my PC.. Please help

i'm trying in the following way
Address : Ip Address\instance name
SQL server authentication
User id : <user id>
Password : <password>

Hope the problem description is clear.

Thanks
Srini

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-08-28 : 06:41:36

see this article

http://msdn.microsoft.com/en-us/library/cc281850.aspx

svakula
Starting Member

4 Posts

Posted - 2008-09-01 : 10:52:07

Hi Thanks for your response, i checked the link you sent. But i feel those settings are required on the sql server machine. I learned from the support team that all those settings are done perfect and many people could connect to the server.

My problem is connecting to the SQL server from SQL client (SQLEXPRESS)

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-01 : 11:17:36

Remove the instance name. And replace it with the port number ie 127.0.0.1:2433

ie

Address : Ip Address:port number
SQL server authentication
User id : user id
Password : password

I have the same experience. Live in the Uk and have servers in the USA remotely and i connect via Express 2005 with no problem. although I am warned there are a couple of security risks involved.

svakula
Starting Member

4 Posts

Posted - 2008-09-02 : 12:00:28

I'm awaiting for the port number from the hosting company, they haven't given me the port number initially.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-02 : 12:36:25

sorry, the default is 2433. Unless they changed it

ddamico
Yak Posting Veteran

76 Posts

Posted - 2008-09-02 : 12:42:29

the default port should be 1433 not 2433

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-02 : 12:48:16

quote:
Originally posted by ddamico

the default port should be 1433 not 2433

Sorry, We changed our port number for security reasons years back. So I guess am used to 2433

swekik
Posting Yak Master

126 Posts

Posted - 2008-09-02 : 16:17:06

If that doesn't work,can you ask them to verify in network configuration settings whether the named pipes is enabled or not??

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-02 : 16:24:13

You would test with IPAddress,PortNumber. Don't use a colon as was previously suggested. Colons might be used in connection strings for certain programming languages, however you'll need to try with a comma for Express.

See my blog for how to troubleshoot this issue:
http://weblogs.sqlteam.com/tarad/archive/2008/05/23/60609.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-02 : 16:24:48

quote:
Originally posted by afrika

quote:
Originally posted by ddamico

the default port should be 1433 not 2433

Sorry, We changed our port number for security reasons years back. So I guess am used to 2433

For security reasons, you should be using a port with 5 digits (starting at 10000).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-02 : 17:51:07

quote:
Originally posted by tkizer

quote:
Originally posted by afrika

quote:
Originally posted by ddamico

the default port should be 1433 not 2433

Sorry, We changed our port number for security reasons years back. So I guess am used to 2433

For security reasons, you should be using a port with 5 digits (starting at 10000).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Advice taken. Thanks

But our present DB is a shared db, though it runs on a dedicated windows server. We hope to move to a dedicated DB by 1st quarter of next year or late 08. Then we can manipulate things as we wish

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-02 : 17:53:43

quote:
Originally posted by tkizer

You would test with IPAddress,PortNumber. Don't use a colon as was previously suggested. Colons might be used in connection strings for certain programming languages, however you'll need to try with a comma for Express.

See my blog for how to troubleshoot this issue:
http://weblogs.sqlteam.com/tarad/archive/2008/05/23/60609.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

My mistake again. Its a comma not a colon. I should cut back on the coffee

hey001us
Posting Yak Master

185 Posts

Posted - 2008-09-02 : 20:20:17

Try to this at command prompt.
telnet <ip address> <sql port number>
Problem with security (firewall etc) if not connecting

hey

svakula
Starting Member

4 Posts

Posted - 2008-09-10 : 10:18:48

Hello all
i tried in the following ways as you all advised, but to no avail...

IPAddress, Portno/Instance name - Fail
PING IPAddress - Success
TELNET IPAddress PortNo. - Fail

I checked with the hosting company again, they say they though they have firewalls, everything is configured perfect on the server in order for the remote clients to connect, and they could successfully tested with the same details they sent to me from one of their remote machines, which confirms no problem from their end.
I don't know what to do now. Do i need to do any settings on my PC in order to connect to remote SQL server. I have a 10GB broadband at home.

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-10 : 10:41:53

Your broadband is not the problem. I have 2gb and never had a problem

dont put the instance name.

IPaddress,portnumber
loginname
password

Remove the instance name. It should work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-10 : 12:34:06

If telnet is failing, then the issue is most likely with your broadband unlike what afrika said. telnet is the bare minimum test that you must pass. Since it fails, you need to talking to your hosting company again to get your SQL port opened on their firewall.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-10 : 13:05:47

quote:
Originally posted by tkizer

If telnet is failing, then the issue is most likely with your broadband unlike what afrika said. telnet is the bare minimum test that you must pass. Since it fails, you need to talking to your hosting company again to get your SQL port opened on their firewall.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Although regarding the port number, it could be changed and assigned a custom port. Our hosting company does not use the default and we are getting a new db late this year and will assign it a custom port.

Your hosting company should have the details

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-10 : 13:30:49

quote:
Originally posted by afrika

quote:
Originally posted by tkizer

If telnet is failing, then the issue is most likely with your broadband unlike what afrika said. telnet is the bare minimum test that you must pass. Since it fails, you need to talking to your hosting company again to get your SQL port opened on their firewall.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Although regarding the port number, it could be changed and assigned a custom port. Our hosting company does not use the default and we are getting a new db late this year and will assign it a custom port.

Your hosting company should have the details

You still won't be able to connect if telnet is failing.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources