asp.net core application connectivity with Azure SQL database using service account with azure active directory password

Question

Hi

We are working on asp.net core application. We are not able to connect to Azure SQL database using service account with azure active directory password authentication.
Getting error that authentication keyword is not supported.
Is there any way to use service account with azure active directory password to access azure SQL database?
Or managed identity is only the way to access database in asp.net core mvc application?

Answer 1

@Kushwah, Shishanki Welcome to Microsoft Q&A forums.

Make sure you are using the latest version of Microsoft.Data.SqlClient
Specify the connection string in the below format

var connectionString = @"Data Source=YOURSERVERNAME.database.windows.net; Authentication=Active Directory Password; Initial Catalog=YOURDATABASENAME;  UID=XYZ@contoso.onmicrosoft.com; PWD=*************";  
  
SqlConnection conn = new SqlConnection(connectionString);  
conn.Open();  

Do let us know if you still face any issues.

----------

If an answer is helpful, please "Accept answer" or "Up-Vote" which might help other community members reading this thread.

Answer 2

Seeing below error while connecting to Azure SQL DB from .NET Core 3.1 with Active Dorectory Password Authentication. Microsoft.Data.SqlClient" Version is 2.1.3.
Same is working fine in .NET Framework 4.7.2. Can you please suggest what is the issue with .NET Core?
Unhandled exception. System.AggregateException: One or more errors occurred. (One or more errors occurred. (One or more errors occurred. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)))
---> System.AggregateException: One or more errors occurred. (One or more errors occurred. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.))
---> System.AggregateException: One or more errors occurred. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)
---> System.Net.Http.HttpRequestException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
---> System.Net.Sockets.SocketException (10060): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
at System.Net.Http.ConnectHelper.ConnectAsync(String host, Int32 port, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at System.Net.Http.ConnectHelper.ConnectAsync(String host, Int32 port, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.ConnectAsync(HttpRequestMessage request, Boolean allowHttp2, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.CreateHttp11ConnectionAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.GetHttpConnectionAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.SendWithRetryAsync(HttpRequestMessage request, Boolean doRequestAuth, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.EstablishProxyTunnel(HttpRequestHeaders headers, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.ConnectAsync(HttpRequestMessage request, Boolean allowHttp2, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.CreateHttp11ConnectionAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.GetHttpConnectionAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpConnectionPool.SendWithRetryAsync(HttpRequestMessage request, Boolean doRequestAuth, CancellationToken cancellationToken)
at System.Net.Http.AuthenticationHelper.SendWithAuthAsync(HttpRequestMessage request, Uri authUri, ICredentials credentials, Boolean preAuthenticate, Boolean isProxyAuth, Boolean doRequestAuth, HttpConnectionPool pool, CancellationToken cancellationToken)
at System.Net.Http.RedirectHandler.SendAsync(HttpRequestMessage request, CancellationToken cancellationToken)
at System.Net.Http.HttpClient.FinishSendAsyncBuffered(Task1 sendTask, HttpRequestMessage request, CancellationTokenSource cts, Boolean disposeCts) at Microsoft.Identity.Client.Http.HttpManager.ExecuteAsync(Uri endpoint, IDictionary2 headers, HttpContent body, HttpMethod method)
at Microsoft.Identity.Client.Http.HttpManager.ExecuteWithRetryAsync(Uri endpoint, IDictionary2 headers, HttpContent body, HttpMethod method, ICoreLogger logger, Boolean doNotThrow, Boolean retry) at Microsoft.Identity.Client.Http.HttpManager.SendGetAsync(Uri endpoint, IDictionary2 headers, ICoreLogger logger)
at Microsoft.Identity.Client.OAuth2.OAuth2Client.ExecuteRequestAsyncT
at Microsoft.Identity.Client.OAuth2.OAuth2Client.DiscoverAadInstanceAsync(Uri endPoint, RequestContext requestContext)
at Microsoft.Identity.Client.Instance.Discovery.NetworkMetadataProvider.SendInstanceDiscoveryRequestAsync(Uri authority, RequestContext requestContext)
at Microsoft.Identity.Client.Instance.Discovery.NetworkMetadataProvider.FetchAllDiscoveryMetadataAsync(Uri authority, RequestContext requestContext)
at Microsoft.Identity.Client.Instance.Discovery.NetworkMetadataProvider.GetMetadataAsync(Uri authority, RequestContext requestContext)
at Microsoft.Identity.Client.Instance.Discovery.InstanceDiscoveryManager.FetchNetworkMetadataOrFallbackAsync(RequestContext requestContext, Uri authorityUri)
at Microsoft.Identity.Client.Instance.Discovery.InstanceDiscoveryManager.GetMetadataEntryAsync(String authority, RequestContext requestContext)
at Microsoft.Identity.Client.Instance.AuthorityEndpoints.UpdateAuthorityWithPreferredNetworkHostAsync(AuthenticationRequestParameters requestParameters)
at Microsoft.Identity.Client.Instance.AuthorityEndpoints.UpdateAuthorityEndpointsAsync(AuthenticationRequestParameters requestParameters)
at Microsoft.Identity.Client.Internal.Requests.RequestBase.ResolveAuthorityEndpointsAsync()
at Microsoft.Identity.Client.Internal.Requests.UsernamePasswordRequest.ExecuteAsync(CancellationToken cancellationToken)
at Microsoft.Identity.Client.Internal.Requests.RequestBase.RunAsync(CancellationToken cancellationToken)
at Microsoft.Identity.Client.ApiConfig.Executors.PublicClientExecutor.ExecuteAsync(AcquireTokenCommonParameters commonParameters, AcquireTokenByUsernamePasswordParameters usernamePasswordParameters, CancellationToken cancellationToken)
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task1.GetResultCore(Boolean waitCompletionNotification) at System.Threading.Tasks.Task1.get_Result()
at Microsoft.Data.SqlClient.ActiveDirectoryAuthenticationProvider.<>c__DisplayClass17_0.<<AcquireTokenAsync>b__0>d.MoveNext()
--- End of inner exception stack trace ---
at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)
at System.Threading.Tasks.Task1.GetResultCore(Boolean waitCompletionNotification) at System.Threading.Tasks.Task1.get_Result()
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.<>c__DisplayClass146_1.<GetFedAuthToken>b__3()
at System.Threading.Tasks.Task1.InnerInvoke() at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj) at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location where exception was thrown --- at System.Threading.ExecutionContext.RunFromThreadPoolDispatchLoop(Thread threadPoolThread, ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of inner exception stack trace --- at Microsoft.Data.ProviderBase.DbConnectionPool.CheckPoolBlockingPeriod(Exception e) at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnectionOptions userOptions) at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry, SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
at Microsoft.Data.SqlClient.SqlConnection.Open()