Use managed identities to connect to a Azure SQL database

Joe Chen 20 Reputation points Microsoft Employee
2024-06-03T06:28:35.22+00:00

I have a question regarding the use of managed identities to connect to a SQL database. Do I need the ‘db_ddladmin’ role for this, or is it only necessary if I’m going to perform operations like CREATE, DROP, etc.? Additionally, if my managed identity is rotated unexpected by the platform, is there any mechanism that will assist me in recreating the USER or something similar so maybe we need the 'db_ddladmin' role? Thank you.User's image

Azure SQL Database
Azure App Service
Azure App Service
Azure App Service is a service used to create and deploy scalable, mission-critical web apps.
7,155 questions
0 comments No comments
{count} votes

Accepted answer
  1. akinbade abiola 4,675 Reputation points
    2024-06-03T07:25:32.63+00:00

    Hello Joe Chen,

    Thanks for your question.

    Depending on the full set of operations you want to perform, you can assign different roles based on least privilege.

    The ‘db_ddladmin’ role is only required if the managed identity needs to perform operations that modify the schema of the database, such as CREATE, ALTER, or DROP. It is the least privileged if you need necessarily to perform ALL DDL operations.

    These are documented here:

    https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

    Per the managed Identity, SQL database access control is managed through database users and their assigned roles. the managed Identity Object ID will change but the SQL user will retain its database level roles.

    Please let me know if you have further questions**

    You can mark it 'Accept Answer' if this helped.

    0 comments No comments

0 additional answers

Sort by: Most helpful