Integrated security - manual kerberos negotiation

[aaomidi]

I would like to be able to manually do the kerberos negotiation with something like: https://github.com/SteveSyfuhs/Kerberos.NET

Then include the security token in the connection string for SqlClient and use that to connect to databases. Is something like this possible?

[cheenamalhotra]

Hi @aaomidi

Have you tried using accessToken connection property?

[aaomidi]

I have not! I'll try to use that :)

Is there documentation available explaining what that token does?

[aaomidi]

I came across this "patch" for SqlClient, https://github.com/macsux/sql-server-kerberos-poc/blob/master/SqlClientPlay/Program.cs I'm guessing it would accomplish the same thing?

[cheenamalhotra]

This accessToken will be passed instead of credentials to perform authentication, you can try using it as:

string ConnectionString =@"Data Source={serverName}; Initial Catalog={db};"
SqlConnection conn = new SqlConnection(ConnectionString);
conn.AccessToken = "Your access token"
conn.Open();

[aaomidi]

@charlienilsson I was curious what is "AccessToken" - like where would this come from? What is the value that's expected there?

[cheenamalhotra]

I came across this "patch" for SqlClient, https://github.com/macsux/sql-server-kerberos-poc/blob/master/SqlClientPlay/Program.cs I'm guessing it would accomplish the same thing?

This is using reflection with driver's SNI and passing GSS Credentials to acquire ticket, which is a hack, I wouldn't recommend as a permanent solution.

[aaomidi]

Yeah I was curious if this AccessToken is basically looking for, say the kerberos negotiated client token.

[cheenamalhotra]

AccessToken is a JWT/OAuth2 token which is acquired from authorizing parties that SQL Server recognizes. It's mostly talked about in terms of Azure DB authentication. Docs

For Kerberos, I haven't tested myself but thought maybe you got something similar to experiment with, if not then that's not going to be possible.

[aaomidi]

Ah no I was mostly talking about trying to use kerberos tickets directly with Sql connections. This would enable different authentication models without using stuff like kinit on Linux/mac systems.

[cheenamalhotra]

That's not supported unfortunately.

[aaomidi]

So I guess this issue has turned into a feature request :D

[cheenamalhotra]

You can take a look at this article here which details everything: https://www.codeproject.com/Articles/1272546/Authenticate-NET-Core-Client-of-SQL-Server-with-In

Calling kinit to generate tickets is always managed by user, and not by client drivers. That's the whole point of client credential security that Kerberos offers. In Kerberos flow, users always generate a ticket at their end with their credentials, which domain controllers authorize for SQL Server when requested by client drivers. The user running client application is authorized for available Kerberos ticket.

Please Note that it is the Domain Manager that authorizes the user with Kerberos Ticket, so providing the ticket to client driver is of no use.

[aaomidi]

I understand the current workarounds for this issue. What this current system does has its own big limitations.

1. I need to create a file under /etc, which usually requires extra permissions that the user may not have.
2. The file under /etc is shared by all users, further complicating the process.
3. In my use case I want to create a kerberos-based account management solution where the user can enter their username and password, pick the realm and service, and be authenticated to that realm and service.

kinit is specific to the Kerberos MIT library anyway - and theres other libraries that re-implement the MIT library without following exactly what that design entailed:

• https://github.com/SteveSyfuhs/Kerberos.NET
• https://github.com/jcmturner/gokrb5

Essentially I want to be able to provide users with a cleaner user experience without forcing them to have to deal with files in /etc.

My request here is let me manually pass in the token that's used in integrated security.

[cheenamalhotra]

SqlClient only communicates to SQL Server, not to Domain Manager. We cannot pass your ticket to Domain Manager, you need to place it in local ticket cache. Location of Kerberos Ticket Cache is controlled by Environment variable, and it can be any location specified by you.

In this scenario, if you want to accept Domain user/pass from users and authenticate application to connect to SQL Server, your application needs to generate ticket and put it in Kerberos Ticket Cache from where the domain manager will fetch and authenticate.

Also to clarify, Kerberos Ticket is not a token, it's not same as Access Token sent to SQL Server to authenticate user. If you were authenticating to Azure DB, you could easily generate a Access token with user credentials and pass that to Azure DB, as I mentioned above.

[saurabh500]

@aaomidi I know that ADS customers had this ask of being able to specify the domain username and password to authenticate to SQL Server instead of getting the credentials from the environment. Is this ask for specify kerberos token related to that particular user scenario or are you trying to alleviate the non-admin setup problem that the customer may have while installing and configuring kinit to get the ticket?

