The Secure Future Initiative is Microsoft’s strategic framework for embedding security into every layer of the data platform—from infrastructure to identity. As part of this initiative, Microsoft Entra authentication for Azure SQL Database offers a modern, password less approach to access control that aligns with Zero Trust principles. By leveraging Entra identities, customers benefit from stronger security postures through multifactor authentication, centralized identity governance, and seamless integration with managed identities and service principals. Onboarding Entra authentication enables organizations to reduce reliance on passwords, simplify access management, and improve auditability across hybrid and cloud environments. With broad support across tools and platforms, and growing customer adoption, Entra authentication is a forward-looking investment in secure, scalable data access.

Migration Steps Overview

Organizations utilizing SQL authentication can strengthen database security by migrating to Entra Id-based authentication. The following steps outline the process.

1. Identify your logins and users – Review the existing SQL databases, along with all related users and logins, to assess what’s needed for migration.
2. Enable Entra auth on Azure SQL logical servers by assigning a Microsoft Entra admin.
3. Identify all permissions associated with the SQL logins & Database users.
4. Recreate SQL logins and users with Microsoft Entra identities.
5. Upgrade application drivers and libraries to min versions & update application connections to SQL Databases to use Entra based managed identities.
6. Update deployments for SQL logical server resources to have Microsoft Entra-only authentication enabled.
7. For all existing Azure SQL Databases, flip to Entra‑only after validation.
8. Enforce Entra-only for all Azure SQL Databases with Azure Policies (deny).

Step 1: Identify your logins and users - Use SQL Auditing

Consider using SQL Audit to monitor which identities are accessing your databases. Alternatively, you may use other methods or skip this step if you already have full visibility of all your logins.

Configure server‑level SQL Auditing. For more information on turning the server level auditing: Configure Auditing for Azure SQL Database series - part1 | Microsoft Community Hub

SQL Audit can be enabled on the logical server, which will enable auditing for all existing and new user databases. When you set up auditing, the audit log will be written to your storage account with the SQL Database audit log format.

Use sys.fn_get_audit_file_v2 to query the audit logs in SQL. You can join the audit data with sys.server_principals and sys.database_principals to view users and logins connecting to your databases.

The following query is an example of how to do this:

SELECT
(CASE WHEN database_principal_id > 0 THEN dp.type_desc ELSE NULL END) AS db_user_type
, (CASE WHEN server_principal_id > 0 THEN sp.type_desc ELSE NULL END) AS srv_login_type
, server_principal_name
, server_principal_sid
, server_principal_id
, database_principal_name
, database_principal_id
, database_name
, SUM(CASE WHEN succeeded = 1 THEN 1 ELSE 0 END) AS sucessful_logins
, SUM(CASE WHEN succeeded = 0 THEN 1 ELSE 0 END) AS failed_logins
FROM sys.fn_get_audit_file_v2(
    '<Storage_endpoint>/<Container>/<ServerName>',
    DEFAULT,
    DEFAULT,
    '2023-11-17T08:40:40Z',
    '2023-11-17T09:10:40Z')
-- join on database principals (users) metadata
LEFT OUTER JOIN sys.database_principals dp ON database_principal_id = dp.principal_id
-- join on server principals (logins) metadata
LEFT OUTER JOIN sys.server_principals sp ON server_principal_id = sp.principal_id 
-- filter to actions DBAF (Database Authentication Failed) and DBAS (Database Authentication Succeeded)
WHERE (action_id = 'DBAF' OR action_id = 'DBAS')
GROUP BY
  server_principal_name
, server_principal_sid
, server_principal_id
, database_principal_name
, database_principal_id
, database_name
, dp.type_desc
, sp.type_desc

 

Step 2: Enable Microsoft Entra authentication (assign admin)

Follow this to enable Entra authentication and assign a Microsoft Entra admin at the server. This is mixed mode; existing SQL auth continues to work.

WARNING: Do NOT enable Entra‑only (azureADOnlyAuthentications) yet. That comes in Step 7.

Entra admin Recommendation: For production environments, it is advisable to utilize an PIM Enabled Entra group as the server administrator for enhanced access control.

Step 3: Identity & document existing permissions (SQL Logins & Users)

• Retrieve a list of all your SQL auth logins. Make sure to run on the master database.:

SELECT * FROM sys.sql_logins

 

• List all SQL auth users, run the below query on all user Databases. This would list the users per Database.

SELECT * FROM sys.database_principals WHERE TYPE = 'S'

Note: You may need only the column ‘name’ to identify the users.

• List permissions per SQL auth user:

