Certificate Based Authentication for Azure SQL Database

This article will help you to understand how certificate-based authentication works for Azure SQL databases. 

The following steps are performed to test certificate-based Authentication.

 

1. Register the APP in Azure AD.
2. Create Azure DB user in Azure SQL Database.
3. Generate a self-signed certificate.
4. Add a certificate to the App created in step 1.
5. Create Console Application with .Net framework to authenticate via certificate. (Attached Solution is for reference.)
6. Run Console Application to verify the connection.

 

1. Register the APP in Azure AD. 

 

• Login to Azure portal --> Go to Azure Active Directory --> App Registration --> New Registration

 

• Once the app is created, please copy the Client ID.

 

 

2. Create Azure DB user in Azure SQL Database.

 

• Connect to Azure SQL database using AAD Authentication and create a user for the app.
• CREATE USER [APPNAME] FROM EXTERNAL PROVIDER

 

3) Generate a self-signed certificate.

 

• We will need to make a use of makecert.exe utilities for generating certificate, which is included in the Windows SDK and can be downloaded using the below link.  http://msdn.microsoft.com/en-US/windows/desktop/aa904949
•  Created directory to generate the certificate.
•  Open Command Prompt and go the path created to generate certificate and execute the following command (Note:- update the necessary parameters as applicable)

c:/"Program Files (x86)\Windows Kits\10\bin\10.0.22000.0\x64"/makecert -r -pe -n "CN=mytokentestCert1" -ss My -len 2048 mytokentestCert1.cer

 

 

4) Add a certificate to the App created in step-1. or can be done via Azure portal.

 

Connect-AzAccount -Tenant $TenantId 

$cer = New-Object System.Security.Cryptography.X509Certificates.X509Certificate2 #create a new certificate object

$cer.Import("Certificatelocation")  # use the local directory that contains the cert created in @3.

$bin = $cer.GetRawCertData()

$base64Value = [System.Convert]::ToBase64String($bin)

$bin = $cer.GetCertHash()

$base64Thumbprint = [System.Convert]::ToBase64String($bin)

$keyid = [System.Guid]::NewGuid().ToString()

# Ensure to use the right ObjectID for the application.

Connect-AzureAD -Tenant $TenantId 

New-AzureADApplicationKeyCredential -ObjectId 96f2002a-887a-4fb1-a513-59d34c92cc04 -CustomKeyIdentifier $base64Thumbprint -Type AsymmetricX509Cert -Usage Verify -Value $base64Value

 

 

5) Create Console Application with .Net framework to authenticate via certificate.

 

• Download and install visual studio to build console app.
• Create new project.

 

• Update the app.config file content.

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

<startup>

<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />

</startup>

<appSettings>

<add key="ida:AADInstance" value="https://login.windows.net/{0}"/>

<add key="ida:Tenant" value="tenantID" />  <!--* update tenantID here */-->

<add key="ida:ClientId" value="AppClientID"/> <!--* update ClientID here */-->

<add key="ida:CertName" value="CN=mytokentestCert"/> <!--* update CertificateName */-->

<add key="sqldb:ResourceId" value="https://database.windows.net/"/>

</appSettings>

</configuration>

 

 

• Update the below content in program.cs 

  static void Main()

        {

            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();

            builder["Data Source"] = "AzureSQL.database.windows.net";//your Azure SQL server

            builder["Initial Catalog"] = "DatabaseName"; // Update Database

            builder["Connect Timeout"] = 120;

 

• Once the changes are made to the solution. Build the solution and execute.

 

6) Run Console Application to verify the connection.