This may be a good ask, though I dont think we should add another property on the connection. We should extend the SqlClient Auth Provider model to be able to take a token for Integrated Security authentication, the provider will be added by the SqlToolsService and will take care of passing in the token to the client.

AccessToken is only applicable while doing AAD auth and for integrated security, we need to use the token to overwrite the SSPI token during Login and AccessToken will not help there.

[aaomidi]

@saurabh500 That's essentially the ask I was working on. The reason I brought kerberos up was that I can make the SqlClient do less work - I can do the authentication myself with a Kerberos library - and if there was a way for me to pass the ticket/token onto the SqlClient to use instead of just getting it from the environment it would enable a lot of new scenarios.

If we can extend the auth provider model, that would essentially solve our problems.

[aaomidi]

@saurabh500 Do you know if this ask will be worked on for future releases of SqlClient?

[saurabh500]

Well I was only commenting on the path forward. @David-Engel and @cheenamalhotra to prioritize.
@aaomidi do you want to take a stab at the change

[cheenamalhotra]

Hi @saurabh500

Does SQL Server support accepting any token/ticket for Kerberos Auth from client driver via TDS?

[saurabh500]

Yes, we send the SSPI token to the server for validating the user. So this is feasible.

[cheenamalhotra]

So that would mean @aaomidi 's client app would provide an SSPI Auth equivalent token and not a Kerberos ticket to put it right, or are they essentially same?

As I was wondering if it would work with Kerberos ticket as asked here: #305 (comment)

[saurabh500]

Yes, the application with provide the ticket or the sspi information and that information will be sent to the server instead of the client trying to get that information from the operating system.
The problem is that the client expects some environment setup on Unix for Kerberos to work which is more involved compared to Windows.
If the app can manage the ticket on Unix then it alleviates the client from figuring out how to get the ticket and allows the app to manage the ticket.

[aaomidi]

I would like to try to make this possible, but my knowledge of C# is quite basic at the moment.

What @saurabh500 also says makes it possible to essentially emulate and authentication on any OS which opens up quite a few interesting use cases.

[PeterHalsted]

@cheenamalhotra when will we be able to get this feature in SqlClient and do you know if it meanwhile is possible to use the Krb5TicketCache in https://github.com/dotnet/Kerberos.NET to write a cache file referred to in the KRB5CCNAME variable that the underlying driver can and would read?

[jakauppila]

The JDBC client has the ability to communicate via Kerberos from Linux simply by defining a userName, password, and authenticationScheme.

jdbc:sqlserver://mydatabase.contoso.com;DatabaseName=DATABASE;authenticationScheme=JavaKerberos;integratedSecurity=true;userName=MYUSERNAME;password=MYPASSWORD

https://docs.microsoft.com/en-us/sql/connect/jdbc/using-kerberos-integrated-authentication-to-connect-to-sql-server?view=sql-server-ver15#kerberos-connection-using-principal-name-password-and-realm

We are successfully doing this with Java applications deployed to Linux VMs and containers today.

As we look to deploy .Net Core applications to Linux VMs we'd rather not have to deal with configuring a systemd service for refreshing the Kerberos ticket and for containers we would rather avoid having to run a Kerberos sidecar to provide that similar functionality.

[jakauppila]

We raised this with our Microsoft rep and was asked to submit the request on the SQL Community forum.

https://feedback.azure.com/d365community/idea/287685dc-6133-ec11-a819-000d3ae2b5ca

[srdan-bozovic-msft]

@jakauppila
I wonder would something similar to below meet your need?

You would then be able to use something like Kerberos.NET to retrieve the ticket.

[jakauppila]

@srdan-bozovic-msft I'm not familiar with Kerberos.NET myself but looking at the docs that seems like it would accomplish what we're looking for.

Once you feel your PR is ready to test, we could give it a try.

[ajkonkol]

@srdan-bozovic-msft, your proposal looks workable.

One item I'd like to point out is that in many cases, we use libraries that abstract away the management of the SQL Connection, and we simply provide the connection string to the library. For example, NHibernate (and I'm thinking Entity Framework may work similarly). In those cases, we'd have to find a way to plug into the library and take over the connection creation process so that we could provide the Kerberos ticket to the connection.

Just wanted to point out that this isn't as seamless as the option provided by the JDBC driver where one can simply provide the username and password as part of the connection string.

[Abhisheik-athi]

@jakauppila I wonder would something similar to below meet your need?

You would then be able to use something like Kerberos.NET to retrieve the ticket.

i need something similar in my case need to get NTLM based token. How can i get a token for a set of AD username - password. The point is i am building a console app (.NET based) and need to create a connection with MS SQL DB (integrated security = true) just that the creds its using is not mine but a service account ... i do not have permission to run the app with any other account hence need to pass through the code(not sure i need to pass creds / ntlm token ). what are my options is it feasible ?

