runtime 🚀 - Support windows authentication for SqlClient on Xplat

Similar issue reported in https://github.com/dotnet/corefx/issues/2461

saurabh500 on 28 Oct 2015

@saurabh500
Linux can join AD well these days.
I just came across this and had to enable SQL Auth on my server.
Samba and Squid both work on my network using Kerberos.

dotnet/runtime#14886 mentions problems getting the windows username. I can use id and getent on Linux to retrieve the windows user info so I am sure there is an sssd api on Linux to get this info. I can also get a ticket for a windows user using kinit and use that to auth against ad. Services can store this in a keytab.

This would be a great feature. :smile:

mikes-gh on 18 Jul 2016

@mikes-gh thanks for the inputs. Yes this is very feasible. We are working to get Integrated Security in Xplat SqlClient.

saurabh500 on 18 Jul 2016

👍1

BTW presume future means its not happening for 1.1?

mikes-gh on 18 Jul 2016

@mikes-gh We will look into this for 1.1. I have changed the Milestone. Thanks for pointing this out :)

saurabh500 on 19 Jul 2016

👍1

@geleems is this your work so far on this? https://github.com/geleems/corefx/commits/integauth

Anything I might be able to test on Mac for you?

boorad on 11 Jan 2017

@boorad The branch I have worked on for integrated authentication feature is:
https://github.com/geleems/corefx/tree/integauth_pr

geleems on 14 Feb 2017

Fixed with https://github.com/dotnet/corefx/issues/15742

saurabh500 on 14 Feb 2017

With the preview 1 release of .Net Core 2.0 I'm just wondering if there's any documentation available on setting this up yet? Also I assume currently there's no way to get this to work in a Linux docker container?

dbason on 11 May 2017

Is there any information on this with .Net Core 2.0? I cannot seem to find any other references except this thread and the github link above returns a 404 error.

arketec on 16 Aug 2017

This works for a .NET Core 2.0 app running on Linux as you'd expect (see SqlClient's integrated auth test for sample code), provided the app is run by a user with an appropriate Kerberos ticket.

You can configure Kerberos and get a TGT in a container, or you could use a keytab. The Linux setup instructions for vscode-mssql cover the first option.

chlowell on 9 Oct 2017

Can someone describe the keytab method plz? That method is commonly used on the jvm side of things.

undecided2013 on 16 May 2018

There's nothing particular to .NET Core, you can use ordinary Linux tools. This guide covers it: run ktutil to create a keytab, authenticate with it using kinit. Please note there are security implications to think through before doing this. Anyone who can read the keytab can use all its keys.

chlowell on 16 May 2018

@chlowell , thanks. We are just having a really hard time authenticating to our windows hosted sql server 2008 R2 from .Net Core 2.0 clients hosted in linux docker images using Kerberos. Our company has premium support from MSFT so we opened up a case with them but it sounds like it may have to escalate a bit before we get a good answer. We did try the instructions from the vs-code-mssql post but it was an unsuccessful attempt.

undecided2013 on 17 May 2018

@undecided2013 this is definitely possible. We are doing exactly that.

We use this script to set up kerberos before launching the actual app:

#!/usr/bin/env bash

set -eu

echo Beginning Kerberos TGT setup

# Create user
useradd --shell /bin/sh -c "" -m ${KERBEROS_USERNAME}

keytabPath="/home/${KERBEROS_USERNAME}/kerberos.keytab"

fqUsername=${KERBEROS_USERNAME}@<YOUR DOMAIN>

# Prepare keytab file
echo -e "Preparing keytab file at ${keytabPath} for principal '${fqUsername}'"
{
  echo -e "add_entry -password -p ${fqUsername} -k 1 -e rc4-hmac"
  sleep 0.5
  echo -e "${KERBEROS_PASSWORD}"
  sleep 0.5
  echo -e "add_entry -password -p ${fqUsername} -k 1 -e aes256-cts"
  sleep 0.5
  echo -e "${KERBEROS_PASSWORD}"
  sleep 0.5
  echo -e "write_kt ${keytabPath}"
} | chroot --userspec=${KERBEROS_USERNAME}:${KERBEROS_USERNAME} / ktutil

