Conclusion

This chapter focused on how to access SQL Server data from remote client applications, either by exposing SQL Server directly to the Internet or by writing a middle-tier service interface. Using a direct connection through the Internet provides a great deal of flexibility, but also exposes your installation to security risks. Using a middle-tier architecture provides a secure interface for your SQL Server, enhances scalability and performance, and eases configuration and maintenance tasks.

Chapter 9 Quick Reference

To	Do this
Open a connection to SQL Server through TCP/IP	Enable the TCP/IP communication protocol. Configure a SQL Server instance to listen on a specific IP address. Provide client applications with the exact IP address and port where SQL Server is listening for requests. Configure the firewall to allow access to that port.
Validate that TCP/IP is enabled in SQL Server	In SQL Server Configuration Manager, expand the SQL Server Network Configuration node and select the Protocols For <instance_name> node; Or In SQL Server Surface Area Configuration, click the Surface Area Configuration For Services And Connections link, expand the instance you are configuring, expand the Database Engine node, and select the Remote Connections node.
Direct a client application to the correct IP address and port number for SQL Server	Specify the IP address and port number in the connection string using the following syntax: Data Source = tcp:<ip_address>/instance, <port_number>;
Access SQL Server using XML / SOAP protocols through HTTP	Configure HTTP Endpoints with the T-SQL CREATE ENDPOINT DDL statement.
Use ASP.NET Web Services in the middle tier	Use Visual Studio 2005 to create a new Web Service project. Encapsulate the data access code inside the Web service to avoid having to expose SQL Server to the Internet.
Use Microsoft .NET Remoting in the middle tier	Use Visual Studio 2005 to create a service class that encapsulates the data access code to avoid having to expose SQL Server to the Internet. Expose the service class using a hosting application, such as IIS.