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
- Using C#
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)