"Login failed for user" C# with SQLConnection

Question

I've been trying to connect to my database (which is on the same computer as my code) through my C# code. The problem is I keep getting the "Login failed for user " "" error... I admit that my knowledge of connecting to databases is minimal and I've tried almost all the steps in other questions!

here's part of my code:

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServerConnection"].ConnectionString);
        SqlCommand command = connection.CreateCommand();
        command.CommandText = @"IF EXISTS
                            (
                              SELECT *
                              FROM user 
                              WHERE EMAILADRES = @email and WACHTWOORD = @password
                            ) SELECT CAST (1 as bit) 
                            ELSE
                              SELECT CAST(0 as bit)";
        command.Parameters.AddWithValue("email", email);
        command.Parameters.AddWithValue("password", password);


        connection.Open();
        object ReturnBool = command.ExecuteScalar();
        connection.Close();

and this is my connection string:

<add name="SQLServerConnection" connectionString="Server=localhost; Database=database1;uid=NT AUTHORITY\NETWORK SERVICE" />

Answer 1

you need to change the connection string;

<add name="SQLServerConnection" connectionString="Server=localhost;Database=database1;Trusted_Connection=True;/>

if you are using windows authentication to connect to the local DB, you need to set Trusted_Connection=True; if you are using SQL server authentication, you need to declare User Id=myUsername; Password=myPassword;.

Answer 2

I would recommend do this:

1) Change connection string to:

<add name="SQLServerConnection" connectionString="Server=.;Database=database1;Trusted_Connection=True;"/>

'Server=.' - default instance of SQL Server on your machine is used,

'Trusted_Connection=True' - Windows Authentication is used to validate your access to SQL Server instance.

2) Check in Sql Management Studio is your windows user has permissions to access 'database1'.

The second error you are getting because you should add '@' in name of parameter like this:

command.Parameters.AddWithValue("@email", email);
command.Parameters.AddWithValue("@password", password);

I would also recommend that you change your code like this:

using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServerConnection"].ConnectionString))
{
    using (var command = connection.CreateCommand())
    {
        command.CommandText = @"IF EXISTS
                        (
                          SELECT *
                          FROM user 
                          WHERE EMAILADRES = @email and WACHTWOORD = @password
                        ) SELECT CAST (1 as bit) 
                        ELSE
                          SELECT CAST(0 as bit)";

        command.Parameters.AddWithValue("@email", email);
        command.Parameters.AddWithValue("@password", password);

        connection.Open();
        var result = command.ExecuteScalar();
    }
}