Generating an Access Token for Azure SQL Database Using Service Principal and Client Secret using PowerShell

Introduction:

In this blog post, we will explore how to generate an access token for Azure SQL Database using a service principal with client secret. Access tokens are required to authenticate and authorize access to various Azure services, including Azure SQL Database. We will be using PowerShell to perform this task and walk through the steps in a well-defined manner.

Prerequisites:

Before we get started, ensure that you have the following prerequisites in place:

1. Azure subscription with appropriate permissions to create and manage service principals.

2. PowerShell installed on your machine.

Step 1: Creating Azure Service Principal and assigning required permission to it

I have already written a blog explaining how to execute SQL scripts in azure pipelines using Service principal and client secret. In that blog, I have explained how to create the Service Principal, how to generate client secret and how to assign permission to the same. Please go here and create service principal, generate secret for it, and assign necessary permissions to it.

I will be using the same service principal for this blog.

Step 2: Creating a PowerShell code for generating an access token

1. Create a new PowerShell file.

2. Add below code to it and run the file, you will get the access token.
   

$TenantID = ‘<TenantId>’
$clientId = '<ClientID>’
$clientSecret = ‘<ClientSecret>’
$resourceAppIdURI = 'https://database.windows.net/'
$sqlServerFQN = ‘YourDBServer.database.windows.net'
$sqlDatabaseName = 'DemoDB'

$tokenResponse = Invoke-RestMethod -Method Post -UseBasicParsing `
  -Uri "https://login.windows.net/$($TenantID)/oauth2/token" `
  -Body @{
         resource=$resourceAppIdURI
         client_id=$clientId
         grant_type='client_credentials'
         client_secret=$clientSecret
  } -ContentType 'application/x-www-form-urlencoded'

if ($tokenResponse) {
   Write-debug "Access token type is $($tokenResponse.token_type), expires $($tokenResponse.expires_on)"
   Write-Host "##vso[task.setvariable variable=Token;]$tokenResponse.access_token"
   $Token = $tokenResponse.access_token
}

Let’s try to understand the code:

• $tokenResponse = Invoke-RestMethod -Method Post -UseBasicParsing : This line makes an HTTP POST request using the Invoke-RestMethod cmdlet. It will send a request to obtain an access token from the Azure AD token endpoint.
• -Uri “https://login.windows.net/$($TenantID)/oauth2/token": This specifies the URI of the Azure AD token endpoint where the request will be sent. It includes the $TenantID variable to indicate the specific AAD tenant to authenticate against.
• -Body @{ … } -ContentType ‘application/x-www-form-urlencoded’: This parameter provides the request body for the HTTP POST request. It includes a hashtable (@{ … }) containing the required parameters for authentication: resource, client_id, grant_type, and client_secret. These parameters are necessary to authenticate the client application and obtain an access token.

Note: This method of generating the access code is not recommended because, we are writing the secret on the code, which is not safe.

Conclusion:

In this blog post, we have learned how to generate an access token for Azure SQL Database using a service principal with client secret in PowerShell. By following the step-by-step instructions, you can authenticate and obtain an access token, which can be used for various tasks, such as connecting to the Azure SQL Database programmatically or performing automated operations.