I think the best recommendation is to have a modern SqlClient, which has built in support for managed identity. No custom provider necessary.
/G On Sun, May 23, 2021 at 8:38 PM Paulo Quicoli <[email protected]> wrote: > Jorge > > That's great! Thank you for sharing your solution! > > On Sun, 23 May 2021, 19:17 Jorge Caballero, <[email protected]> > wrote: > >> Hi Quicoli, Gunnar: >> >> Thank you so much for replying. I followed your recommendations and I was >> able to implement a custom provider. Let me share with you the Custom >> Provider approach's code: >> >> public class SqlAzureConnectionProvider : >> NHibernate.Connection.DriverConnectionProvider >> { >> private readonly NLog.Logger Logger = >> NLog.LogManager.GetLogger("Default"); >> >> public override DbConnection GetConnection() >> { >> SqlConnection connection = (SqlConnection) Driver.CreateConnection(); >> >> try >> { >> string accessToken = GetAccessToken(); >> >> connection.ConnectionString = >> System.Configuration.ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString; >> connection.AccessToken = accessToken; >> connection.Open(); >> } >> catch (Exception) >> { >> connection.Dispose(); >> throw; >> } >> //return base.GetConnection(); >> return connection; >> } >> >> private string GetAccessToken() >> { >> string accessToken; >> if (System.Configuration.ConfigurationManager.AppSettings["Environment"] >> == "Development") >> { >> Logger.Info("Development - Reading credential fom web.config file"); >> >> // For local debugging it will use the AppReg credentials to generate the >> Access Token >> string clientId = >> System.Configuration.ConfigurationManager.AppSettings["client_id"]; >> string aadTenantId = >> System.Configuration.ConfigurationManager.AppSettings["tenant_id"]; >> string clientSecretKey = >> System.Configuration.ConfigurationManager.AppSettings["client_secret"]; >> >> string AadInstance = "https://login.windows.net/{0}";; >> string ResourceId = "https://database.windows.net/";; >> >> AuthenticationContext authenticationContext = new >> AuthenticationContext(string.Format(AadInstance, aadTenantId)); >> ClientCredential clientCredential = new ClientCredential(clientId, >> clientSecretKey); >> >> AuthenticationResult authenticationResult = >> authenticationContext.AcquireTokenAsync(ResourceId, >> clientCredential).Result; >> accessToken = authenticationResult.AccessToken; >> Logger.Info("Development - Access Token generated"); >> } >> else >> { >> // On Production Release, Virtual Machine Managed Identity will be used >> to generate the Access Token >> Logger.Info("Generating Managed Identity-based Access Token"); >> Logger.Info(@"Hitting Azure Instance Metadata Service endpoint: >> http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F >> "); >> >> HttpWebRequest webRequest = (HttpWebRequest)WebRequest.Create(@" >> http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F >> "); >> webRequest.Headers["Metadata"] = "true"; >> webRequest.Method = "GET"; >> >> try >> { >> HttpWebResponse webResponse = (HttpWebResponse)webRequest.GetResponse(); >> Logger.Info("Azure Instance Metadata Service REST API status code: {0}", >> webResponse.StatusCode); >> >> StreamReader streamResponse = new >> StreamReader(webResponse.GetResponseStream()); >> string stringResponse = streamResponse.ReadToEnd(); >> JObject jsonResponse = JObject.Parse(stringResponse); >> >> accessToken = jsonResponse["access_token"].Value<string>(); >> Logger.Info("Access Token generated"); >> } >> catch (Exception exc) >> { >> Logger.Error(exc, "Error by generatin Access Token."); >> throw; >> } >> } >> >> return accessToken; >> } >> } >> >> I hope this implementation can help other users... >> El sábado, 15 de mayo de 2021 a las 7:43:36 UTC-3, Gunnar Liljas escribió: >> >>> https://github.com/nhibernate/nhibernate-core/discussions/2694 >>> >>> On Fri, May 14, 2021 at 7:54 PM Jorge Caballero <[email protected]> >>> wrote: >>> >>>> Hi everyone, >>>> >>>> I'm trying to connect to Azure SQL Database by using VM Managed >>>> Identity but I get this error during the SessionFactory creation: >>>> >>>> System.Data.SqlClient.SqlException: 'Login failed for user ''.' >>>> >>>> The connection string is: >>>> >>>> Data Source=tcp:customproject.database.windows.net,1433;Initial >>>> Catalog=custom-db;Persist Security Info=False;Connect >>>> Timeout=30;Encrypt=True;TrustServerCertificate=False >>>> >>>> Code: >>>> // Creating NHibernate session factory >>>> hibernateConfig.DataBaseIntegration(db => >>>> { >>>> db.Driver<SqlAzureClientDriver>(); >>>> db.ConnectionStringName = "DBConnectionString"; >>>> //db.ConnectionString = "Server=tcp: >>>> ukspprddnssql01.database.windows.net,1433;Initial >>>> Catalog=CAPO-DB;Persist Security Info=False;User >>>> ID=dnsrestapi;Password=Pa$$w0rd.dns;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection >>>> Timeout=30;"; >>>> db.Dialect<MsSqlAzure2008Dialect>(); >>>> db.BatchSize = 20; >>>> db.LogFormattedSql = true; >>>> }) >>>> .AddMapping(domainMapping); >>>> >>>> return hibernateConfig.BuildSessionFactory(); // ==> Here the exception >>>> is thrown. >>>> >>>> Do you know what is the right NHbinernate configuration? >>>> >>>> Thank you in advanced >>>> >>>> -- >>>> >>> You received this message because you are subscribed to the Google >>>> Groups "nhusers" group. >>>> To unsubscribe from this group and stop receiving emails from it, send >>>> an email to [email protected]. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/nhusers/b372e5d2-da3c-4e1d-a09e-84fb42e95127n%40googlegroups.com >>>> <https://groups.google.com/d/msgid/nhusers/b372e5d2-da3c-4e1d-a09e-84fb42e95127n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>> -- >> You received this message because you are subscribed to the Google Groups >> "nhusers" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/nhusers/4e801446-8fe0-4468-9013-a5a41ee79ef3n%40googlegroups.com >> <https://groups.google.com/d/msgid/nhusers/4e801446-8fe0-4468-9013-a5a41ee79ef3n%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> > -- > You received this message because you are subscribed to the Google Groups > "nhusers" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/nhusers/CAF8vUGUdeV3Om4sTKi6wp1cMOQPQgzF5JGdqbNfbgShV7k4yMw%40mail.gmail.com > <https://groups.google.com/d/msgid/nhusers/CAF8vUGUdeV3Om4sTKi6wp1cMOQPQgzF5JGdqbNfbgShV7k4yMw%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > -- You received this message because you are subscribed to the Google Groups "nhusers" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/CAAOnFsOc5Bruy_WGk%2BzyD6uWTKv2zuK0nCFxtqDMq%3DuR6emy8Q%40mail.gmail.com.
