Configuring MSDE 2000 and SQL Server 2000 under Windows 2003 or XP SP2

Last updated: Wednesday, February 09, 2005

TOC :: Up

This article explains how to configure Microforge.net Enterprise Server to connect to MSDE and MSSQL Servers that are installed on machines running the Windows 2003 and Windows XP Service pack 2 operating systems.

Article Details

Microsoft Windows 2003 and Windows XP Service Pack 2 (SP2) include the Windows Firewall, which will filter/discard unsolicited incoming TCP/IP traffic. By default, Windows Firewall is enabled on computers that are running Windows XP SP2 and will block all ports.

Configuring Enterprise Server to access a local SQL Server

If your Enterprise Server is on the same machine as your MSDE or MSSQL Server, Enterprise Server won't need to use the network or TCP/IP to connect to your SQL Server, so the Windows Firewall shouldn't present any problems to you. In this case, the only thing you will have to ensure is that

Your server is configured to accept SQL Server Authentication (which our software requires in order to establish a connection). For more information on how to enable this, please see our knowledge base article: How to configure MSDE/SQL to allow SQL Server authentication.
You specify either "(local)" or "." as the address for your SQL Server. This will force ENT Server to connect to your SQL Server using the shared memory pipe for SQL Server, which is guaranteed to be installed and enabled.

Configuring Enterprise Server to access an MSDE/MSSQL server on your network

If your MSDE or MSSQL Server is not running on the same machine as your Enterprise Server, the Windows Firewall may be preventing your SQL Server from receiving any requests from the network (and thus preventing your ENT Server from making a connection to this SQL Server). In this case you will need to add an exception to the Windows Firewall, on the machine where your SQL Server is installed.

For maximum security, Microsoft currently recommend that MSDE and MSSQL servers be configured to accept network connections using the TCP/IP protocol (and not Named Pipes, also known as RPC or the Remote Procedure Call protocol). This requires:

The TCP/IP network libraries are installed and enabled on your SQL Server. You can check what network libraries you have installed by running the Server Network Utility on the machine where your SQL Server is installed. This should be installed by both SQL Server 2000 and MSDE2000, even though no shortcut is installed for this. It is actually the file srvnetcn.exe in your System32 directory.
The TCP/IP libraries are configured to use a static port (normally the case, unless you are running a named instance of MSDE - if you are running a named instance of MSDE then talk to your DB Administrator about this - if you don't know what one of these is then don't worry about it).
You add an exception to the Windows Firewall either for the port that your TCP/IP network libraries are configured to use (port 1433 by default) or for the MSDE or MSSQL Server application itself. The later is only recommended by Microsoft if you have a named instance of MSDE that is configured to use a dynamic port... and even then they recommend that rather than adding an exception for SQL Server you reconfigure your SQL Server to use static ports.

You can find further information on how to configure your SQL Server to be accessible over the network by enabling TCP, in Microsoft KB Article 841252.