Make sure we support Azure SQL databases with all kinds of strange authentication types

[erikbra]

Ref. https://github.com/chucknorris/roundhouse/issues/343

• Username/password
• AD username/password
• AD username/password with MFA
• Token
• others?

[kimjamia]

I tested that AD username/password with MFA works very well with grate already in Windows. Pass a correct connection string and it will open a web browser where you can login with MFA: --connectionstring="Server=tcp:<somedatabase>.database.windows.net,1433;Initial Catalog=<somedb>;Authentication=Active Directory Interactive"

Using access token requires code changes. Currently --accesstoken is marked as obsolete. Access token login is handy for CI/CD pipelines where a service principal is used and you can just ask for a token and then pass it to grate.

[wokket]

I haven't looked in a while, but I thought you could pass tokens on the conn string?

Eg see https://www.connectionstrings.com/ole-db-driver-for-sql-server/azure-active-directory-authentication-with-access-token/

Does the SqlClient parse this differently to OleDB?

[kimjamia]

I tried passing that connection string to both System.Data.SqlClient.SqlConnection and Microsoft.Data.SqlClientSqlConnection and both first complain:

Keyword not supported: 'provider'

After I remove the "Provider=" part, they both complain:

Keyword not supported: 'access token'.

Neither list Access Token or anything token-related as supported keywords in the documentation.

• system.data.sqlclient.sqlconnection.connectionstring
• microsoft.data.sqlclient.sqlconnection.connectionstring

In both docs, the first sentence is:

The ConnectionString is similar to an OLE DB connection string, but is not identical.

Also neither SqlConnectionStringBuilder have any token-related properties.

There was a similar discussion in RH as well: https://github.com/chucknorris/roundhouse/issues/344

[wokket]

Just following up on this as I've run across it elsewhere: Modern versions of the SqlClient (as used in grate) have baked in support for acquiring and using tokens without requiring more than a connection string. See the MS docco for more info.

[erikbra]

Nice, thanks, @wokket ! I think this should solve the issue, do you agree, @kimjamia ?

[erikbra]

This should Just Work (TM) as of now, all auth methods should be able to specify in the connection string (see e.g. https://learn.microsoft.com/en-us/sql/connect/ado-net/sql/azure-active-directory-authentication?view=sql-server-ver16)