Unable to Connect on Mac with Kerberos

[skodaice]

• SQL Operations Studio Version: 0.23.6

Steps to Reproduce:
Get a valid kerberos ticket on Mac OS High Sierra
Attempt to connect to sql server with Windows Authentication

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:c2676b9d-8cf9-41f3-b38c-60c28835d52b

[kburtram]

@skodaice could you please check if Kerberos is enabled on the SQL Server following @saurabh500 suggestions on this issue microsoft/vscode-mssql#985? Thanks!

[smokinguns]

i got the same error, but ran kinit from bash prompt. It then prompted me for my active directory password. I restarted sqlops and it worked when windows auth selected.

[sloncho]

I see simila error, connecting from Linux. Using kinit changes the message to:

GSSAPI operation failed with error - An invalid status code was supplied (Server not found in Kerberos database).

We are using SQL 2016 in always on mode. Using the suggested setspn commands we find on the linked bugs does not help, as when the target is the virtual listener, it requires the service account.

We set SPN like this:

setspn -S MSSQLSvc/vagentname.domain.com domain.com\svcaccount

After the above setup, setspn -L vagentname does not show MSSQLSvc principle name, but going with setspn -L svcaccount lists the service enabled for the virtual agent:

Registered ServicePrincipalNames for CN=svcaccount,CN=Managed Service Accounts,DC=domain,DC=COM:
        MSSQLSvc/VAGEBTNAME.DOMAIN.COM:1433

Still, trying to connect using sqlops produces the error above.

[skodaice]

@kburtram yes Kerberos is enabled and working on the server.

[genio]

Related: #162 #112

[Code-DJ]

Hi using setspn works when the SQL Server is on the same domain (Domain A) but doesn't when it is on a different domain (Domain B). There is a two way trust between the two domains. Are there additional steps required to make this work across domains? /cc @kburtram @saurabh500

[Code-DJ]

I was able to connect to SQL Servers in other domain that has a two-way trust with the current domain. Had to make the following changes to make it work. All these steps are on macOS.

Create a new file (if missing) /etc/krb5.conf with the following contents. This file did not exist on my Mac.

[libdefaults]
    default_realm = MYDOMAIN.LOCAL

[realms]
MYDOMAIN.LOCAL = {
    kdc = mydomainserver.mydomain.local
    admin_server = mydomainserver.mydomain.local
    master_kdc = mydomainserver.mydomain.local
    default_domain = mydomain.local
}

OTHERDOMAIN.LOCAL = {
    kdc = otherdomainserver.otherdomain.local
    admin_server = otherdomainserver.otherdomain.local
    master_kdc = otherdomainserver.otherdomain.local
    default_domain = otherdomain.local
}

[domain_realm]
    .mydomain.local = MYDOMAIN.LOCAL
    mydomain.local = MYDOMAIN.LOCAL
    .otherdomain.local = OTHERDOMAIN.LOCAL
    otherdomain.local = OTHERDOMAIN.LOCAL

On each SQL Server, if you have not already run setspn, run it to enable kerberos:

setspn -A MSSQLSvc/SQLSERVERNAME.otherdomain.local otherdomain\serviceaccount

Back on your Mac, run the following and enter your domain password:

kinit

In SQLOps make sure you qualify all SQL Servers on the other domain with the domain name, like ServerName.OtherDomain.local. You should now be able to connect to servers on other domains.

[HoveyJr]

@skodaice @Code-DJ @kburtram I Have the same configuration as @sloncho, where the SPN for MSSQLSvc is bound to a service account to accommodate kerberos auth. See: https://technet.microsoft.com/en-us/library/bb735885.aspx

Curious if anyone has gotten this connection from SQLOpsStudio working with a setup like that, where instead of the SPN being bound to the host, it is bound to the service account SQL runs as. I have confirmed Kerberos is enabled and other programs can connect using kerberos without trouble.

[kburtram]

We've fixes several issues in this feature area. Please reactive if this particular issue persists.