How to create connection with SQL Server in Visual Studio? Getting SqlException: Login failed for user

user_9689945 0
Viewed 596

I am new to ASP.NET and I'm trying to implement an ASP.NET Core MVC project.

I try to create database connection, but I got the error when the project tries to access to database with options:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;Database=DevShop;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

In the result I got the error

Cannot open database “DevShop” requested by the login. The login failed. Login failed for user 'DESKTOP-4S2H73J\Valeryi'

I found solution there: Cannot open database "test" requested by the login. The login failed. Login failed for user 'xyz\ASPNET'

and changed the connection string to:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;Database=DevShop;User ID=DESKTOP-4S2H73J;pwd=top$secret"
  }
}

In this case I get the error:

SqlException: A connection was successfully established with the server, but then an error occurred during the login process

Then I modified the string again:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\MSSQLLocalDB;Database=DevShop;User ID=DESKTOP-4S2H73J;pwd=top$secret;Trusted_Connection=True"
  }
}

And now I get error

SqlException: Cannot open database "DevShop" requested by the login. The login failed. Login failed for user 'DESKTOP-4S2H73J\Valeryi'.

I am totally confused...

  • How to correctly create connection with SQL Server in Visual Studio?
  • Can I use MySql server instead of SQL Server for ASP.NET Core MVC?
asp.net-core-mvc visual-studio sql-server
1 Answers

Let's clarify this part, somehow you cannot access to the database engine.

The SQL Server supports two authentication modes:

  • Windows Authentication
  • SQL Server Authentication

First, you tried to used Windows Authentication, and then SQL Server Authentication.

In the connection string Trusted_Connection=True or Integrated Security=true means that connection will use Windows Authentication. So, if there is one of these two parameters then you don't need to specify User and Password.

To check your server name connect to the SQL Engine through SQL Server Management Studio, and execute the following query:

SELECT @@SERVERNAME

As a result, I got DESKTOP-29GJ2IJ because this is a local machine and there are no named instances like for example DESKTOP-29GJ2IJ\MSSQLSERVER2017 I can use the name localhost in the connection string, but let's use the value from the result of the query.

Once, I know the exact name of the server then I can write a connection string:

Server=DESKTOP-29GJ2IJ;Database=DevShop;Trusted_Connection=True;

Useful page for connection strings is https://www.connectionstrings.com/sql-server/

You can connect to the another databases, so it is not only related to the MSSQL Server.

user_111207740
Related