User authentication to database using .NET web service

1. I would urge you to use parameters when dealing with SQL as to not be vulnerable to SQL Injection; especially for a public facing web service.

2. It appears from your queries you are storing passwords in plain text. Again, I would urge you to salt and hash your user's passwords for better security.

3. You can accomplish what you are looking for by changing your query to a COUNT(*), and executing as a scalar, and making sure the count is greater equal to one. Something like this:

   myCommand.CommandText = "select COUNT(*) from Login where Username = @Username AND Password = @HashedPassword";
   int rowCount = (int)mycommand.ExecuteScalar();
   return rowCount == 1 ? "success" : "bad username or password";
   

The SqlDataReader should be removed from your original code.

EDIT: So what is this code doing?

We've changed the SQL Query slightly. I've also modified it to use parameters. So what this query is saying is Get me the number of users that have this username and this password as an integer.

So if one user has that username and password, it would return 1. If no one had that username and password, it would return 0.

ExecuteScalar is useful when you aren't expecting any rows back; just a single value. This is true in our case; all we are expecting is a number: the number of users.

So when you put all of this code together; it should look something like this:

try
{
    using(SqlConnection myConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;Initial Catalog=student;User ID=sa;Password=123"))
    {
        myConnection.Open();

        using (SqlCommand myCommand = new SqlCommand())
        {
            myCommand.Connection = myConnection;
            myCommand.CommandText = "SELECT COUNT(*) FROM Login WHERE UserName = @UserName AND Password = @Password";
            myCommand.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName;
            myCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = Password;
            return (int) myCommand.ExecuteScalar() == 1 ? "success" : "bad username or password";
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
    return "an error occurred.";
}