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 IIS & MS SQL connection problem ;-(

Have successfully installed MS SQL on my Win XP Pro machine, created tables,
and a dsn, which is working fine in Dreamweaver. So far so good.

When I run the page on IIS I get the following:-

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
requested in login 'MenuPlanner'. Login fails.

I think it's permissions - anyone got any clues as to where to look?

TIA

GrantBuzby wrote:
> Have successfully installed MS SQL on my Win XP Pro machine, created
tables,
> and a dsn, which is working fine in Dreamweaver. So far so good.
> When I run the page on IIS I get the following:-
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'MenuPlanner'. Login fails.
> I think it's permissions - anyone got any clues as to where to look?

You could see if the login MenuPlanner has got permissions to the
database you are logging in to. That would be a start.

--
David Rowland
For a good user and performance monitor, check DBMonitor
http://dbmonitor.tripod.com

> TIA
> Grant|||"dbmonitor" <dbmonitor_support@.hotmail.com> wrote in message
news:1107519068.678569.226760@.o13g2000cwo.googlegr oups.com...
> Buzby wrote:
>> Have successfully installed MS SQL on my Win XP Pro machine, created
> tables,
>> and a dsn, which is working fine in Dreamweaver. So far so good.
>>
>> When I run the page on IIS I get the following:-
>>
>> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
>> requested in login 'MenuPlanner'. Login fails.
>>
>> I think it's permissions - anyone got any clues as to where to look?
> You could see if the login MenuPlanner has got permissions to the
> database you are logging in to. That would be a start.

This is what has got me - permissions are set up. I've created a dsn, which
I can run queries in Dreamweaver and filter results just fine. I'm having
trouble when running the page on my webserver (IIS which is working fine)

Stumped ;-(|||Buzby wrote:
> "dbmonitor" <dbmonitor_support@.hotmail.com> wrote in message
> news:1107519068.678569.226760@.o13g2000cwo.googlegr oups.com...
> > Buzby wrote:
> >> Have successfully installed MS SQL on my Win XP Pro machine,
created
> > tables,
> >> and a dsn, which is working fine in Dreamweaver. So far so good.
> >>
> >> When I run the page on IIS I get the following:-
> >>
> >> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> >> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open
database
> >> requested in login 'MenuPlanner'. Login fails.
> >>
> >> I think it's permissions - anyone got any clues as to where to
look?
> > You could see if the login MenuPlanner has got permissions to the
> > database you are logging in to. That would be a start.
> This is what has got me - permissions are set up. I've created a dsn,
which
> I can run queries in Dreamweaver and filter results just fine. I'm
having
> trouble when running the page on my webserver (IIS which is working
fine)
> Stumped ;-(

Is MenuPlanner the database name or the login name?

If it is the database name, are you connecting to the database via a
userid/password or are you connecting with Windows interactive UserID?
--
David Rowland
For a good user and performance monitor, check DBMonitor
http://dbmonitor.tripod.com|||Buzby (gb@.pumpupthe.net) writes:
> Have successfully installed MS SQL on my Win XP Pro machine, created
> tables, and a dsn, which is working fine in Dreamweaver. So far so
> good.
> When I run the page on IIS I get the following:-
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
> requested in login 'MenuPlanner'. Login fails.
> I think it's permissions - anyone got any clues as to where to look?

Sounds like the login has a default db which does not exist, or the login
does have access to. Use sp_helplogins to check, use sp_defaultdb to change.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 2/4/05 5:37 PM, in article Xns95F3F003A2514Yazorman@.127.0.0.1, "Erland
Sommarskog" <esquel@.sommarskog.se> wrote:

> Buzby (gb@.pumpupthe.net) writes:
>> Have successfully installed MS SQL on my Win XP Pro machine, created
>> tables, and a dsn, which is working fine in Dreamweaver. So far so
>> good.
>>
>> When I run the page on IIS I get the following:-
>>
>> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
>> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
>> requested in login 'MenuPlanner'. Login fails.
>>
>> I think it's permissions - anyone got any clues as to where to look?
> Sounds like the login has a default db which does not exist, or the login
> does have access to. Use sp_helplogins to check, use sp_defaultdb to change.

Yea, but a DSN is being used so we have to believe that MenuPlanner is the
name of the DSN Connection. I am assuming that the DSN was created on the
IIS server and connects successfully when you "test connection" in the ODBC
dialog.

I highly recommend using a DSN-less connection in your ASP pages. There is
lots of documentation on ADODB.

If you need some sample connection strings let me know.|||Gregory Dean (gdean@.datapex.com) writes:
> On 2/4/05 5:37 PM, in article Xns95F3F003A2514Yazorman@.127.0.0.1, "Erland
> Sommarskog" <esquel@.sommarskog.se> wrote:
>> Buzby (gb@.pumpupthe.net) writes:
>>> Have successfully installed MS SQL on my Win XP Pro machine, created
>>> tables, and a dsn, which is working fine in Dreamweaver. So far so
>>> good.
>>>
>>> When I run the page on IIS I get the following:-
>>>
>>> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
>>> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database
>>> requested in login 'MenuPlanner'. Login fails.
>>>
>>> I think it's permissions - anyone got any clues as to where to look?
>>
>> Sounds like the login has a default db which does not exist, or the
>> login does have access to. Use sp_helplogins to check, use sp_defaultdb
>> to change.
> Yea, but a DSN is being used so we have to believe that MenuPlanner is
> the name of the DSN Connection. I am assuming that the DSN was created
> on the IIS server and connects successfully when you "test connection"
> in the ODBC dialog.

Not sure what you mean, but since SQL Server does not know what a DSN
is, MenuPlanner cannot be the name of the DNS. But it can be the
login name specified in the DSN.

> I highly recommend using a DSN-less connection in your ASP pages.

I echo that. DSN is a concept that I never understood the point with.
An extra layer that only causes hassle.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks one and all - it turned out it was an IIS permissions issue - however
I've taken on board what you have suggested and dsn less is the way we are
going!

Cheers

Buzby