Azure Sql Managed Instance - Service Principal access issue

pdsqsql 411

We have configured Sql Managed Instance and currently configured MS Entra MFA but we can't use for application login/Service account which is prompting for MFA so we have created Service Principal and provided access after adding into Managed Sql Server Instance Database.

I am able to connect to the Instance/Database and can run SELECT but can't run DML statement (Insert/Update/Delete).

I have provided Read and Write access also, evne thogu I have added DB_Owner role permission but still getting error:

`The UPDATE permission was denied on the object 'order', database 'Ordertest', schema 'dbo'`

Appreciate your feedback!

Thank you in advance!

Mallaiah Sangi 85 Reputation points Microsoft Vendor

2025-02-27T22:09:22.7+00:00
Hi @pdsqsql

Thanks for the Question and using Microsoft Q&A

As per my understanding, your facing issues in DML operations in Azure SQL managed instance.

Granting DML (Data Manipulation Language) permissions in Azure SQL Managed Instance involves providing users or roles with the necessary permissions to perform operations like INSERT, UPDATE, DELETE, and SELECT on database tables. Here’s a step-by-step guide to help you through the process:

Step-by-Step Guide

Connect to Azure SQL Managed Instance: Use SQL Server Management Studio (SSMS) or any other SQL client to connect to your Azure SQL Managed Instance.

Select the Database: Ensure you are connected to the correct database where you want to grant permissions.

Grant Permissions: Use the GRANT statement to provide the necessary DML permissions. Here are some examples: Grant SELECT Permission: GRANT SELECT ON [schema_name].[table_name] TO [user_or_role]; Grant INSERT Permission: GRANT INSERT ON [schema_name].[table_name] TO [user_or_role]; Grant UPDATE Permission: GRANT UPDATE ON [schema_name].[table_name] TO [user_or_role]; Grant DELETE Permission: GRANT DELETE ON [schema_name].[table_name] TO [user_or_role];

Example

Suppose you have a table named Employees in the HR schema, and you want to grant SELECT and INSERT permissions to a user named xyz. You would execute the following commands:

GRANT SELECT ON HR.Employees TO xyz;

GRANT INSERT ON HR.Employees TO

xyz

Hope this helps. Do let us know if you have any further queries
pdsqsql 411 Reputation points

2025-02-27T23:23:32.95+00:00

Sangi,

Thanks for details steps.

I would like to correct here that I am not assigning permission to individual regular user.

I am assigning permission to Service Principal which is as below:

SqlTestMi Principal (Managed Instance Name Principal)

This one associated with Application (Client Id) and Value which is using connect to the Managed Instance and I have added 'SqlTestMi Principal' into database with proper permissions.

I have assigned Data_Reader and Data_Writer Role Permissions along with DB_Owner also.

I also added using similar way which you have shown with GRANT command for that database to 'SqlTestMi Principal'

1 answer

Erland Sommarskog 117.8K Reputation points MVP

2025-02-27T22:58:05.0233333+00:00
Apparently, you have granted permissions to another principal than the one you think you did. Or you granted permissions in the wrong database.

Have the application to run

SELECT SYSTEM_USER, * FROM sys.user_token

to see what comes back.
Please sign in to rate this answer.
pdsqsql 411 Reputation points

2025-02-27T23:30:14.27+00:00

Thanks Erland.

I have assigned to right service Principal and into right database.

I also verified in SSMS too.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Sql Managed Instance - Service Principal access issue

`The UPDATE permission was denied on the object 'order', database 'Ordertest', schema 'dbo'`

1 answer

Your answer