Share via

Reliably making a web-request from the database

  • Article

One of the prime use cases of the CLR integration in SQL Server 2005 is to make outbound HTTP requests to web applications or web services. But how do you do that reliably given the large number of failures that need to be handled ranging from network unavailability, server timeouts, 404 errors, 500 errors, etc. While some of these errors may be transient and we could retry the requests, some may necessitate giving up altogether. This seems like a lot of effort for a user, who just wants to perform a simple web-request.

One solution is to use Service Broker to add reliability. The user can simply begin a dialog and send the request over to a proxy service that handles the task of actually performing the web-request and sending the response back over the dialog. The service could perform all the magic of determining transient errors and retrying requests.

Starting with this idea, I built a sample web proxy service that can be downloaded from the SSB CodeGallery. The sample comprises of a Service Broker service called WebProxyService which exposes a request/response contract. The contract defines custom message types that marshall HTTP requests and responses into XML. Services like the WebClient service can initiate a dialog to the WebProxyService using this custom contract and send HTTP requests over the dialog.

The WebProxyService itself is implemented as an internally activated stored procedure that uses CLR. Its behavior is controlled by two tables – the RequestFilter table and the ResponseFilter table.

Each row in the RequestFilter table (i.e. a request rule) maps a class of incoming requests (based on method and/or URL pattern) to the action to be taken (deny or accept). If the request is to be accepted, additional columns indicate how the service is to behave in case of failures. The columns of the table are as follows:

RequestFilterID Identifier for the rule
Method HTTP method used in the request – GET, POST, etc
UrlPattern Regular expression to match URL with
Timeout Amount to wait for HTTP response from target server before failing and retrying
NumberOfRetries Number of times to try before giving up and failing the request
BackoffFactor Factor to multiple the Timeout with for each retry. (eg> if RetryDelay = 4 seconds and BackoffFactor is 1.5, we will retry after 4, 6, 9, etc seconds)
RetryDelay Number of seconds to wait before next retry
Action 0 indicates DENY, 1 indicates ACCEPT

The service maps incoming requests to rules in the RequestFilter. Exact matches (i.e. both Method and UrlPattern match) are preferred to partial matches. If no rules match or if the rule specifies ‘deny’ action, the service ends the conversation on which the request was received with an error declining the request. If there is a matching ‘accept’ rule, the service makes an outbound HTTP request to the Web server with the timeout specified in the rule. If a response is received, it is matched against the ResponseFilter table.

Each row in the ResponseFilter table maps a class of responses (based on the HTTP status code) to the action to be taken (respond, retry or error). The columns of the table are as follows:

ResponseFilterID Identifier for the rule
StatusCodeLower Lower bound of the status code interval
StatusCodeUpper Upper bound of the status code interval
Action 0 indicates RESPOND, 1 indicates RETRY and 2 indicates ERROR

If the matching rule indicates ‘respond’, the service marshalls the incoming HTTP response into the XML format and sends it as a message back on the conversation that had sent the request. If the matching rule indicates ‘error’, the service ends the conversation with an error that wraps the status code and message returned by the Web server. If the matching rule indicates ‘retry’, the service saves the original HTTP request to a table indexed by the conversation handle and begins a timer on that conversation with an appropriate interval (calculated as ‘RetryDelay X BackoffFactor^NumberOfRetries’).

When the timer fires (i.e. sends a dialog timer message to the service), the service loads the saved request from the table and tries it again after incrementing the number of retries. Once the request completes (i.e. either a valid response is returned or the number of retries reach maximum), the conversation is ended and the saved request is deleted from the table.

Security Consideration: SQL allows CLR threads to impersonate the caller (eg> the user that invokes a CLR stored procedure) in order to allow external access under the security context of that user instead of the security context of the service account running sqlservr.exe. However, in the context of internal activation, there is no concept of a caller. There may be no user interactively logged on to the server and hence there is no way to obtain the security context of any appropriate user. Hence CLR impersonation does not work in internally activated stored procedures. Our HTTP requests will have to be made in the security context of the service account running sqlservr.exe (eg> NETWORK SERVICE or LOCALSYSTEM). If the firewall does not allow the service account user to access the network (or the Internet) for making the HTTP requests, this is bound to fail.

Apart from firewall issues, it may not be such a good idea to use a backend database server for making HTTP requests. First, this requires putting the backend in the DMZ thus exposing it to security threats. Second, making a web-request uses up a database thread that could be doing potentially more important data access work. Hence the recommendation is to use a dedicated instance (even SQL express) to host the WebProxyService. This instance can be placed in the DMZ and it could be running using a Windows account with permission to penetrate the firewall.

Comments