Integrated authentication failing when attempting to connect to SQL Server from macOS Sierra

[carlowahlstedt]

This issue is originally discussed here.

Following the Kerberos Setup Instructions. Steps 1 through 3 work perfectly. However, when attempting step 4 I get the following:

mssql: Failed to connect: Cannot access Kerberos ticket. Ensure Kerberos has been initialized with 'kinit'.

[3:36:58 PM] Error connecting to server "[ServerName]". Details: 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).
   at System.Net.Security.NegotiateStreamPal.GssInitSecurityContext(SafeGssContextHandle& context, SafeGssCredHandle credential, Boolean isNtlm, SafeGssNameHandle targetName, GssFlags inFlags, Byte[] buffer, Byte[]& outputBuffer, UInt32& outFlags, Int32& isNtlmUsed)
   at System.Net.Security.NegotiateStreamPal.EstablishSecurityContext(SafeFreeNegoCredentials credential, SafeDeleteContext& context, String targetName, ContextFlagsPal inFlags, SecurityBuffer inputBuffer, SecurityBuffer outputBuffer, 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)

Per @kevcunnane request, I'm submitting an issue here.

The only other thing I've noticed since the other issue, is that if I run the klist command without first running the kinit command I have several other entries:

Credentials cache: API:ED70540F-329F-44FB-9C08-059260828BBB
        Principal: me@DOMAIN.COMPANY.COM

  Issued                Expires               Principal
Jul 19 22:31:24 2017  Jul 20 08:31:24 2017  krbtgt/DOMIAN.COMPANY.COM@DOMIAN.COMPANY.COM
Jul 19 22:31:41 2017  Jul 20 08:31:24 2017  host/COMPANYSERVER.DOMIAN.COMPANY.COM@DOMIAN.COMPANY.COM
Jul 19 22:31:41 2017  Jul 20 08:31:24 2017  HTTP/COMPANYSERVER.DOMIAN.COMPANY.COM@DOMIAN.COMPANY.COM

We do use the Jamf Casper Suite to manage our Macs, and I'm assuming that's what those other entries are from.

Finally, I also setup a .Net Core Console app to see if integrated security would run from there, following along the same lines as this issue and I received the same exception as the one the VS Code is giving me with the mssql extension.

macOS 10.12.5
dotnet 2.0.0-preview2-006497

[carlowahlstedt]

For clarity, updated to dotnet 2.0.0 and still seeing the issue.

[Code-DJ]

This has not been assigned any milestone. Can we get an idea on the timeline? Thanks.

[geleems]

