| In order to provide direct access to SQL Server from a remote network, you first need to decide the communication protocol that client applications will use to communicate with the server. In this section we are going to focus on: With both approaches, you must take security measures into account. Connecting through TCP/IP SQL Server implements its own native communication protocol when using TCP/IP, called Tabular Data Stream (TDS). Client applications must use a compatible provider (ODBC, OLE DB, or SQLNCLI) in order to transform their requests into the TDS format. Figure 9-1 shows the minimum recommended physical infrastructure required to make SQL Server available through TCP/IP over the Internet. Figure 9-1. Physical infrastructure needed to make SQL Server available through TCP/IP over the Internet. 
The firewall restricts access to the internal network by forwarding only requests targeted at specific TCP/IP addresses in the local network. This implies that: The client application must know the TCP/IP address and network port where SQL Server is listening for requests. The firewall must be configured to allow access to the specific TCP/IP address were SQL Server is listening for requests.
Connecting to SQL Server through TCP/IP over the Internet 1. | Validate that the TCP/IP communication protocol is enabled in SQL Server.
| 2. | Configure a SQL Server instance to listen on a specific IP address.
| 3. | Provide client applications with the exact IP address and port where SQL Server is listening for requests, open a connection from the client application, and execute your queries.
|
The steps outlined above are detailed in the next sections of this chapter. Validating that TCP/IP Is Enabled in SQL Server SQL Server provides support for multiple communications protocols. To validate that TCP/IP is enabled, follow these steps: 1. | From the Start menu, select All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager. The SQL Server Configuration Manager is shown below:
 | 2. | In the tree view control on the left pane, click the plus (+) sign next to the SQL Server Network Configuration node. Select the Protocols For <instance_name> node for the SQL Server instance you want to configure, as shown here:
 | 3. | The righthand side pane displays a list of the available network protocols. If TCP/IP is marked as Enabled, then the server is ready to accept connections through the TCP/IP protocol. If TCP/IP is marked as Disabled, then right-click on the TCP/IP icon and choose Enable from the context menu, as shown here:
 |
The SQL Server Surface Area Configuration Tool The SQL Server Surface Area Configuration tool can also be used to validate whether TCP/IP is enabled. If you use the SQL Server Surface Area Configuration tool, you can further configure settings for the available communication protocols. To use this tool, do the following: 1. | From the Start menu, select All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration. The SQL Server Surface Area Configuration tool is shown below:
 | 2. | Click on the Surface Area Configuration For Services And Connections link at the bottom of the window.
| 3. | In the Surface Area Configuration For Services And Connections window, in the tree view on the left side, click the plus (+) sign next to the SQL Server instance you want to configure. Similarly, open the Database Engine node and then select the Remote Connections node, as shown here:
 | 4. | On the righthand side of the window, select the Local And Remote Connections option and then select the Using TCP/IP Only option, as shown here:
 | 5. | Click the OK button. SQL Server notifies you that the change will take effect after the SQL Server service is restarted.
|
|
Configuring SQL Server to Listen on a Specific IP Address Default instances in SQL Server 2005 listen on TCP port 1433. Named SQL Server instances receive a dynamically assigned TCP port address when the instance is loaded. If you want to access a named SQL Server instance from the Internet, you must configure the SQL Server instance to listen on a specific port that is not assigned dynamically. To configure a named instance to listen on a specific TCP port: 1. | Again open SQL Server Configuration Manager.
| 2. | In the tree view control in the left pane, open the Server Network Configuration node by pressing the plus (+) sign. Select the Protocols For <instance_name> node for the SQL Server instances you want to configure.
| 3. | Double-click the TCP/IP element shown on the righthand side pane.
| 4. | In the TCP/IP Properties window, click on the IP Addresses tab, shown here:
 | 5. | Notice the IPAll section at the bottom. If the TCP Dynamic Ports property contains the value 0, delete the 0, leaving the property empty. Then change the TCP Port property to the specific port number on which you want SQL Server to listen, as shown here:
 | 6. | Click the OK button. SQL Server Configuration Manager notifies you that the change will take effect after the SQL Server service is restarted.
