NHibernate + Azure SQL database with Azure AD Manage Identity

[Jorge Caballero]

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

[Paulo Quicoli]

Hi

I believe you will need to pass to NHibernate a custom ADO.NET connection.

So, you would need to connect to your database using your custom ADO.NET connection like this:

 SqlConnection sqlConnection = new SqlConnection();
 sqlConnection = new SqlConnection(ConnectionString);

 ///https://docs.microsoft.com/en-us/azure/app-service/overview-managed-identity?tabs=dotnet#asal

 var azureServiceTokenProvider = new AzureServiceTokenProvider();
 string accessToken = await azureServiceTokenProvider.GetAccessTokenAsync("https://database.windows.net/");
 sqlConnection.AccessToken = accessToken;

var session = nhSessions.OpenSession(sqlConnection);

--
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 nhusers+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/b372e5d2-da3c-4e1d-a09e-84fb42e95127n%40googlegroups.com.

[Gunnar Liljas]

https://github.com/nhibernate/nhibernate-core/discussions/2694

--

[Jorge Caballero]

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...

[Paulo Quicoli]

Jorge

That's great! Thank you for sharing your solution! 

To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/4e801446-8fe0-4468-9013-a5a41ee79ef3n%40googlegroups.com.

[Gunnar Liljas]

I think the best recommendation is to have a modern SqlClient, which has built in support for managed identity. No custom provider necessary.

/G

To view this discussion on the web visit https://groups.google.com/d/msgid/nhusers/CAF8vUGUdeV3Om4sTKi6wp1cMOQPQgzF5JGdqbNfbgShV7k4yMw%40mail.gmail.com.