Error connecting to remote SQL 2000 Server

I'm experiencing a problem connecting to a SQL 2000 server through my ASP code. My connection string is as follows:

<addname="TheConnectionString"connectionString="driver={Sql Server};provider=MSDASQL;server=10.0.1.42;database=dbname;uid=*********;pwd=*********"providerName="System.Data.Odbc" />

The problem doesn't occur when I run my ASP code from my workstation using VS.NET's builtin webserver. It makes the connections and executes the CRUD commands successfully. However, when I publish my site to the webserver (which resides on 10.0.1.16) it fails out with the following error:

System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

I've verified that the webserver can talk to the DB server by connecting to the remote DB server through SQL Enterprise Manager running locally on the webserver. If I try to do this with a DSN I get the same results. I get the same error from any other webserver on the internal network. The difference between my workstation and the internal network is that I'm using a VPN to connect to our internal network while the webservers are physically connected to it. Firewalling isn't the issue in this case because the webservers and DB server are on a trusted network. I've seen other ways of connecting to the DB server including using Named Pipes (which I would rather not do because I don't want to setup a named pipe on the production db server).

I'm relatively new to ASP.NET 2.0, so the above connection string is an adaptation of some old ASP code. If anybody has any suggestions on a better way to construct this connection string, please let me know. I've been racking my brains trying to get this to work outside of the devel env.

Try with this:

<add name="TheConnectionString" connectionString="Server=10.0.1.42;Database=dbname;User ID=*********;Password=*********;"/>

|||

johnladda:

System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

First, this error is a generic one so your actual problem may range from incorrect credentials to a network problem.

Second, I'm not too sure why you've used ODBC library to connect to SQL while you've the SQL library.

johnladda:

I'm relatively new to ASP.NET 2.0, so the above connection string is an adaptation of some old ASP code. If anybody has any suggestions on a better way to construct this connection string, please let me know.

Third, you can use "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx" to connect to SQL Server. You can referwww.connectionstrings.com . This site provides a number of connection string examples for almost every database.

Hope this will help.

|||

This site is fantastic. It's exactly what I needed. I was wondering if the "Data Source" property of the connection string works similar to the "Server" property in that you can specify the port number too? If not, how would I go about specifying the port number in the connection string. I only just recently found out that the person who implimented the database server put it on an alternate port.

|||

johnladda:

I was wondering if the "Data Source" property of the connection string works similar to the "Server" property in that you can specify the port number too? If not, how would I go about specifying the port number in the connection string. I only just recently found out that the person who implimented the database server put it on an alternate port.

No, you've mistaken the Data Source property of the connection string. It is no way similar to any server control. In fact, you can user Data Source or Server property in your connection string. Below are some of the alternatives you can use in your connection string.

Data Source = Server
Initial Catalog = Database
User Id = uId
Password = pwd

The default port is 1433 for SQL Server TCP/IP connection. You can specify any other port in your connection string as below:

"Data Source=xxx,portNumber;Initial Catalog=xxx;User ID=xxx;Password=xxx". That is, after either the IP or the name of the server put a comma and then provide the port number.