So you’re moving to (or starting with… good for you!) Azure Active Directory for your connection to an Azure SQL database. I just did the same thing, and… it wasn’t the most straight-forward process. In my scenario, I was using an Azure Data Factory to copy data from another database into my Azure SQL db.
Using an app (or user/identity) to connect to some Azure resource is usually all about making sure it has permissions on the resource itself. My first idea was that I’d head over to my SQL Server resource and add role permissions for my app. But this isn’t strictly necessary. Largely, you just need to set up permissions within the database itself, and then worry about setting up your app’s AAD connection via the means you want to connect (e.g. via ADF, you’ll need to create an Azure SQL AAD connection, but that’s specific to where/how you’re authenticating your app).
To get your AAD app the proper permissions it needs, there are two things to do:
- Add your app as a user on the database
- Grant your app the corresponding SQL permissions as needed
Adding your AAD App as a User in the Database
- On your SQL database page, select the Query Editor tab on the left panel
2. Sign in to your database
3. In query editor, run the following command, replacing <your-app-name> with the name of your AAD app.
CREATE USER [<your-app-name>] FROM EXTERNAL PROVIDER;
4. Your AAD app is now a recognized user in the database.
Granting permissions to your App
- In the query editor, you can assign permissions as needed for your app using this structure (SQL permissions can be found here)
GRANT <permission> ON [dbo].[<your-table-name>] TO [<your-app-name>];For example, if I wanted my app (JW-Motors-App) to have permissions to INSERT into my Sales table.
That’s it. Moving forward, you can just modify permissions in SQL fashion for your AAD app.
