Expose an API to external users (Outside of the organisation/subscription) - To access a table in synapse dedicated sql pool

Question

Expose an API to external users (Outside of the organisation/subscription) - To access a table in synapse dedicated sql pool

Vivek Komarla Bhaskar 956

Is there a way to create an API which will be used by external users who are outside of an organisation and not part of any organisations azure subscription. This API will need to access a table in synapse dedicated sql pool. Any thoughts, please advise?

Vivek Komarla Bhaskar 956 Reputation points

2022-11-26T21:09:44.087+00:00

@AnnuKumari-MSFT , @ShaikMaheer-MSFT , @PRADEEPCHEEKATLA

Can anyone please help me out here?? I have a requirement and we need to take a decision on the possibility ASAP
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-12-05T06:18:46.153+00:00

Hi @Vivek Komarla Bhaskar ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Accepted answer

1 additional answer

Your answer

Vivek Komarla Bhaskar 956 Reputation points

2022-11-26T21:09:44.087+00:00

@AnnuKumari-MSFT , @ShaikMaheer-MSFT , @PRADEEPCHEEKATLA

Can anyone please help me out here?? I have a requirement and we need to take a decision on the possibility ASAP
AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2022-12-05T06:18:46.153+00:00

Hi @Vivek Komarla Bhaskar ,
Just checking in to see if the below answer helped. Please do consider clicking Accept Answer and Up-Vote for the same as accepted answers help community as well. If you have any further query do let us know.

Answer 1

Sonny Gillissen 3,751 Volunteer Moderator

Sure Vivek!

So to start off: an Azure Function App approach could also work. I usually aim to use low-code (Logic Apps) when possible and full-code (Function App) when needed. However, it depends on your needs and preferences.

Note: check your scaling and pricing based on the needed performance and abilities.

So, either way, you can:

Set up an Azure Function using the HTTP-trigger where you load the ‘System.Data.SqlClient’ NuGet package which you can use to connect to SQL, read the table and provide the query output as response
- Please find more information on working with SQL in Azure Functions in the article below which can act as guidance on your case: https://learn.microsoft.com/en-us/azure/azure-functions/functions-scenario-database-table-cleanup
Create a Logic App where you use a Request-trigger and a ‘SQL Server’ action to read the table. Next you’ll provide the result in a response action.

After you’ve connected either your Logic App or Function App you can use the following docs to import them into the API Management:

Please note that the default security of the APIM is subscription key. You can also, based on your requirments, extend this to OAuth or OpenID for better security.

Let me know if this was the level of detail you were looking for. If we need to touch on something more in depth, feel free to reach out.

Vivek Komarla Bhaskar 956 Reputation points

2022-11-28T16:23:30.713+00:00

Thanks Sonny,

I was trying to use Logic Apps as you advised, Please see the screenshot for the first step in the process. I get an error -
Am I doing it the right way here?
Sonny Gillissen 3,751 Reputation points Volunteer Moderator

2022-11-28T19:08:05.777+00:00

Hi Vivek, the top ‘block’ is the issue here. This logic app should be able to receive HTTP requests from the API manager, which is done through the ‘Request’ trigger. Not the “HTTP” trigger, to which I agree it can be a bit confusing.

The HTTP trigger is actually a recurrence trigger checking an endpoint on a scheduled basis. However, we don’t want to check a HTTP endpoint, we want to become one (and therefor be able to receive requests).

Please feel free to reach out if you want to discuss further! I’m happy to help.
Vivek Komarla Bhaskar 956 Reputation points

2022-12-01T12:21:54.51+00:00

Thank you. I will try as you suggested and get back to you soon
Sonny Gillissen 3,751 Reputation points Volunteer Moderator

2022-12-01T19:16:15.88+00:00

Sure! Feel free to reach out when you ned more info.
Vivek Komarla Bhaskar 956 Reputation points

2022-12-06T17:47:23.19+00:00

Hi Sonny,

I was able to create an API as you suggested, how can I ignore the attributes resulting -

"@odata.etag"
"ItemInternalId"

These attributes are not part of the table, do I have to pass something to my LogicApp Response action so I can ignore these attributes? Right now I have this -
Sonny Gillissen 3,751 Reputation points Volunteer Moderator

