Cannot connect to Azure SQL using Authentication=Active Directory Default

[tuol]

I want to migrate how my local machine connects to the SQL database on Azure. Instead of having a connection string that has a SQL username + password, I'd like to authenticate using Azure Directory.

When I change my Settings.ConnectionString to: Settings.ConnectionString = "Server=tcp:<server-name>.database.windows.net,1433;Initial Catalog=<database-name>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Default;";

The output is:

// ------------------------------------------------------------------------------------------------
// WARNING: Failed to load provider "System.Data.SqlClient" - Invalid value for key 'authentication'.
// Allowed providers:
//    "System.Data.Odbc"
//    "System.Data.OleDb"
//    "System.Data.OracleClient"
//    "System.Data.SqlClient"
//    "Microsoft.SqlServerCe.Client.4.0"
//    "Microsoft.Data.SqlClient"

Whilst working on migrating other parts of our infrastructure (NET 6 & NET Framework 4.8), I've seen this error when System.Data.SqlClient was used to connect to the database or when an older version of Microsoft.Data.SqlClient was used.

My assumption is that Reverse POCO is using System.Data.SqlClient as a provider to generate the model.

Is there perhaps some way to change this provider to Microsoft.Data.SqlClient?

[sjh37]

This works for me:

Server=tcp:<server-name>.database.windows.net,1433;Initial Catalog=<db-name>;Persist Security Info=False;User ID=<user>;Password=<password>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

However, I need to update the SQL to reverse engineer Azure stored procedures as I received an error to do with a UNION.

[sjh37]

Released SQL fix for Azure stored procs in v3.8.4

[tuol]

Thanks for responding so quickly!

In your suggestion, you mention a connection string that uses a SQL UserId + Password to connect. However, this is exactly what I want to move away from and Connect to SQL by using AD identities instead.

FYI: On my development machine, it means that I am signed into Visual Studio with my Microsoft Account, which has an associated Azure AD identity on the Azure Active Directory in my subscription. This identity has in turn access to the SQL database on Azure.

When I connect using the connection string (note the Authentication=Active Directory Default part), Visual Studio requests a token for authentication to the Azure SQL Database.

"Server=tcp:<server-name>.database.windows.net,1433;Initial Catalog=<database-name>;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication=Active Directory Default;"

Basically, it means that I don't need to know about a SQL UserId / Password as a developer / in my configuration after deployment on an App Service.

This works like a charm for connections I make from within my application, using the most recent version of Microsoft.Data.SqlClient. And it's where I'm stumped with reverse POCO; it seems System.Data.SqlClient is used as a provider? Is this something that I can configure differently?

[morelockc]

I'm trying to do the same thing as @tuol, getting the same warning/error message.

[morelockc]

@tuol, it worked for me using connection string like this:

Settings.ConnectionString = "Server=tcp:my-server-name.database.windows.net,1433;Initial Catalog=my-database-name;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Integrated;";

[tuol]

@morelockc, thanks, unfortunately no success. I still get the same error in the generated model, using v3.9.0

// ------------------------------------------------------------------------------------------------
// WARNING: Failed to load provider "System.Data.SqlClient" - One or more errors occurred.
// Allowed providers:
//    "System.Data.Odbc"
//    "System.Data.OleDb"
//    "System.Data.OracleClient"
//    "System.Data.SqlClient"
//    "Microsoft.SqlServerCe.Client.4.0"
//    "Microsoft.Data.SqlClient"

/*   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
   at System.Threading.Tasks.Task.Wait(Int32 millisecondsTimeout, CancellationToken cancellationToken)
   at System.Data.SqlClient.SqlInternalConnectionTds.GetFedAuthToken(SqlFedAuthInfo fedAuthInfo)
   at System.Data.SqlClient.SqlInternalConnectionTds.OnFedAuthInfo(SqlFedAuthInfo fedAuthInfo)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.VisualStudio.TextTemplatingFF618B039FE57746C4AC78586738C270E834F81B4ED02A500576198435840FB5241D236190AE7F7866CE119088D4BA861C2332B3EE33CC9301449D2E2AF2847E.GeneratedTextTransformation.DatabaseReader.Init() in C:\****\../EF.Reverse.POCO.v3.ttinclude:line 12524
   at Microsoft.VisualStudio.TextTemplatingFF618B039FE57746C4AC78586738C270E834F81B4ED02A500576198435840FB5241D236190AE7F7866CE119088D4BA861C2332B3EE33CC9301449D2E2AF2847E.GeneratedTextTransformation.SqlServerDatabaseReader.Init() in C:\*****\../EF.Reverse.POCO.v3.ttinclude:line 16447
   at Microsoft.VisualStudio.TextTemplatingFF618B039FE57746C4AC78586738C270E834F81B4ED02A500576198435840FB5241D236190AE7F7866CE119088D4BA861C2332B3EE33CC9301449D2E2AF2847E.GeneratedTextTransformation.Generator.Init(DatabaseReader databaseReader, String singleDbContextSubNamespace) in C:\****\../EF.Reverse.POCO.v3.ttinclude:line 4272
   at Microsoft.VisualStudio.TextTemplatingFF618B039FE57746C4AC78586738C270E834F81B4ED02A500576198435840FB5241D236190AE7F7866CE119088D4BA861C2332B3EE33CC9301449D2E2AF2847E.GeneratedTextTransformation.GeneratorFactory.Create(FileManagementService fileManagementService, Type fileManagerType, String singleDbContextSubNamespace) in C:\***\Model\../EF.Reverse.POCO.v3.ttinclude:line 6506*/
// ------------------------------------------------------------------------------------------------

I am wondering if my account isn't passed correctly to Active Directory: In my case, I run the T4 template from Visual Studio (latest) . I'm signed into Visual Studio with the same account that has (admin) access to the SQL database on Azure. Is this also your setup?

[sjh37]

I too get the same error using:

"Server=tcp:<dbserver>.database.windows.net,1433;Initial Catalog=<dbname>;Encrypt=True;
TrustServerCertificate=False;Connection Timeout=30;Authentication=\"Active Directory Default\";"

I'm not authenticated, and simply changed the connection string. Will investigate.