I have done Azure AD authentication for my SQL database. For that I followed bellow steps.
I set the Azure AD admin for the SQL database in the portal
List item
get authentication Token
private static string GetAccessTokenAsync(string clientId, string clientSecret, string authority, string resource, string scope) { var authContext = new AuthenticationContext(authority, TokenCache.DefaultShared); var clientCred = new ClientCredential(clientId, clientSecret); var token = authContext.AcquireTokenAsync(resource, clientCred).Result.AccessToken; return token; }did the sql connection
string clientId = ConfigurationManager.AppSettings["ida:AADClientId"]; string clientSecret = ConfigurationManager.AppSettings["ida:AADAppKey"]; var authority = string.Format("https://login.microsoftonline.com/{0}", tenantId); var resource = "https://database.windows.net/"; var scope = ""; try { var token = GetAccessTokenAsync(clientId, clientSecret, authority, resource, scope); var builder = new SqlConnectionStringBuilder(); builder["Data Source"] = $"{dbServer}.database.windows.net"; builder["Initial Catalog"] = dbName; builder["Connect Timeout"] = 1500; builder["Persist Security Info"] = false; builder["TrustServerCertificate"] = false; builder["Encrypt"] = true; builder["MultipleActiveResultSets"] = false; SqlConnection con = new SqlConnection(builder.ToString()); con.AccessToken = token; return con; }DB Context Class
public partial class DBEntities : DbContext { //string dbConnectionString = string.Concat(ConfigurationManager.AppSettings["subdbconnectionstring"], '"', string.Format(ConfigurationManager.AppSettings["dbconnectionstring"], ConfigurationManager.AppSettings["DBPassword"]),'"'); //string test = ConfigurationManager.AppSettings["subdbconnectionstring"] + "\"" + ConfigurationManager.AppSettings["dbconnectionstring"]; public DBEntities(SqlConnection con) : base(con, true) { { Database.SetInitializer<DBEntities>(null); ((IObjectContextAdapter)this).ObjectContext.CommandTimeout = 1800; } }Finally connecting Database table
var con = AuthenticationHelper.GetSqlConnectionAsync(Constants.CDSDBServer, Constants.CDSDBDatabaseName); using (var dbContext = new DBEntities(con)) { var teamRolesList = await dbContext.TEAM_ROLE. Where(t=> t.IsDeleted.Equals(false)) .Select(t => new TeamRole { RoleId = t.RoleId, RoleName = t.RoleName, IsDeleted = t.IsDeleted, UserInput=t.UserInput,AllowMultiples=t.AllowMultiples }). ToListAsync();
}
Now I am getting error while connecting to Table,
The underlying provider failed on Open.Login failed for user '<token-identified principal>
at System.Data.Entity.Core.EntityClient.EntityConnection.<OpenAsync>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Core.Objects.ObjectContext.<EnsureConnectionAsync>d__9.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Core.Objects.ObjectContext.<ExecuteInTransactionAsync>d__3d`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task
task)
at System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.<ExecuteAsyncImplementation>d__9`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
at System.Data.Entity.Core.Objects.ObjectQuery`1.<GetResultsAsync>d__e.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Utilities.TaskExtensions.CultureAwaiter`1.GetResult()
at System.Data.Entity.Internal.LazyAsyncEnumerator`1.<FirstMoveNextAsync>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Data.Entity.Infrastructure.IDbAsyncEnumerableExtensions.<ForEachAsync>d__5`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
at CompanyDataService.Controllers.TeamController.<GetAllTeamRoles>d__0.MoveNext() in D:\sol\vs_project\DataService\DataService\Controllers\TeamController.cs:line 32