We have 2 computers, one runs our web pages and the other runs the sql server. These 2 computers are located in a remote place and are only accessible by remote desktop connection.
Since remote desktop connection isn't always trustable, we want to use the Query Analyzer we have installed here in our office in order to make changes to the databases located at the remote SQL Server. Until now, we couldn't do it because we keep receiving the "server does not exist message". The strange thing is that we have successfully configured the remote SQL Server with the web server in order to access the databases by ADO in ASP.
I've tried to open the ODBC Data Sources in our local computer and try to create a system DSN, linking our office computer to the SQL Server. It didn't work. And the funny thing is that we CAN do it with our web server. We use remote desktop connection and, commanding our remote web server, we enter the ODBC Data Sources and we are able to create an ODBC connection to the SQL Server. That's why ADO works I guess.
So, why can't we access the SQL Server with our computer and we CAN access it using our remote web server?
The error we get when trying to add an ODBC Connection with our local computer is:
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect()),
SQL State: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied
If we do the same thing with our web server, the ODBC connection works just fine .... what is happening ??
Your webserver can get there because, from what i can tell, it is on the same network as your SQL Server.
You said that the web and sql server were only accessible by the RDP (remote desktop protocol) port. That would be port 3389.
When you try to get from your office computer to your SQL Server, you are attempting to hit port 1433, which is closed off by the firewall.
You can have your servers' ISP open up port 1433 (tcp and udp and sometimes 1434) and give only your IP access to that port.
you can make a web page in ASP that allows you to execute queries and stuff. Make sure it is password/ip protected.