Workato authentication to Azure SQL Database with Azure AD

Dwayne Lott 31 Reputation points
2022-04-14T21:26:26.39+00:00

I have a client that would like to connect to my Azure SQL Database using Workato. I am unfamiliar with Workato.

I have setup an AD group called testdbreader. I added testdbreader as a user in the database and gave them db_datareader role, as follows.

create user [testdbreader] from external provider
alter role db_datareader add member [testdbreader]

I created an Azure AD user and assigned them to the testdbreader group.

Using SSMS, I successfully connect to the database. (FYI... I have also been able to connect using DBeaver SQL Client. So, I know it can be done through more than just SSMS.)

I added Workato's IP addresses to my firewall setup.

I have I am being told that the connection does not work via Workato.

Does anyone have experience with this type of authentication with Workato?

Thanks!

Azure SQL Database
Microsoft Entra ID
Microsoft Entra ID
A Microsoft Entra identity service that provides identity management and access control capabilities. Replaces Azure Active Directory.
19,089 questions
0 comments No comments
{count} votes

Accepted answer
  1. Alberto Morillo 32,716 Reputation points MVP
    2022-04-14T23:39:04.85+00:00

    Based on what I see on the authentication video shown on this Workato documentation page I can see that to connect to an Azure SQL database you have to provide the SQL login name and the database name in this format userlogin@DBase , however when you add an AD Azure user to Azure SQL you use the @ to specify the domain.

    CREATE USER [bob@Company portal .com] FROM EXTERNAL PROVIDER;

    So when you try to login as bob@Company portal .com where contoso is the Azure AD domain, the Workato authentication page will interpret that the contoso domain name is the database name and the authentication will fail.

    Based on above judgements I will say Workato only supports SQL authentication when using the "SQL Server connector".


0 additional answers

Sort by: Most helpful