检查SQL Login的凭据

本文关键字：Login SQL 检查 | 更新日期: 2023-09-27 18:18:03

我可以为一个用户创建一个SQL登录帐户，如下所示:

CREATE LOGIN [myusername] WITH PASSWORD = N'mypassword', CHECK_POLICY= OFF;

我可以检查例如'myusername'是否存在:

SELECT * FROM sys.server_principals WHERE name = N'myusername'

但是我如何检查用户提供的'myusername'和密码'wrong_password'是否正确?

我的c#解决方案是在Roger的帮助下，如下所示:

    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
        }
    }

检查SQL Login的凭据

您可以使用pwdcompare()函数:

select pwdcompare(N'MyWrongPassword', sl.password_hash) as [Match]
from master.sys.sql_logins sl
where sl.name = N'MyLogin';

但是，您很有可能在错误的方向上工作-使用提供的凭据尝试建立连接是更正确的。

相关文章：