Kerberos authentication doesn't work with NuGet package 2.1.1

[Code-DJ]

Can't authenticate with kerberos 2.1.1. Works fine with 2.0.1

Following is the error I get with Azure Data Studio where they upgraded their reference from older version to 2.1.1 in ADS 1.26.0:

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot authenticate using Kerberos. Ensure Kerberos has been initialized on the client with 'kinit' and a Service Principal Name has been registered for the SQL Server to allow Kerberos authentication.
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 Microsoft.Data.SqlClient.SNI.SNIProxy.GenSspiClientContext(SspiClientContextStatus sspiClientContextStatus, Byte[] receivedBuff, Byte[]& sendBuff, Byte[] serverName)
   at Microsoft.Data.SqlClient.SNI.TdsParserStateObjectManaged.GenerateSspiClientContext(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength, Byte[] _sniSpnBuffer)
   at Microsoft.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.SSPIError(String error, String procedure)
   at Microsoft.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength)
   at Microsoft.Data.SqlClient.TdsParser.SSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength)
   at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, Nullable`1 fedAuthFeatureExtensionData)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.<>c__DisplayClass47_0.<CreateReplaceConnectionContinuation>b__0(Task`1 _)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__274_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location where exception was thrown ---
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.SqlTools.ServiceLayer.Connection.ReliableConnection.ReliableSqlConnection.<>c__DisplayClass30_0.<<OpenAsync>b__0>d.MoveNext() in D:\a\1\s\src\Microsoft.SqlTools.ManagedBatchParser\ReliableConnection\ReliableSqlConnection.cs:line 314
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.SqlTools.ServiceLayer.Connection.ConnectionService.TryOpenConnection(ConnectionInfo connectionInfo, ConnectParams connectionParams) in D:\a\1\s\src\Microsoft.SqlTools.ServiceLayer\Connection\ConnectionService.cs:line 550
ClientConnectionId:dafb7f1c-dc64-44b5-91b3-578734a9c5d0

[Code-DJ]

I have since updated one of our .net projects from 2.0.1 to 2.1.1 and see the same issue. Works with 2.0.1.