SELECT   database_principals.name 
       , database_principals.principal_id 
       , database_principals.type_desc 
       , database_permissions.permission_name 
       , CASE 
           WHEN class = 0 THEN 'DATABASE' 
           WHEN class = 3 THEN 'SCHEMA: ' + SCHEMA_NAME(major_id) 
           WHEN class = 4 THEN 'Database Principal: ' + USER_NAME(major_id) 
           ELSE OBJECT_SCHEMA_NAME(database_permissions.major_id) + '.' + OBJECT_NAME(database_permissions.major_id) 
           END AS object_name 
       , columns.name                    AS column_name 
       , database_permissions.state_desc AS permission_type 
FROM sys.database_principals             AS database_principals 
INNER JOIN sys.database_permissions      AS database_permissions   
    ON database_principals.principal_id = database_permissions.grantee_principal_id 
LEFT JOIN sys.columns                    AS columns   
    ON database_permissions.major_id = columns.object_id  
   AND database_permissions.minor_id = columns.column_id 
WHERE type_desc = 'SQL_USER' ORDER BY database_principals.name

 

Step 4: Create SQL users for your Microsoft Entra identities

You can create users(preferred) for all Entra identities.

Learn more on Create user The "FROM EXTERNAL PROVIDER" clause in TSQL distinguishes Entra users from SQL authentication users.

The most straightforward approach to adding Entra users is to use a managed identity for Azure SQL and grant the required three Graph API permissions. These permissions are necessary for Azure SQL to validate Entra users.

• User.Read.All: Allows access to Microsoft Entra user information.
• GroupMember.Read.All: Allows access to Microsoft Entra group information.
• Application.Read.ALL: Allows access to Microsoft Entra service principal (application) information.

For creating Entra users with non-unique display names, use Object_Id in the Create User TSQL:

-- Retrieve the Object Id from the Entra blade from the Azure portal.
CREATE USER [myapp4466e] FROM EXTERNAL PROVIDER WITH OBJECT_ID = 'aaaaaaaa-0000-1111-2222-bbbbbbbbbbbb'

 

For more information on finding the Entra Object ID: Find tenant ID, domain name, user object ID - Partner Center | Microsoft Learn

Alternatively, if granting these API permissions to SQL is undesirable, you may add Entra users directly using the T-SQL commands provided below. In these scenarios, Azure SQL will bypass Entra user validation.

Create SQL user for managed identity or an application -

This T-SQL code snippet establishes a SQL user for an application or managed identity. Please substitute the `MSIname` and `clientId` (note: use the client id, not the object id), variables with the Display Name and Client ID of your managed identity or application.

-- Replace the two variables with the managed identity display name and client ID
declare @MSIname sysname = '<Managed Identity/App Display Name>'
declare @clientId uniqueidentifier = '<Managed Identity/App Client ID>';

-- convert the guid to the right type and create the SQL user
declare @castClientId nvarchar(max) = CONVERT(varchar(max), convert (varbinary(16), @clientId), 1);

-- Construct command: CREATE USER [@MSIname] WITH SID = @castClientId, TYPE = E;
declare  nvarchar(max) = N'CREATE USER [' + @MSIname + '] WITH SID = ' + @castClientId + ', TYPE = E;'

EXEC (@cmd)

For more information on finding the Entra Client ID: Register a client application in Microsoft Entra ID for the Azure Health Data Services | Microsoft Learn

Create SQL user for Microsoft Entra user -

Use this T-SQL to create a SQL user for a Microsoft Entra account. Enter your username and object Id:

-- Replace the two variables with the MS Entra user alias and object ID
declare  sysname = '<MS Entra user alias>'; -- (e.g., username@contoso.com)
declare  uniqueidentifier = '<User Object ID>';

-- convert the guid to the right type
declare @castObjectId nvarchar(max) = CONVERT(varchar(max), convert (varbinary(16), ), 1);

-- Construct command: CREATE USER [@username] WITH SID = @castObjectId, TYPE = E;
declare  nvarchar(max) = N'CREATE USER [' +  + '] WITH SID = ' + @castObjectId + ', TYPE = E;'

EXEC (@cmd)

 

Create SQL user for Microsoft Entra group -

This T-SQL snippet creates a SQL user for a Microsoft Entra group. Set groupName and object Id to your values.

-- Replace the two variables with the MS Entra group display name and object ID
declare @groupName sysname = '<MS Entra group display name>'; -- (e.g., ContosoUsersGroup)
declare  uniqueidentifier = '<Group Object ID>';

-- convert the guid to the right type and create the SQL user
declare @castObjectId nvarchar(max) = CONVERT(varchar(max), convert (varbinary(16), ), 1);