|
Important  | Some ports are reserved for specific applications. SQL Server cannot listen on a port being used by another application. Check the Internet Assigned Numbers Authority listings to validate which port numbers are not in use: http://www.iana.org/assignments/port-numbers. |
Directing the Client Application to the Correct IP Address and Executing Queries The last step in allowing client applications to connect to SQL Server through the Internet using the TCP/IP protocol is to direct all the client application calls to the correct server, using the IP address and port number through which the server is listening for requests. The connection string for specifying the server name must follow this format: Data Source=tcp:<ip_address>/instance,<port_number>; Initial Catalog=<database_name>; User ID=<user_id>; Password=<password>;
Here's an example of a connection string using the above format: Data Source=tcp:190.190.200.100/Sales,1344; Initial Catalog=AdventureWorks; User ID=sa;Password=Pa$$w0rd;
Note  | You don't have to specify the instance if you are connecting to the default instance. |
The following code sample (included in the sample files in the folder ConnectThroughTCPport) shows how to open a connection to a SQL Server through the Internet using TCP/IP and retrieve all of the employee records from the AdventureWorks database. To use this sample, you will need to update the parts of the connection string shown in bold to match your environment. You will need a public IP address to communicate with a server across the Internet. If you are communicating within an Intranet, the server's Intranet IP address is sufficient. Use the <port_number> that you set up in the section titled "Configuring SQL Server to Listen on a Specific IP Address." Tip  | To connect to SQL Server with a user name and password, you must set SQL Server to use mixed authentication mode. See Chapter 2, "Basic Database Security Principles," for more information about mixed authentication mode. At the time of this writing, SQL Server does not recognize your Windows credentials if you use Integrated Security = true when connecting through a TCP port because TCP/IP is a non-authenticated protocol. This means that connections are not authenticated using Windows credentials. To authenticate, you must use SQL identities and specify a user name and password in the connection string. |
Note | To determine your computer's IP address, select Run from the Start menu. Type cmd in the Run dialog box and click the OK button. A command line window will open. At the prompt, type ipconfig and press the Enter key. You will see your computer's IP address. Type exit and press the Enter key to close the command line window. |
Public Sub GetEmployeeList() Dim connectionString As String connectionString = "Data Source=tcp:192.168.1.102,49152;" + _ "Initial Catalog=AdventureWorks; User ID=Mary; password=34TY$$543" Dim query As String query = "SELECT Person.Contact.FirstName + ' ' + " + _ "Person.Contact.LastName AS 'Employees' " + _ "FROM Person.Contact " + _ "INNER JOIN HumanResources.Employee " + _ "ON Person.Contact.ContactID = HumanResources.Employee.ContactID" Using cn As New SqlClient.SqlConnection(connectionString) Using cmd As New SqlClient.SqlCommand(query, cn) cn.Open() Dim dr As SqlClient.SqlDataReader = cmd.ExecuteReader() While (dr.Read()) Console.WriteLine(dr(0)) End While End Using End Using End Sub
Important | By following the steps above, you have configured SQL Server to: 1. | Use TCP/IP as a network protocol.
| 2. | Listen on a specific IP port.
|
You still have to configure the firewall or proxy server in your organization to allow access to SQL Server from an external application. |
Connecting through HTTP Endpoints SQL Server 2005 introduces the ability to use HTTP as a communication protocol through HTTP Endpoints. Here are the main benefits of using HTTP Endpoints: External client applications can connect to SQL Server through the Internet using the HTTP communication protocol no matter where they are located physically. Client applications written in programming languages or execution platforms that do not support any of the SQL Server data access providers can still execute queries in SQL Server through HTTP access. You don't need to open any additional ports on your firewall configuration. Communication is through port 80 just as for any other HTTP communication.
Connecting to SQL Server through HTTP 1. | Create stored procedures or user-defined functions to encapsulate the operations to be exposed publicly.
| 2. | Create and configure an HTTP Endpoint.
| 3. | Create a reference from the client application to the HTTP Endpoint.
|
The steps outlined above are detailed in the next sections of this chapter. More Info The HTTP capabilities in SQL Server depend on the HTTP API (HTTP.sys) available on the host operating system. Currently only Windows XP SP2 and Windows Server 2003 provide support for HTTP.sys.
Creating Stored Procedures or User-Defined Functions to Encapsulate the Operations to Be Exposed Publicly HTTP Endpoints in SQL Server 2005 provide a way to define a service-oriented interface to database operations. Service orientation means that the client applications will not be connecting or directly executing the defined stored procedures or user-defined functions, but the HTTP Endpoint will expose the stored procedures and user-defined functions as services (XML Web Services) through a predefined service contract, known as a Web Services Description Language (WSDL) contract. Both client and server must adhere to the defined contract to exchange XML messages. In the example in this section, you will create a new stored procedure that returns the list of sales order headers. Creating a Stored Procedure 1. | From the Start menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.
| 2. | If asked to log in to a server, provide the proper authentication credentials and click the Connect button to log in to SQL Server.
| 3. | If not already opened, click the New Query button to open a New Query window. Then enter the following text, which is included in the sample file httpEndpoints.sql.
USE AdventureWorks GO CREATE PROCEDURE GET_HEADER_LIST AS SELECT * FROM Sales.SalesOrderHeader GO
| 4. | Press F5 or click the Execute button to execute the T-SQL script. Test your work so far by executing the following statement to run the stored procedure.
EXEC GET_HEADER_LIST
|
Creating and Configuring an HTTP Endpoint After you have created all of the stored procedures and user-defined functions that will be exposed through the HTTP Endpoint, you must create and configure the HTTP Endpoint. To configure an HTTP Endpoint, database administrators use a new Data Definition Language (DDL) statement, CREATE ENDPOINT. Creating an HTTP Endpoint 1. | Open a New Query window in SQL Server Management Studio.
| 2. | Enter the following T-SQL code, which is included in the sample file httpEndpoints.sql. (The URL tempuri.org represents an XML namespace. You can use any string as long as it is unique to you or your organization.)
Note | If Internet Information Server (IIS) is running on the same machine as SQL Server, you must stop the IIS service before running the following code. Otherwise, you will receive an error stating that the specified port may be bound to another process. |
USE MASTER GO EXEC sp_reserve_http_namespace N'http://localhost:80/sql/myservices' GO CREATE ENDPOINT [MyServices] STATE=STARTED AS HTTP ( PATH=N'/sql/myservices', PORTS = (CLEAR), AUTHENTICATION = (INTEGRATED) ) FOR SOAP ( WEBMETHOD 'http://tempuri.org/'.'SalesHeadersList'( NAME=N'[AdventureWorks].[dbo].[GET_HEADER_LIST]', FORMAT=ROWSETS_ONLY), WSDL=DEFAULT) GO
| 3. | Press F5 or click the Execute button to execute the T-SQL script.
|
The code creates an HTTP Endpoint to expose the GET_HEADER_LIST stored procedure as a service available to clients through the HTTP and Simple Object Access Protocol (SOAP) protocols. Note | To avoid name clashes between multiple HTTP endpoints, each application must reserve its namespace and register it with HTTP.sys. This can be done implicitly when creating a new Endpoint, or it can be done explicitly using the sp_reserve_http_namespace stored procedure. See the SQL Server Books Online topic "Reserving an HTTP Namespace" for more information. |
The HTTP Endpoint defined above was given the MyServices identifier, and it is ready to start receiving requests as soon as the code above is executed because the code sets the STATE property to STARTED. Other possible values for the STATE property are STOPPED and DISABLED. The code also sets the AUTHENTICATION property to INTEGRATED. This means that SQL Server will try to authenticate the calling application based on a Windows credential using the NTLM protocol or the Kerberos protocol. Note | If you receive an HTTP 403 Forbidden Access error when trying this example, it means that SQL Server is not able to authenticate the user identity you are using to call the Endpoint. This works differently depending on the operating system version you are using and how user security is configured. See the SQL Server Books Online topic "Specifying Non-Kerberos Authentication in Visual Studio Projects" for more information. |
Other authentication protocols, such as Basic authentication, send the password in clear text during the authentication process. When using Basic authentication, SQL Server 2005 requires the communication channel to be encrypted and secured by using Secure Sockets Layers (SSL). The PORTS setting can be set to CLEAR or SSL. CLEAR indicates that the communication channel will not use SSL. The PATH setting indicates the relative path that will be used to identify the Endpoint externally. The complete path that client applications will specify is: http://server_name/sql/myservices. For this example you can point Internet Explorer to http://localhost/sql/myservices?wsdl to examine the XML that is created. Important | When an Endpoint is created, only members of the sysadmin role and the owner of the Endpoint can connect to the Endpoint. You must grant connect permission for users to access your Endpoint. This is accomplished by executing the following statement: GRANT CONNECT ON HTTP ENDPOINT::[EndPoint_Name] TO [Domain\User_Account]. |
After configuring the Endpoint itself, the second part of the CREATE ENDPOINT DDL statement configures the stored procedures and user-defined functions that are going to be exposed as a service. Database administrators can declare as many WEBMETHODs as needed. Each WEBMETHOD configures one service mapped to a single stored procedure or user-defined function. The exposed service in this case is identified by the alias 'http://tempuri.org/ '.'SalesHeadersList'. This WEBMETHOD is mapped to the stored procedure specified in the NAME setting. The FORMAT setting configures the type of information being returned by SQL Server. ROWSETS_ONLY indicates that only the results should be returned. A client application can then use an ADO.NET DataSet object to receive the data. The WSDL setting indicates that the WSDL contract must be generated automatically by SQL Server 2005. Note | There are many important configuration options to set when creating HTTP Endpoints. See the SQL Server Books Online topic "CREATE ENDPOINT (Transact-SQL)" for more information. |
Creating a Reference from the Client Application to the HTTP Endpoint Client applications communicating through the HTTP Endpoint can only send requests that adhere to the WSDL contract. The WSDL contract is created dynamically by SQL Server by combining the metadata of all the exposed WEBMETHODs and formatting it as a valid WSDL contract. Client applications developed using the Microsoft Visual Studio 2005 development environment can easily create a Web reference to the HTTP Endpoint. To create a Web reference to a SQL Server HTTP Endpoint, follow the procedure below. (This project is included in the sample files in the ConsumeHTTPEndpoint folder.) Creating a Web Reference to an HTTP Endpoint 1. | From the Start menu, select All Programs | Microsoft Visual Studio 2005 | Microsoft Visual Studio 2005.
| 2. | From the File menu, select New | Project. Select Visual Basic in the Project Types pane, and select Windows Application in the Templates pane. Give your project a name and specify a location for it. Click the OK button to create the project.
| 3. | If it is not shown, display the Toolbox by selecting Toolbox from the View menu. Drag and drop a DataGridView control from the Toolbox to the Form1 form in Design view.
| 4. | Click the small arrow in the upper-right corner of the DataGridView control to display the DataGridView Tasks smart tag. On the smart tag, uncheck the Enable Adding, Enable Editing and Enable Deleting checkboxes.
| 5. | Right-click on the project in the Solution Explorer window and choose Add Web Reference from the context menu.
| 6. | In the Add Web Reference window, type the following URL:
http://localhost/sql/myservices?wsdl
| 7. | Click the Go button, and then click the Add Reference button.
| 8. | Double-click the form in the designer and add the following code to the Form1_Load event handler.
Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load Dim ws As New localhost.MyServices ws.Credentials = System.Net.CredentialCache.DefaultCredentials Dim headers As New DataSet headers = ws.SalesHeadersList () DataGridView1.DataSource = headers.Tables(0) DataGridView1.AutoGenerateColumns = True End Sub
| 9. | Switch to the Form1.vb[Design] tab and press F4 to open the Properties window. Select the DataGridView control in the properties window and set the Dock property to Fill by clicking the central panel in the dropdown display.
| 10. | Press the F5 key to build and run the application.
| 11. | The form loads and the DataGridView control shows the list of all the sales order headers retrieved from SQL Server.
|
Important | Microsoft strongly recommends that you use SQL Server behind a firewall, even when connecting through HTTP Endpoints. |
HTTP Endpoints allow you to integrate SQL Server 2005 into a service-oriented architecture. There are some drawbacks to using HTTP Endpoints: Because no Web server is used, the scalability of this solution is limited. Not many organizations would want to expose SQL Server directly to the Internet.
You can overcome these drawbacks by providing access to SQL Server through an extra layer that keeps your SQL Server installation protected. | 