[norgie]

So what's the status as per today? According to this #1411 Kerberos should work on Linux (Docker/k8s). I'm in a situation where Quartz.net manages to access the database using a Kerberos Ticket (kinit et.al.), but when NHibernate tries less than a second later the application fails. For us to even be able to move to Linux/k8s with SqlServer as the db-backend the Microsoft.Data.SqlClient just got to work. There's enough moving parts wrt Linux/k8s for us to have to worry about basic connectivity as well.

[JRahnama]

@norgie Kerberos authentication with integrated security should be working on Linux. What are the issues you are facing? PR #1411fixes the issue with net 6. Some apps were using fine on net5 or other netcoreapp application, but when they migrated to net6 it stopped working. The issue is on dotnet runtime and the fix we added here is to cover that untill next patch of net6 is released. How is the application flow?

[norgie]

@JRahnama The flow is as follows. When the application starts it calls into Quartz.net (v.3.3.3) which then accesses the database with whatever means it deems necessary. Then the application issues a series of queries using NHibernate 5.3.3 (with FluentNHibernate 3.1.0). When I first started dabbling with kerberos on Linux (Docker) the application would fail when Quartz tried to connect to the database. After some tinkering the Quartz-job seems to be able to access the database but the NHibernate side of things still fails producing the following error:

 Cannot authenticate using Kerberos. Ensure Kerberos has been initialized on the client with 'kinit' and a Service Principal Name has been registered for the SQL Server to allow Kerberos authentication.
ErrorCode=InternalError, Exception=Interop+NetSecurityNative+GssApiException: GSSAPI operation failed with error - Unspecified GSS failure.  Minor code may provide more information (Server not found in Kerberos database).
   at System.Net.Security.NegotiateStreamPal.GssInitSecurityContext(SafeGssContextHandle& context, SafeGssCredHandle credential, Boolean isNtlm, ChannelBinding channelBinding, SafeGssNameHandle targetName, GssFlags inFlags, Byte[] buffer, Byte[]& outputBuffer, UInt32& outFlags, Boolean& isNtlmUsed)
   at System.Net.Security.NegotiateStreamPal.EstablishSecurityContext(SafeFreeNegoCredentials credential, SafeDeleteContext& context, ChannelBinding channelBinding, String targetName, ContextFlagsPal inFlags, Byte[] incomingBlob, Byte[]& resultBuffer, ContextFlagsPal& outFlags)
   at System.Data.SqlClient.SNI.SNIProxy.GenSspiClientContext(SspiClientContextStatus sspiClientContextStatus, Byte[] receivedBuff, Byte[]& sendBuff, Byte[] serverName)
   at System.Data.SqlClient.SNI.TdsParserStateObjectManaged.GenerateSspiClientContext(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength, Byte[] _sniSpnBuffer)
   at System.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength)

I'm using a shell scritp to start the application and in that shell script I've got the following:

kinit someuser -k -t ./ktbs/someuser.keytab
dotnet ./NOA.Server.dll

I've also tried with both SSPI and true as the values for Integrated Security in the connection string. Encrypt is set to false in said connection string.

[norgie]

@JRahnama This is the article I've followed to configure my image for kerberos: https://www.codeproject.com/Articles/1272546/Authenticate-Net-Core-client-of-SQL-Server-with-In

(one difference is that I don't include the keytab file in the image)

[norgie]

The NHibernate issue has been solved. Turns out that when configuring NHibernate one can specify which "driver" NHibernate should use. E.g. using FluentNhibernate this would look something like

Fluently.Configure()
        .Database(MsSqlConfiguration.MsSql2012
                                    .Driver<MicrosoftDataSqlClientDriver>()
                                    .ConnectionString(connectionString)
                 )

The important bit being .Driver<MicrosoftDataSqlClientDriver>() . Now the application happily connects to its database issuing queries and receiving the expected results from the database.

Just for the record, my assumption regarding Quartz.net working was wrong so I guess there's more investigation to be done on my part.

[cmoski]

I'm quite interested in this PR -- my use case is as follows @JRahnama (open to suggestions to other ways to do this!):

• I've got a Blazor server side application I want to deploy to a Linux container which connects to a SQL Server using integrated authentication.
• I'd like to use Kerberos, and it does work inside of the container when following the guide specified here (https://www.codeproject.com/Articles/1272546/Authenticate-Net-Core-client-of-SQL-Server-with-In) and this seems easy enough to replicate inside of Kerberos.NET
• I'd like users to be able to authenticate (either by Kerberos passthrough, double hop to SQL, or username/password login) validate it against the domain controller, and log them in using integrated authentication. (Credential management isn't a blocker in this case, we could use either method)
• There doesn't seem to be a way to do this without writing code store a keytab / grab a ticket with [email protected] and specify username in the connection string
• Periodic refreshing of that ticket has to take place via a sidecar container or (shudder) crontab in the container itself.

