Configure the managed identity user as an AAD admin:

• Set the AAD admin on the SQL server or the Managed instance.

• Go to Settings > Azure Active Directory > 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.

• Login to the SQL database or Managed database to create a user under that database.

      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.

• 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>'@'%'

• 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>

• Login using the AAD admin user and the access token, run:

   # 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;