@carlowahlstedt
Could you confirm that you use Fully Qualified Domain Name for SQL Server SPN?
The error is thrown from this code part (line# 139) :
https://github.com/dotnet/corefx/blob/dcc29528c9c4633e81b1937e7dd9fde7115a938c/src/Common/src/System/Net/Security/NegotiateStreamPal.Unix.cs#L122-L144
https://github.com/dotnet/corefx/blob/dcc29528c9c4633e81b1937e7dd9fde7115a938c/src/System.Net.Security/src/Resources/Strings.resx#L334-L336

• status: An unsupported mechanism was requested
• minorStatus: unknown mech-code 0 for mech unknown

We need to get information from System.Net.Security team under what condition that status / minorStatus message we get.

[geleems]

Do you have the following two lines in your ~/.ssh/config file, or in your /etc/ssh_config? :

GSSAPIAuthentication yes
GSSAPITrustDNS yes

The first is required to enable GSSAPI (Kerberos) authentication, the second one is required to get ssh to canonicalize the hostname via DNS and use the canonical name to obtain a host service ticket. Without the second one, ssh will use the exact hostname or IP address entered on the command line to try and obtain a host service ticket, and in this case it fails.

[carlowahlstedt]

@geleems I created the config file in ~/.ssh/config and added these two settings.

GSSAPIAuthentication yes
GSSAPITrustDNS yes

I then restarted but realized I should have first gone back through the original setup document. After I did that, it connected! So, looks like that document needs updated with your suggesting of adding these two lines into an ssh config file.

Would that be something that makes sense for everyone to make sure they have setup? Seems confusing to me why it would work for many others but not me?

Thanks so much for your help @geleems!!

[Code-DJ]

@geleems This did not work for me. Note: My mac is domain joined. I login as the domain user to the mac.

Created ~/.ssh/config file (was missing):
GSSAPIAuthentication yes
GSSAPITrustDNS yes

Created /etc/krb5.conf file (was missing):
[libdefaults]
default_realm = DOMAIN.COM

[realms]
DOMAIN.COM = {
kdc = adservername.domain.com
}

Ran:
kinit myuser@domain.com
entered my domain password.

klist (note I first tried kinit with lowercase domain.com, then uppercase DOMAIN.COM)

Credentials cache: API:{GUID}
Principal: myuser@DOMAIN.COM

Issued Expires Principal
Sep 25 11:43:16 2017 Sep 25 21:43:16 2017 krbtgt/DOMAIN.COM@DOMAIN.COM
Sep 25 11:43:46 2017 Sep 25 21:43:16 2017 HTTP/SERVERNAME1.DOMAIN.com@DOMAIN.COM
Sep 25 11:46:13 2017 Sep 25 21:43:16 2017 HTTP/SERVERNAME2@DOMAIN.COM
Sep 25 11:59:30 2017 Sep 25 21:43:16 2017 cifs/SERVERNAME3@DOMAIN.COM

In VSCode:
Create Create Connection Profile
sqlservername.domain.com
dbname
Integrated
profile name

Still get the same error.
mssql: Failed to connect: Cannot access Kerberos ticket. Ensure Kerberos has been initialized with 'kinit'.
mssql: Error: Unable to connect using the connection information provided. Retry profile creation?

Rebooted my computer and tried again in VSCode.
Still get the same error.

❯ dotnet --version
2.0.0

[carlowahlstedt]

@Code-DJ What version of macOS are you running? The release notes for v1.1 of the mssql extension states:

macOS "El Capitan" and older versions will not support this feature

[Code-DJ]

@carlowahlstedt macOS Sierra.

[carlowahlstedt]

I also think I had to update OpenSSL for some other tool I used. Not sure if that's applicable here. I believe I used Homebrew to do the update. You could potentially try that.

[Code-DJ]

@carlowahlstedt I had 1.0.2k 1.0.2l and it shows that it is the latest version. Thanks.

[geleems]

@carlowahlstedt
Thanks for the confirmation, and I am so glad yours is now working.
We realized some customers are experiencing Kerberos issue as you did, and currently work hard to find what configurations are missing in the instruction such as GSSAPIAuthentication and GSSAPITrustDNS.
I will definitely update the instruction for macOS by adding the missing config.

[carlowahlstedt]

Thanks @geleems we can close this issue whenever you're satisfied. Thanks again for the help.

[geleems]

@Code-DJ Can you check those settings in your machine?

---

/etc/pam.d/authorization :

# authorization: auth account
auth       optional       pam_krb5.so use_first_pass use_kcminit default_principal
auth       sufficient     pam_krb5.so use_first_pass default_principal
...

---

/etc/pam.d/screensaver :

# screensaver: auth account
auth optional pam_krb5.so use_first_pass use_kcminit default_principal
...

---

/etc/pam.d/sudo :

# sudo: auth account password session
auth sufficient pam_krb5.so try_first_pass default_principal
...

---

~/.ssh/config :

GSSAPIAuthentication yes
GSSAPITrustDNS yes
GSSAPIDelegateCredentials yes

[Code-DJ]

@geleems here are the values:

# authorization: auth account
auth       optional       pam_krb5.so use_first_pass use_kcminit
auth       optional       pam_ntlm.so use_first_pass
auth       required       pam_opendirectory.so use_first_pass nullok
account    required       pam_opendirectory.so

❯ cat /etc/pam.d/screensaver
# screensaver: auth account
auth       optional       pam_krb5.so use_first_pass use_kcminit
auth       required       pam_opendirectory.so use_first_pass nullok
account    required       pam_opendirectory.so
account    sufficient     pam_self.so
account    required       pam_group.so no_warn group=admin,wheel fail_safe
account    required       pam_group.so no_warn deny group=admin,wheel ruser fail_safe

❯ cat /etc/pam.d/sudo
# sudo: auth account password session
auth       sufficient     pam_smartcard.so
auth       required       pam_opendirectory.so
account    required       pam_permit.so
password   required       pam_deny.so
session    required       pam_permit.so

❯ cat ~/.ssh/config
GSSAPIAuthentication yes
GSSAPITrustDNS yes
GSSAPIDelegateCredentials yes

I didn't have the last line in .ssh/config. Didn't work even after adding it.

[geleems]

@Code-DJ
Thanks for confirming that.
Are you able to ssh and login to your macOS machine with Kerberos?

[Code-DJ]

@geleems I had never tried ssh using kerberos so not sure what exactly needs to be done but here is what I have tried:

I upgraded to macOS High Sierra yesterday

I login to my Mac using domain credentials. There are other macs at work that are setup the same way.

I ran the following command on my machine to ssh into another:

ssh -K ip-address

Got the following error:
line 2: Bad configuration option: gssapitrustdns

Removed the following line from .ssh/config

GSSAPITrustDNS yes

Now I have this

GSSAPIAuthentication yes
GSSAPIDelegateCredentials yes

Ran the same command:
ssh -K ip-address

It asked me to enter password, on entering the password it said Authentication failed.

[geleems]

@Code-DJ
Thanks for the information. Since the issue of the author was resolve, I am closing this ticket. Your issue is more closer to microsoft/vscode-mssql#985, so please refer this thread.