How to open a System.Data.SQLClient.SQLConnection with Active Directory Universal Authentication

Question

I was using the below code to connect to SQL Azure DB that was using Active Directory Integrated Authentication.

private string GenerateConnectionString(string databaseName, string serverName)
{
    SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder();
    sqlConnectionBuilder.DataSource = string.Format(
        "tcp:{0}.database.windows.net",
        serverName);
    connBuilder.InitialCatalog = databaseName;
    connBuilder.Authentication = SqlAuthenticationMethod.ActiveDirectoryIntegrated;
    connBuilder.ConnectTimeout = 30;
    return connBuilder.ConnectionString;
}

The authentication is changed from Active Directory Integrated Authentication to Active Directory Universal Authentication to support multi-factor authentication.

I see the enumeration System.Data.SqlClient.SqlAuthenticationMethod doesn't have a value for Active Directory Universal Authentication. Is it possible to still use the System.Data.SqlClient to connect to the DB? If yes, what is the change I have to do in the code?

score 3 · Answer 1 · answered Apr 20 '18 at 20:31

3

ActiveDirectoryInteractive authentication method is available since the .NET Framework 4.7.2. Not sure if it is the same as "Universal" or not.

answered Apr 20 '18 at 20:31

Andrey Belykh

2,578
4
32
46

.NET Framework 4.7.2 was not released when this question was first asked. Also, Active Directory Universal Authentication is indeed the same as ActiveDirectoryInteractive. See this: https://learn.microsoft.com/en-us/dotnet/framework/whats-new/index#sql472 – Loren Paulsen May 01 '18 at 05:04

score 0 · Answer 2 · answered Aug 31 '20 at 08:28

As of August 2020, there's a better way to connect to Azure SQL DB's or Azure Synapse DW (SQL pools). By using the MSOLEDBSQL driver (which you may redistribute along with your application), your application can perform interactive/MFA authentication using the normal System.Data.OleDb objects:

using System.Data.OleDb;
...
OleDbConnection con = new OleDbConnection("Provider=MSOLEDBSQL;Data Source=sqlserver.database.windows.net;User ID=user@domain.com;Initial Catalog=database;Authentication=ActiveDirectoryInteractive");

In fact, this is the recommended way of connecting to any Microsoft SQL product programatically, as both SQLOLEDB and SQLNCLI (aka. "SNAC") have been deprecated.

How to open a System.Data.SQLClient.SQLConnection with Active Directory Universal Authentication

2 Answers2