ASP Connections to SQL 2008 Named Instance

Question

I have an SQL 2008 server running three instances. Each instance is assigned a unique IP address and listens on port 1433. Only TCPIP is enabled.

All of my ASP.Net applications connect successfully using the IP address, with a connection string similar to:

User ID=SQLUser;Password=userpass;Database=TestDB;Data Source=sqlserver

My ASP applications will only connect to the default instance; I am getting this error whenever I try to connect to another instance:

Microsoft OLE DB Provider for ODBC Drivers error '80004005' <br />
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.

The ASP connection strings are like this:

driver={SQL Server};server=sqlserver;uid=SqlUser; pwd=userpass; database=TestDB

I turned off Windows Firewall on both the IIS and SQL Server for now. There's not any other firewalls between the SQL Server and the IIS server. SQL Browser is running on the SQL Server. I tried modifying the ASP connection strings to include the instance name, like:

driver={SQL Server};server=sqlserver/InstanceName;uid=SqlUser;pwd=userpass;database=TestDB

but I get the same error.

Is there any way to connect to a named instance from ASP?

Answer 1

Try supplying the IP address. I suspect that when it resolves the name of the server it's getting the IP address of the default instance. Since you've bound them to separate IP addresses, you'll need to either supply the IP address or, if the IP address resolves to a different hostname, use the hostname that goes with that IP address. I don't think you'll need to specify the instance name, but I'm not sure. We have two instances on our production server -- one for public web apps, the other for non-public web apps. Each is bound to a separate IP address, but the addresses reverse map to different names. I only have to use the correct server name when connecting to each instance.

Answer 2

EDIT: Sql Server names are not UNC ;)

Trying it with a \ instead of a / like:

mysqlserver\myinstance

Personally I always try to include the port. It saves a roundtrip where the client tries to figure out the port number:

server=mysqlserver\myinstance,1433;uid=SqlUser;pwd=userpass;database=TestDB

Interesting, is database a synonym for initial catalog?

Answer 3

Write the server name like so:

server=sqlserver\InstanceName

Also, check the port number as it might not be on 1433.

Related SO question:

• SQL Server 2005 Named Instance port problem

Answer 4

This is pure guess.

You might be using SQL OLE DB provider when connecting to the server from ASP.net.
Whereas, you are using OLEDB for ODBC when it comes to ASP (or it is choosing that kind of connection to pick that based on your connection string).

Is that the case?
If so, it could be a problem with connection string. See www.connectionstrings.com for example on SQL Server connection string.

If you are using ODBC, make sure you have the right set of drivers installed in order to connect to SQL Server 2008.

Does that help?

EDIT: I think you will have to change your connection string inside ASP when using ADO to use this http://www.connectionstrings.com/sql-server-2008#20