As part of migrating IIS virtual machines into Azure (IaaS) and migrating SQL Server databases into Azure SQL (DB or Managed Instances), we use the following steps so the websites can access their associated DBs without putting credentials into the web.config or appsettings.json files.
This is inline with the existing SQL connection strings using integrated authentication. The IIS website app pool runs under the service account, the service account is granted appropriate permissions to the SQL database, resulting in the website having access to the database.
To use Azure SQL, the connection string needs to change. Microsoft provides plenty of information on this new variant, but there are some catches and missing details. Likely because they are making it generic, not catering to IIS.
To test and determine an approach, I did the following steps:
- Make sure the Windows Active Directory (AD) is synchronizing to Azure Active Directory (AAD). Note the User Principal Name (UPN) Login (e.g., email@example.com).
- Make sure the server that is hosting IIS appears in Azure Active Directory as a device.
- Create an Azure SQL DB (within Azure SQL Server) in Azure Portal.
- Within Azure SQL Server, set the Active Directory admin. This can be a single user or a group. If this isn’t set, the following error appeared during connecting to SQL from ASP.NET:
Microsoft.Identity.Client.MsalServiceException: AADSTS900021: Requested tenant identifier ‘00000000-0000-0000-0000-000000000000’ is not valid. Tenant identifiers may not be an empty GUID.
- Using an AAD login, open the Azure SQL Server with SQL Server Management Studio (SSMS) to access your database and issue T-SQL commands. Alternatively, use Query editor (preview) in Azure Portal (again, use AAD authentication). Issue the following T-SQL commands, depending on security needs:
create user [<UPN Login Account>] from external provider;
alter role db_datareader add member [<UPN Login Account>];
alter role db_datawriter add member [<UPN Login Account>];
- Create a new Visual Studio .NET Framework 4.6.1 MVC project (it can be a newer .NET Framework, it can be Razor, it can be .NET Core 3.1 or .NET 5). I wanted to start with the oldest technology stack first.
- Upgrade System.Data.SqlClient references to use NuGet package Microsoft.Data.SqlClient v2.0.0 or higher (verified with v2.1.3). While the existing System.Data.SqlClient works with Active Directory Integrated security, it doesn’t federate with Azure Active Directory.
- Add (or change) the SQL Connection string:
Server=tcp:<azure-sql-server>.database.windows.net,1433; Authentication=Active Directory Integrated; Database=<azure-sql-db-name>;
- (Optional) Change the IIS App Pool to use the AD UPN, rather than AD NetBIOS account. Both account styles work, but the UPN will look more natural in matching the AAD account.
- If you encounter the error:
System.Data.SqlClient.SqlException (0x80131904): Unable to load adalsql.dll (Authentication=ActiveDirectoryIntegrated)We downloaded and installed the Active Directory Authentication Library for SQL Server (adalsql.msi) onto the IIS server.
- If you encounter the error:
AdalException: Integrated Windows authentication supported only in federation flow
Make sure you are referencing Microsoft.Data.SqlClient instead of System.Data.SqlClient library for operations such as SqlConnection, SqlCommand, etc.