Article ID: 269882 - Last Review: July 15, 2004 - Revision: 4.5

How To Use ADO to Connect to a SQL Server That Is Behind a Firewall

View products that this article applies to.

System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

This article was previously published under Q269882

Expand all | Collapse all

SUMMARY

When you use ActiveX Data Objects (ADO) to connect to a SQL Server 7.0 or SQL Server 2000 server that is behind a firewall, consider the following:

The firewall must be configured to permit port 1433 incoming (or the port numbers that SQL Server listens to on TCP/IP), and ports 1024 to 65535 outgoing.
The connection string must specify the SQL Server address: the IP address, the server DNS name, or a name inside the hosts file.
The connection string should specify the Network Library type, in this case "dbmssocn" (without the quotes) for TCP/IP Sockets Net-Library.

WARNING: Opening up the ports on the firewall may pose security issues; consult with your System Administrator or Security Administrator to configure the firewall.

Back to the top

MORE INFORMATION

In the following sample code, servername should be the server DNS name, IP address, or a name inside the hosts file:

Set Conn = CreateObject("ADODB.Connection")
Set Rs = CreateObject("ADODB.Recordset")
Set Cmd = Createobject("ADODB.Command")
	
Conn.Open "Provider=SQLOLEDB;Password=password;Persist Security Info=True;User ID=username;Initial Catalog=DBNAME;Data Source=servername;Network Library=dbmssocn"
Cmd.ActiveConnection=Conn

SQL = "Select * from TABLE"

Cmd.CommandText = SQL
Set Rs = Cmd.Execute

Back to the top