rusanu.com

I have covered before how certificate based authentication works for endpoints. I think is only fair to have a similar article covering how services use certificates for authentication and security. Service Broker conversations are very different though from endpoints in the way they use certificates for authentication and security. Unfortunately there is no similar protocol I could reffer to, like it was the case with endpoints and TLS.

Service Broker conversations need authentication in order to allow or reject the sender of a message as being permitted to send messages to a destination service. The message sender in this context is the service that initiated the conversation. In Service Broker does not authenticate the user that sent a message (ie. the user logged in that issues the SEND or BEGIN CONVERSATION statement). Because Service Broker is designed as a mean to communicate between applications, users connected to an application are a local concept that cannot be used for authentication purposes remotely. For example when Joe from accounting logs in to the Accounting application and this application has some data from the Inventory application, this later application will authorize the Accounting application not Joe. Perhaps the Inventory application doesn’t even have Joe’s credentials, and no one wants to add additional tight coupling between the applications by having them be aware of each other user base just to be able to communicate.

Read the rest of this entry »

In my previous post I have explained how Database Mirroring and Service Broker use certificates for endpoint authentication. The only thing validated by SSB/DBM on a certificate are the valid-from date and the expiration date. In fact, even if SSB would not validate these dates, the TLS protocol used underneath by SSB/DBM authentication mechanism would validate these dates. In practice the only one that matter is the expiration date since the valid-from date is usually valid from the moment the certificate was created. Although if you follow this blog you know that I have already talked about a problem that may appear with certificates not yet valid, see http://rusanu.com/2008/08/25/certificate-not-yet-valid.

If you have in care servers that use Certificate based Authentication for Database Mirroring or Service Broker endpoints you must be prepared to replace the certificates used when they expire. Neither DBM nor SSB will not interrupt an existing connection when the certificate used to authenticate expires, but when a new connection is attempted the expired certificate will prevent it from being established resulting and some difficult to diagnose and troubleshoot errors. Luckily the certificates about to expire ca be identified and replaced before they expire, with minimal impact on a production server.

Identifying Endpoints that use Certificates

To find out if the Database Mirroring or Service Broker endpoints are using certificates based authentication run the following queries:

select * from sys.database_mirroring_endpoints;
select * from sys.service_broker_endpoints;

The authentication used by the endpoint is described in the connection_auth_desc column. If it says CERTIFICATE then the endpoint is using certificates for authentication and you must next check the expiration date of the certificate used. To find out just which one is this certificate, check the certificate_id column and then check the sys.certificates metadata catalog to see if the certificate used by the endpoint is about to expire. The expiry_date column will show the certificate expiration date. If the expiration date is near then you need to prepare the certificate replacement.

Replacing Certificates used by Endpoints

The replacement of certificates that are near expiration can be a very smooth operation with no impact on production if done correctly. The procedure is identical for Database Mirroring and for Service Broker endpoints:

1. Create a new certificate.
2. Backup the newly created certificate.
3. Copy the certificate to all the peer servers.
4. Restore the certificate on each peer server in master database, under the same ownership as the old certificate.
5. Alter the endpoint to use the new certificate.
6. Drop the old certificate on each peer.
7. Drop the old certificate on the server hosting the endpoint being updated.

Because the certificates are deployed (exchanged) prior to changing endpoint’s certificate, this procedure ensures that the communication is only stopped for a very short brief during the ALTER statement at step 5. And since we’re deploying the certificates on the peer servers using the same owner as the previously used certificate, there is no need to create a new login and to grant CONNECT permission, the old login is reused and it’s existing permissions are just fine.

Read the rest of this entry »

Service Broker and Database Mirroring may use certificates for authenticating endpoints as an alternative to NTLM/Kerberos authentication. This alternative is actually the only possible one whenever the servers involved are members of unrelated domains (or aren’t even members of a domain) and the default Windows based authentication is not possible. For Service Broker this scenario is more of the norm rather the exception, while for Database Mirroring this is the exceptional case. To configure an endpoint to use certificates for authentication you must specify the CERTIFICATE keyword in the CREATE/ALTER ENDPOINT statement:

CREATE ENDPOINT [mirroring]
	STATE = STARTED
	AS TCP (LISTENER_PORT = 5022)
	FOR DATABASE_MIRRORING (
		AUTHENTICATION = CERTIFICATE [MyCertName],
		ROLE = PARTNER);

‘Certificate based authentication’ for Service Broker and Database Mirroring sounds esoteric, yet is really nothing else but a variation of the SSL protocol used to authenticate web sites. To be strict, SQL Server will use TLS not SSL.

SSL and TLS provide a secure way to transmit a certificate from the server to the client and to establish a common secret later used to encrypt and sign traffic. How this is achieved is perhaps out of the scope of a database development oriented discussion, but if you really want to know the gory details MSDN documents the process in the SChannel SSPI reference:

1. Client calls InitializeSecurityContext and sends to the server the output buffer(s).
2. The server calls AcquireCredentialsHandle. The pAuthData parameter contains an SCHANNEL_CRED structure that describes the certificate used by the server for authentication.
3. The server calls AcceptSecurityContext passing in the buffer(s) provided by the client. Any output buffer is sent back to the client.
4. The client receives the buffer(s) from the server and calls again InitializeSecurityContext passing in the buffer(s) from the server. If any output buffer results, it is sent to the server.
5. The server receives more buffer(s) from the client and calls again AcceptSecurityContext passing in the buffer(s) provided by the client. If any out buffer results, it is sent to the client.
6. Steps 4 and 5 are repeated until no more output buffers are produced.
7. The client calls QueryContextAttributes on the resulted security context and asks for the SECPKG_ATTR_REMOTE_CERT_CONTEXT attribute. With this call the client has obtained a copy of the certificate used by the server in step 2 to initiate the authentication process.
8. Further traffic between client and server can be encrypted using the EncryptMessage and DecryptMessage functions.

Read the rest of this entry »

I have previously talked here about the queue monitors and the role they play in launching activated procedures. If you recall, I’ve mentioned that the Queue Monitors will enter the NOTIFIED state after they launch a stored procedure and not launch again the procedure until the Queue Monitor notices that RECEIVE statements are being issued against the queue. In an older post I have also talked about how difficult is to get error handling right, and in particular cast and convention errors. This may seem a trivial problem but in the Service Broker programs it is actually a serious problem because of the frequent conversation to and from XML. These two separate issues can actually ‘cooperate’ into a somehow surprising behavior. Namely if the activated procedure hits an error before it completes the RECEIVE statement, the Queue Monitor will stay in NOTIFIED state and won’t activate again the procedure. Although this looks similar to the typical poison message behavior when the queue is automatically disabled, this is a different issue. And unlike the poison message case, in the case when the Queue Monitor is stranded in NOTIFIED state you can not get a notification that the queue is no longer functional.

This post continues with an example showing how a relatively safe activated procedure can end up in this situation.

Read the rest of this entry »

A major class of Service Broker applications have nothing to do with the distributed application capabilities SSB was designed for. Instead they leverage SSB’s capability to run a stored procedure outside the context of the user connection. This capability enables to schedule execution and don’t wait for it to finish, or to schedule multiple procedures to execute in paralel. Often the developers that use these capabilities don’t care for the reliable message delivery SSB offers and many see the complex infrastructure of message types, contracts and services as a mere hurdle. Nonetheless sooner or later they have to troubleshoot an issue and then is when many of you find my blog and my articles. Troubleshooting activated procedure is not difficult, but one simply has to know where to look and what to look for.

In this entry I want to shed some light in the soul that drives the activation process: the Queue Monitors.

Read the rest of this entry »