Microsoft KB Archive/269882

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

Article ID: 269882

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.0
 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 1
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q269882



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 &quot;dbmssocn&quot; (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.



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(&quot;ADODB.Connection&quot;) Set Rs = CreateObject(&quot;ADODB.Recordset&quot;) Set Cmd = Createobject(&quot;ADODB.Command&quot;) Conn.Open &quot;Provider=SQLOLEDB;Password=password;Persist Security Info=True;User ID=username;Initial Catalog=DBNAME;Data Source=servername;Network Library=dbmssocn&quot; Cmd.ActiveConnection=Conn

SQL = &quot;Select * from TABLE&quot;

Cmd.CommandText = SQL Set Rs = Cmd.Execute

