Deploying Logic Apps Standard with Managed Identity and private networking
I was working with a customer who needed to implement some automation tasks to support their application. The automation tasks would be driven on data in their Azure SQL database. As most of their developers were busy tackling their backlog, I thought "What if we could use Logic Apps to do a no code solution with their operations team?" The first step was of course to deploy the Logic App. As the customer is running fully private networking for all services (Azure SQL, Storage, etc.), we would deploy Logic Apps Standard, which runs on the Azure App Service runtime similar to Function Apps. Like a Function App, the Logic App uses a storage account in the background. However, when deploying through the portal, the deployment failed with a 403 Unauthorized error. Of course! The customer had disabled shared key access to storage accounts, utilizing Entra ID exclusively. Like our post about setting up an Azure Container Instance to use Managed Identity to connect to an Azure Container Registry, we'd have to write a bit of Bicep script to utilize a User Assigned Managed Identity which has rights to Azure Storage and SQL. I created the User Assigned Managed Identity and granted it the following roles on the storage account: Storage Account Contributor Storage Blob Data Contributor Storage Queue Data Contributor Storage Table Data Contributor While that solved the access issue and allowed the deployment to complete, the Logic App was showing a runtime error in the portal, stating that it was "Unable to load the proper Managed Identity." As it turns out, we need to explicitly tell the Logic App in the App Service configuration that we are using Managed Identity as our authentication mechanism, and which Managed Identity we want to use. Once I added that to the configuration, my Managed Identity error went away, but I was still getting a runtime error. Looking in the log stream, I could see that I was getting many errors trying to talk to the storage account queue and table endpoints. Because the client is using all private networking, we needed to setup a private endpoint and associated private DNS entry for all three storage account endpoints: blob, queue and table. Once I added those private endpoints and added them to my App Service configuration, my Logic App deployed and ran successfully. I've added the Bicep code for the Logic App service here. One final "Gotcha": if you look in my Bicep, you will note that I am specifying both the User Assigned Managed Identity as well as a System Assigned Managed Identity. The reason for this is, when using a SQL connector, Managed Identity was not listed as an option for authentication. I was stumped by this at first, but then I noticed that it was an option in a portal deployed Logic App. The difference was that the portal deployment adds a System Assigned Managed Identity. Once I added this to my Bicep, the Managed Identity option showed up on the SQL connector. It appears that the connector is looking for the presence of a System Assigned Managed Identity to toggle that authentication option, but you can still use your User Assinged Managed Identity for SQL authentication.Enabling the Logic Apps PowerShell Connector for performing Azure tasks
This is a fairly short post, but in my last post about deploying a Logic Apps Standard using Managed Identity and private networking, I'd mentioned the customer needed to run some automation tasks. Some of these tasks pertained to maintenance within their application, but some were cleaning up unused resources in Azure, specifically removing stale containers in blob storage. However, when trying to run the Remove-AzStorageContainer PowerShell cmdlet, we got the following error: "The term 'Remove-AzStorageContainer' is not recognized as a name of a cmdlet, function, script file, or executable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again." By default, the PowerShell Connector in Logic Apps does not include the Az PowerShell module. To enable this (or other PowerShell modules you want to use): Open Kudu and open a CMD or PowerShell console Navigate to site/wwwroot Open the host.json file and ensure that you see a property "managedDependency":{"enabled":true} (this is the default and should be present). Open the requirements.psd1 file and add any PowerShell modules and version you want installed, e.g. 'Az' = '12.*' Save the file and restart the web service After that, you can add the Execute PowerShell Code connector. To use a Managed Identity as the user context for performing tasks in Azure, add the following line at the beginning of your script: Connect-AzAccount -Identity -Environment AzureUSGovernment Enjoy!Logic Apps, Windows auth, the On-Premises Data Gateway and you
I was working with a customer recently who had a somewhat unusual situation, unusual in that I have become accustomed to most government customers having an Express Route or site-to-site VPN connection to connect their on-premises network to their Azure environment. However, this customer had a need to connect to an on-premises SQL instance via the on-premises data gateway (OPDG). The OPDG is installed as a Windows service on a machine inside the on-premises network and, when registered in Azure, allows Azure resources to connect to services on premises (more here). I did not have any direct experience with this tool but worked with the customer to get it installed on their network, which included getting it to work with their proxy (thanks to the larger Microsoft team for the assist and thanks to the customer for their patience figuring this out). We got the gateway registered in Azure and were ready to test it out. The customer is using Logic Apps and the SQL Connector to connect back to their on-premises SQL instance. The initial attempt to connect to SQL using SQL credentials was successful (Yay!!!) but the customer wanted to use Windows Authentication (which is a best practice). However, when trying to establish the connection with Windows Authentication, they were getting the error "status 401 - Credentials are missing or not valid...The credentials provided for the SQL source are invalid." Doing a little sleuthing in the Security logs on the SQL server, we could see that the authentication attempt being made, but with null credentials. Of course! We're passing our Windows credentials to the OPDG, but we haven't configured the gateway service account with delegation permissions to pass them on to SQL! To configure the on-premises data gateway account for delegation, do the following: Create a Service Principal Name (SPN) using the setspn command line tool for the OPDG service account: setspn -S gateway\gatewayMachineName domainname\gatewaySvcAcctName Open Active Directory Users and Computers (ADUC) and locate the OPDG service account Open the Delegation tab and select "Trust this user for delegation to specified services only" and "Use Kerberos Only" then add the services for which you want the OPDG to have delegation, e.g. SQL Server. Once this was done, we could create the Logic Apps SQL Connection successfully. A good check is to see if you can enumerate the list of tables in the database to which you are querying. As it turns out, there is fairly good documentation on this topic under the Power BI documentation, but searching for this under the context of Logic Apps didn't yield much success! In any case, I hope this helps someone else in the future!Calling APIs using private Certificate Authorities from Logic Apps
A colleague reached out to me to help with a customer's Logic App issue. They were trying to make what looked like a simple HTTP request to a Jira API but were getting back an SSL error. The root of the error was that the customer was securing the API endpoint with a certificate generated from a private Certificate Authority in their environment. Because this certificate was not signed by VeriSign, GoDaddy or other public certificate authority, and the Logic App HTTP Connector was failing as it did not trust the issuer. The fix for this is simple as it turns out but required a bit of reading the manual. First, go to Certificates and load the root CA and any intermediate CAs you might be using to the store. Then, to get the Logic App to load these certs, go to Environment Variables and add a new setting WEBSITE_LOAD_ROOT_CERTIFICATES and place the thumbprints of the added root and intermediate CAs as a comma delimited string (hat tip to Glen from our consulting team for figuring that one out). When you save the change, the service will restart and will now trust these private certificates. It should be noted that Logic Apps Standard is a flavor of Azure App Service, so this fix would also work for a regular App Service or a Function App as well. I hope this helps you out!
