having issues getting connection string to work against Azure SQL

[garthbf]

we are trying to run against Azure SQL that has Azure AD to auth. the Auth option of ActiveDirectoryIntegrated is givign an error.
We tried both ODBC and ADO connection string formats and the same error
Invalid value for key "authentication'
Running Command: .\RH.exe /c="Server=tcp:,1433;Database=,Uid=;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated" /f="mypath
" /o="mypath\logs" /dc /silent

Error:
Invalid value for key 'authentication'.
System.ArgumentException: Invalid value for key 'authentication'.
at System.Data.Common.DbConnectionStringBuilderUtil.ConvertToAuthenticationType(String keyword, Object value)
at System.Data.SqlClient.SqlConnectionStringBuilder.set_Item(String keyword, Object value)
at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
at System.Data.SqlClient.SqlConnectionStringBuilder..ctor(String connectionString)
at roundhouse.databases.sqlserver.SqlServerDatabase.initialize_connections(ConfigurationPropertyHolder configuration_property_holder)
at roundhouse.infrastructure.app.ApplicationConfiguraton.build_items_for_container(ConfigurationPropertyHolder configuration_property_holder)
at roundhouse.infrastructure.app.ApplicationConfiguraton.build_the_container(ConfigurationPropertyHolder configuration_property_holder)
at roundhouse.console.Program.set_up_configuration_and_build_the_container(String[] args, Mode mode)
at roundhouse.console.Program.Main(String[] args)

[erikbra]

First, I'll say that this is not supported, at the moment. We should look into whether we should let the Sql Connection itself decide what is a valid connection string or not, to be future-proof. Secondly, have you got this working with e.g. sqlcmd? Have you set up an ADFS server, etc, as describede here?

https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-using-azure-active-directory-authentication?view=sql-server-2017

[garthbf]

we did get this to work. the issue was we had to change the connection string format to be csharp format.
you guys should document that somewhere. we had to get into the code to figure this out.

[erikbra]

Glad you got it to work! Could you please explain a bit what you mean by "Csharp format"? Do you have an example on what you tried first, vs. what you actually had to do in the end?

You originally tried:

"Server=tcp:,1433;Database=,Uid=;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated"

What did you end up with?

[raiderruler]

Glad you got it to work! Could you please explain a bit what you mean by "Csharp format"? Do you have an example on what you tried first, vs. what you actually had to do in the end?

You originally tried:

"Server=tcp:,1433;Database=,Uid=;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;Authentication=ActiveDirectoryIntegrated"

What did you end up with?

I just stumbled upon this issue today and solved it. What garthbf is probably referring to is the following (Notice the space and the backslash):

"Data Source=(local);Initial Catalog=Database;Authentication=\"Sql Password\";Application Name=MyApp"

[garthbf]

thanks,
we still would like to be able to use a AAD token. and this is something that is not part of the connection string. it is a separate property on the connection object. so roundhouse would have to allow another parm and set it after the connecton object is created.

[garthbf]

the wiki page needs to be updated to explain this.
thanks,

[fleed]

@garthbf can you please share the working solution?

[jarleli]

I've stumbled upon a similar issue that I can't seem to fix. Please let me know if I should create a seperate issue for this.

I'm trying to use roundhouse to patch an sql server in azure. This connection string is working from the application itself, but roundhouse throws an error.
The command I'm running from powershell is:

.\rh.exe -c "Server=mylongservername.mydomain.com;Database=dbname;User ID=myuser@mydomain;Password=mypassword;Persist Security Info=False;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Authentication='Active Directory Password'" -f \scripts\sqlserver --dc --dt sqlserver --sc MyRoundhousE

The error message I get is:

==================================================
Migration Scripts
==================================================
RoundhousE encountered an error.
System.Data.SqlClient.SqlException (0x80131904): Login failed for user '<token-identified principal>'.
   ved 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)
   ved System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   ved System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   ved System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   ved System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   ved System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   ved System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   ved System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   ved System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   ved System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   ved System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   ved System.Data.SqlClient.SqlConnection.Open()
   ved Polly.Policy.<>c__DisplayClass108_0.<Execute>b__0(Context ctx, CancellationToken ct)
   ved Polly.Policy.<>c__DisplayClass138_0.<Implementation>b__0(Context ctx, CancellationToken token)
   ved Polly.Retry.RetryEngine.Implementation[TResult](Func`3 action, Context context, CancellationToken cancellationToken, ExceptionPredicates shouldRetryExceptionPredicates, ResultPredicates`1 shouldRetryResultPredicates, Action`4 onRetry, Int32 permittedRetryCount, IEnumerable`1 sleepDurationsEnumerable, Func`4 sleepDurationProvider)
   ved Polly.Retry.RetryPolicy.Implementation[TResult](Func`3 action, Context context, CancellationToken cancellationToken)
   ved Polly.Policy.Implementation(Action`2 action, Context context, CancellationToken cancellationToken)
   ved Polly.Policy.Execute(Action`2 action, Context context, CancellationToken cancellationToken)
   ved roundhouse.databases.sqlserver.ReliableSqlConnection.Open(RetryPolicy retryPolicy) i C:\projects\roundhouse\product\roundhouse.databases.sqlserver\ReliableSqlConnection.cs:linje 171
   ved roundhouse.databases.sqlserver.ReliableSqlConnection.System.Data.IDbConnection.Open() i C:\projects\roundhouse\product\roundhouse.databases.sqlserver\ReliableSqlConnection.cs:linje 352
   ved roundhouse.connections.AdoNetConnection.open() i C:\projects\roundhouse\product\roundhouse.core\connections\AdoNetConnection.cs:linje 23
   ved roundhouse.databases.AdoNetDatabase.open_admin_connection() i C:\projects\roundhouse\product\roundhouse.core\databases\AdoNetDatabase.cs:linje 48
   ved roundhouse.migrators.DefaultDatabaseMigrator.open_admin_connection() i C:\projects\roundhouse\product\roundhouse.core\migrators\DefaultDatabaseMigrator.cs:linje 54
   ved roundhouse.runners.RoundhouseMigrationRunner.run() i C:\projects\roundhouse\product\roundhouse.core\runners\RoundhouseMigrationRunner.cs:linje 134
ClientConnectionId:41c964a6-0721-43e6-a188-2e738ce5d812
Error Number:18456,State:1,Class:14

My fear was that you weren't supporting the azure ad password authentication mode with System.Data.SqlClient, but I tried to build the executable from source after updating to Microsoft.Data.SqlClient and that didn't help any either.

What else could be wrong here?