# Generate token immediately and also setup a cron job to renew it daily
echo Generating initial token
kinitCmd="chroot --userspec=${KERBEROS_USERNAME}:${KERBEROS_USERNAME} / kinit -k -t ${keytabPath} -f -p -r 7d ${fqUsername}"
renewCmd="chroot --userspec=${KERBEROS_USERNAME}:${KERBEROS_USERNAME} / kinit -R"
renewalLog=/proc/$$/fd/1
eval ${kinitCmd}

echo Creating daily renewal cron job
retrievalScript=/etc/cron.daily/retrieve_kerberos_tgt
cat <<-EOF > ${retrievalScript}
    /bin/date --rfc-3339=seconds >> ${renewalLog}
    echo "Retrieving new TGT..." >> ${renewalLog}
    ${kinitCmd} >> ${renewalLog} 2>&1
    if [ \$? == 0 ]; then
        echo Done. >> ${renewalLog}
    fi
    EOF
chmod +x ${retrievalScript}

echo Creating hourly refresh cron job
renewalScript=/etc/cron.hourly/renew_kerberos_tgt
cat <<-EOF > ${renewalScript}
    /bin/date --rfc-3339=seconds >> ${renewalLog}
    echo "Renewing TGT..." >> ${renewalLog}
    ${renewCmd} >> ${renewalLog} 2>&1
    if [ \$? == 0 ]; then
        echo Done. >> ${renewalLog}
    else
        echo "Retrieving new TGT..." >> ${renewalLog}
        ${kinitCmd} >> ${renewalLog} 2>&1
        if [ \$? == 0 ]; then
            echo Done. >> ${renewalLog}
        fi
    fi
    EOF
chmod +x ${renewalScript}

crond -s

echo Kerberos TGT setup complete

# Finally, spawn the user's command. This has to be done from here (rather than
# RUN in the Dockerfile) so that it inherits the above env var.

# The term trap is to allow graceful shut down of the service when a SIGTERM signal is received. Bash doesn't properly
# forward these signals to its child processes by default. We need to keep bash around instead of exec'ing the service
# because that seems to break the kerberos renew functionality. This is because we are chrooting the service to a different user
# which means the cron job's output somehow can't go anywhere anymore.
_term() {
    echo "Caught SIGTERM signal!"
    kill -TERM "$child" 2>/dev/null
}
trap _term SIGTERM

currentWorkingDir=`pwd`
cmd="$@"

echo -e "Handing over to command: $@"
chroot --userspec=${KERBEROS_USERNAME}:${KERBEROS_USERNAME} / "$@" &
child=$!
wait "$child"

Also we use this krb5.conf inside the docker image:

[logging]
    default = FILE:/var/log/krb5libs.log
    kdc = FILE:/var/log/krb5kdc.log
    admin_server = FILE:/var/log/kadmind.log
[libdefaults]
    dns_lookup_realm = false
    ticket_lifetime = 24h
    renew_lifetime = 7d
    forwardable = true
    rdns = false
    default_ccache_name = FILE:/tmp/krb5cc_%{uid}
    default_realm = <YOUR DOMAIN>
[realms]
    <YOUR DOMAIN> = {}
[domain_realm]
    <your domain (lowercase)> = <YOUR DOMAIN>
    .<your domain (lowercase)> = <YOUR DOMAIN>

It's very important to use file based key caching. If you use the default (kernel key store) your docker containers will interfere with each other because the keystore is not virtualised!

roederja2 on 17 May 2018

👍1

@roederja2 , Jann, thanks.
We got your script working but the .net core client still throws an exception:

with connection string:
Data Source=devdb;Initial Catalog=someCatalog;Integrated Security=True;Trusted_Connection=True;

