SQL Server 2005 supports both the traditional, binary Tabulate Data Stream for client access to data as well as native XML Web service access using HTTP. The primary benefit of allowing access via HTTP is that any client software and development tools that understand Web service protocols can access data stored in SQL Server. This means SQL Server 2005 can provide standalone Web service methods as well as be a complete endpoint in a Service Oriented Architecture (SOA).
Using SQL Server 2005 as a Web service host requires two general steps, each with plenty of possible variations: defining stored procedures and user-defined functions that provide the Web service methods, and defining an HTTP endpoint that receives method calls via HTTP and routes them to the appropriate procedure.
Because XML Web services in SQL Server uses HTTP and, by default, port 80, most firewalls are configured to allow the traffic to pass. But an unprotected endpoint is a potential vector for attacks and must be secured, so SQL Server is designed with strong authentication and authorization. No endpoints are installed by default with SQL Server and you have to have a high level of permissions to create, alter, and enable HTTP endpoints.
SQL Server 2005 provides five different authentication types, similar to those used by IIS for Web site authentication.
• Basic authentication
Basic authentication is defined as part of the HTTP 1.1 protocol, which transmits the login credentials in clear text that is base-64 encoded. The credential must map to a Windows login, which is then used to authorize access to database resources. If you use Basic authentication, you cannot set the PORTS argument to CLEAR but must instead set it to SSL and use a digital certificate with SSL to encrypt the communication with the client software.
• Digest authentication
Digest authentication is also specified as part of the HTTP 1.1 protocol. It hashes the credentials with MD5 before sending to the server so that they are not sent across the wire, even in encrypted form. The credentials must map to a valid Windows domain account; you cannot use local user accounts.
• NTLM authentication
NTLM uses the challenge-response protocol originally introduced in Microsoft Windows NT® and supported in all client and server versions of Windows since. It provides secure authentication when both client and server are Windows systems, and requires a valid domain account.
• Kerberos authentication
Kerberos authentication is available with Windows 2000 and later, based on an industry-standard protocol available on many operation systems. It allows for mutual authentication in which both the client and server are reasonably assured of the other’s identity and provides a highly secure form of authentication. To use Kerberos on Windows Server 2003, you must register the Kerberos Service Principal Name (SPN) with Http.sys by using the SetSPN.exe utility that is part of the Windows Support Tools.
• Integrated authentication
Integrated authentication provides the best of NTLM and Kerberos authentication. The server uses whichever of the two authentication types the client requests, allowing the most secure authentication the client supports while making the service available to older versions of Windows. You can configure Http.sys in Windows 2003 to negotiate with the client which protocol to use.
The authentication method used for an endpoint is set with the AUTHENTICATION attribute of the CREATE or ALTER ENDPOINT statement. For example, the following code creates an endpoint that uses Kerberos for authentication:
CREATE ENDPOINT myEndpoint
STATE=STARTED
AS HTTP (PATH = '/JainHttpEndpoint',
AUTHENTICATION = (KERBEROS),
PORTS = (CLEAR),
SITE = 'JainSqlServer')
FOR SOAP (WSDL = DEFAULT,
DATABASE = 'Jain',
NAMESPACE = 'http://example.com/jainSqlServer/myDB/WebService')
SQL Server 2005 supports endpoints that listen both to HTTP as well as a user-defined port on TCP. You can also format requests using a variety of formats: SOAP, Transact-SQL, a format specific to Service Broker, and another used for database mirroring. When using SOAP you can take advantage of WS-Security headers to authenticate SQL Server logins.
Microsoft has implemented Web service endpoint authentication to support a wide variety of protocols and specifications, of which this paper has touched on just a few. You’ll need to explicitly enable your authentication option and ensure that clients are able to provide the type of credentials required.