-- Construct command: CREATE USER [@groupName] WITH SID = @castObjectId, TYPE = X;
declare  nvarchar(max) = N'CREATE USER [' + @groupName + '] WITH SID = ' + @castObjectId + ', TYPE = X;'

EXEC (@cmd)

For more information on finding the Entra Object ID: Find tenant ID, domain name, user object ID - Partner Center | Microsoft Learn

Validate SQL user creation -

When a user is created correctly, the EntraID column in this query shows the user's original MS Entra ID.

select CAST(sid as uniqueidentifier) AS EntraID, * from sys.database_principals

 

Assign permissions to Entra based users –

After creating Entra users, assign them SQL permissions to read or write by either using GRANT statements or adding them to roles like db_datareader. Refer to your documentation from Step 3, ensuring you include all necessary user permissions for new Entra SQL users and that security policies remain enforced.

Step 5: Update Programmatic Connections

Change your application connection strings to managed identities for SQL authentication and test each app for Microsoft Entra compatibility.

Upgrade your drivers to these versions or newer.

1. JDBC driver version 7.2.0 (Java)
2. ODBC driver version 17.3 (C/C++, COBOL, Perl, PHP, Python)
3. OLE DB driver version 18.3.0 (COM-based applications)
4. Microsoft.Data.SqlClient 5.2.2+ (ADO.NET)
5. Microsoft.EntityFramework.SqlServer 6.5.0 (Entity Framework)

 

• System.Data.SqlClient(SDS) doesn't support managed identity; switch to Microsoft.Data.SqlClient(MDS).
• If you need to port your applications from SDS to MDS the following cheat sheet will be helpful: https://github.com/dotnet/SqlClient/blob/main/porting-cheat-sheet.md.
• Microsoft.Data.SqlClient also takes a dependency on these packages & most notably the MSAL for .NET (Version 4.56.0+).

Here is an example of Azure web application connecting to Azure SQL, using managed identity.

Step 6: Validate No Local Auth Traffic

Be sure to switch all your connections to managed identity before you redeploy your Azure SQL logical servers with Microsoft Entra-only authentication turned on.

Repeat the use of SQL Audit, just as you did in Step 1, but now to confirm that every connection has moved away from SQL authentication.

Once your server is up and running with only Entra authentication, any connections still based on SQL authentication will not work, which could disrupt services.

Test your systems thoroughly to verify that everything operates correctly.

Step 7: Enable Microsoft Entra‑only & disable local auth

Once all your connections & applications are built to use managed identity, you can disable the SQL Authentication, by turning the Entra-only authentication via Azure portal, or using the APIs.

Step 8: Enforce at scale (Azure Policy)

Additionally, after successful migration and validation, it is recommended to deploy the built-in Azure Policy across your subscriptions to ensure that all SQL resources do not use local authentication. During resource creation, Azure SQL instances will be required to have Microsoft Entra-only authentication enabled.

This requirement can be enforced through Azure policies.

Best Practices for Entra-Enabled Azure SQL Applications

• Use exponential backoff with decorrelated jitter for retrying transient SQL errors, and set a max retry cap to avoid resource drain.
• Separate retry logic for connection setup and query execution.
• Cache and proactively refresh Entra tokens before expiration.
• Use Microsoft.Data.SqlClient v3.0+ with Azure.Identity for secure token management.
• Enable connection pooling and use consistent connection strings.
• Set appropriate timeouts to prevent hanging operations.
• Handle token/auth failures with targeted remediation, not blanket retries.
• Apply least-privilege identity principles; avoid global/shared tokens.
• Monitor retry counts, failures, and token refreshes via telemetry.
• Maintain auditing for compliance and security.
• Enforce TLS 1.2+ (Encrypt=True, TrustServerCertificate=False).
• Prefer pooled over static connections.
• Log SQL exception codes for precise error handling.
• Keep libraries and drivers up to date for latest features and resilience.

References

• Use this resource to troubleshoot issues with Entra authentication (previously known as Azure AD Authentication): Troubleshooting problems related to Azure AD authentication with Azure SQL DB and DW | Microsoft Community Hub
• To add Entra users from an external tenant, invite them as guest users to the Azure SQL Database's Entra administrator tenant. For more information on adding Entra guest users: Quickstart: Add a guest user and send an invitation - Microsoft Entra External ID | Microsoft Learn

Conclusion

Migrating to Microsoft Entra password-less authentication for Azure SQL Database is a strategic investment in security, compliance, and operational efficiency. By following this guide and adopting best practices, organizations can reduce risk, improve resilience, and future-proof their data platform in alignment with Microsoft’s Secure Future Initiative.