Unhandled Exception: System.Data.SqlClient.SqlException: Cannot access Kerberos ticket. Ensure Kerberos has been initialized with 'kinit'.
ErrorCode=InternalError, Exception=Interop+NetSecurityNative+GssApiException: GSSAPI operation failed with error - An invalid status code was supplied (Server not found in Kerberos database).

Thx in advance

undecided2013 on 17 May 2018

I have not seen this before.
Can you get it to work outside of docker, but on linux? Have you tried running klist inside your container to see if you have a valid ticket?

roederja2 on 18 May 2018

@roederja2 , klist returns valid tickets. We will probably try on native linux on Monday.

This problem surfaces all over different msft products
https://github.com/Microsoft/sqlopsstudio/issues/141
https://github.com/Microsoft/vscode-mssql/issues/985

and other flavours as well with

sqlException: Cannot access Kerberos ticket. Ensure Kerberos has been initialized with 'kinit'. ErrorCode=InternalError, Exception=Interop+NetSecurityNative+GssApiException: GSSAPI operation failed with error - An unsupported mechanism was requested (unknown mech-code 0 for mech unknown)

The commonality that I see is the the mechanism by which the spn is set on the sql server. If it's done at the service account level, these errors seem to pop up. If it's done at the computer name level, things seem to work. Which mechanism are you using?

undecided2013 on 18 May 2018

Just talked to our DBA and apparently we are also using SPNs at the service account level.

roederja2 on 22 May 2018

