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:
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).
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.