How To Use ADO to Connect to a SQL Server That Is Behind a Firewall
This article was previously published under Q269882
This article has been archived. It is offered "as is" and will no longer be updated.
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.
In the following sample code,
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=ConnSQL = "Select * from TABLE"Cmd.CommandText = SQLSet Rs = Cmd.Execute
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
How To Set the SQL Server Network Library in an ADO Connection String
OLE DB NetLib sockets TCP IP
Article ID: 269882 - Last Review: 12/05/2015 21:15:37 - Revision: 4.5
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
Keywords: kbnosurvey kbarchive kbhowto KB269882