newbie question -- error when connecting via System DSN on any network but the SQL Servers

I'm very new to using SQL Server. All I'm trying to do at the moment, is
create a System DSN on a Windows 2000 Professional desktop machine to
connect to a SQL Server 2000 server which is running on a different
network. When I try the DSN while connected to the SAME network that the
SQL Server machine is on, it works fine. But when I connect from any
other network, I receive the following error:
Connection failed:
SQLState: '01000'
SQL Server Error: 10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
exist or access denied.

Here are the settings that I'm using for my System DSN:
Name: test
Which SQL Server do you want to connect to? 129.22.180.203
* With SQL Server Authentication using a login ID and password entered by
the user
* Connect to SQL Server to obtain default settings for the additional
configuration options
Login ID: SA
Password: <correct password entered
And in the Client Configuration:
Server Alias: 129.22.180.203
Network libraries: TCP/IP
Server Name: 129.22.180.203
* Dynamically determine port

Also, the driver is:
Microsoft SQL Server ODBC Driver version 03.85.1025

Please, if anyone has any ideas on what I am doing wrong, I can use all
the help I can get!!
--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/sheree <sah18@.case.edu> wrote in message news:<opsb5642gy6l2js5@.uh007637>...
> I'm very new to using SQL Server. All I'm trying to do at the moment, is
> create a System DSN on a Windows 2000 Professional desktop machine to
> connect to a SQL Server 2000 server which is running on a different
> network. When I try the DSN while connected to the SAME network that the
> SQL Server machine is on, it works fine. But when I connect from any
> other network, I receive the following error:
> Connection failed:
> SQLState: '01000'
> SQL Server Error: 10061
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
> (Connect()).
> Connection failed:
> SQLState: '08001'
> SQL Server Error: 17
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> exist or access denied.
> Here are the settings that I'm using for my System DSN:
> Name: test
> Which SQL Server do you want to connect to? 129.22.180.203
> * With SQL Server Authentication using a login ID and password entered by
> the user
> * Connect to SQL Server to obtain default settings for the additional
> configuration options
> Login ID: SA
> Password: <correct password entered>
> And in the Client Configuration:
> Server Alias: 129.22.180.203
> Network libraries: TCP/IP
> Server Name: 129.22.180.203
> * Dynamically determine port
> Also, the driver is:
> Microsoft SQL Server ODBC Driver version 03.85.1025
> Please, if anyone has any ideas on what I am doing wrong, I can use all
> the help I can get!!

I'm not sure what you mean by "a different network". If you mean that
the networks are physically separated by a firewall, or if you're
connecting over a VPN, then you might need to open the ports for
MSSQL:

http://support.microsoft.com/defaul...2&Product=sql2k

Simon|||Simon,
By "different network", I mean the following:
The SQL Server is running on a Windows 2000 Server machine that is located
on a University network. If I am on any machine that is also connected to
the University network, OR if I VPN into the University network, I can
successfully connect to the SQL Server in my DSN connection with the
parameters I gave in my original posting. However, if I try from my
office computer which is on a hospital network (and IS behind a firewall)
or if I try from any computer where I'm dialed up to my ISP (no firewall
would block anything here) I still can't connect to the SQL Server in my
DSN connection.
So I really don't think it's the port that is being blocked.

Are there any connection settings in SQL Server itself that I may have
configured incorrectly? Since I know so little about the software, I'm
not sure where even to begin to look.

Thanks for helping out!
-sheree

On 4 Aug 2004 01:16:19 -0700, Simon Hayes <sql@.hayes.ch> wrote:

