Repository navigation
Kerberos authentication doesn't work with NuGet package 2.1.1 #926
Description
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
Activity
Code-DJ commentedon Feb 23, 2021
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:
cheenamalhotra commentedon Feb 23, 2021
@Code-DJ
Could you please share your connection string?
karinazhou commentedon Feb 23, 2021
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 commentedon Feb 23, 2021
Code-DJ commentedon Feb 23, 2021
@karinazhou when I click that link it shows "401 - Uh-oh, you do not have access.".
karinazhou commentedon Feb 23, 2021
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 commentedon Feb 23, 2021
@karinazhou I still get the same error.
Did the following:
Note that if fails for the following as well but works for 2.0.1:
cheenamalhotra commentedon Feb 23, 2021
I'm not sure I follow, is this also reproducible with v2.1.1 or just v2.1.0?
Related issue #825 was fixed in v2.1.1.
Code-DJ commentedon Feb 23, 2021
@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 commentedon Feb 23, 2021
@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 commentedon Feb 23, 2021
@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 commentedon Feb 23, 2021
@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 commentedon Feb 23, 2021
@karinazhou ran the following on one of the SQL Server VMs:
It returned the following:
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 commentedon Feb 23, 2021
@Code-DJ Can you try setSPN with port? It should give you something like
MSSQLSvc/SERVER1.Domain.local:1433
Code-DJ commentedon Feb 23, 2021
@karinazhou yay! that worked. It is the else condition causing the problem.
3 remaining items
karinazhou commentedon Feb 24, 2021
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.
Triangulum9r commentedon Feb 24, 2021
@karinazhou I'm not sure how to do this as I am a python developer, not familiar with .net.
karinazhou commentedon Feb 24, 2021
@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 commentedon Feb 24, 2021
@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 commentedon Feb 24, 2021
@Code-DJ Please feel free to reopen it and we will look into this further.
Thanks!
Triangulum9r commentedon Feb 24, 2021
@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 commentedon Feb 24, 2021
@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 commentedon Feb 25, 2021
@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 -Ewhen you register the server as MSSQLSvc/SERVERNAME.Domain.com
I would like to see the behavior of ODBC driver too.
Code-DJ commentedon Feb 25, 2021
@karinazhou followed these steps:
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 commentedon Feb 25, 2021
@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 commentedon Feb 26, 2021
@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 commentedon Feb 26, 2021
Hi @karinazhou that worked.
Added 2.1.0-pull-3be647b.21057.9 to my project and debugged, it fetched data from SQL Server. Thanks!