CODEWITHSUNDEEP
azure-sql-databaseazure-logic-appsazure-managed-identity
SeaDude
SeaDude

Reputation: 4375

Unable to connect Azure Logic App w/ System Assigned Managed Identity to Azure SQL Server

I'm unsure what I'm missing here, but I cannot connect from Logic App to Azure SQL. Any help is appreciated.

Steps performed:

1. Enabled System Assigned Managed Identity (SAMI) for the Logic App

enter image description here

2. Added the SAMI in a Contributor role for the SQL Server (its in another resource group)

enter image description here

3. Verified the SAMI role by clicking the Azure role assignments button

enter image description here

4. Added the SAMI in both the db_datareader and db_datawriter roles within the SQL Server itself using:

CREATE USER [SAMI Name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [SAMI Name]
ALTER ROLE db_datawriter ADD MEMBER [SAMI Name]

5. Verified the internal roles were successful with:

SELECT DP1.name AS DatabaseRoleName,
    isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members as DRM
RIGHT OUTER JOIN sys.database_principals as DP1 
    ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals as DP2  
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;

6. Add the Get Rows (V2) Azure SQL action in the Logic Apps Designer and create the connection

enter image description here

7. I can see that no server is found, so I enter the SQL server name as a custom value

enter image description here

8. After letting the Database Name load for a few mins, the action errors-out with:

enter image description here

Error text:

Could not retrieve values. Error code: 'Unauthorized', Message: 'Credentials are missing or not valid.
     inner exception: The credentials provided for the SQL source are invalid. (Source at xxx.database.windows.net.)
clientRequestId: da158fe9-xxx'. More diagnostic information: x-ms-client-request-id is 'B1CBF136-xxx'.

I have allowed Azure Services on the SQL Server, so I don't get why this is happening

enter image description here

Upvotes: 2

Views: 3053

Answers (1)

Sean McGrath
Sean McGrath

Reputation: 90

I'm not sure of the cause of this, but I found that if you also enter the database name as a custom value then the connection works.

I experienced exactly the same symptoms as you. After manually entering the database name, the list of table names populates automatically.

I also found that it's not necessary to add the SAMI as a contributor to the SQL Server (at least, not for this particular workaround).

Before entering custom database name: Before entering custom database name

After entering custom database name: After entering custom database name

Upvotes: 5

Related Questions