> sheree <sah18@.case.edu> wrote in message
> news:<opsb5642gy6l2js5@.uh007637>...
>> I'm very new to using SQL Server. All I'm trying to do at the moment,
>> is
>> create a System DSN on a Windows 2000 Professional desktop machine to
>> connect to a SQL Server 2000 server which is running on a different
>> network. When I try the DSN while connected to the SAME network that
>> the
>> SQL Server machine is on, it works fine. But when I connect from any
>> other network, I receive the following error:
>> Connection failed:
>> SQLState: '01000'
>> SQL Server Error: 10061
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
>> (Connect()).
>> Connection failed:
>> SQLState: '08001'
>> SQL Server Error: 17
>> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
>> exist or access denied.
>>
>> Here are the settings that I'm using for my System DSN:
>> Name: test
>> Which SQL Server do you want to connect to? 129.22.180.203
>> * With SQL Server Authentication using a login ID and password entered
>> by
>> the user
>> * Connect to SQL Server to obtain default settings for the additional
>> configuration options
>> Login ID: SA
>> Password: <correct password entered>
>>
>> And in the Client Configuration:
>> Server Alias: 129.22.180.203
>> Network libraries: TCP/IP
>> Server Name: 129.22.180.203
>> * Dynamically determine port
>>
>> Also, the driver is:
>> Microsoft SQL Server ODBC Driver version 03.85.1025
>>
>> Please, if anyone has any ideas on what I am doing wrong, I can use all
>> the help I can get!!
> I'm not sure what you mean by "a different network". If you mean that
> the networks are physically separated by a firewall, or if you're
> connecting over a VPN, then you might need to open the ports for
> MSSQL:
> http://support.microsoft.com/defaul...2&Product=sql2k
> Simon

--
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/|||> However, if I try from my
> office computer which is on a hospital network (and IS behind a firewall)
> or if I try from any computer where I'm dialed up to my ISP (no firewall
> would block anything here) I still can't connect to the SQL Server in my
> DSN connection.
> So I really don't think it's the port that is being blocked.

From your description, it seems you are trying to connect over the public
internet to the SQL Server at your university. Port 1433 needs to be open
to allow this and it is likely that the firewall at the university has the
port closed for security reasons. It works when you are connected to the
university LAN or VPN because the firewall doesn't block 1433 in those
cases.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"sheree" <sah18@.case.edu> wrote in message
news:opsb7ky5r56l2js5@.v129-22-124-109.vclient.cwru.edu...
> Simon,
> By "different network", I mean the following:
> The SQL Server is running on a Windows 2000 Server machine that is located
> on a University network. If I am on any machine that is also connected to
> the University network, OR if I VPN into the University network, I can
> successfully connect to the SQL Server in my DSN connection with the
> parameters I gave in my original posting. However, if I try from my
> office computer which is on a hospital network (and IS behind a firewall)
> or if I try from any computer where I'm dialed up to my ISP (no firewall
> would block anything here) I still can't connect to the SQL Server in my
> DSN connection.
> So I really don't think it's the port that is being blocked.
> Are there any connection settings in SQL Server itself that I may have
> configured incorrectly? Since I know so little about the software, I'm
> not sure where even to begin to look.
> Thanks for helping out!
> -sheree
>
> On 4 Aug 2004 01:16:19 -0700, Simon Hayes <sql@.hayes.ch> wrote:
> > sheree <sah18@.case.edu> wrote in message
> > news:<opsb5642gy6l2js5@.uh007637>...
> >> I'm very new to using SQL Server. All I'm trying to do at the moment,
> >> is
> >> create a System DSN on a Windows 2000 Professional desktop machine to
> >> connect to a SQL Server 2000 server which is running on a different
> >> network. When I try the DSN while connected to the SAME network that
> >> the
> >> SQL Server machine is on, it works fine. But when I connect from any
> >> other network, I receive the following error:
> >> Connection failed:
> >> SQLState: '01000'
> >> SQL Server Error: 10061
> >> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpent
> >> (Connect()).
> >> Connection failed:
> >> SQLState: '08001'
> >> SQL Server Error: 17
> >> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not
> >> exist or access denied.
> >>
> >> Here are the settings that I'm using for my System DSN:
> >> Name: test
> >> Which SQL Server do you want to connect to? 129.22.180.203
> >> * With SQL Server Authentication using a login ID and password entered
> >> by
> >> the user
> >> * Connect to SQL Server to obtain default settings for the additional
> >> configuration options
> >> Login ID: SA
> >> Password: <correct password entered>
> >>
> >> And in the Client Configuration:
> >> Server Alias: 129.22.180.203
> >> Network libraries: TCP/IP
> >> Server Name: 129.22.180.203
> >> * Dynamically determine port
> >>
> >> Also, the driver is:
> >> Microsoft SQL Server ODBC Driver version 03.85.1025
> >>
> >> Please, if anyone has any ideas on what I am doing wrong, I can use all
> >> the help I can get!!
> > I'm not sure what you mean by "a different network". If you mean that
> > the networks are physically separated by a firewall, or if you're
> > connecting over a VPN, then you might need to open the ports for
> > MSSQL:
http://support.microsoft.com/defaul...2&Product=sql2k
> > Simon
>
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/|||Dan,
Can another port be used for SQL Server to run on (rather than using the
default port 1433, which is what it is currently running on)? It might be
easier for me to use a different port that is already open at the
university, rather than attempt to request that they open port 1433
(unlikely they will do this).
Thanks so much for your help!
-sheree

