Step By Step: How to use SQLPackage with Access Token

[Yochanan_Rachamim]

Purpose: 

SQLPackage allows you to authenticate with Access Token instead of providing Login name and password.

This article will show you how to do that end to end. 

General steps:

1. Create App Registration in your Azure Active Directory (AAD)
2. Create user for the Application to access Azure SQL DB and grant the needed permissions. 
3. Generate Access token for your Application. 
4. Use the Access token to import or export your database.

Detailed steps: 

1. Create App Registration in your Azure Active Directory (AAD)
1. Open Azure portal and access you Azure Active Directory management blade
2. Click on App Registrations
3. Click on New Registration
4. Give your application a name so it can be identified afterwards
5. Click on “Register”
6. Once the App is created you will be redirected to the App blade
7. Note your application (client) ID – you will use that later
8. Click on “Certificate & Secrets”
9. Click on “New Client Secret”
10. Set the expiry time and click “Add”
11. Note the value of the key – we will use it later.
2. Create user for the Application to access Azure SQL DB and grant the needed permissions. 
   1. CREATE USER [SQLAccess] FROM EXTERNAL PROVIDER
   1. alter role dbmanager add member [SQLAccess]
1. Make sure your server has AAD Admin account configured.
2. Connect to you SQL DB with your AAD account
3. Create the user for the application access
4. Grant the needed permissions.
3. Generate Access token for your Application. 
1. Using PowerShell (ADAL.PS is required)

# Module MSAL.PS is needed
# to install it please run the following command
# Install-Module MSAL.PS

$key= ConvertTo-SecureString `
         -String "{Key Secret}" `
         -AsPlainText `
         -Force
       
Get-MsalToken `
    -Scope "https://database.windows.net/.default" `
    -ClientId "{Application ID}" `
    -ClientSecret $key `
    -TenantId "{Tenant ID}"

1. Using C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.IdentityModel.Clients.ActiveDirectory;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {

            string clientId = "{Client ID}";
            string aadTenantId = "{Tenant ID}";
            string clientSecretKey = "{Key Secret}";

            string AadInstance = "https://login.windows.net/{0}";

            string ResourceId = "https://database.windows.net/.default";

            AuthenticationContext authenticationContext = new AuthenticationContext(string.Format(AadInstance, aadTenantId));

            ClientCredential clientCredential = new ClientCredential(clientId, clientSecretKey);
            DateTime startTime = DateTime.Now;
            Console.WriteLine("Time " + String.Format("{0:mm:ss.fff}", startTime));
            AuthenticationResult authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result;
            DateTime endTime = DateTime.Now;
            Console.WriteLine("Got token at " + String.Format("{0:mm:ss.fff}", endTime));
            Console.WriteLine("Total time to get token in milliseconds " + (endTime - startTime).TotalMilliseconds);
            Console.WriteLine(authenticationResult.AccessToken.ToString());
            Console.ReadKey();
        }
    }
}

4. Use the Access token to import or export your database.

1. Use your SQLPackage command and instead of using Login / User and password use the /AccessToken:{AccessTokenHere} (or /at)

ChangeLog: 

2022-04-26: Changed Powershell module from ADAL to MSAL