Clash Royale CLAN TAG#URR8PPP

Authenticate ASP.NET Core 2.1 Web API with Azure Active Directory and Azure SQL with Entity Framework

I have implemented Azure AD authentication on an Azure SQL database and want to write a Web API for it which uses the same AD authentication, but I need to pass through that authentication from the Web API to the Azure SQL connection. We are also looking to use the Entity Framework with this project.

The UseSqlServer function in the Web API's Startup class passes through a connection string with authentication details, so I suppose a solution around this would be to somehow make the connection dynamic based on the user authenticated with the Web API.

UseSqlServer

Here is a more graphical map of what we're looking at:

| azure active directory |

|| ||
/ /

client ------> | web api | ------> | azure sql |


What is the best way to go about this?

1 Answer
1

Have you considered using a token for authentication? You will find a sample application here.

using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TodoListService.DAL


public class PerWebUserCache

[Key]
public int EntryId get; set;
public string webUserUniqueId get; set;
public byte cacheBits get; set;
public DateTime LastWrite get; set;


public class DbTokenCache : TokenCache

private TodoListServiceContext db = new TodoListServiceContext();
string User;
PerWebUserCache Cache;

// constructor
public DbTokenCache(string user)

// associate the cache to the current user of the web app
User = user;

this.AfterAccess = AfterAccessNotification;
this.BeforeAccess = BeforeAccessNotification;
this.BeforeWrite = BeforeWriteNotification;

// look up the entry in the DB
Cache = db.PerUserCacheList.FirstOrDefault(c => c.webUserUniqueId == User);
// place the entry in memory
this.Deserialize((Cache == null) ? null : Cache.cacheBits);


// clean up the DB
public override void Clear()

base.Clear();
foreach (var cacheEntry in db.PerUserCacheList.Where(u => u.webUserUniqueId == User).ToList())
db.PerUserCacheList.Remove(cacheEntry);
db.SaveChanges();


// Notification raised before ADAL accesses the cache.
// This is your chance to update the in-memory copy from the DB, if the in-memory version is stale
void BeforeAccessNotification(TokenCacheNotificationArgs args)

if (Cache == null)

// first time access
Cache = db.PerUserCacheList.FirstOrDefault(c => c.webUserUniqueId == User);

else
// retrieve last write from the DB
var status = from e in db.PerUserCacheList
where (e.webUserUniqueId == User)
select new

LastWrite = e.LastWrite
;
// if the in-memory copy is older than the persistent copy
if (status.First().LastWrite > Cache.LastWrite)
//// read from from storage, update in-memory copy

Cache = db.PerUserCacheList.FirstOrDefault(c => c.webUserUniqueId == User);


this.Deserialize((Cache == null) ? null : Cache.cacheBits);

// Notification raised after ADAL accessed the cache.
// If the HasStateChanged flag is set, ADAL changed the content of the cache
void AfterAccessNotification(TokenCacheNotificationArgs args)

// if state changed
if (this.HasStateChanged)

Cache = new PerWebUserCache

webUserUniqueId = User,
cacheBits = this.Serialize(),
LastWrite = DateTime.Now
;
//// update the DB and the lastwrite
db.Entry(Cache).State = Cache.EntryId == 0 ? EntityState.Added : EntityState.Modified;
db.SaveChanges();
this.HasStateChanged = false;


void BeforeWriteNotification(TokenCacheNotificationArgs args)

// if you want to ensure that no concurrent write take place, use this notification to place a lock on the entry





By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.