rusanu.com

The error 833 is usually associated with hardware or system driver problems and the typical recommendation is to replace the hardware or update the drivers and firmware used. However there is a common scenario that leads to this problem when your hardware is fine and sound.

Read the rest of this entry »

You may have noticed that the post comments on the site are no longer displayed. Unfortunately I could no longer manage the amount of spam post I had to go through every day so for the moment I removed completely the possibility to post comments. I apologize for the inconvenience but right now I’m going to have to ask you to use the contact form if you wish to give feedback on an article. Your old comments are still in the site database and I will look for a way to post them back on the blog articles.

And since we’re on the subject of site changes, I have added some time ago a star ratting system for you to rate posts and a content page at http://rusanu.com/articles.

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 »