SqlConnection should support Azure AD token-based authentication

[ndavids-msft]

The desktop .NET Framework 4.6 and newer has an AccessToken property on the SqlConnection class (MSDN) which can be used to authenticate to a SQL Azure database using an access token issued by Azure AD (examples here). However, this property is not present on the version of SqlConnection provided in the System.Data.SqlClient NuGet package, including the latest preview v4.3.0-preview1-24530-04. In case it's an important detail, the project in question is a library project targeting 'netstandard1.6'.

The lack of the AccessToken property makes it difficult, if not impossible, to support modern directory-based auth scenarios for Azure SQL DB while also targeting .NET Standard (I have not attempted to use the connection string-based AAD options, but for various reasons those would not be applicable for my project). I have yet to find a workaround other than setting the target framework to 'net46', which I would like to avoid except as a last resort.

[saurabh500]

Related to dotnet/SqlClient#10

[stijnherreman]

It's a bit strange that Azure is such a huge and important part of Microsoft, but .NET Standard and .NET Core aren't given the necessary resources to support it.

I don't mean to blame anyone here, it's just that I'd love to use .NET Core but still cannot do so more than 3 years after it was announced.

[stijnherreman]

The PR referred to by @keeratsingh is dotnet/corefx#30342

[AfsanehR-zz]

@nathandavidson @stijnherreman Closing this is issue since it is addressed by pr #30342.

[Liero]

@afsanehr: great! So this will be released in .NET Core 2.2?

[BruceHunter]

Security should be number 1. This should have been released already.

[karelz]

The PR was merged into master, which will ship in 3.0.
I let @afsanehr @David-Engel @keeratsingh to comment on option porting it to 2.2.

[stijnherreman]

I think everyone waiting for this would like to see it in 2.2.

[Trontin]

How this implementation will impact the pool connection key ? If there is two differents AccessToken with the same connection string, does it mean that they can share the same connection pool or not ?

[saurabh500]

A new connection pool is created for every access token.

[paulirwin]

+1 for getting this into .NET Core 2.2. By the time 2.2 comes out this issue will be 2 years old.

[wienleung]

@afsanehr @David-Engel @keeratsingh any decision on whether this update will be ported to 2.2?

[keeratsingh]

@wienleung @Liero @paulirwin @stijnherreman We are currently in the process of porting this fix over to .NET Core 2.2, will updated here once the process is complete.

[karelz]

