How do I access on-prem sql server from Azure Logic App (using on-prem data gateway) and a Gmsa?

Question

I have an Azure "On-premise data gateway" setup and configured to log-on with a Gmsa.

I have a Logic App setup with a SQL "get row" connector as follows:

I want to get the SQL Connector to retrieve rows from the dbo.Company table in my on-prem database whilst connecting to the database using Gmsa "DMN\APP-BI-Gateway$".

Is it possible? If so, what 'Authentication Type' do I need to use in the API Connector?

I have tried Logic App Managed Identity and get error: "Gateway has determined that DirectQuery is not supported for the connection."

I can't use Windows Authentication because password is required (but not required for Gmsa).

Any help would be appreciated.

Cheers,

Answer 1

You just need to add an API connection that uses gateway.

Something like this:

{
   "type": "Microsoft.Web/connections",
   "apiVersion": "2016-06-01",
   "name": "[parameters('connection_Name')]",
   "location": "[parameters('logicAppLocation')]",
   "properties": {
    "displayName": "[parameters('connection_DisplayName')]",
    "customParameterValues": {},
    "api": {
        "id": "[concat(subscription().id, '/providers/Microsoft.Web/locations/', parameters('logicAppLocation'), '/managedApis/', 'sql')]"
    },
    "parameterValues": {
     "server": "[parameters('server')]",
     "database": "[parameters('database')]",
     "username": "[parameters('username')]",
     "password": "[parameters('password')]",
     "authType": "basic",
     "gateway": {
      "id": "/subscriptions/{{subscriptionId}}/resourceGroups/{{resourceGroupName}}/providers/Microsoft.Web/connectionGateways/{{On-Premise DatagatewayName}}"
     }
}

The gateway ID should be available in the URL bar if you navigate to the resource within the Azure portal.

Then use this connection as you do with other direct sql connection.