Muokkaa

Jaa

Connect to Azure SQL Database with Microsoft Entra multifactor authentication

  • Artikkeli

Applies to: Azure SQL Database SQL database in Fabric

This article provides a C# program that connects to Azure SQL Database. The program uses interactive mode authentication, which supports multifactor authentication using Microsoft Entra ID (formerly Azure Active Directory).

For more information about multifactor authentication support for SQL tools, see Using Microsoft Entra multifactor authentication.

Note

Microsoft Entra ID was previously known as Azure Active Directory (Azure AD).

Multifactor authentication for Azure SQL Database

Active Directory Interactive authentication supports multifactor authentication using Microsoft.Data.SqlClient to connect to Azure SQL data sources. In a client C# program, the enum value directs the system to use the Microsoft Entra interactive mode that supports multifactor authentication to connect to Azure SQL Database. The user who runs the program sees the following dialog boxes:

  • A dialog box that displays a Microsoft Entra user name and asks for the user's password.

    If the user's domain is federated with Microsoft Entra ID, the dialog box doesn't appear, because no password is needed.

    If the Microsoft Entra policy imposes multifactor authentication on the user, a dialog box to sign in to your account will display.

  • The first time a user goes through multifactor authentication, the system displays a dialog box that asks for a mobile phone number to send text messages to. Each message provides the verification code that the user must enter in the next dialog box.

  • A dialog box that asks for a multifactor authentication verification code, which the system has sent to a mobile phone.

For information about how to configure Microsoft Entra ID to require multifactor authentication, see Getting started with Microsoft Entra multifactor authentication in the cloud.

For screenshots of these dialog boxes, see Using Microsoft Entra multifactor authentication.

Tip

You can search .NET Framework APIs with the .NET API Browser tool page.

You can also search directly with the optional ?term=<search value> parameter.

Prerequisite

Before you begin, you should have a logical SQL server created and available.

Set a Microsoft Entra admin for your server

For the C# example to run, a logical server admin needs to assign a Microsoft Entra admin from Microsoft Entra ID for your server.

In the Azure portal, on the SQL server page, select Microsoft Entra ID from the resource menu, then select Set admin.

For more information about Microsoft Entra admins and users for Azure SQL Database, see the screenshots in Configure and manage Microsoft Entra authentication with SQL Database.

Microsoft.Data.SqlClient

The C# example relies on the Microsoft.Data.SqlClient namespace. For more information, see Using Microsoft Entra authentication with SqlClient.

Note

System.Data.SqlClient uses the Azure Active Directory Authentication Library (ADAL), which is deprecated. If you're using the System.Data.SqlClient namespace for Microsoft Entra authentication, migrate applications to Microsoft.Data.SqlClient and the Microsoft Authentication Library (MSAL). For more information about using Microsoft Entra authentication with SqlClient, see Using Microsoft Entra authentication with SqlClient.

Verify with SQL Server Management Studio

Before you run the C# example, it's a good idea to check that your setup and configurations are correct in SQL Server Management Studio (SSMS). Any C# program failure can then be narrowed to source code.

Verify server-level firewall IP addresses

Run SSMS from the same computer, in the same building, where you plan to run the C# example. For this test, any Authentication mode is OK. If there's any indication that the server isn't accepting your IP address, see server-level and database-level firewall rules for help.

Verify Microsoft Entra multifactor authentication

Run SSMS again, this time with Authentication set to Azure Active Directory - Universal with MFA. This option requires SSMS version 18.6 or later.

For more information, see Using Microsoft Entra multifactor authentication.

Note

For SSMS versions prior to 18.x, guest users must provide the Microsoft Entra domain name or tenant ID for the database: Select Options > AD domain name or tenant ID. SSMS 18.x and later automatically recognizes the tenant.

To find the domain name in the Azure portal, select Microsoft Entra ID > Custom domain names. In the C# example program, providing a domain name is not necessary.

C# code example

Note

If you are using .NET Core, you will want to use the Microsoft.Data.SqlClient namespace. For more information, see the following blog.

This is an example of C# source code.


using System;
using Microsoft.Data.SqlClient;

public class Program
{
    public static void Main(string[] args)
    {
        // Use your own server, database, and user ID.
        // Connetion string - user ID is not provided and is asked interactively.
        string ConnectionString = @"Server=<your server>.database.windows.net; Authentication=Active Directory Interactive; Database=<your database>";


        using (SqlConnection conn = new SqlConnection(ConnectionString))

        {
            conn.Open();
            Console.WriteLine("ConnectionString2 succeeded.");
            using (var cmd = new SqlCommand("SELECT @@Version", conn))
            {
                Console.WriteLine("select @@version");
                var result = cmd.ExecuteScalar();
                Console.WriteLine(result.ToString());
            }

        }
        Console.ReadKey();

    }
}

 

This is an example of the C# test output.

ConnectionString2 succeeded.
select @@version
Microsoft SQL Azure (RTM) - 12.0.2000.8
   ...

Next steps