On the Lambda

Programming, Technology, and Systems Administration

On the Lambda

Certificate Authentication in SQL Server

November 23rd, 2020 · No Comments · development, networking, security, sql, Sql Server

I sometimes see questions asking how to do certificate authentication in SQL Server.

If you’re not aware, certificate authentication for databases works by issuing a certificate that can be trusted by the database server. The certificate might be signed by public certificate authority or by some other entity trusted by the database, and is associated with a specific user in the database. When a certificate is presented to the database, it can know both that the certificate is valid, and what user it’s dealing with.

Certificate authentication has a number of benefits over legacy passwords, including automating credential rotation, support for multiple credentials per user isolated by application or device, revocation, and more.

Contrary to what you may have heard, SQL Server does indeed have support for certificate authentication. It just looks a little different from how some other database products do it. Instead, SQL Server’s equivalent option is the Integrated Security feature.

It works like this:

When you use integrated security across machines, where the server lives on a different computer from the client, you also rely on an Active Directory domain to mediate trust between the machines. A user logs in to a client computer joined to an Active Directory domain. As part of the authentication process, a domain controller (DC) from the domain will issue an authentication token for the session.

The user then wants to login to SQL Server using integrated security, where the SQL Server installation is a member of the same domain. To do this, the token is passed to SQL Server, which in turn validates the token against a DC. Now SQL Server can trust the user is who they say they are. From there it consults it’s own user information to decide whether or what access to grant.

The integrity of this process is guaranteed using – you guessed it – certificates. Also a clock (thanks Kerberos).

The weakness here is it limits authentication to devices with Active Directory membership. However, it otherwise grants the same security benefits as more-traditional certificate authentication, as well as the additional benefit of automating certificate transport and issuance in a secure way.


No Comments so far ↓

There are no comments yet...Kick things off by filling out the form below.

Leave a Comment