title: Check credentials of SQL Login
body: <p>I can create an SQL login account for an user as follows:</p>
  
  <pre><code>CREATE LOGIN [myusername] WITH PASSWORD = N'mypassword', CHECK_POLICY= OFF;
  </code></pre>
  
  <p>I can check e.g. if 'myusername' exists:</p>
  
  <pre><code>SELECT * FROM sys.server_principals WHERE name = N'myusername'
  </code></pre>
  
  <p>But how can I check if 'myusername' and a password 'wrong_password' supplied by the user are correct?</p>
  
  <p>My solution in C# is with the help of Roger as follows:</p>
  
  <pre><code>    private static bool SqlLoginExists(string username)
      {
          SqlConnection connection = new SqlConnection();
          connection.ConnectionString = "Data Source=.;" + "Integrated Security=SSPI;";
          connection.Open();
  
          string sql_command = "SELECT count(*) FROM sys.server_principals WHERE name = N'" + username + "'";
  
          using (SqlCommand command = new SqlCommand(sql_command, connection))
          {
              using (SqlDataReader reader = command.ExecuteReader())
              {
                  while (reader.Read())
                  {
                      if (reader.GetValue(0).ToString() == "1")
                      {
                          return true;
                      }
                  }
              }
          }
          return false;
      }
  
      public static bool CheckSQLCredentialsIfLoginExists(string username, string password)
      {
          bool SqlUserExists = SqlLoginExists(username);
  
          if (SqlUserExists)
          {
              SqlConnection connection = new SqlConnection();
              connection.ConnectionString = "Data Source=.;" + "User id=" + username+";Password=" + password +";";
              try
              {
                  connection.Open();
                  return true;
              }
              catch (SqlException ex)
              {
                  connection.Dispose();
                  return false;
              }
  
          }
          else
          {
              return true; // it is not a problem for me if account does not exist (as it will be created later), I am only worried about the case when the credentials are wrong
          }
      }
  </code></pre>