This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
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:
- Create App Registration in your Azure Active Directory (AAD)
- Create user for the Application to access Azure SQL DB and grant the needed permissions.
- Generate Access token for your Application.
- Use the Access token to import or export your database.
Detailed steps:
- Create App Registration in your Azure Active Directory (AAD)
- Open Azure portal and access you Azure Active Directory management blade
- Click on App Registrations
- Click on New Registration
- Give your application a name so it can be identified afterwards
- Click on “Register”
- Once the App is created you will be redirected to the App blade
- Note your application (client) ID – you will use that later
- Click on “Endpoints” at the top and note the “OAuth 2.0 token endpoint (v2)” url – we will use this later as well.
- Click on “Certificate & Secrets”
- Click on “New Client Secret”
- Set the expiry time and click “Add”
- Note the value of the key – we will use it later.
- Create user for the Application to access Azure SQL DB and grant the needed permissions.
- CREATE USER [SQLAccess] FROM EXTERNAL PROVIDER
- alter role dbmanager add member [SQLAccess]
- Make sure your server has AAD Admin account configured.
- Connect to you SQL DB with your AAD account
- Create the user for the application access
- Grant the needed permissions.
- Generate Access token for your Application.
- Using PowerShell
$key= ConvertTo-SecureString ` -String "{Key Secret}" ` -AsPlainText ` -Force Get-AdalToken ` -Resource "https://database.windows.net/" ` -ClientId "{Application ID}" ` -ClientSecret $key ` -TenantId "{Tenant ID}"
- 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/"; 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.
- Use your SQLPackage command and instead of using Login / User and password use the /AccessToken:{AccessTokenHere} (or /at)