CODEWITHSUNDEEP
c#sqldatabasesqlconnection
Lourens
Lourens

Reputation: 211

"Login failed for user" C# with SQLConnection

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" />

Upvotes: 19

Views: 90680

Answers (5)

Monika Fand
Monika Fand

Reputation: 11

Probably you are missing "Integrated Security=true" in the connection string. If the connection string looks like:

"server=@server_name\\SQLEXPRESS;database=EmployeeDB;TrustServerCertificate=true"

and you are connected to that database server using Windows Authentication then you need to add "Integrated Security=true" to the connection string.

Upvotes: 1

Pandian
Pandian

Reputation: 9126

Try like below... it will help you..

        string email ="[email protected]" //Give your email id to check
        string password ="Test" //Give your Password to check
        SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQLServerConnection"].ConnectionString);
        SqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM user WHERE EMAILADRES = @email and WACHTWOORD = @password" 
        command.Parameters.AddWithValue("@email", email);
        command.Parameters.AddWithValue("@password", password);
        connection.Open();
        SqlDataReader dr = command.ExecuteReader();
        if (dr.Read())
        MessageBox.Show("Exists");
        else
        MessageBox.Show("Not Exists");
        connection.Close();

Upvotes: 2

Bartłomiej Mucha
Bartłomiej Mucha

Reputation: 2782

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();
    }
}

Upvotes: 9

Night Hunter
Night Hunter

Reputation: 123

Change your connection string.

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

If you use server authentication,

<add name="SQLServerConnection" connectionString="Server=.;Database=database1; UserId = Username; Password = Password;"/>

If you still have error,

Check your sql services and protocols in sql server configuration manager.

Upvotes: 3

daryal
daryal

Reputation: 14929

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;.

Upvotes: 33

Related Questions