Azure - Connection between App Service and Azure PostgreSQL Database using AD

Question

I'm trying to establish a direct connection between an Azure App Service and an Azure Database for PostgreSQL single server.

I'm currently connecting to the database through a string connection and a user created in the database itself. I need to switch this method to connect with a Service Principal.

I've managed to successfully connected to the database using my assigned Azure AD group through the use of an Azure AD token. But the token retrieval process involves user interaction.

I need the full connection process to be done programmatically with zero interaction with the user.

How would I go about doing this? I've investigated through the documentation but there are so many different suggestions for so many different use cases that I don't know which posts will be red herrings.

Answer 1

Upon literally trial and error I finally found out.

For anyone that stumbles on the same error, this is how I bypassed it:

Scenario resources:

• An instance of Azure PostgreSQL Database;
• An Azure AD Group;
• A Service Principal resource.

Resource setup:

• The Azure AD Group will be set as the Active Directory admin of the PostgreSQL database;
• The Service Principal is a member of the AD Group. (this was the step I was missing).

Now for the actual "coding" bit:

Azure provides a REST API that you can use to get an access token. This access token will then be used as the password in a string connection.

Use this REST call with your Service Principal information and it will return the previously mentioned token.

You can get specifics at:

• https://learn.microsoft.com/en-us/rest/api/servicebus/get-azure-active-directory-token
• https://learn.microsoft.com/en-us/azure/databricks/dev-tools/api/latest/aad/service-prin-aad-token

Now you can simply connect through a basic string connection (string structure depends on the language you're using):

(...)?user={ad_group_name}@{your_database}&password={the_received_token}(...)

Note: The token usually as a set expiration time. Be sure to catch the request if you try to connect to the database with an expired token so you can get a fresh one.