How does Certificate based Authentication work

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.

The actual content of those ‘black box’ buffers exchanged by the client and server during authentication is described in RFC 2246. Or if you prefer a more digestible read go for Eric Rescorla’s excellent book on the topic SSL and TLS. And as a side note, the ‘Windows’ authentication is identical but uses the SPNego, NTLM or Kerberos functions instead of the SChannel ones.

The SSL/TLS protocol in itself does not provide any authentication, it only provides the client with the certificate used by the server and the client is supposed to use this certificate to authenticate the server. In SSL this authentication is done by checking a certificate property and comparing it with the web site address. If they match, and the certificate is signed by an authority trusted by the client, the client can safely conclude that the server really is the web site it desired to connect to, so in effect the client has authenticated the server.

With SQL Server Service Broker and Database Mirroring authentication things are a bit different. After the TLS protocol has provided the ‘client’ with the certificate used by the ‘server’ (the one specified in the CREATE/ALTER ENDPOINT statement) the client will search this certificate in the master database. If is found, then client has successfully authenticated the server: it will use the identity of the certificate owner as the identity of the peer server. This identity is then authorized by the client by checking the CONNECT permission on the SSB/DBM endpoint. As you can see from my description, even though the protocol used is the same or similar, the actual authentication mechanism used for authentication is very different between the typical SSL web site authentication and SQL Server SSB/DBM one. In SQL Server the authentication is based on the physical deployment of the certificate into the master database of the peer. That is, if the certificate used by the ‘server’ is found in the ‘client’ master database, then the ‘server’ is authenticated.

As you can see, there isn’t any requirement on the certificate used for authentication by SSB/DBM. Unlike the SSL web site case, there is no property validated to match the ‘server’ name, nor is there any check done that the certificate is signed by a trusted authority. I know a number of people I talked with were surprised by the later. But there simply isn’t a need to verify the certificate’s chain of signatures against a trusted authority, because in SSB/DBM case the certificate is deployed upfront and hence it can be trusted because the administrator doing the deployment was trusted. So the only requirement on the certificate is not be expired. Also in SSB/DBM a self-signed certificate offers the same level of protection and security as a certificate signed by a trusted authority. Please note that this applies only to SSB and DBM endpoints and not to normal T-SQL endpoints that are exposed to man-in-the-middle attacks when using self-signed certificates to encrypt the T-SQL traffic. And since we’re talking about the T-SQL endpoints use of certificates, note that they do not use the certificates for clientauthentication, but instead they are used to authenticate the server and to establish an encrypted channel of communication. The client is authenticated via name and password (for SQL Logins) or using NTLM/Kerberos (for Windows Logins).

We have mentioned so far several times ‘client’ and ‘server’ but SSB and DBM do not have these roles, both participants are considered ‘peers’ in a SSB/DBM connection. Hence when establishing a connection the authentication is literally done twice, once in each direction, thus enabling mutual authentication between the two peers that participate in the connection.

Anonymous authentication

For Service Broker the authentication has to support the scenario when a service is exposed for public access and it allows anybody to connect. Obviously, it would be impossible to physically deploy the certificate used by the peer in such scenario because the list of peers is not known upfront. For such a scenario it is possible to grant CONNECT permission on the Service Broker endpoint to the public role. After the TLS protocol has presented to the ‘client’ the certificate used by the ‘server’, if the ‘client’ cannot find this certificate in master it will check if public is granted CONNECT permission on the endpoint. If that is true, then the connection is authorized. To enable this scenario, simply grant CONNECT permission to public:

GRANT CONNECT ON ENDPOINT::[broker] TO [public];

For Database Mirroring this scenario is not allowed, since there is no realistic need to establish a mirroring session with a peer that is not known upfront.