How to get SQL Server Express connection string for Dapper/EF/SQLClient/etc-CodePudding

I just installed SQL Server 2019 Express, locally, and with Windows authentication (afaik). What I can't seem to do is connect to it with code. I copy/pasted the connection string output from the setup into Azure Data Studio, and it worked fine. However, I can't connect via code with Dapper / EF Core etc. Every connection string combination I've tried gives me a failed to authenticate message.

My question is this, how can I get the correct connection string so when I run an ASP.NET Web app I can connect to the database? My guess is that Windows auth is not getting passed in correctly, but I don't know if it's because of how VS runs the application, how I'm constructing the connection string, or something else.

Any advice/explanations would be appreciated!

That's also what gets displayed in the error message. So, it looks like the admin can't connect via VS, and that seems odd to me that the user listed as an admin can't authenticate with Integrated Security.

Current connection string:

{
    "ConnectionStrings": {
        "CustomerConnection": "Server=localhost\\SQLEXPRESS;Database=Customers;Integrated Security=True;"
    },
    ...
}

Dapper controller based from their tutorial:

[HttpPost(Name = "AddCustomer")]
public IActionResult Post(Customer newCustomer)
{
    try
    {
        var connString = _config.GetConnectionString("CustomerConnection");

        using IDbConnection db = new SqlConnection(_config.GetConnectionString("CustomerConnection"));

        string query = "INSERT INTO Customers (FirstName, LastName, Email) Values(@FirstName, @LastName, @Email)";

        int rowsAffected = db.Execute(query, newCustomer);

        return new JsonResult(new { RowsAffected = rowsAffected, Status = "success" });
    }
    catch (Exception ex)
    {
        return new JsonResult(new { RowsAffected = 0, Status = "failure", Error = ex.Message });
    }
}

Specific error message from code snippets, when run:

Cannot open database "Customers" requested by the login. The login failed.
Login failed for user [my-pc-user-here]

Update: the database name had a typo, correcting the spelling fixes the issue.

CodePudding user response：

As the error says

Cannot open database "Customers" requested by the login.

So either the database doesn’t exist or your login doesn’t have rights to connect to it.

CodePudding user response：

I copy/pasted the connection string output from the setup into Azure Data Studio, and it worked fine. However, I can't connect via code with Dapper / EF Core etc.

Sound like your application does not run under the same user. you need to use the same windows user which could connect using Azure Data Studio when you execute the application