@roederja2 Thanks for putting the script here. I tried it from a Linux machine. The Kerberos setup seems to work fine, my klist shows 06/22/2018 11:31:37 06/22/2018 21:31:37 krbtgt/****.******@****.******.

However, executing sqlcmd or a dotnet app (with SqlClient) doesn' work with Integrated Security=true. Any suggestions how should I debug this further ?

sqlcmd -E -S <server> -d <database> gives me:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider: Server not found in Kerberos database.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot generate SSPI context.

SqlClient gives me:

Unhandled Exception: System.NotSupportedException: The keyword 'integrated security' is not supported on this platform.
   at System.Data.SqlClient.SqlConnectionString..ctor(String connectionString)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(DbConnectionPoolKey key, DbConnectionPoolGroupOptions poolOptions, DbConnectionOptions& userConnectionOptions)
   at System.Data.SqlClient.SqlConnection.ConnectionString_Set(DbConnectionPoolKey key)
   at System.Data.SqlClient.SqlConnection.set_ConnectionString(String value)
   at KerbTester.MsSqlTester.TestIntegratedAuthConnection() in /mnt/c/Repos/LinuxKerberosPlayground/KerbTester/KerbTester/MsSqlTester.cs:line 19
   at KerbTester.Program.Main(String[] args) in /mnt/c/Repos/LinuxKerberosPlayground/KerbTester/KerbTester/Program.cs:line 24

valorl on 22 Jun 2018

Looks like you aren't using a recent version of the Data.SqlClient package. I think you need to be on dotnetcore v2.0 or higher and also install Data.SqlClient explicitly.

roederja2 on 22 Jun 2018

@roederja2 My System.Data.SqlClient package is @ 4.5.1 (that should be latest)

This is my dotnet --info:

.NET Core SDK (reflecting any global.json):
 Version:   2.1.301
 Commit:    59524873d6

Runtime Environment:
 OS Name:     ubuntu
 OS Version:  16.04
 OS Platform: Linux
 RID:         ubuntu.16.04-x64
 Base Path:   /usr/share/dotnet/sdk/2.1.301/

Host (useful for support):
  Version: 2.1.1
  Commit:  6985b9f684

.NET Core SDKs installed:
  2.1.301 [/usr/share/dotnet/sdk]

.NET Core runtimes installed:
  Microsoft.AspNetCore.All 2.1.1 [/usr/share/dotnet/shared/Microsoft.AspNetCore.All]
  Microsoft.AspNetCore.App 2.1.1 [/usr/share/dotnet/shared/Microsoft.AspNetCore.App]
  Microsoft.NETCore.App 2.1.1 [/usr/share/dotnet/shared/Microsoft.NETCore.App]

valorl on 22 Jun 2018

From the error message it is possible that your Sql Server SPN is not registered with KDC and hence a ticket to authenticate to the Sql server is not being generated.
Can you confirm if you have Kerberos configured on the server?

You could login using SSMS andbtrybthe following query
SELECT
s.session_id,
c.connect_time,
s.login_time,
s.login_name,
c.protocol_type,
c.auth_scheme,
s.HOST_NAME,
s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id

saurabh500 on 22 Jun 2018

@saurabh500 I don't have the permissions to do that, but I've confirmed with an admin Kerberos should be setup. I did try to do setspn -L <SQL_SERVER_HOSTNAME> as per the vscode-mssql instructions, after logging in to the server using SSMS. I did see some entries with the server name but none of them started with MSSQLSvc\. Is that important ?

valorl on 22 Jun 2018

The MSSQLSvc is important because the client tries to construct an SPN prefixed with MSSQLSvc and the SqlServer SPNs begin with MSSQLSvc

Re ::
I did try to do setspn -L <SQL_SERVER_HOSTNAME>

Above command give you correct results if your SQL Server running under LocalSystem or NetworkService account.

If the server is running under a domain account, then you can get the list of SPN by using setspn -L <mydomain><username>

saurabh500 on 22 Jun 2018

@saurabh500 The SQL Server is running on a remote machine of which I only know the hostname unfortunately.

valorl on 23 Jun 2018

I would like to jump in here and say that sqlcmd works fine with the spn as is for us. SqlClient does not. Why is SqlClient operating differently? Our dbas have said that the SPN of the Sql server is prefixed with MSSQLSvc if I remember correctly.

undecided2013 on 23 Jun 2018

@undecided2013 I would recommend filing a new bug describing how this issue can be reproduced, to initiate an investigation.

saurabh500 on 23 Jun 2018

@saurabh500 , I posted earlier in this issue about how to repro. Used @roederja2 script to set and verify kerberos. SqlCmd works fine after that but SqlClient does not. Obviously they are doing things differently.

undecided2013 on 23 Jun 2018

@undecided2013 I understand that you have described the problem on this issue, however this issue is closed and it was tracking the overall bigger work for integrated auth. The engineers working on SqlClient are not looking at closed issues to investigate. In fact the active engineers are not even subscribed to this issue. If issue exists and wasn't resolved I recommend you file a new bug which goes through our triage process and someone from SqlClient team will respond and if needed, fix this.

saurabh500 on 23 Jun 2018

@saurabh500 I have just confirmed with our admin that Kerberos is enabled, SQL Server runs under a domain account and that it does indeed have a MSSQLSvc\ prefixed SPN.
Any idea why would I get SSPI Provider: Server not found in Kerberos database. from sqlcmd then ? Do you know what exactly does the error msg say and what could I do to debug it further ?

valorl on 25 Jun 2018

@saurabh500 I also managed to get the right SqlClient version now. Maybe the error message from SqlClient is more descriptive regarding what my problem might be?

Unhandled Exception: System.Data.SqlClient.SqlException: Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at KerbTester.MsSqlTester.TestIntegratedAuthConnection() in C:\Repos\LinuxKerberosPlayground\KerbTester\KerbTester\MsSqlTester.cs:line 22
   at KerbTester.Program.Main(String[] args) in C:\Repos\LinuxKerberosPlayground\KerbTester\KerbTester\Program.cs:line 24

valorl on 25 Jun 2018

@valorl At this point, I think this needs more investigation from the SqlClient team. Can you file a new bug and especially mention that the server process is running as a domain user?
Thanks

saurabh500 on 26 Jun 2018

cc @keeratsingh FYI.

saurabh500 on 26 Jun 2018

@saurabh500 Here's the new isssue dotnet/corefx#30755

valorl on 29 Jun 2018

@roederja2

I’m trying ur cron, but when executing dotnet xxx.dll I’m getting/procs/1/map_files access denied.

The stack trace points to system file watcher trying to read appsettings and got denied. How is it working for u?