On Wed, 04 Aug 2004 13:13:12 GMT, Dan Guzman
<danguzman@.nospam-earthlink.net> wrote:

> From your description, it seems you are trying to connect over the public
> internet to the SQL Server at your university. Port 1433 needs to be
> open
> to allow this and it is likely that the firewall at the university has
> the
> port closed for security reasons. It works when you are connected to the
> university LAN or VPN because the firewall doesn't block 1433 in those
> cases.|||sheree (sah18@.case.edu) writes:
> Can another port be used for SQL Server to run on (rather than using the
> default port 1433, which is what it is currently running on)? It might be
> easier for me to use a different port that is already open at the
> university, rather than attempt to request that they open port 1433

Yes, it is possible to use another port. (Which would affect everyone
using that server, as SQL Server uses only one port at a time.) No,
I don't think your university is likely to make SQL Server available on the
Internet on any port, least of all if you insist on using SQL Server
authentication.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Although it is certainly possible to configure SQL Server to listen on an
alternate port, it appears the SQL Server is configured to use 1433 because
your working local/VPN connections use the default port.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"sheree" <sah18@.case.edu> wrote in message
news:opsb7mufwi6l2js5@.v129-22-124-109.vclient.cwru.edu...
> Dan,
> Can another port be used for SQL Server to run on (rather than using the
> default port 1433, which is what it is currently running on)? It might be
> easier for me to use a different port that is already open at the
> university, rather than attempt to request that they open port 1433
> (unlikely they will do this).
> Thanks so much for your help!
> -sheree
>
> On Wed, 04 Aug 2004 13:13:12 GMT, Dan Guzman
> <danguzman@.nospam-earthlink.net> wrote:
> > From your description, it seems you are trying to connect over the
public
> > internet to the SQL Server at your university. Port 1433 needs to be
> > open
> > to allow this and it is likely that the firewall at the university has
> > the
> > port closed for security reasons. It works when you are connected to
the
> > university LAN or VPN because the firewall doesn't block 1433 in those
> > cases.

Newbie Q: System DSN from DMZ?

Hello,
I have a webserver running in a DMZ off my network. It needs access to a
SQL2000 server running inside my network. How can I create a system DSN
that points to my SQL2000 server when there is no direct path?
When I create the DSN, it asks, "which SQL Server to you want to connect
to?" But, the drop down box is empty. What should I be typing in here, and
what holes do I need to create in my firewall to make it work?
-Trevori'm not a firewall expert here, but as far as i'm aware you need to open up the port for SQL server on the firewall - you ca fid this port number from the SQL Cliet manager tool|||simply type in the name of your db server when creating the dsn.
assuming your sqlserver is listening on tcp port 1433, you'll need to open up
port 1433 in the firewall, but only open the hole for your web server. check
your sql server log to see what port it's listening on.
Trevor Clark wrote:
> Hello,
> I have a webserver running in a DMZ off my network. It needs access to a
> SQL2000 server running inside my network. How can I create a system DSN
> that points to my SQL2000 server when there is no direct path?
> When I create the DSN, it asks, "which SQL Server to you want to connect
> to?" But, the drop down box is empty. What should I be typing in here, and
> what holes do I need to create in my firewall to make it work?
> -Trevor

Newbie Q: System DSN from DMZ?

Hello,
I have a webserver running in a DMZ off my network. It needs access to a
SQL2000 server running inside my network. How can I create a system DSN
that points to my SQL2000 server when there is no direct path?
When I create the DSN, it asks, "which SQL Server to you want to connect
to?" But, the drop down box is empty. What should I be typing in here, and
what holes do I need to create in my firewall to make it work?
-Trevori'm not a firewall expert here, but as far as i'm aware you need to open up
the port for SQL server on the firewall - you ca fid this port number from t
he SQL Cliet manager tool|||simply type in the name of your db server when creating the dsn.
assuming your sqlserver is listening on tcp port 1433, you'll need to open u
p
port 1433 in the firewall, but only open the hole for your web server. chec
k
your sql server log to see what port it's listening on.
Trevor Clark wrote:

