How Do I Connect to Remote Machine with SQL Server on it?

[mickeyj2]

Hi,

I have SQL Server 2005 installed on my local machine. I already have a server instance (local) setup.

However, my Production machine is offsite. This machine is both a webserver and is my database server.

My webhost has basically given me the IP address and username and password for connecting into my REMOTE SQL SERVER instance.

However, when I open up Management Studio from my local machine, I put in the IP address and the SQL Server authentication username information, and here's the error message I receive:


"An error has occurred while establishing a connection to the server. When connecting to SQL SErver 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 53)"



My web host has assured me that they're allowing my desktop's IP address special access to my server, but I don't know what all that entails. Could this be only for their security, and they need to modify something within SQL Server?

Thanks in advance for your help.

mickeyj2

 

[gmmastros]

On the 'Connect To Server' screen, click 'Options >>'.
Under 'Network protocol', select TCP/IP.

Then, try again.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mickeyj2]

Hi George,

Thanks, I tried that, but no dice. I have also talked with my web host, and I'm not even hitting their log files.

Also, when I try to setup a very basic Data Source connection to this database, I can't do that either.

We did check in SQL Server, though to SQL Server allows remote connections.

Thanks in advance for any help you can provide.

mickeyj2

 

[gmmastros]

One last thought (something else to check)...

If you are using Windows Vista, you may need to check your firewall settings. With XP and below, the firewall was used to block incoming connections. With Vista, the firewall can be used to block outgoing connections also. I'm not using vista, so I can't help with the 'click sequence' for changing the configuration, but I bet google could help you there.

In any case, good luck. Please post a response back here once you have figured out the problem so that others may benefit from the solution.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLWilts]

Couple of other things to check:

As the error message suggests, from a flat instal of SQL 2005 remote connections are turned off. The system management guys at the remote site will need to ensure that it is turned on.

Also, although you have the IP address, SQL will still try to connect on the default port (1433) - check that your SQL server instance is listening on that port

Just a thought or 2 from experience

 

[mickeyj2]

Hi, George,

Thanks for your help. Come to find out it was a Firewall issue with my web host. They have since fixed the problem.

Thanks for your help.

mickeyj2

 

[mickeyj2]

Oh, thanks, gmmastros and SQLWits. I just read your posts after I had submitted the previous one. Yes, it was indeed the firewall. Thanks for all of your help.

mickeyj2