Authenticating to Azure SQL with access token fails

[eruponen]

Using the newly added support for the use of Access Token (added in #395) leads to the following exception:

 `RoundhousE encountered an error.
FluentNHibernate.Cfg.FluentConfigurationException: An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.

 ---> System.Data.SqlClient.SqlException: Login failed for user ''.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at NHibernate.Connection.DriverConnectionProvider.GetConnection()
   at NHibernate.Tool.hbm2ddl.SuppliedConnectionProviderConnectionHelper.Prepare()
   at NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.GetReservedWords(Dialect dialect, IConnectionHelper connectionHelper)
   at NHibernate.Tool.hbm2ddl.SchemaMetadataUpdater.Update(ISessionFactoryImplementor sessionFactory)
   at NHibernate.Impl.SessionFactoryImpl..ctor(Configuration cfg, IMapping mapping, Settings settings, EventListeners listeners)
   at NHibernate.Cfg.Configuration.BuildSessionFactory()
   at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory()
   --- End of inner exception stack trace ---
   at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory()
   at roundhouse.infrastructure.persistence.NHibernateSessionFactoryBuilder.build_session_factory(IPersistenceConfigurer db_configuration, Assembly assembly, String top_namespace, Action`1 additional_function) in C:\Projects\roundhouse\product\roundhouse.core\infrastructure\persistence\NHibernateSessionFactoryBuilder.cs:line 121
   at roundhouse.infrastructure.persistence.NHibernateSessionFactoryBuilder.build_session_factory(Action`1 additional_function) in C:\Projects\roundhouse\product\roundhouse.core\infrastructure\persistence\NHibernateSessionFactoryBuilder.cs:line 91
   at roundhouse.databases.DefaultDatabase`1.set_repository() in C:\Projects\roundhouse\product\roundhouse.core\databases\DefaultDatabase.cs:line 76
   at roundhouse.databases.AdoNetDatabase.open_connection(Boolean with_transaction) in C:\Projects\roundhouse\product\roundhouse.core\databases\AdoNetDatabase.cs:line 69
   at roundhouse.migrators.DefaultDatabaseMigrator.open_connection(Boolean with_transaction) in C:\Projects\roundhouse\product\roundhouse.core\migrators\DefaultDatabaseMigrator.cs:line 65
   at roundhouse.runners.RoundhouseMigrationRunner.run() in C:\Projects\roundhouse\product\roundhouse.core\runners\RoundhouseMigrationRunner.cs:line 112`

The access tokens I used should have been valid as I can use them when creating a connection with another piece of code.

For instance @Peckham, do you have an insight on this matter?

[tdawgy]

We have also ran into this issue. We tried to downgrade back to 1.2.1 and were still unable to work through this issue. @eruponen did you have any luck getting an access token working?

[eruponen]

Haven't tried to debug this issue since posting this. So, no I haven't yet found a solution for this. But good to know that this at least is reproducible issue.

[eruponen]

@tdawgy Have you had any success related to this issue?
@Peckham as the original author it would be nice to hear any thoughts about this issue.

[merekel]

@eruponen, @tdawgy - I encountered this issue as well. The root cause is that DriverConnectionProvider in nhibernate-core does not open the connection to the RH repository tables (ScriptsRun, ScriptsRunErrors, Version) with the access token. nhibernate-core does not seem to have any concept of SQL access tokens. I believe a fix or workaround would be to use a custom provider and pass it the token.
Also #395 mentions that it was never tested against an Azure SQL db.

[tdawgy]

I don't really have much else to add, we encountered the issue while we were doing some spikes to choose the best platform for managing database migrations. Obviously, being unable to get a working spike influenced our decision to go with DbUp instead.

[CloudAjourNPE]

Bump - anyone who found a workaround for this ?

[kimjamia]

Access tokens work in grate. It's an easy switch.

[CloudAjourNPE]

Access tokens work in grate. It's an easy switch.
Thanks. That would be amazing if it works. I had somehow missed that grate should be almost plug n play.