For the first workdays of this new year, I had the pleasurable task of resetting a bunch of passwords. The passwords belong to Azure AD users, service principals, SQL Server users etc. and all of these are tied into the ETL process of a data warehouse. Some are used to connect to source data, others to connect to the Power BI REST API and some are used in Power BI to connect to the data warehouse. The reason I had to reset all these passwords was because of the LastPass breach (we use LastPass to store our passwords). Even though there were no actual passwords stolen, our IT Security Officer send an “encouraging” email to change all passwords, internal and external. GDPR and all that.
This however presented me with the opportunity to review what users were actually necessary in the ETL. Turns out, not that many. In many cases, one Azure resource (for example, an Logic App) can use a managed identity to access another Azure resource (such as an Azure SQL DB). For those of you not familiar with the concept, a managed identity is basically a service principal in Azure AD with the same name as your resource. If your Azure Data Factory instance is called myADF, you’ll have an entry in AAD with the name myADF (it’s very much alike the SQL Server service account used on-premises). You can then assign role permissions to this managed identity.
Let’s take Azure Data Factory for example. In the Managed Identities tab of the properties, you can assign a system-assigned managed identity (MI).
This means we have in AAD an enterprise application with the name “mssqltips-df”.
We can treat this as any other user/group in AAD. For example, in Azure SQL DB, we can add this user and assign permissions to it.
CREATE USER [myADF] FOR EXTERNAL PROVIDER; GO ALTER ROLE [db_datareader] ADD MEMBER [myADF]; GO
And now this specific instance of ADF can read data from the database. In the linked service, you’ll have to specify that you’re using the managed identity.
Aside from system-assigned MI, you can also use user-assigned MI. In this case, you create a managed identity yourself, choose a name and assign it to one or more resources.
The great benefit of managed identities is that they don’t have a password! As implied by the name, they are managed by Azure itself.
I was already using managed identities in a couple of places:
- To connect from ADF to Azure SQL DB (as seen in the example)
- To connect ADF to Azure Key Vault. You use the managed identity in the access policies.
- To connect Function Apps to Azure Key Vault.
- To connect ADF with Azure Blob Storage
But during my review I discovered other use cases where I could apply managed identities:
- I had a bunch of Logic Apps connecting to Azure SQL DB using SQL Server authentication. I created a user-assigned managed identity, assigned it to the Logic Apps and gave the MI the necessary permissions in the database. The reason to choose for a user-assigned MI is because I have the same Logic App in different environments (dev, test and prod). This means that if I would have used system-assigned MI, I would have ended up with three enterprise applications with the same name. Recipe for disaster. With a user-assigned MI, I don’t have this problem.
- I had an Azure Function using PowerShell to interact with the Power BI REST API. This used an app registration, which means you need to keep track of a token. Such a token expires after a while, thus you need to periodically refresh it. I replaced the Function with the same functionality in ADF. I can use the MI of ADF to connect to the REST API. Check out the blog post of fellow MVP Joost on how to do this.
In the end, by using managed identities, removing redundant users and by making some processes more efficient (for example by writing data directly to Azure SQL DB in a Logic App instead of dumping it first in Blob Storage), I could remove almost all passwords from the ETL flow. There are only two users left:
- an actual AD user with permissions to read data from SharePoint Online (both Lists and document libraries). Managed identities are not (yet?) supported.
- a SQL Server user to connect from Power BI Desktop to Azure SQL DB. I’d rather manage one single user than I have to give everyone in the company access to the database.
Maybe there are alternatives for these last two? Let me know in the comments!
The post Why Managed Identities are Awesome first appeared on Under the kover of business intelligence.