Windows Authentication does not work on macOS

[Code-DJ]

• SQL Operations Studio Version:
  Version 0.23.6 (0.23.6)

Steps to Reproduce:
Open SQL Operations Studio
Enter Server name - tried both just the name and name.domain.com
Authentication type = Windows Authentication
Click Connect
Get an error listed below

My iMac is connected to windows domain. I login to the mac using domain credentials. When I try to connect using windows authentication, I get the following error:

System.Data.SqlClient.SqlException (0x80131904): 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)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, 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.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass22_0.b__0(Task1 _) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass28_0.<b__0>d.MoveNext() in C:\J\jobs\sqltoolsservice_master_win\workspace\src\Microsoft.SqlTools.ServiceLayer\Connection\ReliableConnection\ReliableSqlConnection.cs:line 298
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.d__46.MoveNext() in C:\J\jobs\sqltoolsservice_master_win\workspace\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 542
ClientConnectionId:719fbb11-e2be-46ca-b44c-fb57f4179fd6

[Code-DJ]

Additionally, I have followed the instructions for the mssql extension of vscode and still get the error:

klist shows the following:

Credentials cache: API:40D6C9CB-493E-4FEB-A227-1B551220F829
        Principal: username@DOMAIN.COM

  Issued                Expires               Principal
Nov 15 10:30:32 2017  Nov 15 20:30:32 2017  krbtgt/DOMAIN.COM@DOMAIN.COM
Nov 15 10:42:06 2017  Nov 15 20:30:32 2017  HTTP/server1.domain.com@DOMAIN.COM
Nov 15 10:53:15 2017  Nov 15 20:30:32 2017  HTTP/server2@DOMAIN.COM

I have dotnet 2.0 installed

❯ dotnet --version
2.0.0

/etc/pam.d/authorization added default_principal at the end of first line as shown below

auth       optional       pam_krb5.so use_first_pass use_kcminit default_principal

/etc/pam.d/screensaver added default_principal at the end of first line as shown below

auth       optional       pam_krb5.so use_first_pass use_kcminit default_principal

created a new file /etc/krb5.conf

[libdefaults]
  default_realm = DOMAIN.COM

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

Rebooted the machine
Tried to connect, didn't work
Ran kinit username@domain.com
Entered password
Tried to connect, didn't work

[jpcho83]

I am also having this exact same issue. Please advise.

[saurabh500]

Can you follow the recommendations mentioned at
microsoft/vscode-mssql#985 (comment)
microsoft/vscode-mssql#985 (comment)

[gedasss]

same here on ubuntu 17.10

[tjbjr02]

I was having the same issues following these instructions.
Make sure your domain information in the /etc/krb5.conf is in all caps. Also ensure when you kinit you put the domain information in your login information in all caps also. Once I did this, it worked immediately.

[makirby]

Having very much the same problem as @Code-DJ my config looks very much the same.

@saurabh500 After running setspn -l SQLSERVERNAME against the SQL server I cannot see MSSQLSvc/SQLSERVERNAME listed, only RestrictedKrbHost/SQLSERVERNAME would this be causing the issue?

[haled]

I used all caps as @tjbjr02 stated above and it worked. My command looked like: kinit USERNAME@DOMAIN.COM

[Code-DJ]

Same as @makirby ran setspn and only see RestrictedKrbHost/SQLSERVERNAME

Tried @tjbjr02 suggestion. Verified that domain information in my /etc/krb5.conf is all uppercase. Even though I am logged in as domain user on the Mac and klist does list Credentials cache. Ran klist USERNAME@DOMAIN.COM. Tried both SQLSERVERNAME and SQLSERVERNAME.DOMAIN.COM in SQL Ops Studio. Still get the same error.

Here's what my /etc/krb5.conf looks like:

[libdefaults]
  default_realm = DOMAIN.COM

[realms] DOMAIN.COM = {
   kdc = DOMAINAD.DOMAIN.COM
}

Here's what my klist looks like immediately after login - note the username is lowercase:

❯ klist
Credentials cache: API:C263F9FC-2D3B-4A1A-88D7-BE4970B51830
        Principal: username@DOMAIN.COM

  Issued                Expires               Principal
Nov 17 09:51:24 2017  Nov 17 19:51:24 2017  krbtgt/DOMAIN.COM@DOMAIN.COM
Nov 17 09:51:27 2017  Nov 17 19:51:24 2017  cifs/FILESERVER@DOMAIN.COM

[saurabh500]

Thanks @Code-DJ and @makirby for the information.
@makirby Yes, not having a Sql Server SPN registered on the Sql Server means that your client OS cannot get a Kerberos token for Windows authentication on the Client machine.
The SPN is registered and used by the KDC to generate a token for the SQL server. Looks like your SQL Server is not configured to allow Kerberos authentication.

From Unix / macOS we support integrated authentication using Kerberos only. You will need to register an SPN for the SQL server to make sure a token can be generated for the server and Kerberos auth can proceed.

[Code-DJ]

Thanks @saurabh500 I can confirm this works.

Ran the following command on my SQL Server:

setspn -A MSSQLSvc/SERVERNAME.Domain.com username

For username I used the same name that is running the SQL Server Service. I hope it is correct and does not have any security impact. Thanks!

Through process of elimination I will try and figure out which items I don't need. For example in trying this out, I had to create the following files:
~/.ssh/config file
/etc/krb5.conf

Didn't have default_principal in:
/etc/pam.d/authorization
/etc/pam.d/screensaver

Side note: After running the setspn -A command, I re-ran setspn -L %COMPUTERNAME%. I still only see RestrictedKrbHost.

[Code-DJ]

Verified that it still works after I did the following:

Deleted the following files:
rm ~/.ssh/config
sudo rm /etc/krb5.conf

Removed default_principal from the following files:
/etc/pam.d/authorization
/etc/pam.d/screensaver

Rebooted the Mac. After login, SQL Ops Studio continues to work. Note that I login to the Mac as a active directory domain user (not a local macOS user).

[saurabh500]

Thanks for the confirmation @Code-DJ

For username I used the same name that is running the SQL Server Service. I hope it is correct and does not have any security impact. Thanks!

You are good here wrt security.

Side note: After running the setspn -A command, I re-ran setspn -L %COMPUTERNAME%. I still only see RestrictedKrbHost.

I have observed this as well. I think this may have to do with the AD replication of the SPN. I am no expert with the internals of AD and its handling and propagation of data. However I have seen these delay in SPN propagation in our corp environment as well.

[saurabh500]

Folks I will close this issue considering that we know about the end to end setup requirements for Windows Authentication to work.

In case of @Code-DJ he could set an SPN but that may always not be possible based on the permissions provided by the Administrators of the Active Directory in the organization.
However since many developers in an organization may need to setup their server SPNs for different servers like IIS or Sql Server, it is a common scenario to allow the members of AD to allow SPN registration.