ASP SQL connection refused, necessary ports are open

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 correc开发者_如何学编程tly 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

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.

This happens when the SQL Server service is not running at the remote machine

You may also want to ensure that the sql server instance allows remote connections

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.

Then one of the following is true:

• port forwarding does not work
• the SQL host firewall is blocking the port (1433)
• there is no process listening on 1433 on target:
  • SQL server stopped or
  • SQL not configured to use TCP or
  • SQL host has multiple-IPs and SQL TCP is not listening on the inbound IP from the forwarder or
  • SQL server listens on non-default port