Creating a system or user-managed identity username

For Azure SQL Server and Managed Instance

Do any of the following configurations:

Configure the managed identity user as an AAD admin:

Set the AAD admin on the SQL server or the Managed instance.
Go to Settings > Microsoft Entra ID> Set admin. Search and set the system-assigned or user-assigned managed identity, and save.
Note:
Only media servers configured with both system-assigned managed identity and AAD administrator permissions can perform backup and restore.

Create a managed identity user on the database using the SSMS client:

To set AAD admin for SQL server, create users, go to Settings > Active Directory admin > Set admin. Pick active directory for the user, and save.

    CREATE USER [<managed_identity>] FROM EXTERNAL PROVIDER;
    ALTER ROLE db_owner ADD MEMBER [<managed_identity>];

Provide login permission for that user on the SQL Server, run
```
   # CREATE USER [<managed_identity>] FROM EXTERNAL PROVIDER;
   # ALTER ROLE loginmanager ADD MEMBER [<managed_identity>];
```
Note:
You must create users for all media servers communicating with the database using the system-assigned managed identity.
Note:
To restore a database, you must configure the managed identity user as an AAD admin on the target server.

For MySQL

To configure the AAD admin for the MySQL server, create a user. Go to Settings > Active Directory admin > Set admin. Pick the active directory user, and save.

Get the client ID for managed identity using Azure CLI, run

# az ad sp list --display-name <managed_identity> --query [*].appId --out tsv

Generate an access token to log on, using Azure CLI, run:
```
# az account get-access-token --resource-type oss-rdbms
```

Log on using the AAD admin user and access token, run:

# mysql -h <server name> --user <user name> --enable-cleartext-plugin --password=<token>

Create the manage identity user and grant the permissions, run:

# SET aad_auth_validate_oids_in_tenant = OFF;
  # CREATE AADUSER '<db_user>' IDENTIFIED BY '<Generated_client_id>';
  # GRANT USAGE, DROP, SELECT, CREATE, SHOW VIEW, EVENT, LOCK TABLES , ALTER, CREATE VIEW, INSERT, REFERENCES, ALTER ROUTINE, PROCESS ON *.* TO '<db_user>'@'%'

For PostgreSQL

To configure the AAD admin for the PostgreSQL server, create a user. Go to Settings > Active Directory admin > Set admin. Pick the active directory user, and save.

Get the client ID for the managed identity:

 # az ad sp list --display-name <managed_identity>  --query [*].appId --out tsv

Generate the access token required to login, run:

# az account get-access-token --resource-type oss-rdbms

Export the password for the generated token, run:
```
# export PGPASSWORD=<token>
```

 # psql "host=<host name> port=5432 dbname=<dbname> user=<user name> sslmode=require"

To create a user and grant permissions, run:

# SET aad_auth_validate_oids_in_tenant = OFF;
  # CREATE ROLE <db_user> WITH LOGIN PASSWORD '<client_id>' IN ROLE azure_ad_user;
  # GRANT azure_pg_admin TO <db_user>;
  # ALTER USER smipguser CREATEDB;
  # ALTER USER smipguser Replication;

Note:

Only user-managed identity is supported for MySQL Flexible Server. Managed Identity support is not available for PostgreSQL Flexible Server.

For Azure Cosmos DB for NoSQL

Log on to your Azure portal.
To assign the Cosmos DB Built-in Data Contributor role to the managed identity, run the command:
# az cosmosdb sql role assignment create -a <Account_Name> -g <Resource_Group_Name> -s "/" -p <Object_ID/Principle_ID> -d 00000000-0000-0000-0000-000000000002
Where:
- Account_Name is the Azure Cosmos account name.
- Resource_Group_Name is the Resource group name of the account.
- Object_ID/Principle_ID is the Managed identity object or principle ID.
- 00000000-0000-0000-0000-000000000002 is the Cosmos DB Built-in Data Contributor role ID.

Creating a system or user-managed identity username

Feedback

Feedback