Best Practices for Using Native XML Web Services

Article
12/03/2008

This topic presents some best practices to consider when you plan and evaluate Native XML Web Services in SQL Server 2005 for use in your business solutions. These recommendations are intended to help you in the following ways:

Help secure your installation of SQL Server when you use Native XML Web Services in SQL Server 2005.
Help improve the performance of your installation of SQL Server by offering usage guidelines. These guidelines can help you decide on whether your application is effectively served by using Native XML Web Services in SQL Server 2005.

Security Best Practices

Consider the following security best practice recommendations when you deploy Native XML Web Services in SQL Server 2005:

Use Kerberos authentication.
Limit endpoint connect permissions to specific users or groups.
Use Secure Socket Layer to exchange sensitive data.
Use SQL Server behind a firewall.
Verify the Windows Guest account is disabled on the server.
Control and update endpoint state as needed.
Use secure endpoint defaults whenever possible.

Use Kerberos Authentication

When you use CREATE ENDPOINT to create endpoints, select either AUTHENTICATION=KERBEROS or AUTHENTICATION = INTEGRATED to enable Windows integrated security under Kerberos to be used as the type of authentication used on an endpoint. The first option, will allow only Kerberos as the mode of authentication for the endpoint. The second option allows the endpoint to support both NTLM and Kerberos authentication.

For authentication, the Kerberos protocol provides improved security by using built-in features such as mutual authentication. This means the identity of both servers and clients are authenticated.

When you are using Kerberos authentication, SQL Server must associate a Service Principal Name (SPN) with the account it will be running on. For more information, see Registering Kerberos Service Principal Names by Using Http.sys.

Limit Endpoint Connect Permissions to Specific Users or Groups

After you have created the endpoints that are required for your deployment, secure them by setting endpoint connection permissions by using Transact-SQL statements, such as GRANT CONNECT and ALTER ON ENDPOINT. When you assign connection permissions, be specific and grant permission only to the specific users or a specific group that is reserved for endpoint access to SQL Server.

Generally, you should limit permissions to only allow individual users to connect to endpoints. Granting access to the public role is not recommended. Instead, we recommend that you fully understand the permissions model for SQL Server. You can use this model to judiciously control endpoint access.

Important

The public role is a special database role to which every SQL Server user belongs. This role contains default access permissions for any user that can access the database. Because this database role is a built-in default role of SQL Server and serves as a way to grant access to all users (similar to Everyone or Authenticated Users in Windows permissions), it should be used with caution when you configure permissions on SQL Server.

For more information, see GRANT Endpoint Permissions (Transact-SQL).

Use Secure Sockets Layer to Exchange Sensitive Data

The Secure Sockets Layer (SSL) protocol provides support for both encryption and decryption of data over a secure TCP/IP socket (IP address and TCP port number combination) interface. For SQL Server endpoints to provide SSL encryption, you must first configure a certificate.

When you register a certificate for the default SSL port of 443, consider that the same certificate might also be shared by other applications. For example, Internet Information Services (IIS) may be hosting SSL traffic on the same port, in which case this configuration may affect IIS users. There could be security implications because of this sharing of the SSL port and its certificates.

For more information, see Configuring Certificate for Use by SSL.

Use SQL Server Behind a Firewall

For the best possible security, you should only use Native XML Web Services in SQL Server 2005 behind a firewall. Make sure that when you set up endpoints that any TCP port numbers you use to provide HTTP access are protected by the firewall. Allowing an installation of SQL Server to be directly accessed by Internet clients and not protected by a firewall is not a recommended network configuration. For more information, see Security Considerations for a SQL Server Installation.

Verify the Windows Guest Account Is Disabled on the Server

The Guest account is a built-in user account provided in most versions of Microsoft Windows. In Windows Server 2003, it is disabled by default. For Windows 2000 Server and Windows NT Server 4.0, it is enabled by default.

To reduce the risk of surface attacks when endpoints are being used, you should make sure that the Guest account is disabled on the server on which SQL Server is running. For more information, see the topic "To disable or activate a local user account" in Windows Help.

