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.
- Register the APP in Azure AD.
- Create Azure DB user in Azure SQL Database.
- Generate a self-signed certificate.
- Add a certificate to the App created in step 1.
- Create Console Application with .Net framework to authenticate via certificate. (Attached Solution is for reference.)
- Run Console Application to verify the connection.
- 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.