2022-12-06T19:24:45.07+00:00
Great @Vivek Komarla Bhaskar !

As of this writing there's not another way to do this then to add in a 'Select' action.
With this action you can "remap" the current array to a new one, leaving those attributes out.

Add the 'Select' action to the workflow and reference the SQL output array in 'From'

Map all values you want to respond in 'Map' using the item() function

Change the 'Response' action to refer to the output of the 'Select' instead of the 'SQL'

For your reference I added the map I used in above example below:

StartDateTime: item()['StartDateTime']

EndDateTime: item()['EndDateTime']

ShowTitle: item()['ShowTitle']

ShowType: item()['ShowType']

ShowImage: item()['ShowImage']

Presenters: item()['Presenters']

Description: item()['Description']

PickEvent: item()['PickEvent']
Vivek Komarla Bhaskar 956 Reputation points

2022-12-07T02:59:52.067+00:00

Thanks @Sonny Gillissen

This works!!
But the only concern I see is, after saving the Logic App & refresh the page the mapped values are sorting (ASC) by itself. Instead I want it to be in the exact same order as in your screenshot.
Sonny Gillissen 3,751 Reputation points Volunteer Moderator

2022-12-07T09:35:41.013+00:00
Hi @Vivek Komarla Bhaskar ,

Yes, I understand. One downside of using the Logic App functionality is that sorting can get lost.

If you want to keep sorting you could also revert your Logic App not to contain the 'Select' action, but directly output the SQL result again, just as you did before, and then add the following code to your APIM's endpoint outbound policy part (see the image below for reference)

<set-body template="liquid">{ "StartDateTime": "{{body.StartDateTime}}", "EndDateTime": "{{body.EndDateTime}}", "ShowTitle": "{{body.ShowTitle}}", "ShowType": "{{body.ShowType}}", "ShowImage": "{{body.ShowImage}}", "Presenters": "{{body.Presenters}}", "Description": "{{body.Description}}", "PickEvent": "{{body.PickEvent}}" }</set-body>

Your APIM will look like below then:

This way you can also "hide" attributes you don't want to show in your response.
Vivek Komarla Bhaskar 956 Reputation points

2022-12-07T13:18:15.74+00:00

Thanks Sonny, I updated outbound policy of my API but the result is just blank.
Sonny Gillissen 3,751 Reputation points Volunteer Moderator

2022-12-07T13:26:40.14+00:00
Whoops, sorry @Vivek Komarla Bhaskar , you're right!
Forgot to mention to add the 'Content-Type: application/json' header to outbound:

Please find the code snippet below:

<set-header name="Content-Type" exists-action="override"> <value>application/json</value> </set-header>
Vivek Komarla Bhaskar 956 Reputation points

2022-12-07T13:37:16.667+00:00

@Sonny Gillissen , Unfortunately still get the same result even after adding 'Content-Type: application/json' header to outbound as you mentioned.
Sonny Gillissen 3,751 Reputation points Volunteer Moderator

2022-12-07T13:43:59.007+00:00

Did you revert the logic app back to the state it was before?
Sonny Gillissen 3,751 Reputation points Volunteer Moderator

2022-12-07T13:46:13.357+00:00

Could you show me the response action in the logic app?
Vivek Komarla Bhaskar 956 Reputation points

2022-12-07T13:55:42.46+00:00

Yes I have reverted the Logicapp to the state it was before.
However I am able to see the response recording data, but not the POST API call. Please see the screenshots below -

Sonny Gillissen 3,751 Volunteer Moderator

Gosh..... I've been sleeping, for real...... Sorry, @Vivek Komarla Bhaskar
I've created the code for an Object, but you're using an Array.

Try this code:

<set-header name="Content-Type" exists-action="override">  
    <value>application/json</value>  
</set-header>  
<set-body template="liquid">[  
    {% for item in body %}  
        {  
            "StartDateTime": "{{item.StartDateTime}}",  
            "EndDateTime": "{{item.EndDateTime}}",  
            "ShowTitle": "{{item.ShowTitle}}",  
            "ShowType": "{{item.ShowType}}",  
            "ShowImage": "{{item.ShowImage}}",  
            "Presenters": "{{item.Presenters}}",  
            "Description": "{{item.Description}}",  
            "PickEvent": "{{item.PickEvent}}"  
        }  
    {% endfor %}  
]</set-body>

