ASP连接到SQL 2008命名实例

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.

我有一个运行三个实例的SQL 2008服务器。为每个实例分配一个唯一的IP地址并侦听端口1433.仅启用TCPIP。

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

我的所有ASP.Net应用程序都使用IP地址成功连接,连接字符串类似于:

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:

我的ASP应用程序只会连接到默认实例;每当我尝试连接到另一个实例时,我都会收到此错误:

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:

ASP连接字符串是这样的:

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:

我暂时关闭了IIS和SQL Server上的Windows防火墙。 SQL Server和IIS服务器之间没有任何其他防火墙。 SQL Browser正在SQL Server上运行。我尝试修改ASP连接字符串以包含实例名称,如:

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?

有没有办法从ASP连接到命名实例?

4 个解决方案

#1

Write the server name like so:

像这样写服务器名称:

server=sqlserver\InstanceName

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

另外,请检查1433上可能没有的端口号。

#2

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).

从ASP.net连接到服务器时,您可能正在使用SQL OLE DB提供程序。然而,当涉及到ASP时,您正在使用OLEDB for ODBC(或者它选择这种连接来根据您的连接字符串选择它)。

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.

是这样的吗?如果是这样,它可能是连接字符串的问题。有关SQL Server连接字符串的信息,请参阅www.connectionstrings.com。

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

如果您使用ODBC,请确保安装了正确的驱动程序集以连接到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

编辑:我认为当使用ADO使用此http://www.connectionstrings.com/sql-server-2008#20时,您将不得不更改ASP中的连接字符串

#3

EDIT: Sql Server names are not UNC ;)

编辑:Sql Server名称不是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?

有趣的是,数据库是初始目录的同义词吗?

#4

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.

尝试提供IP地址。我怀疑当它解析服务器的名称时,它获取默认实例的IP地址。由于您已将它们绑定到单独的IP地址,因此您需要提供IP地址,或者,如果IP地址解析为其他主机名,请使用与该IP地址一起使用的主机名。我认为你不需要指定实例名称,但我不确定。我们的生产服务器上有两个实例 - 一个用于公共Web应用程序,另一个用于非公共Web应用程序。每个都绑定到一个单独的IP地址,但地址反向映射到不同的名称。连接到每个实例时,我只需要使用正确的服务器名称。

#1