Recently, we got a request from a customer to let them know what are the server certificates that are sent by Azure SQL DB and SQL MI when a connection is created either with SQL or Azure Active Directory (AAD) authentication.
At the time of writing, SQL DB and SQL MI have the below root certificates
- Baltimore CyberTrust Root
- DigiCert Global Root G2
Please check this article to see if there are any SQL root certificate changes in the future.
When you attempt to connect to SQL DB or SQL MI using AAD authentication, SQL is no longer responsible for validating the identity of the client/application that is trying to sign in.
So, when the client application contacts AAD to establish the authentication process, AAD will be sending a root certificate to help establish a TLS session.
The AAD root certificates at the time of writing are the following:
- Baltimore CyberTrust Root
- DigiCert Global Root G2
- DigiCert Global Root CA
Please check this article to see if there are any AAD root certificate changes in the future.
This should be all the certificates that you will need to connect to Azure SQL DB/MI using either SQL authentication or AAD authentication
How can I check the certificate that is sent by the server?
We have a very useful PowerShell script that tests connectivity to Azure SQL DB or SQL MI, when you inspect the log file of the connection test you will see that this script prints out the name of the server certificate that was sent by the database service.
The Advanced connectivity policy test will print out the name and expiration date of the certificate.
There are many tools that you can use. For example, DigiCert has a handy tool that will show you the certificate chain of any server name. (This tool will only work with Azure SQL Server; SQL MI will not work as it cannot connect to the MI as it is contained in a VNET)
Just put the FQDN of your server and click on “Check Server”.
As you can see above the tool shows us the server and intermediate certificate details.
Another tool you can use is OpenSSL in the command line, you can use the syntax below
openssl s_client -showcerts -connect <your-sql-server-name>:443
For validating the AAD Password and Integrated Authentication certificates you can use the following endpoint
For Universal with MFA authentication:
https://login.microsoftonline.com:443
https://secure.aadcdn.microsoftonline-p.com:443
How can I ensure that the application correctly verifies the root certificate?
Most of the client drivers will use the OS certificate store to validate the certificates. If you keep your OS regularly updated, you shouldn’t need to import the certificate into the store.
However, some client drivers most commonly Java based use a local file as the certificate store instead of using the one on the OS.
Our customer asked how they can verify their certificate store has the certificates required to support the connection.
The following example is using Java, be sure to check your client driver documentation. In Java you will have a file “cacerts” and that one is the default local file store for root certificate authorities.
Using the keytool utility that comes bundled with Java we can list the certificates
keytool -list -v -cacerts
And using the following one liner in bash or PowerShell you can search if the store has the required certificate authorities
Bash
keytool -list -v -cacerts | grep -i -P "Issuer: CN=(DigiCert Global.+(?:CA|G2)|Baltimore.+), OU"
PowerShell
keytool -list -v -cacerts | sls "Issuer: CN=(DigiCert Global.+(?:CA|G2)|Baltimore.+), OU"
Another nifty tool that you can use is either Fiddler Classic (Windows only / freeware) or Fiddler Everywhere (cross platform / subscription based)
I created a simple Java application and was able to use Fiddler to check the certificate chain.
package com.example;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
public class App
{
public static void main(String[] args) throws Exception{
//Comment next 4 lines to stop using Fiddler as a proxy
System.setProperty("http.proxyHost", "127.0.0.1");
System.setProperty("https.proxyHost", "127.0.0.1");
System.setProperty("http.proxyPort", "8866");
System.setProperty("https.proxyPort", "8866");
SQLServerDataSource ds = new SQLServerDataSource();
ds.setServerName("<your-server>.database.windows.net"); // Replace with your server name
ds.setDatabaseName("<your-database>"); // Replace with your database
// ds.setUser("<your-user>"); // Replace with your user name and uncomment to use SQL authentication
ds.setUser("<your-AAD-user>"); // Replace with your AAD UPN
ds.setPassword("<your-password>"); // Replace with your password
ds.setAuthentication("ActiveDirectoryPassword"); //Comment if want to use SQL authentication
try (Connection connection = ds.getConnection();
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT SUSER_SNAME()")) {
if (rs.next()) {
System.out.println("You have successfully logged on as: " + rs.getString(1));
}
}
}
}
Checking the certificate chain with Fiddler
Double click on “certificate valid” to inspect the certificate chain.
With the above steps you should be able to validate that your application has been correctly configured to support a connection to Azure SQL DB/MI.
A previous blogpost explains what would happen if the java certificate store is incorrectly configured and also goes about how to fix it.
Cheers!
Microsoft
