Failed to retrieve dynamic inputs. Error details: 'Request failed with status code 401' - Azure Logic Apps, SQL connection

Hi I have created an API connection for connecting to SQLserver from logic Apps. The API Connection has the default Server name, DB name, now I need to reuse the same SQL connection to connect to same server but different DB.

"Failed to retrieve dynamic inputs. Error details: 'Request failed with status code 401'"

Full Error: Failed to retrieve dynamic inputs. Error details: 'Permission Exception. inner exception: Internal error: dataSourceCredentials

Is it not possible to reuse the same connection to connect to different DB ?

Yes you can do so by creating a connection using system assigned managed identity or any other authentication type which doesn't require database name while establishing the connection.

In my case, I am using system assigned managed identity to create an API connection.

Primarily, enable managed identity in logic app and the execute below query in both the databases.

CREATE USER [System assigned managed Identity Name (Your logic app Name)] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [System assigned managed Identity Name]
ALTER ROLE db_datawriter ADD MEMBER [System assigned managed Identity Name]

Also, grant SQL Server Contributor role to your logic app in Server -> Access Control (IAM) -> Role assignment.

Then, proceed to create a connection to Azure SQL server.

You need to enter the server name by selecting Enter custom value.

You might get unauthorized message when selecting database name but if you have granted all the required accesses then you can simply add the database name by clicking Enter custom value as even I got this message but by doing so it worked for me.

In this way, you can use single API connection to connect to two different databases created in same server or different server too.