I'm unable to connect to my local SQL Server

Question

I am trying to connect to my SQL Server local database:

builder.Services.AddDbContextPool<UserDbContext>(options =>
{
        options.UseSqlServer(builder.Configuration.GetConnectionString("ConnectionString"), o =>
        {
               o.EnableRetryOnFailure();
        });
});

API controller:

[ApiController]
[Route("[controller]")]
public class UserController : Controller
{
    private readonly UserDbContext userDbContext;

    public UserController(UserDbContext dbContext) => userDbContext = dbContext;

    [HttpGet]
    [Route("all")]
    public ActionResult<IEnumerable<User>> GetAllUsers()
    {
        IEnumerable<User> allUsers = userDbContext.Users;
        return Ok(allUsers);
    }
}

And here is my connection string:

"ConnectionString": "server=(localdb)\\MSSQLLocalDB;Database=todoapp;User ID=todouser;Password=1234"

Now, when I am trying to send GET request, I get response:

Microsoft.Data.SqlClient.SqlException (0x80131904): Login failed for user 'todouser'.

With SQL Server Management Studio, I can connect to the database without any problems. Server authentication is set to: "SQL Server and Windows authentication mode", but I cannot connect with Windows authentication as well.

LocalDB instances are local to the Windows user starting them (unless using an instance shared through `sqllocaldb share`). Is your API running under the same credential as Management Studio? — Jeroen Mostert, Jul 04 '22 at 20:52
Have you created a sql server login called "todouser"? How are you connecting through management studio, with windows authentication, or with the "todouser" login and entering the password "1234"? See [this question](https://stackoverflow.com/questions/27085957/how-to-add-username-and-password-in-sqllocaldb) — allmhuran, Jul 04 '22 at 21:44
Of course I have created this login. Both ways work - with windows authentication, and with login and password, but only in management studio. — Ether, Jul 04 '22 at 22:02
Have you created the todouser in the `todoapp` database? Have you looked at the sql error log for more information on the error? Have you checked for typos? — allmhuran, Jul 06 '22 at 14:42

Andreas Sundström · Answer 1 · 2022-07-05T13:44:30.527

0

Try changing the connection-string to use Windows Authentication:

"ConnectionString": "server=(localdb)\\MSSQLLocalDB;Database=todoapp;Integrated Security=true"

edited Jul 05 '22 at 13:44

answered Jul 04 '22 at 21:34

Andreas Sundström

218
1
7

Unfortunately, as I said, I am not able to connect with windows authentication as well. – Ether Jul 04 '22 at 22:04
@Ether use the same credentials you use to login to SQL Server Management Studio. You've installed an instance with mixed mode - so you can either use Windows Integrated or SQLAuthentication. You probably need to map the Integrated User to be in a DbReader/dBWriter role. – Jeremy Thompson Jul 05 '22 at 03:57
Mapping as a database user, with dbreader/writer privileges, did nothing. I'm still wondering why through the management studio I can connect without any problems (both ways), and not with my API. – Ether Jul 05 '22 at 11:37
Are you actually connecting to the same instance with management studio then? Do you have any other instances of sql server installed localy? How did you install sql server express localdb, via Visual studio or as stand alone? I used to have both developer edition and run localdb from Visual Studio and remembered that it could cause trouble. – Andreas Sundström Jul 05 '22 at 13:48

score -1 · Answer 2 · answered Jul 04 '22 at 21:06

-1

if you want to connect with todouser you must first create it:

go to computer management via win+x next in left panel go to LocalUser and Groups and next in right panel add new user with username todouser and password 1234
next you must go to MSSQL MS and go to security>login>right click on todouser>properties on right set default db to todoapp -- in User Mapping check the todoapp database.

answered Jul 04 '22 at 21:06

Ali

1
2

A local windows account can't be used to login to sql server with `User ID` and `Password`. This is only for sqlauthentication so the account needs to be created in SSMS, not windows computer management. – Andreas Sundström Jul 04 '22 at 21:38
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jul 05 '22 at 06:56

I'm unable to connect to my local SQL Server

2 Answers2