ADO.NET Keyword not supported: 'authentication' - Azure Managed Instance

[harlecin]

• MSSQL Extension Version:
• VSCode Version: 1.28.2
• OS Version: W10 pro

I tried to connect to our Azure Managed Instance using the ADO.NET connection string from the Azure Portal. Using the ADO.NET connection string works fine in SQL Import/Export wizard, so I think this might be an issue with the mssql extension.

Steps to Reproduce:

1. Create Connection Profile
2. Paste ADO.NET connection string from Azure Portal from Managed Instance Database into popup with Authentication="Active Directory Integrated"
3. Error:

   mssql: Failed to connect: System.ArgumentException: Keyword not supported: 'authentication'.
   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 Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.CreateConnectionStringBuilder(ConnectionDetails connectionDetails) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 1119
   at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 521

Any help greatly appreciated:)

PS: This extension is brilliant! Thx for all the great work!

[kevcunnane]

This extension is built on .Net Core, which didn't have support for Azure Active Directory authentication at all until recent prerelease versions. That is why the keyword error above is shown. Is it possible to work around by using regular Windows or SQL Password-based authentication?

@MattIrv is working on token-based AAD authentication in Azure Data Studio (see https://github.com/Microsoft/azuredatastudio/issues/13), and it's possible that once this is done we could work to support the same in this project.

Please note that even with token-based support you'd need interactive authentication and have to change the connection string. That's because Active Directory Integrated / Active Directory Password authentication still isn't available in .Net Core SQLClient. If and when that's added, we can fully fix this issue.

[harlecin]

Hey, thx a lot for your quick reply!

Unfortunately, Azure SQL Server Managed Instance only supports Integrated and SQL authentication, but we want to avoid sql users whenever possible.

Thanks again for your help!

[anish714]

Can you comment when the active directory password authentication will be added to .Net core SQL Client?

[AndersMalmgren]

Hi, how is issue going? Management here rather want us to go full framework than use SQL authentication. Something I'm against since we now have a functional NET Core app working.

Whats the ETA? Thanks

[SentinelVFS]

I am also running into this issue and am curious on a fix.

[dragan-bjelosevic]

Is there any hope that we get AD authentication in ADO.Net Core soon

[AndersMalmgren]

This is a workaround that works. Its a huge cost to get the token but once cached its zero overhead

                .AddScoped<DbContext>(p =>
                {
                    var ctx = new AuthenticationContext("https://login.microsoftonline.com/xxx");
                    var result = ctx.AcquireTokenAsync("https://database.windows.net/", new ClientCredential("xxx", "xxx"))
                        .ConfigureAwait(false)
                        .GetAwaiter()
                        .GetResult();

                    var db = p.GetService<MyDbContext>();
                    ((SqlConnection)db.Database.GetDbConnection()).AccessToken = result.AccessToken;
                    return db;
                })

[ahernandez-allegion]

We would also like an update on this as it's pretty important for us not to use credentials directly and we certainly are not looking to convert to full framework. Thanks!

[JonElster]

Hi.. any updates... having same issue

[olivier-voutat]

I knew that core is still too young. That's not the first feature that is missing. Argghhhh. And you still wanna drop support for the framework... :(

[srinuece60]

Is .net core 2.2 web api can work for SQL with Azure AD Password Authentication. (but I am facing the error "Exception: Keyword not supported: 'authentication'")

[jeevanbalanmanoj]

We are trying to use .netcore sqlclient to connect to an Azure sql server instance. We don’t have any control over the sql server instance and hence can only do Active directory Password based authentication. Everything works perfectly in .net framework with the following code.

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(); builder.Authentication = SqlAuthenticationMethod.ActiveDirectoryPassword;

However we aren’t able to do the same in .netcore as Authentication is not a valid property in the builder class. I was going throw some github comments which suggested putting the login in credentials in the connection string in .netcore. That leads to the error System.ArgumentException: 'Keyword not supported: 'authentication'

Is there any way we can manually perform the token exchange in .net core? I’m using .netcore 2.2.401

[multco-malan]

I too would like this in .net core 2.2 And would expect it to be available in .net core 3.x else i'm scrapping any applications using .net core and reverting back to full framework.

And no, I don't want to supply pwds either.

Any updates on progress on this issue?

[cheenamalhotra]

Will close this as addressed, it's supported with Microsoft.Data.SqlClient and the extension now depends on it. Ref: SqlAuthenticationMethod Enum