Vivek Komarla Bhaskar 956 Reputation points

2022-12-07T16:53:39.143+00:00

This works!! Thank you so much @Sonny Gillissen .
I Accepted the answer

Just for my future use case, If you don't mind I request if you can help me on this too. If in case I have my response output as below what changes do I have to make to the policy output?

Sonny Gillissen 3,751 Volunteer Moderator

Great @Vivek Komarla Bhaskar ! Nice to hear I could be of help.
In regards to your future ask: sure thing! Something like the code below would do:

<set-header name="Content-Type" exists-action="override">  
    <value>application/json</value>  
</set-header>  
<set-body template="liquid">{  
    "ResultSets": {  
    "Table1":   
        [  
        {% for item in body.ResultSets.Table1 %}  
            {  
                "StartDateTime": "{{item.StartDateTime}}",  
                "EndDateTime": "{{item.EndDateTime}}",  
                "ShowTitle": "{{item.ShowTitle}}",  
                "ShowType": "{{item.ShowType}}",  
                "ShowImage": "{{item.ShowImage}}",  
                "Presenters": "{{item.Presenters}}",  
                "Description": "{{item.Description}}",  
                "PickEvent": "{{item.PickEvent}}"  
            }  
        {% endfor %}  
        ]  
    },  
    "ReturnCode": "{{body.ReturnCode}}",  
    "OutputParameters": "{{body.OutputParameters}}"  
}</set-body>

Sonny Gillissen 3,751 Volunteer Moderator

When you only want to return the array, and not everything surrounding it (like the returncode and such) use the code below.
Please note this is the exact same code as in my previous answer, however the 'body' reference turned into 'body.ResultSets.Table1' to refer to the array again in the new body structure.

<set-header name="Content-Type" exists-action="override">  
    <value>application/json</value>  
</set-header>  
<set-body template="liquid">[  
{% for item in body.ResultSets.Table1 %}  
    {  
        "StartDateTime": "{{item.StartDateTime}}",  
        "EndDateTime": "{{item.EndDateTime}}",  
        "ShowTitle": "{{item.ShowTitle}}",  
        "ShowType": "{{item.ShowType}}",  
        "ShowImage": "{{item.ShowImage}}",  
        "Presenters": "{{item.Presenters}}",  
        "Description": "{{item.Description}}",  
        "PickEvent": "{{item.PickEvent}}"  
    }  
{% endfor %}  
]</set-body>

Vivek Komarla Bhaskar 956 Reputation points

2022-12-13T14:15:06.513+00:00

Thanks @Sonny Gillissen ,

This works! However I see another issue with DateTime attributes, they are automatically converting to a different format. I have created a question for this, could you please help?

https://learn.microsoft.com/en-us/answers/questions/1127185/azure-api-management-datatime-conversion-issues.html
https://learn.microsoft.com/en-us/answers/questions/1127286/how-to-set-optional-parameter-from-azure-api-manag.html
Sonny Gillissen 3,751 Reputation points Volunteer Moderator

2022-12-13T20:40:12.427+00:00

Thanks @Vivek Komarla Bhaskar , I replied to it within the other questions :-)

Answer 2

Sonny Gillissen 3,751 Volunteer Moderator

As far as I can see no API's are available to read tables directly from SQL Synapse, however you can create a Logic App to provide this functionality using the SQL Server connector which you expose through the Azure API Management service. When necessary you can perform transformations in the either the Logic App or Azure API Management policy.

You can set authentication based on requirements (and possibilities) for the receiving end in your Azure API Management policy to provide external access in the way that suits your use-case best.

Please feel free to reach out if you want to further discuss on this topic!
When this was helpful please accept this answer :-)

Vivek Komarla Bhaskar 956 Reputation points

2022-11-28T01:09:54.243+00:00

I found another reply on the forum for similar question like mine -

https://learn.microsoft.com/en-us/answers/questions/195829/is-it-possible-to-access-data-within-a-table-in-a.html

It says use of Azure functions, are you able to detail a bit more on how this can be approached?

Share via

Expose an API to external users (Outside of the organisation/subscription) - To access a table in synapse dedicated sql pool

1 additional answer

Your answer