ASP SQL 连接被拒绝，必要的端口已打开
我正在尝试开发与 MSSQL 2005 服务器的 ASP SQL 连接。 SQL 服务器是我们主域服务器后面的子服务器，因此已经从 WAN 设置了端口转发> LAN 端口 1433 允许流量访问第二台服务器。
我已经在我的一端进行了 telnet 测试，以查看端口转发是否正常工作，它告诉我连接被拒绝。当我运行脚本时，我得到以下结果：
适用于 SQL Server 的 Microsoft OLE DB 提供程序错误“80004005”
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server 不存在或 访问被拒绝。
代码如下：
<%
Dim cn
Dim rs
Dim SQL
SQL = "SELECT count(*) FROM master"
'create an instance of the ADO cn and rs objects
Set cn = Server.CreateObject("adodb.connection")
Set rs = Server.CreateObject("adodb.recordset")
'open the cn to the database
cn.Open "Provider=SQLOLEDB;Data Source=###.###.###.###,1433;Network Library=DBMSSOCN;Initial Catalog=blah;User ID=blah;Password=blah"
'Open the rs object executing the SQL statement and return records
rs.Open SQL,cn,adOpenKeyset,adLockOptimistic
'first of all determine whether there are any records
If rs.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT rs.Eof
Response.write rs("count")
Response.write "<br>"
rs.MoveNext
Loop
End If
'close the cn and rs objects to free up resources
rs.Close
Set rs=nothing
cn.Close
Set cn=nothing
%>
谢谢
I'm trying to develop an ASP SQL connection to a MSSQL 2005 server. The SQL server is a sub server behind our main domain server, so a port forward has been setup from the WAN > LAN on port 1433 to allow the traffic to access the second server.
I've done a telnet test on my end to see if the port forward is working correctly and it tells me the connection is refused. When I run the script I get the following result:
Microsoft OLE DB Provider for SQL Server error '80004005'
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or
access denied.
The code is as follows:
<%
Dim cn
Dim rs
Dim SQL
SQL = "SELECT count(*) FROM master"
'create an instance of the ADO cn and rs objects
Set cn = Server.CreateObject("adodb.connection")
Set rs = Server.CreateObject("adodb.recordset")
'open the cn to the database
cn.Open "Provider=SQLOLEDB;Data Source=###.###.###.###,1433;Network Library=DBMSSOCN;Initial Catalog=blah;User ID=blah;Password=blah"
'Open the rs object executing the SQL statement and return records
rs.Open SQL,cn,adOpenKeyset,adLockOptimistic
'first of all determine whether there are any records
If rs.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT rs.Eof
Response.write rs("count")
Response.write "<br>"
rs.MoveNext
Loop
End If
'close the cn and rs objects to free up resources
rs.Close
Set rs=nothing
cn.Close
Set cn=nothing
%>
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
另外，请检查“开始”菜单中“Microsoft SQL Server 2005”/“配置工具”下的表面区域配置，以确保TCP 已启用。我认为默认情况下仅启用命名管道。
防火墙
首先可以阻止与 SQL Server 的连接的是防火墙。如果您有任何防火墙，请确保将它们配置为允许连接到 SQL Server。 SQL Server 使用的默认 TCP 端口是 1433。防火墙包括 McAfee、Norton、Windows XP SP2 附带的 Windows 防火墙以及 Windows 2000 附带的 Internet 连接防火墙 (ICF)。
服务验证
能否连接到SQL Server 2005，需要验证SQL Server是否正在运行。默认情况下，SQL Server Express 版本作为命名实例 (SQLEXPRESS) 安装。这意味着您需要从本地计算机使用 (local)\SQLEXPRESS 来访问它。没有前缀的 SQLEXPRESS 将不起作用。您还可以在本地计算机上使用 127.0.0.1 IP 地址来避免与 DNS 相关的问题。
要验证该服务是否正在运行，请在命令提示符下键入 sqlcmd –S(local)\SQLEXPRESS。如果您看到“1>”这意味着您已成功连接。键入 exit 以退出 sqlcmd 程序。
连接协议
SQL Server 2005 支持多种用于连接客户端应用程序与数据库服务器的协议。这些协议是 TCP、命名管道 (NP)、共享内存 (SM)、VIA 和 HTTP。 SQL Server Express 仅支持 TCP、NP 和 SM。
默认情况下，本地计算机上的 SQL Server Express 只能访问 SM。这意味着除非启用 TCP 和/或 NP，否则从远程计算机到 SQL Server Express 的连接将失败。如果要使用 TCP 和 NP，则必须显式打开它们。使用 SQL Server 配置管理器为服务器和客户端显式启用 TCP 和 NP。启用TCP和NP后，需要启动SQL Browser服务（参见下面的SQL Browser）。
如果您是远程连接，则需要将“（本地）”替换为服务器的IP地址。如果 DNS 可以解析该名称，您还可以使用服务器名称而不是 IP 地址。
如果您通过特定 IP 地址进行连接，请确保为其启用连接。在 SQL 配置管理器中，展开 SQL Server 2005 网络配置节点，然后从右侧窗格中选择 TCP/IP 属性。选择“IP 地址”选项卡并将特定 IP 地址的“启用”更改为“是”。
SQL Server 配置管理器
SQL Server 2005 和 SQL Server Express 中的 SQL Server 配置管理器取代了客户端网络实用程序和服务器网络实用程序。它允许您配置 SQL Server 侦听的协议以及 ADO.NET 2.0 应用程序可以使用的协议。但是，要为使用 ADO 而不是 ADO.NET 2.0 的应用程序配置客户端协议，您仍然需要使用客户端网络实用程序。客户端网络实用程序随 ADO 一起提供，并且是 Windows 2000、Windows XP 和 Windows 2003 的一部分。
要远程连接到 SQL Server Express，请确保服务器可以侦听 TCP 连接。从 SQL Server 配置管理器中，展开“SQL Server 2005 网络配置”并导航至“SQL Server Express 协议”，然后启用 TCP。您需要重新启动服务器才能使更改生效。
如果您使用 Teratrax Database Manager，则可以通过单击连接对话框中的“客户端网络实用程序”按钮来配置客户端协议。确保您满足 Teratrax Database Manager 的操作系统要求（Windows 2000、Windows XP 或 Windows 2003）。
SQL Server Browser
SQL Browser 是 SQL Server 2005 中的一项新服务，用于识别命名实例侦听的端口。 SM协议不使用此服务。默认情况下，此服务在 SQL Server Express 中处于关闭状态。这意味着用户必须启动此服务才能进行远程访问。您可以从服务管理器或从命令行键入“NET START SQLBROWSER”来启动 SQL Browser 服务。
SQL Browser 侦听 UDP 端口 1434。但是，保留端口 UDP 1434 的 SQL Server 2000 SP3 之前的版本可能会导致 SQL Browser 名称解析失败，因为它们可能拒绝放弃该端口。解决方法是将计算机上的所有 SQL Server 2000/MSDE 实例升级到 SP3 或更高版本。
Also, check Surface Area Configuration in Start menu under 'Microsoft SQL Server 2005' / 'Configuration Tools' to make sure that TCP is enabled. I think by default only Named Pipes are enabled.
Firewalls
The first thing that can block a connection to SQL Server is a firewall. If you have any firewalls, make sure they are configured to allow connections to SQL Server. The default TCP port that SQL Server uses is 1433. Firewalls include McAfee, Norton, Windows Firewall which ships with Windows XP SP2, and Internet Connection Firewall (ICF) which ships with Windows 2000.
Service verification
Before you can connect to SQL Server 2005, you need to verify that SQL Server is running. By default, the SQL Server Express edition is installed as a named instance (SQLEXPRESS). This means that you need to access it by using (local)\SQLEXPRESS from the local machine. SQLEXPRESS without the prefix will not work. You can also use the 127.0.0.1 IP address on a local machine to avoided DNS related problems.
To verify that the service is running, type sqlcmd –S(local)\SQLEXPRESS at the command prompt. If you see "1>" that means that you managed to connect. Type exit to exit the sqlcmd program.
Connection protocols
SQL Server 2005 supports a number of protocols for connecting client applications with the database server. These protocols are TCP, Named Pipes (NP), Shared Memory (SM), VIA, and HTTP. Only TCP, NP, and SM are supported in SQL Server Express.
By default, only SM is accessible for SQL Server Express on the local machine. This means that connections from a remote machine to SQL Server Express will fail unless TCP and/or NP is enabled. If you want to use TCP and NP, you must explicitly turn them on. Use SQL Server Configuration Manager to explicitly enable TCP and NP for server and client. After enabling TCP and NP, you need to start the SQL Browser service (See SQL Browser below).
If you are connecting remotely, you need to substitute "(local)" with the IP address of the server. You can also use the server name instead of the IP address if DNS can resolve the name.
If you are connecting via a specific IP address, make sure you enable the connection for it. In SQL Configuration Manager, expand the SQL Server 2005 Network Configuration node then select TCP/IP Properties from the pane on the right. Select the IP Addresses tab and change Enabled to Yes for the specific IP address.
SQL Server Configuration Manager
The SQL Server Configuration Manager in SQL Server 2005 and SQL Server Express replaces both Client Network Utility and the Server Network Utility. It allows you to configure the protocols that SQL Server listens to as well as the protocols that ADO.NET 2.0 application can use. However, to configure client protocol for applications that use ADO instead of ADO.NET 2.0, you still need to use the Client Network Utility. The Client Network Utility ships with ADO and is part of Windows 2000, Windows XP, and Windows 2003.
To connect to SQL Server Express remotely, make sure that the server can listen to TCP connections. From the SQL Server Configuration Manager, expand "SQL Server 2005 Network Configuration" and navigate to "Protocols for SQL Server Express" then enable TCP. You need to restart the server for the change to take effect.
If you are using Teratrax Database Manager, you can configure client protocols by clicking on the "Client Network Utility" button in the connection dialog. Make sure that you meet the operating system requirement for Teratrax Database Manager (Windows 2000, Windows XP, or Windows 2003).
SQL Server Browser
SQL Browser is a new service in SQL Server 2005 that is used to identify the ports that named instances listen on. The SM protocol does not use this service. This service is turned off in SQL Server Express by default. This means that the user will have to start this service so that remote access can work. You can start the SQL Browser service from the Service Manager or by typing "NET START SQLBROWSER" from the command line.
SQL Browser listens on UDP port 1434. However, pre-SP3 versions of SQL Server 2000 holding port UDP 1434 may result in failure of SQL Browser name resolution, since they may refuse to give up the port. The workaround is to upgrade all SQL Server 2000/MSDE instances on the machine to SP3 or higher.
当 SQL Server 服务未在远程计算机上运行时会发生这种情况
This happens when the SQL Server service is not running at the remote machine
您可能还想确保 sql server 实例允许远程连接
You may also want to ensure that the sql server instance allows remote connections
那么以下情况之一为真：
Then one of the following is true: