Few Errors in SQL Server and How to Fix it

When starting to work with Entity Framework and SQL Server, you often run into the same errors. Sometimes these errors are caused by missing permissions for the database connection, and sometimes it’s caused the way that the database is accessed. In this article, we will go through some of the most common errors and some of the possible ways these errors could be fixed.

In Sql Server, user accounts and credentials are set at the server level, not at the database level. When you deploy an SQL database, only the database info moves over. You need to connect to the Database Server in your deployed environment , set up the username and password you want to use, and give the account appropriate permissions into the database. So, first you have to make sure that the user exist on SQL Server , that the user is enabled, and has access (mapped) to the correct database.

How to Fix Login failed for user ‘myUsername’

At first glance, this seems like it would cover a quite simple error: That the user credentials passed were not valid. This would seem like it could only be a problem with the connection string like a misspelling of the username or password, and that’s one of the possible causes of the problem. It could also be a problem in the SQL Server like that your user does not have permission to access the database or that the server does not allow SQL Server authentication. The local version of SQL Server has the Server authentication set to Windows Authentication mode by default, which does not enable the possibility to connect without Integrated Security.

The measures one can take to fix the problem are the following:

1. Check that the User Id and Password in the connection string are correct.
2. Ensure that Integrated Security is set to False in the connection string.
3. Check that the user is present in Security>Logins in the SQL Server connection in Microsoft SQL Server Management Studio.
4. Try resetting the password of the user.
5. Ensure that SQL Server authentication is enabled for the server in Server Properties>Security>Server authentication and then select SQL Server and Windows Authentication mode.

How to Fix Cannot open database “myDB” requested by the login. The login failed.

The error states that there could not be an established connection to a specific database on the server. This could mean that the specified database in the connection string does not exist or that there was a typo in the connection string.

Possible solutions to the problem could be:

1. Check that there are no typos in the database field in the connection string.
2. If your project is code-first, ensure that you’ve called myContext.Database.EnsureCreated() before using your context.
3. If your project is database-first, check the server if the database is present on the server using Microsoft SQL Server Management Studio.

How to Fix A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

This error states that it was not possible to connect to the specified server. The error often comes together with a long wait time because it wants to make sure that it does not just have a slow connection. A typo could cause the error in the connection string or that the server is configured wrong.

These are some of the ways to troubleshoot the problem:

1. Check that the server field in the connection string is written correctly.
2. Ensure that remote connection is allowed in Microsoft SQL Server Management Studio in Server Properties>Connection>Remote server connections.
3. When installing Microsoft SQL Server Management Studio, you also get a program called SQL Server Configuration Manager. This can only be used on the computer that the server is running. In SQL Network Configuration>Protocols for SERVERNAME TCP/IP needs to be enabled for it to be accessed.

Microsoft.EntityFrameworkCore.DbUpdateException

This error covers a lot of different errors. What they all have in common is that Entity Framework failed to save the changes to the database. It is essential to inspect the inner exceptions. It is often just a wrapper around all the System.Data.SqlClient.SqlException‘s that occur when the connection is established.
Since the ways to fix these errors are unique to each case, we will list some common causes and how to avoid and fix these errors in general terms.

There have been changes to the models that Entity Framework uses in your project
Entity Framework creates all the tables in a database by itself, but when the models in a project start to change, there is a clash between what Entity Framework expects and what’s actually on the SQL Server. A way to solve this is by using the Migration feature from Entity Framework when you make changes to your models.

The same SQL Server is used for many projects
When a lot of projects use the same database by accident or intentionally, there can be clashes in the naming of tables. This would mean that one project’s Entity Framework connection would think that its table was made, while the scheme is the one of another project. This can be avoided by using unique database names for different projects.

Duplicate of entries with the same primary key
If multiple entries with the same primary key are inserted into a table, then there is a conflict in the database. This can sometimes be solved by doing context.SaveChanges() only once per update and sometimes be avoided by explicitly making foreign keys in one-to-many relations instead of letting Entity Framework handle it.