In an ideal world I'd like to just be able to grab kerberos via Kerberos.net and pass it through to SQLClient to perform authentication to run queries under the users context.

This PR seems to be the easiest way to do it and have Kerberos.net manage the backend instead of the krb5 package.

If there is a better way to do this, I'm totally open -- but just wanted to register my use case on this issue and see if this PR will ever be merged. (Compliance for my particular use case doesn't allow modification of the SQLClient package)

Thanks!

[JRahnama]

@cmoski what PR are we talking about #1411 or some other PR?

[cmoski]

@JRahnama #1379

That said, if #1141 would fix the issue, great!

Still unable to use Kerberos passthrough in a double hop scenario with kestrel to connect into a SQL Server on a Linux based docker container. Would much prefer to have a Linux alternative as Impersonate doesn't work on Linux platforms.

Any update that would allow that to happen would be what I'd be interested in here :) (1379 is more of a workaround if you already have a kerberos ticket acquired by Kerberos.NET or similar, at least in my uneducated-about-kerberos view)

It would save a ton of work (for the end developer) to be able to use the krb ticket that is already passed in by the client for double hop just like you would do on a Windows platform. (GssApi/kinit succeeds on the container, and and its on the windows domain)

[AThomsen]

Is there any news on this issue? How are people handling this in 2023?

[jakauppila]

Is there any news on this issue? How are people handling this in 2023?

For our traditionally deployed applications, we created a systemd service using k5start that is responsible for keeping the ticket renewed for the application.

For our containerized use-cases we built and run a Kerberos renewal sidecar based off of the AWS example here: https://github.com/aws-samples/amazon-ecs-windows-authentication-blog

[AThomsen]

For our containerized use-cases we built and run a Kerberos renewal sidecar based off of the AWS example here: https://github.com/aws-samples/amazon-ecs-windows-authentication-blog

Same here. Great complexity compared to how it's handled in the Java Sql driver though.

[twsouthwick]

FYI - I've got a plan and PRs slowly getting merged in to enable this. Please see the #2253 for details. The general idea is to add a hook at the right point to handle the sspi blobs.

I have a project at https://github.com/twsouthwick/Swick.SqlClient.Sspi that I'll release when it's merged in to enable some easy providers for it. Initially I have one that enables NTLM that I currently need for another project, but wanted to share it in case others have use of it.

[nhart12]

@twsouthwick thank you for your efforts on this!! Our DBA's will be happy to no longer have to maintain separate SQL auth accounts for our container deployments when this goes through (it was not feasible for us to generate keytabs + deploy sidecars to maintain an active kerberos ticket for each application in our environment).
I tested your Swick client and updated it to support kerberos package in a docker image and it worked for our use case.. Is there plans to add some connectionstring syntax sugar like "Integrated Security = SSPI/kerberos;User Id= ..." or similar?
Otherwise there's going to be a lot of variations of the following in the wild just to have SqlClient not look to the OS for the integrated security creds

internal class NegotiateAuthenticationSSPIContextProvider : SSPIContextProvider
{
    private NegotiateAuthentication? _negotiateAuth = null;

    protected override void GenerateSspiClientContext(ReadOnlySpan<byte> incomingBlob, IBufferWriter<byte> outgoingBlobWriter)
    {
        _negotiateAuth ??= new(new NegotiateAuthenticationClientOptions
        {
            Package = "Kerberos", 
            TargetName = AuthenticationParameters.ServerName,
            Credential = new NetworkCredential(AuthenticationParameters.UserId, AuthenticationParameters.Password)
        });

        var sendBuff = _negotiateAuth.GetOutgoingBlob(incomingBlob, out NegotiateAuthenticationStatusCode statusCode)!;

        if (statusCode is not NegotiateAuthenticationStatusCode.Completed and not NegotiateAuthenticationStatusCode.ContinueNeeded)
        {
            throw new InvalidOperationException($"Negotiate error: {statusCode}");
        }

        outgoingBlobWriter.Write(sendBuff);
    }
}

I also investigated using kerberos.net to get the ticket in trimmed down container images like Chiseled ubuntu that wouldn't necessarily have the MIT krb5 libs on them, but it doesn't yet support GSS/SSPI interfaces like the above
dotnet/Kerberos.NET#54

[nhart12]

@twsouthwick do you expect this feature will make it into 6.0 ?