Use Microsoft.Data.SqlClient to allow Azure Active Directory connections

[chriswill]

Referencing this post.

I want to use a SQL connection string like Server=myserver.database.windows.net,1433;Database=mydatabase;UID=a;Authentication=Active Directory Interactive

but the app fails with a Keyword not supported: 'authentication' exception. This is because System.Data.SqlClient doesn't understand the new authentication schemes supported in Microsoft.Data.SqlClient that support Sql Azure.

Upgrading to Microsoft.Data.SqlClient is a relatively painless process and would resolve this issue. Would you accept a PR for this?

Note: I applied the overloads to .SqlDatabase suggested in https://github.com/DbUp/dbup-sqlserver/issues/7 but this did not help.

[chriswill]

I forked the repo and updated the dbup-sqlserver project to use Microsoft.Data.SqlClient and this resolved the issue.

[Hooch180]

When will the support for Microsoft.Data.SqlClient be added?

[chriswill]

@Hooch180 For one reason or another it seems like PRs are not being accepted here in a timely fashion. You're welcome to use my fork https://github.com/chriswill/DbUpReboot if you need Microsoft.Data.SqlClient support.

[JCarnall]

@chriswill, many thanks, just grabbed your 'DbUp.Reboot' package and it works a charm using the newer connection string format for Azure Sql

[TroyWitthoeft]

@chriswill - Good to see someone out there picking up a reboot for DbUp! Thank you. Let me know if I can help. Thanks for pointing out the new connection string authentication keywords in Microsoft.Data.SqlClient. FWIW, the overload in DbUp/dbup-sqlserver#7 is working for me? I've also got an example of how to manually do something like Active Directory Default does in the TestConnect() method of AzureDbUp, here.

[chriswill]

@TroyWitthoeft good luck with your project!

[LordBenjamin]

+1 for this request - I'm getting an error after upgrading to .NET 6.0 (which has resulted in having to add TrustServerCertificate=True for local dev).

Any update from the maintainers would be appreciated. Happy to contribute if there is anything I can do to help?

Keyword not supported: 'trust server certificate'
at System.Data.SqlClient.SqlConnectionStringBuilder.GetIndex(String keyword)
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 SqlServerExtensions.GetMasterConnectionStringBuilder(String connectionString, IUpgradeLog logger, String& masterConnectionString, String& databaseName)
at SqlServerExtensions.SqlDatabase(SupportedDatabasesForEnsureDatabase supported, String connectionString, IUpgradeLog logger, Int32 timeout, AzureDatabaseEdition azureDatabaseEdition, String collation)

[osexpert]

I wonder why this is still open? It seems to work fine now (tested net60 + dbup-sqlserver 5.0.8)

[mjauernig]

@osexpert Thank you for the feedback. I will test it in the next days and then close the issue.

[JumpingJezza]

The latest version still does not work for passwordless Managed Identities. Testing with a .Net Framework 4.8 project and using a connection string like this: "data source=<resource>.database.windows.net;initial catalog=<database name>;Authentication=Active Directory Managed Identity;...other properties"

Code:

var upgrader = DeployChanges.To.SqlDatabase(_connectionString)
   .WithScriptsEmbeddedInAssembly(typeof(DbScriptAutomation).Assembly)
   .LogToConsole()
   .WithExecutionTimeout(TimeSpan.FromSeconds(timeout))
   .Build();

throws an error: System.ArgumentException: Invalid value for key 'authentication'

The same connection string can be used to connect successfully using the Microsoft.Data.SqlClient. The old System.Data.SqlClient however throws the same Invalid value for key 'authentication' error.

[kAleksei]

Hi everyone. Do you have any updates on it? The package System.Data.SqlClient is considered highly vulnerable: https://avd.aquasec.com/nvd/2024/cve-2024-0056. It would be great if it could be prioritized!

[droyad]

@kAleksei The SqlServer provider already uses version 4.8.6 which is listed as fixed in that CVE

[droyad]

Resolved by #17