Express Essentials: Using SQL Server Express with Remote Applications

In the past several Express Essentials columns, I've shown how you can connect various applications of the Microsoft Office suite to SQL Server Express. In all those cases, my examples assumed that you were running SQL Server Express locally--in other words, that both Office and SQL Server Express were installed on the same physical system. But what if they aren't? Can you still use SQL Server Express as a database back end, and if so, how?

The quick answer is yes, and you're about to find out how. SQL Server Express is built on the same client/server style architecture as the full-blown members of the SQL Server family and fully supports multiple simultaneous network connections. This is one of the key features that sets SQL Server Express apart from file-based databases such as Microsoft Access.

The first step toward using SQL Server Express as a networked database is to enable SQL Server Express's ability to accept remote connections. (For instructions on enabling remote connections, see Express Essentials, "Is SQL Server Express Multiuser-Capable?" at www.sqlmag.com/articles/index.cfm?articleid=49736 .) However, there is one other important component--the client networking support. SQL Server Express supports multiple network protocols, and to allow it to do so, Microsoft provides several network libraries, each of which supports a different network protocol. The network libraries that SQL Server Express supports are

- Shared Memory, which is used for local system connections

- TCP/IP, which is used for TCP/IP network connections

- Named Pipes, which is used for NetBIOS (or NetBIOS over TCP/IP) network connections

- VIA (Virtual Interface Adapter), which is used for System Area Network communication connectors

The SQL Server Express system has support for all these protocols, but some of that support might not be enabled. To find out which protocols are enabled, click Start, Programs, Microsoft SQL Server 2005, Configuration Tools, SQL Server Configuration Manager. Expand the SQL Native Client Configuration node and select the Client Protocols node. Each protocol and its status--either enabled or disabled--is listed. To enable or disable a protocol, double-click it and select Yes or No for the Enabled property.

Finally, you need to make sure that the networked systems that are running Office (or whatever application you want to connect to SQL Server Express) have suitable client networking software. The most efficient client software is the SQL Server Native Client, which you can download from http://msdn2.microsoft.com/en-us/data/aa937733.aspx . The installation of the SQL Native Client must use a protocol that's enabled on the SQL Server Express system--the protocol that's most likely to be enabled is TCP/IP. You can configure the client-side SQL Native Client just as you do the SQL Server Express system: by using the SQL Server Configuration Manager.