quote:

---

> Hello,
> I have a webserver running in a DMZ off my network. It needs access to a
> SQL2000 server running inside my network. How can I create a system DSN
> that points to my SQL2000 server when there is no direct path?
> When I create the DSN, it asks, "which SQL Server to you want to connect
> to?" But, the drop down box is empty. What should I be typing in here, a
nd
> what holes do I need to create in my firewall to make it work?
> -Trevor

---

newbie on SQL Server needs some direction .adp and .mde

I'm trying to figure out what is going on. I have an .adp file that has a
connection DNS to a .mde file on the network. It is confusing. In the
connection dialog it has my computer as the server name and it has the
database I am using to connect to the database I want. How can I find out
what is the DNS database source? It seems like it is connected to itself
instead of the right DNS. I don't see any tables in tables but I do get a
list of queries on the server so it must be connected but why don't I see the
tables as well? This is the one I would like to use but I don't get the
world icons that show the real SQL server data. In fact I don't even see the
tables on the access .mde file.
I try to view some queries and I get the error message "ADO error: MSDTC
on Server 'myComputer' is not available. I have MS SQL Server running in
Services as well as SQL Server express. Mainly because I don't know which one
is best. I also get the error 'Ole DB error trace Ole 10B Profvider MS Jet
OlebDB 4.0 Open rowset returned ox.... The specified table does not exist'
which I think means the query cannot access the tables. Why can I get the
queries and not the tables?
If I access it through terminal server access frontend I can make changes to
the queries. I also can see the tables which include dbo tables on a Server
but I would rather write my queries on my own desktop version and not do
anything on the terminal services version.
On the terminal services .mde database there are some pass through queries.
In fact the one I think I need appear to be a pass through query. If I open
the query the data is 2 years old. Can I make a make table from the pass
through query to see if it updates the data? I really do not want to do
anything wrong to the frontend db on terminal services but I need to write
this one query for a report.
tia,DNS are only a name given to a connection string. You don't have to use a
DSN to connect to a SQL-Server; it's only one possibility amongst others.
Second, DNS are only for ODBC while ADP use only ADO. So, you cannot use a
DSN with ADP and even with ADO, ADP can connect only to a SQL-Server and not
to a MDB or a MDE file. Also, the fact that you can connect to a sql-server
doesn't mean that you will be able to see everything (tables, views, sp,
functions, etc.) on the server because of permission issues.
Looks like that you have copied an ADP file from a terminal server to a
local machine but that the permissions on the server are set to block you
from accessing the data from outside the forms in the ADP project or
something like that.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Janis" <Janis@.discussions.microsoft.com> wrote in message
news:7333903F-5E59-4DF0-8BAB-5C45137AF7A5@.microsoft.com...
> I'm trying to figure out what is going on. I have an .adp file that has a
> connection DNS to a .mde file on the network. It is confusing. In the
> connection dialog it has my computer as the server name and it has the
> database I am using to connect to the database I want. How can I find out
> what is the DNS database source? It seems like it is connected to itself
> instead of the right DNS. I don't see any tables in tables but I do get a
> list of queries on the server so it must be connected but why don't I see
> the
> tables as well? This is the one I would like to use but I don't get the
> world icons that show the real SQL server data. In fact I don't even see
> the
> tables on the access .mde file.
> I try to view some queries and I get the error message "ADO error: MSDTC
> on Server 'myComputer' is not available. I have MS SQL Server running in
> Services as well as SQL Server express. Mainly because I don't know which
> one
> is best. I also get the error 'Ole DB error trace Ole 10B Profvider MS
> Jet
> OlebDB 4.0 Open rowset returned ox.... The specified table does not
> exist'
> which I think means the query cannot access the tables. Why can I get the
> queries and not the tables?
>
> If I access it through terminal server access frontend I can make changes
> to
> the queries. I also can see the tables which include dbo tables on a
> Server
> but I would rather write my queries on my own desktop version and not do
> anything on the terminal services version.
> On the terminal services .mde database there are some pass through
> queries.
> In fact the one I think I need appear to be a pass through query. If I
> open
> the query the data is 2 years old. Can I make a make table from the pass
> through query to see if it updates the data? I really do not want to do
> anything wrong to the frontend db on terminal services but I need to write
> this one query for a report.
> tia,