How to solve 'Cannot authenticate using Kerberos' issue doing EF Core database scaffolding in Linux(Ubuntu 18.04)? Are there any solutions?

Question

I've been trying to develop a simple AspNetCore application with EntityFrameworkCore to connect and work with the MSSQL server database. And manage all this by Rider IDE, a tool for Database client (DBeaver) and dotnet command line interface(dotnet ef). I'm using the database first approach(create a database on the MSSQL server, fill it with tables, and then build Models based on tables).

My STEP-by-STEP actions:

1)install and set up MSSQL server for my machine working on Ubuntu 18.04. Install the command line tool "SQLCMD". /// Link to guide - https://learn.microsoft.com/en-gb/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-ver15

2)locally connected to my MSSQLServer instance. sqlcmd -S localhost -U SA -P 'MyPasswd'

3)Using Transact-SQL created a Database and installed a DB client (DBeaver) to quickly manage my databases now and in the future.

The next step, as I supposed, was to use tutorials about connecting my Web Application to a database that was found here https://blog.jetbrains.com/dotnet/2017/08/09/running-entity-framework-core-commands-rider/ and here https://www.entityframeworktutorial.net/efcore/create-model-for-existing-database-in-ef-core.aspx

My ASP.NET Core project's package references:

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Tools

After typing in the CLI command dotnet ef dbcontext scaffold "Server=localhost;Database=WebAppDB;Integrated Security=true;" Microsoft.EntityFrameworkCore.SqlServer -c RsvpContext ( to build "RsvpContext" context to connect to my database WebAppDB.)

I see what I see:

Build started...
Build succeeded.
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 - Unspecified GSS failure.  Minor code may 
provide more information (SPNEGO cannot find mechanisms to negotiate).


   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.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, SqlAuthenticationProviderManager sqlAuthProviderManager)

   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)

   at Microsoft.Data.SqlClient.SqlConnection.Open()

   at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(DbConnection connection, DatabaseModelFactoryOptions options)
   
at Microsoft.EntityFrameworkCore.SqlServer.Scaffolding.Internal.SqlServerDatabaseModelFactory.Create(String connectionString, DatabaseModelFactoryOptions options)

   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ReverseEngineerScaffolder.ScaffoldModel(String connectionString, DatabaseModelFactoryOptions databaseOptions, ModelReverseEngineerOptions modelOptions, ModelCodeGenerationOptions codeOptions)

   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String outputContextDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)

   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String outputDbContextDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)

   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_0.<.ctor>b__0()

   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()

   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:38f805bc-5879-458b-9256-d6a201d7ce99
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 - Unspecified GSS failure.  Minor code may 
provide more information (SPNEGO cannot find mechanisms to negotiate).

   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)

If someone, preferably working on Linux, had the same issue, please let me know and share your solutions(guide on what to do in this situation).

Contempt

Configuring Kerboros on Linux server is a long journey, and not simple. The simple answer is to use SQL Authentication here. Unless you're very familiar with Linux, and SQL Server, I don't suggest trying to even go down the Kerboros route. – Passus

preferably working on Linux Windows uses Kerberos for Windows Authentication, where the configuration is performed by the OS automatically when joining a domain, when the machine starts up etc. That's not the case with Linux. You didn't post any Kerberos configuration steps in your question. Is the machine part of an AD domain? Is Kerberos configured in some other way? – Infusorian

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. Have you done this? – Infusorian

@PanagiotisKanavos, answering your 1 question, I can say that no connection via Kerberos is configured, cause after typing SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@spid ; the console output is auth_scheme | SQL – Contempt

This is from blog learn.microsoft.com/en-us/sql/database-engine/configure-windows/… And I'm just stucked on what to do next – Contempt

Is it true, that for Linux I should install and configure Kerberos both client and server as here howtoforge.com/tutorial/… or not. Cause this article is for win – Contempt

@PanagiotisKanavos, could you explain, how use SQL Authentication? I'll try – Contempt

@Contempt the below answer is good for me. – Tyus

Answer 1

I had this issue when I was transforming from an on-premise to an Azure Kubernetes (AKS cluster) cloud solution.

The short answer: it is related to Integrated Authentication.

Longer answer: The reason is when using Windows OS like the on-premise solution we have had, it can support integrated authentication, but when using Linux OS that is for example hosted as stand-alone, VM, Docker, or/and Container solutions in Kubernetes, then integrated authentication is not possible.

I changed integrated security to false, so the Integrated Authentication should be like (integrated security=false;) and I also provided a username and password that have the proper access rights to the database in the connection string (User ID=YourProperUserName;Password=YourProperUserPassword;).

The problem was solved.

In addition:

• @Ismail Umar mentioned in a comment, to remove Trusted_Connection=True
• @alexDuty mentioned in a comment, to add TrustServerCertificate=True

I personally did not have the issues mentioned in the comments but highlighted them in case someone gets the benefit of it.

Contracture

In addition to the answer above, incase you have Trusted_Connection=True present in your connection string. remove it. – Oolite

One more addition that maybe prove useful for others: applying both Integrated security=False and TrustServerCertificate=True has solved my issue – Courage

Answer 2

Removing Trusted_Connection=True and adding TrustServerCertificate=True worked for me to run EF migrations on a sql server hosted in a docker container.

Connection String:

Server=127.0.0.1,1433;Database=AuthDb;Integrated security=False;User=sa;Password=password;TrustServerCertificate=True;

Bladdernose