Here's the stack trace for it:

   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.SSPIError(String error, String procedure)
   at Microsoft.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength)
   at Microsoft.Data.SqlClient.TdsParser.SSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[]& sendBuff, UInt32& sendLength)
   at Microsoft.Data.SqlClient.TdsParser.TdsLogin(SqlLogin rec, FeatureExtension requestedFeatures, SessionData recoverySessionData, Nullable`1 fedAuthFeatureExtensionData)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.Login(ServerInfo server, TimeoutTimer timeout, String newPassword, SecureString newSecurePassword)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool)
   at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.Open()
   at .....

[cheenamalhotra]

@Code-DJ

Could you please share your connection string?

[karinazhou]

Thank you @Code-DJ for moving the issue here. Could you please try this Microsoft.Data.SqlClient nuget package directly from a .net console appliation with the same connection string you are having the issue?

Nuget from Pipeline

[Code-DJ]

data source=SERVERNAME;initial catalog=DATABASENAME;trusted_connection=true

Also tried variations of the following based on other issues here:
data source=tcp:SERVERNAME,1433;initial catalog=DATABASENAME;trusted_connection=true

[Code-DJ]

@karinazhou when I click that link it shows "401 - Uh-oh, you do not have access.".

[karinazhou]

Microsoft.Data.SqlClient.2.1.0-pull-4fae95f.21054.2.nupkg.zip

@Code-DJ Could you try this attachment? You will need to unzip it first since github doesn't allow me to attach the nuget file directly.

[Code-DJ]

@karinazhou I still get the same error.

Did the following:

• Updated my NuGet.config to point to local folder where I placed the nupkg.
• Added the package

 dotnet add package Microsoft.Data.SqlClient --prerelease

• Verified it added the following to csproj

<PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.0-pull-4fae95f.21054.2" />

• When I debug, get the same kerberos error.

Note that if fails for the following as well but works for 2.0.1:

<PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.0" />

[cheenamalhotra]

I'm not sure I follow, is this also reproducible with v2.1.1 or just v2.1.0?

<PackageReference Include="Microsoft.Data.SqlClient" Version="2.1.1" />

Related issue #825 was fixed in v2.1.1.

[Code-DJ]

@cheenamalhotra yes, it fails in 2.1.0, 2.1.0-pull-4fae95f.21054.2 and 2.1.1 but works on 2.0.1.

[karinazhou]

@Code-DJ Can you also try with 2.0.1-preview1 and 2.0.1-preview2 to see how it works?
When searching some information online, I see that you created a similar issue about the same error in 2017. Do you still use the same environment setting?
microsoft/azuredatastudio#23

[Code-DJ]

@karinazhou I assume you mean 2.1.0-preview2.20297.7 and 2.1.0-preview1.20235.1.

It fails for both. I didn't find any previews for 2.0.1 on https://www.nuget.org/packages/Microsoft.Data.SqlClient/

I no longer have /etc/krb5.conf and it worked for 2.0.1 as well as for Azure Data Studio 1.25*
Earlier today, I tried adding it back and then doing kinit but it had no impact.

Verified SPN is still active on the server hosting SQL Server.

[karinazhou]

@Code-DJ Sorry, my bad. Yes, I mean 2.1.0-previews. Thank you for the information about different version testing. This can help us narrow down the possible change may cause this issue.

Can you share your SPN setting with us? In preview1, we have changed the SPN generation:
#629

[Code-DJ]

@karinazhou ran the following on one of the SQL Server VMs:

setspn -l mysqluser

# mysqluser is the domain user running the SQL Server service

It returned the following:

Registered ServicePrincipalNames for CN=mysqluser,OU=foo,OU=bar,DC=DOMAIN,DC=local:
    MSSQLSvc/SERVER1.Domain.local
    MSSQLSvc/SERVER2.Domain.local
    :

If I run "setspn -l SERVER1", I don't see MSSQLSvc/SERVER1.Domain.local listed.

Note that I am connecting to the server from my local machine using my domain user on the mac.

[karinazhou]

@Code-DJ Can you try setSPN with port? It should give you something like
MSSQLSvc/SERVER1.Domain.local:1433

[Code-DJ]

@karinazhou yay! that worked. It is the else condition causing the problem.

• Deleted the existing SPNs for all server using setspn -D MSSQLSvc/SERVERNAME.Domain.com username.
• Ran setspn -A MSSQLSvc/SERVERNAME.Domain.com:1433 username
• On my local machine ran kinit
• Tried the .net project with 2.1.1, worked!
• Tried Azure Data Studio 1.26, worked!

[karinazhou]

@Code-DJ Cool 👍 Thank you for confirming this. We will add this information to our wiki to avoid further similar issues.

[juls858]

@karinazhou is there a work around that doesn't require changing the SPN? I don't manage the SQL servers at my work so I don't have permissions to change SPN.

[karinazhou]

Hi @mas-dse-juremigi, can you first try with the nuget package I attached this comment:
#926 (comment)

We have another fix for the instance name which uses named instance in Linux environment. The SPN change is specific to the Kerberos authentication failure.

Do you have backslash in the server name? If so, that may be due to another issue which has been fixed in the nuget package I have attached.

[juls858]

@karinazhou I'm not sure how to do this as I am a python developer, not familiar with .net.

[karinazhou]

@mas-dse-juremigi Can you share the connection string you use?

And also, can you run
setspn -L <your_username>
to check if you have port number in the SPN?

If you are connecting to the default instance and don't have port number in registered SPNs, you are probably facing the same SPN issue. If so, I am afraid that there is no other workaround unless adding the port to the new SPNs.

[Code-DJ]

@cheenamalhotra, @karinazhou should I reopen this issue?

Per @saurabh500's note #627 (comment) it says that the code checks for both with and without port number, but that wasn't our experience with the recent update. I had to change the SPN specifically to include port number. It didn't work without.

[karinazhou]

@Code-DJ Please feel free to reopen it and we will look into this further.
Thanks!

[juls858]

@karinazhou I am using named instance with port number: server\instance, port
Does this mean the feature is working as designed and there is no future fix?

[karinazhou]

@mas-dse-juremigi For named instance, we have the fix for it ready which will be released soon.
We are currently working on the SPN generation change for default instance. I agree with @Code-DJ that we should check both with and without port number.

[karinazhou]

@Code-DJ One thing I would like to check with you. Have you tried with sqlcmd or isql with the same server name when you register the SPN without port number? You can test with the command like
sqlcmd -S SERVERNAME -E
when you register the server as MSSQLSvc/SERVERNAME.Domain.com

I would like to see the behavior of ODBC driver too.

[Code-DJ]

@karinazhou followed these steps:

1. Installed sqlcmd on mac using the steps in here https://cloudblogs.microsoft.com/sqlserver/2017/05/16/sql-server-command-line-tools-for-macos-released/
2. Removed SPN with port number
3. Added SPN without port number
4. Ran kinit
5. Ran sqlcmd -S SERVERNAME -E
6. Got the following error:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider: Server (MSSQLSvc/servername.domain.local:1433@DOMAIN.LOCAL) unknown while looking up 'MSSQLSvc/servername.domain.local:1433@DOMAIN.LOCAL' (cached result, timeout in 1200 sec).
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot generate SSPI context.

BTW, before removing the SPN, I had tried the sqlcmd and was connected with a "1>" prompt.

Also, not sure if sqlcmd and ODBC behind the scenes uses 2.1.1 as well.

[karinazhou]

@Code-DJ Thank you for the experiment.

sqlcmd uses ODBC driver which is separate from Microsoft.Data.SqlClient. If no port number is provided for TCP connection, they will append default 1433 to the SPN generation.

[karinazhou]

@Code-DJ Would you mind giving this custom nuget a try which contains the change to allow default instance without port:

MDS_Kerberos_fix.zip

It is expected to allow you to be connected successfully when there is only 1 SPN without port number registered.

[Code-DJ]

Hi @karinazhou that worked.

Added 2.1.0-pull-3be647b.21057.9 to my project and debugged, it fetched data from SQL Server. Thanks!