Sqlclient: 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: https://github.com/dotnet/SqlClient/issues/305#issuecomment-553608759

[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.