2

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?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Valentyn Hruzytskyi
  • 1,772
  • 5
  • 27
  • 59
  • 3
    Looks like you need to check the security in the DB side to ensure the ID you are using has access to the DB (and has permissions to do what you are trying to do) – Brad Dec 15 '20 at 21:09
  • TY @Brad. Can you clarify to me - How can I manage database access with visual studio? – Valentyn Hruzytskyi Dec 15 '20 at 21:40
  • 1
    Hi @ValentynHruzytskyi As the error message said, the user is not allowed to login the SQL Server Database, you could either create a login on SQL Server for that account, or use another valid SQL Server account in your connection string. – Zhi Lv Dec 16 '20 at 02:39
  • 1
    To create a connection with SQL Server in VS, you could check [Add new connections](https://docs.microsoft.com/en-us/visualstudio/data-tools/add-new-connections?view=vs-2019) and [Connect to SQL Server using VS](https://blogs.gre.ac.uk/cmssupport/application-development/programming/asp-net/connecting-to-sql-server-using-visual-studio/). To use MySQL database in Asp.net core application, you can install the MySql.Data.EntityFrameworkCore NuGet package, then using the EF core to manage data, check [this tutorial](https://dev.mysql.com/doc/connector-net/en/connector-net-entityframework-core.html) – Zhi Lv Dec 16 '20 at 02:42

1 Answers1

2

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.

Emin Mesic
  • 1,681
  • 2
  • 8
  • 18
  • 2
    Thanks. I installed SQL Managment Studio, logged with windows autorization ("(localdb)\MSSQLLocalDB" server name used) and created DevShop database. And all work well now. – Valentyn Hruzytskyi Dec 20 '20 at 16:27