A few years ago Microsoft started rolling out "managed identity" support to various Azure services. The idea was that you could create identities in Entra ID (at the time, Azure Active Directory) that a service ran as. Other services could then use Identity and Access Management (IAM) roles and permissions to grant specific rights to that identity.
The benefits to this are not only that it means you don't need to ship secrets to the "client" service, but that you invert the way the trust works - now, the providing service has to explicitly trust a specific identity to use it. And if you decide to revoke permissions for that service, you can do it without impacting any others. I wrote about this at the time, as well as on the official Azure devblog, illustrating how Farmer made this very easy to do.
Creating SQL and App resources in Farmer
SQL is another popular service in Azure, and although it has had support for its own managed identity, it isn't especially clear how you can set up SQL to trust another service's managed identity as a means of authentication. Luckily, the newest version of Farmer makes this as simple as possible.
Let's assume we're building an App Service and a SQL Azure instance - a popular scenario within the .NET world. Creating the resources for this in Farmer itself is very easy:
open Farmeropen Farmer.Buildersopen Farmer.Sqllet app = webApp { name "yourwebapp" system_identity operating_system Linux}// Get the Object ID of the userlet myEntraAccount = AccessPolicy.findUsers [ "myentraid@mycompany.com" ] |> Array.headlet theServer = sqlServer { name "yoursqlserver" entra_id_admin_user "myentraid@mycompany.com" myEntraAccount.Id // using the ObjectID of the retrieved user add_databases [ sqlDb { name "yoursqldb" sku (GeneralPurpose Gen5_2) } ]}If you've used Farmer before, this will be quite familiar to you. The main differences may be:
- The use of
system_identity. This turns on an automatic identity for the app service that we can use later. - The use of
entra_id_admin_user. Instead of a conventional admin username / password, we supply an identity of a user in Entra ID that acts as the admin; you could also supply an Entra ID group e.g."SQL Server Administrators".
That's enough to create the SQL and App Service instances as well turn on Entra ID authentication in SQL (and disable standard username / password access!).
Farmer is smart enough to only disable username/password access if you don't specify one using the
admin_usernamekeyword. If you do, it'll allow both authentication mechanisms.
Building the trust from SQL to App Service
However, you may have noticed in this example that nowhere do we actually "connect" the App Service to the SQL Server. So, how does this work? This is where we hit the limits of what ARM Templates can do: establishing the trust requires us to actually run SQL on the newly-deployed database to add a user to SQL and put them into specific roles. The following SQL script would do just that:
CREATE USER [yourwebapp] FROM EXTERNAL PROVIDERALTER ROLE db_datareader ADD MEMBER [yourwebapp];ALTER ROLE db_datawriter ADD MEMBER [yourwebapp];There are several ways you can create users in SQL. In this example we're using Contained Users, which are simple to create and administer, and live exclusively within the database itself.
This is all that's required - SQL Azure understands that [yourwebapp] is an external identity for an Azure service and does the rest for us. Of course, you may elect to have more fine-grained custom roles in your database for the app to use!
You could elect to put the System Identity (or other Managed Identity) into the same group that you created as an administrator of the SQL server, which would eliminate the need for running this script. However, this would mean that this identity would be an administrator to any other SQL servers that granted this group admin rights! And, you've now lost the fine-grained control that we have with the above script.
Creating a SQL connection in .NET
You may be asking yourself "but how do I connect to SQL without a username / password to grant the system identity access?". Well, as long as you (or whoever is running the script) is in the group that you defined earlier with the entra_id_admin_user or entra_id_admin_group keywords, you can connect without a username / password as you're a system administrator of the SQL instance!
We can connect to SQL using any well-known .NET SQL library, whether that's Microsoft.Data.SqlClient or Dapper or anything else really, except our connection string looks slightly different:
let connectionString = "Server=yoursqlserver.database.windows.net;Database=yoursqldb;Authentication=Active Directory Default;"Notice the Authentication = Active Directory Default, which makes use of the Azure.Identity package to figure out the identity to use when connecting to SQL, including the system identity if running on an enabled service in Azure (such as App Service). If you're running locally, you might even be asked to authenticate into Azure interactively so that the Identity package can use that identity.
Summary
Although there is a lot of documentation out there on this subject, not all of it makes it clear exactly what is required - or only covers a part of this story. Hopefully, this article makes it clear end-to-end how to connect to SQL securely from within Azure without exposing username / password credentials. Enjoy!
