Event ID 833: I/O requests taking longer than 15 seconds

October 28th, 2008

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 »

Replacing Endpoint Certificates that are near expiration

October 25th, 2008

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.

Read the rest of this entry »

How does Certificate based Authentication work

October 23rd, 2008

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 »