Control and Update Endpoint State As Needed

When you create an endpoint by using CREATE ENDPOINT, the default state is stopped, unless you explicitly start it by specifying STATE = STARTED. To control the state of an existing endpoint, use ALTER ENDPOINT to specify STOPPED, STARTED, or DISABLED.

For example, use the following statements to start or stop the previously created endpoint sql_endpoint:

ALTER ENDPOINT sql_endpoint STATE=STARTED

ALTER ENDPOINT sql_endpoint STATE=STOPPED

You should also disable endpoints or drop specific Web methods on an endpoint, or the endpoint, if you have no foreseeable use for them.

The following example shows disabling an endpoint:

ALTER ENDPOINT sql_endpoint STATE=DISABLED

Note

After an endpoint is disabled, it cannot be restarted until the SQL Server service (MSSQLServer) is restarted.

To drop a Web method from an endpoint, you would use a statement similar to the following format:

ALTER ENDPOINT sql_endpoint

FOR SOAP

(

DROP WEBMETHOD 'SayHello'

)

To drop an endpoint, use DROP ENDPOINT.

Use Secure Endpoint Defaults Whenever Possible

When you create or modify an endpoint by using CREATE ENDPOINT or ALTER ENDPOINT, the following option defaults are applied, unless you explicitly set otherwise:

BATCHES = DISABLED
Transact-SQL batch mode is disabled for the endpoint.
LOGIN_TYPE = WINDOWS
Only Windows authentication is permitted for endpoint users.

Unless you must modify these settings to support access or functionality that you intend and require in your application deployment, we recommend that you use the defaults for these options whenever possible.

For information on choosing an encryption algorithm, see Choosing an Encryption Algorithm.

Performance Best Practices

Consider the following performance best practice recommendations when you deploy Native XML Web Services in SQL Server 2005:

Deploy in appropriate scenarios.
Factor in additional server resources when planning SOAP-based solutions.
Configure the appropriate WSDL option for your requirements.

Deploy Appropriate Scenarios

Native XML Web Services in SQL Server 2005 is best suited to scenarios with the following requirements:

Your application returns or consumes XML data.
Your application relies heavily on stored procedures for business logic.
As part of your business solution, you want to integrate a SQL Server-hosted Web service application with other Web service applications to achieve the goals of a Service Oriented Architecture (SOA).
You are looking for a better performing replacement for the SQLXML mid-tier solution for deploying Web services together on the same server.
You want to produce and publish a static report for an intranet Web site where the rich feature set and additional overhead of SQL Server 2005 Reporting Services (SSRS) might exceed your requirements.

Similarly, in scenarios with the following requirements, we do not recommend using Native XML Web Services in SQL Server 2005:

Your application is used to insert or retrieve binary large object (BLOB) data, such as large binaryimage, or text values.
Your application requires real-time transaction processing and mission-critical response times.
You are using SQL Server in combination with other processing-intensive applications such as TPC Benchmark C (TPC-C) applications.

Factor in Additional Server Resources When Planning SOAP-based Solutions

For capacity planning purposes, note that unlike the Tabular Data Stream (TDS) protocol, SOAP performance varies by application and can require additional server resource overhead. For example, in preliminary testing of SQL Server 2005 performed by the SQL Server product team, in scenarios in which large XML documents were returned, the performance for SOAP-based access took 20 to 30 percent longer than TDS-based access.

Configure the Appropriate WSDL Option for Your Requirements

Before deploying Native XML Web Services in SQL Server 2005, you should determine the appropriate WSDL option to use in supporting all clients and operating systems that are required for your solution.

For maximum interoperability within heterogeneous environments in which Web service clients include operating systems other than Windows, use the simple WSDL. For Windows-only environments in which you are developing with Microsoft Visual Studio 2005, you can use the default WSDL to access the rich type support included in Visual Studio 2005.

Sometimes, third-party SOAP clients might require a customized WSDL for interoperability. For more information, see Implementing Custom WSDL Support.

Share via