How to create external tables in Azure SQL Database using Managed Identities (Azure AD authentication)

Question

Hi, I have one Azure SQL Server (fully managed offering from Azure) which contains multiple databases. As per security best practices "Microsoft Entra authentication only" is enabled, SQL authentication is disabled. Applications use managed identities to access the database. I have a requirement where I need to access tables in Database B from Database A. I want to use external tables functionality. Is it possible to use this functionality with managed identities.

Answer 1

Hey, Unfortunately based on my understanding cross database queries in Azure SQL database is possible via the concept of elastic query and elastic query external table as of now supports only SQL Auth and not AD related auth

Reference : https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-overview?view=azuresql

Answer 2

Hello @RajivBansal-2486 Yes, it is possible to use managed identities to access tables in different databases within an Azure SQL Server. However, the process can be complex and might require some additional steps. Here’s a high-level overview of how you might achieve this:

1. Create a Contained User: This user represents the managed identity in the database. You can create this user using Microsoft SQL Server Management Studio (SSMS).
2. Grant Access: To grant your managed identity access to a database in Azure SQL Database, you can use an existing logical SQL server or create a new one. There are two steps to granting your managed identity access to a database: Enable Microsoft Entra authentication for the server. Create a contained user in the database that represents the managed identity.
3. Use Managed Identities to Access the Database: You can use system-assigned managed identity (SMI) and user-assigned managed identity (UMI) to access the database by using the SQL connection string option Authentication=Active Directory Managed Identity. You need to create a SQL user from the managed identity in the target database by using the CREATE USER statement.
4. Create External Tables: Once you have established a connection using managed identities, you can then create external tables in Database A that reference tables in Database B.

Please note that you need to have the necessary permissions to perform these actions. If you need assistance with role assignment, see Assign Azure roles to manage access to your Azure subscription resources. However, it’s important to note that while this process is technically possible, it may not be officially supported by Microsoft. Therefore, it’s recommended to test this thoroughly in a non-production environment before implementing it in a production scenario. It’s also a good idea to stay updated with Microsoft’s documentation as they frequently update their features and services. I hope this answers your question.