Reopening the issue to track port to 2.2 (PR dotnet/corefx#31039).

[AfsanehR-zz]

The AccessToken property is now added to .NET Core 2.2. 🎉

EDIT: While the PR was merged into the branch prior to 2.2 Preview1 release, it missed packaging changes and the package didn't ship at all, so it will be part of 2.2 Preview2 release (released on 2018/9/12).

[danmoseley]

Thanks @afsanehr for addressing this popular issue. For those who will ask: we have not AFAIK announced a date for 2.2 yet. It will definitely be before 3.0 😀 Meantime, if anyone has a chance to try this out of the repo (either master or release/2.2) it would be great if you could indicate here to confirm you were successful.

[karelz]

2.2. is currently planned for Q4 2018: https://github.com/dotnet/core/blob/master/roadmap.md#upcoming-ship-dates

[bloodybeet]

Can you give us an update on when we should be able to test this feature/start using it in development. Would that be 2.2.100-preview2?

[jnevins-gcm]

Why can't this be distributed as a nuget package as opposed to with the full core sdk?

[karelz]

@jnevins-gcm shipping standalone packages leads to test matrix explosion + things don't work properly together when they should, etc. It caused lots of troubles for both the customers and the team and with .NET Core we started shipping platform as single NuGet package.
You can check the feature in daily builds of release/2.2 build, or wait for next preview which will contain the fix (based on times and the schedule I am aware of, it should be Preview2).

[jnevins-gcm]

Can we consume 2.2 as a nuget package instead of having to use the windows installer for the core sdk?

[karelz]

@jnevins-gcm I think you will need the SDK, but I let area owners to have final word on the topic.

[jnevins-gcm]

Ok, is this the right forum to get that answer? Who should I be asking?

[AfsanehR-zz]

@jnevins-gcm @karelz is correct. You need to update the SDK and SqlClient version both.
I was able to test it with SDK version 2.2.0-preview-26820-02 and SqlClient version 4.6.0-preview2-26905-02

[jnevins-gcm]

The sqlclient is included in the sdk’s net ore reference assemblies though?

[jnevins-gcm]

If the sqlclient update is separate, where do I get it?

[karelz]

@jnevins-gcm SlqClient update is NOT separate. We do not ship or support separate packages which are part of .NET Core platform -- see my previous reply https://github.com/dotnet/corefx/issues/13660#issuecomment-420337898 explaining why.

[AfsanehR-zz]

It is included but you need to include the version you want in your csproj file.
For example:

<ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.6.0-preview2-26905-02" />
</ItemGroup>

You can check the latest version of SqlClient from here:
https://dotnet.myget.org/feed/dotnet-core/package/nuget/System.Data.SqlClient

[jnevins-gcm]

I guess I don’t understand your release model.

netstandard2.0 started down the road of “no individual nuget packages for libraries that are part of the netstandard surface area”. You reference netstandard (implicitly) and the platform specific netstandard implementation forwards to the correct platform specific (or platform agnostic) implementation assembly that is distributed as part of that platform’s clr redistributable.

SqlClient isn’t part of netstandard.

Okay, so that being the case, how is it tied to a specific version of the netcore sdk? I’m looking at your nuget package on myget. It has both platform neutral (netstandard) implementations and a netcoreapp specific implementation. But no netcoreapp2.2 specific tfm.

Can you please help me understand how SqlClient is coupled to the netcore sdk and where that dependency is expressed and why I can’t or shouldn’t be able to just add a PackageReference to the one you linked to on myget without installing netcore sdk 2.2?

[swettstein]

according to the readme, this should be included in preview1 but after i installed the SDK and added the new Sql package to version 4.6.0-preview2-26905-02 but still don't see the AccessToken on SqlConnection

[karelz]

@swettstein while the PR was merged for 2.2 Preview1, it omitted packaging changes leading to not shipping the bits at all. They will be available in 2.2 Preview2 (also clarified above - https://github.com/dotnet/corefx/issues/13660#issuecomment-413921458).
I checked with experts and this particular package is shipping also standalone, so it should be applicable to older version (although we do not test the combination).

[jnevins-gcm]

So is that to say my understanding as I described above is actually correct?

[karelz]

@jnevins-gcm (had to check with experts again)
All APIs which are part of netstandard has to be implemented in each platform (.NET Core, Mono, .NET Framework, UWP) that supports that version of .NET Standard inbox (without external packages).
There are APIs which are in the platform, but do not ship as individual packages.
SqlClient already shipped as individual package before and we kept it shipping that way, that's why it ships standalone.

In nutshell, whatever is part of netstandard, has to be in each platform inbox. Some APIs (outside of netstandard) may ship as well as standalone packages. Decision what ships as standalone package is based on business need, and not on "can it be done".

Hope it clarifies things. Let's keep the general platform/targeting discussion separate. It is not specific to SqlClient.

[karelz]

@bloodybeet did you use 2.2 Preview2?
If you found a limitation in functionality, I would recommend to file a new issue to avoid mixing up multiple problems and discussions here.

[AfsanehR-zz]

@bloodybeet Would you be able to file a new issue as Karel recommended? It would be easier to continue discussion there. In the meantime, I would recommend to check if you have created a contained database user and grant the necessary permissions. Please refer to this link for more information
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure

[bloodybeet]

Even better: the access token wasn't coming from database.windows.net, but management.core.windows.net - fixed and working as expected. I have deleted all messages to keep this thread clean ;)