Login failed for user '' and Cannot open database "Database1.mdf" requested by the login. The login failed. Login failed for user 'rBcollo-PC\rBcollo'

Question

So..I managed to almost finish all my problems.But now i deal with another one. I used this connectionstring :

SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;Database=Database1.mdf");

And it throws the next error:

Login failed for user '

If i remove the .mdf extension it throws the same error

Now if i'll add the following to the string:

Integrated Security = true

It throws this:

Cannot open database "Database1.mdf" requested by the login. The login failed. Login failed for user 'rBcollo-PC\rBcollo'.

P.S for Integrated Security = false it throws the "Login failed for user'"

The problem is that i'm not using any username or password for the Database.

Any help,please?

Answer 1

Note: My answer would be applicable if you are using EntityFramework Core with SQL Server

This error could be because of the reason 'Database does not exist' and application try to perform DB operations.

All you need to use the following line before performing any database operation.

_context.Database.EnsureCreated(); 

And what is EnsureCreated?

    // Summary:
    //     Ensures that the database for the context exists. If it exists, no action is
    //     taken. If it does not exist then the database and all its schema are created.
    //     If the database exists, then no effort is made to ensure it is compatible with
    //     the model for this context.
    //     Note that this API does not use migrations to create the database. In addition,
    //     the database that is created cannot be later updated using migrations. If you
    //     are targeting a relational database and using migrations, you can use the DbContext.Database.Migrate()
    //     method to ensure the database is created and all migrations are applied.
    //
    // Returns:
    //     True if the database is created, false if it already existed.

Answer 2

If you set Integrated Security = true then the system will attempt to login to the database with your current windows credentials. If you have not specifically given permission to the current user in your database then this wont work.

If you set Integrated Security = false or do not have the Integrated Security option at all then you need to supply the username and password.

Data Source=.\SQLEXPRESS;Database=Database1.mdf;User Id=myUsername;
Password=myPassword;

Your statement that you're not using a username or password doesnt make any sense. You must have a username and password on a database.

Answer 3

This wasn't answered so I thought I'd list my suggestion, seems to be present in VS Community 2015 also, run VS as an administrator and it seems to fix the problem.

Even though the database was created in visual studio running under my account, and the web services are running under my account, can't seem to access the Database without admin.

Maybe someone else can answer why this is required, just know admin is required for a lot of VS functionality to work correctly, not sure why its not enabled in the first place.

Very annoying just to use a local dev database.. should just work without all this fiddling and misinformation.

Answer 4

If you're using netcore, don't forget to run dotnet ef database update in the project directory. I was getting the same error and that fixed it for me.

Answer 5

as soon as you use integrated security = true, it uses your windows credentials to login to the sql instance. you have to give this user the permissions to the sql instance. if you don't specify integrated security = true, then its logging in as an anonymous user and that's why Login failed for user'.'. you could use the third option of creating a sql account and then passing them in the connection string and login. depends on what you want to do.

Answer 6

In ASP.Net Core, similar error may be raised if the database is not created yet! (the error message may confuse you as it does not related to login!).

There are several ways to solve it:

You can run dotnet ef database update after dotnet ef migrations add Initial in the command line in root path of project where you usually see .csproj file.

Alternatively, you may call dbContext.Database.EnsureCreated() or dbContext.Database.Migrate() but the latter Migrate() method is recommended as it doesn't damage later migrations.

public class Program
{
    public static void Main(string[] args)
    {
        var host = BuildWebHost(args);

        using (var scope = host.Services.CreateScope())
        {
            var services = scope.ServiceProvider;

            try
            {
                var dbContext = services.GetRequiredService<MyDbContext>();
                //dbContext.Database.EnsureCreated(); //ensure db is created (created database can't be later updated using migrations!)
                dbContext.Database.Migrate(); //ensure pending migrations applied and db is created 
            }
            catch (Exception ex)
            {
                var logger = services.GetRequiredService<ILogger<Program>>();
                logger.LogError(ex, "An error occurred creating the DB.");
            }
        }

        host.Run();
    }

    public static IWebHost BuildWebHost(string[] args) =>
        WebHost.CreateDefaultBuilder(args)
            .UseStartup<Startup>()
            .Build();
}