Connect to private SQL database with Logic Apps Single tenant (Preview)

Published May 14 2021 02:08 AM 1,571 Views
Senior Member

 

One of the major limitations with multi-tenant logic app was its inability to integrate with private resources (that are behind a firewall and/or deny public connections). Integration service environment  (ISE) was one solution to achieve this task. But there is certain limitations in using ISE and that's a different topic for discussion.

Let's see how to integrate single tenant logic app with private resources.

 

  1. Create a Single tenant logic app and workflow. I am using a HTTP trigger to start the workflow.

Add an action and search for SQL. We can see two connector options. Built-in and Azure.

 

Haris_Abdulkareem_0-1619326184569.png

 

Private connection is possible only using the built-in actions. If we use Azure action, we will have to whitelist the connector outbound IP ranges (public) in the destination system firewall.

 

At the moment, we have only one built-in action for SQL that is 'Execute SQL query'. More built-in actions are expected to be added in the future.

 

  1. To create a built-in SQL connection we can use connection string. We can obtain the connection string from SQL database and add the password to create the action. For this example, I use a select query to one of the tables in the SQL database. I will add a response action to complete the workflow design. Let's use the result of SQL action in the response using Dynamic content.

 

If the SQL server is currently not behind a firewall and 'Allow Azure services and resources to access this server' flag is set to Yes we can test the logic app and make sure it is working fine.

Let's use the HTTP URL that is generated when the workflow is saved in an API testing tool (like Postman) to trigger the logic app. I tested and got the response with the SQL query results.

 

If the SQL server is already behind a firewall and (or) if it denies public connections, workflow would not be able to connect at the moment. We need to create a private endpoint for the Azure SQL server that we need to connect from logic app workflow.

 

We get below error in workflow (without a private endpoint).

 

Haris_Abdulkareem_1-1619326184571.png

 

 

  1. Let's create a private endpoint in the SQL server.
     

Private endpoint enables connectivity between the consumers from the same VNet, regionally peered VNets, globally peered VNets and on premises using VPN or Express Route and services powered by Private Link.

 

Reference: https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-overview

 

Haris_Abdulkareem_2-1619326184573.png

 

Select the resource.

 

Haris_Abdulkareem_3-1619326184574.png

 

 

Next step, choose a virtual network and subnet for the private endpoint. We need to integrate the private endpoint with a private DNS zone.

Haris_Abdulkareem_4-1619326184576.png

 

 

 

  1. After the private endpoint is created, let's deny the public network access.

 

Haris_Abdulkareem_5-1619326184577.png

 

 

Test the logic app again. Now we are able to integrate a logic app with a private SQL server using private endpoint. Please let me know your questions or thoughts via comments below.

%3CLINGO-SUB%20id%3D%22lingo-sub-2287420%22%20slang%3D%22en-US%22%3EConnect%20to%20private%20SQL%20database%20with%20Logic%20Apps%20Single%20tenant%20(Preview)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2287420%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EOne%20of%20the%20major%20limitations%20with%20multi-tenant%20logic%20app%20was%20its%20inability%20to%20integrate%20with%20private%20resources%20(that%20are%20behind%20a%20firewall%20and%2For%20deny%20public%20connections).%20%3CA%20href%3D%22http%3A%2F%2Faka.ms%2Fintegration-service-environment%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3EIntegration%20service%20environment%3C%2FA%3E%26nbsp%3B%20(ISE)%20was%20one%20solution%20to%20achieve%20this%20task.%20But%20there%20is%20certain%20limitations%20in%20using%20ISE%20and%20that's%20a%20different%20topic%20for%20discussion.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ELet's%20see%20how%20to%20integrate%20single%20tenant%20logic%20app%20with%20private%20resources.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20style%3D%22margin-left%3A%20.375in%3B%20direction%3A%20ltr%3B%20unicode-bidi%3A%20embed%3B%20margin-top%3A%200in%3B%20margin-bottom%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%20font-weight%3A%20normal%3B%20font-style%3A%20normal%3B%22%20type%3D%221%22%3E%0A%3CLI%20style%3D%22margin-top%3A%200%3B%20margin-bottom%3A%200%3B%20vertical-align%3A%20middle%3B%22%20value%3D%221%22%3E%3CSPAN%20style%3D%22font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%20font-weight%3A%20normal%3B%20font-style%3A%20normal%3B%22%3ECreate%20a%20Single%20tenant%20logic%20app%20and%20workflow.%20I%20am%20using%20a%20HTTP%20trigger%20to%20start%20the%20workflow.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20margin-left%3A%20.375in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EAdd%20an%20action%20and%20search%20for%20SQL.%20We%20can%20see%20two%20connector%20options.%20Built-in%20and%20Azure.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20margin-left%3A%20.375in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Haris_Abdulkareem_0-1619326184569.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275313i033689E1D3FDE818%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Haris_Abdulkareem_0-1619326184569.png%22%20alt%3D%22Haris_Abdulkareem_0-1619326184569.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EPrivate%20connection%20is%20possible%20only%20using%20the%20built-in%20actions.%20If%20we%20use%20Azure%20action%2C%20we%20will%20have%20to%20whitelist%20the%20connector%20outbound%20IP%20ranges%20(public)%20in%20the%20destination%20system%20firewall.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EAt%20the%20moment%2C%20we%20have%20only%20one%20built-in%20action%20for%20SQL%20that%20is%20'Execute%20SQL%20query'.%20More%20built-in%20actions%20are%20expected%20to%20be%20added%20in%20the%20future.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20style%3D%22margin-left%3A%20.375in%3B%20direction%3A%20ltr%3B%20unicode-bidi%3A%20embed%3B%20margin-top%3A%200in%3B%20margin-bottom%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%20font-weight%3A%20normal%3B%20font-style%3A%20normal%3B%22%20type%3D%221%22%3E%0A%3CLI%20style%3D%22margin-top%3A%200%3B%20margin-bottom%3A%200%3B%20vertical-align%3A%20middle%3B%22%20value%3D%222%22%3E%3CSPAN%20style%3D%22font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%20font-weight%3A%20normal%3B%20font-style%3A%20normal%3B%22%3ETo%20create%20a%20built-in%20SQL%20connection%20we%20can%20use%20connection%20string.%20We%20can%20obtain%20the%20connection%20string%20from%20SQL%20database%20and%20add%20the%20password%20to%20create%20the%20action.%20For%20this%20example%2C%20I%20use%20a%20select%20query%20to%20one%20of%20the%20tables%20in%20the%20SQL%20database.%20I%20will%20add%20a%20response%20action%20to%20complete%20the%20workflow%20design.%20Let's%20use%20the%20result%20of%20SQL%20action%20in%20the%20response%20using%20Dynamic%20content.%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EIf%20the%20SQL%20server%20is%20currently%20not%20behind%20a%20firewall%20and%20'Allow%20Azure%20services%20and%20resources%20to%20access%20this%20server'%20flag%20is%20set%20to%20Yes%20we%20can%20test%20the%20logic%20app%20and%20make%20sure%20it%20is%20working%20fine.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ELet's%20use%20the%20HTTP%20URL%20that%20is%20generated%20when%20the%20workflow%20is%20saved%20in%20an%20API%20testing%20tool%20(like%20Postman)%20to%20trigger%20the%20logic%20app.%20I%20tested%20and%20got%20the%20response%20with%20the%20SQL%20query%20results.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EIf%20the%20SQL%20server%20is%20already%20behind%20a%20firewall%20and%20(or)%20if%20it%20denies%20public%20connections%2C%20workflow%20would%20not%20be%20able%20to%20connect%20at%20the%20moment.%20We%20need%20to%20create%20a%20private%20endpoint%20for%20the%20Azure%20SQL%20server%20that%20we%20need%20to%20connect%20from%20logic%20app%20workflow.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EWe%20get%20below%20error%20in%20workflow%20(without%20a%20private%20endpoint).%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Haris_Abdulkareem_1-1619326184571.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275314i8C281FFE38396C4C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Haris_Abdulkareem_1-1619326184571.png%22%20alt%3D%22Haris_Abdulkareem_1-1619326184571.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20style%3D%22margin-left%3A%20.375in%3B%20direction%3A%20ltr%3B%20unicode-bidi%3A%20embed%3B%20margin-top%3A%200in%3B%20margin-bottom%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%20font-weight%3A%20normal%3B%20font-style%3A%20normal%3B%22%20type%3D%221%22%3E%0A%3CLI%20style%3D%22margin-top%3A%200%3B%20margin-bottom%3A%200%3B%20vertical-align%3A%20middle%3B%22%20value%3D%223%22%3E%3CSPAN%20style%3D%22font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%20font-weight%3A%20normal%3B%20font-style%3A%20normal%3B%22%3ELet's%20create%20a%20private%20endpoint%20in%20the%20SQL%20server.%3CBR%20%2F%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EPrivate%20endpoint%20enables%20connectivity%20between%20the%20consumers%20from%20the%20same%20VNet%2C%20regionally%20peered%20VNets%2C%20globally%20peered%20VNets%20and%20on%20premises%20using%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fservices%2Fvpn-gateway%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%20style%3D%22background%3A%20white%3B%22%3EVPN%3C%2FSPAN%3E%3C%2FA%3E%26nbsp%3Bor%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fservices%2Fexpressroute%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%3CSPAN%20style%3D%22background%3A%20white%3B%22%3EExpress%20Route%3C%2FSPAN%3E%3C%2FA%3E%26nbsp%3Band%20services%20powered%20by%20Private%20Link.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EReference%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fprivate-link%2Fprivate-endpoint-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fprivate-link%2Fprivate-endpoint-overview%3C%2FA%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Haris_Abdulkareem_2-1619326184573.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275315i2FBE1A304277CC48%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Haris_Abdulkareem_2-1619326184573.png%22%20alt%3D%22Haris_Abdulkareem_2-1619326184573.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ESelect%20the%20resource.%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Haris_Abdulkareem_3-1619326184574.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275316i6136E4265F60610A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Haris_Abdulkareem_3-1619326184574.png%22%20alt%3D%22Haris_Abdulkareem_3-1619326184574.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ENext%20step%2C%20choose%20a%20virtual%20network%20and%20subnet%20for%20the%20private%20endpoint.%20We%20need%20to%20integrate%20the%20private%20endpoint%20with%20a%20private%20DNS%20zone.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Haris_Abdulkareem_4-1619326184576.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275318i54A98C60CC29B6E0%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Haris_Abdulkareem_4-1619326184576.png%22%20alt%3D%22Haris_Abdulkareem_4-1619326184576.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3COL%20style%3D%22margin-left%3A%20.375in%3B%20direction%3A%20ltr%3B%20unicode-bidi%3A%20embed%3B%20margin-top%3A%200in%3B%20margin-bottom%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%20font-weight%3A%20normal%3B%20font-style%3A%20normal%3B%22%20type%3D%221%22%3E%0A%3CLI%20style%3D%22margin-top%3A%200%3B%20margin-bottom%3A%200%3B%20vertical-align%3A%20middle%3B%22%20value%3D%224%22%3E%3CSPAN%20style%3D%22font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%20font-weight%3A%20normal%3B%20font-style%3A%20normal%3B%22%3EAfter%20the%20private%20endpoint%20is%20created%2C%20let's%20deny%20the%20public%20network%20access.%20%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Haris_Abdulkareem_5-1619326184577.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275317i7258583C6EEB3329%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Haris_Abdulkareem_5-1619326184577.png%22%20alt%3D%22Haris_Abdulkareem_5-1619326184577.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3ETest%20the%20logic%20app%20again.%20Now%20we%20are%20able%20to%20integrate%20a%20logic%20app%20with%20a%20private%20SQL%20server%20using%20private%20endpoint.%20Please%20let%20me%20know%20your%20questions%20or%20thoughts%20via%20comments%20below.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2287420%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ELogic%20Apps%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎May 14 2021 02:08 AM
Updated by: