SQL Server密码更改后导致登录失败的.Net连接池 [英] .Net connection pool causing login failed after SQL Server password change

问题描述

我们的配置文件中包含连接字符串，其中不包含密码.

We have connection strings in our config files which don't contain passwords.

Data Source=OurDataSource;Initial Catalog=OurCatalog;Integrated Security=False;UID=OurUser;Encrypt=True;TrustServerCertificate=False;

在我们的代码中，密码是从服务加载的，并附加如下:

In our code the password is loaded from a service and appended as follows:

var connectionString = ConfigurationManager.ConnectionStrings["ourCS"].ConnectionString;
var builder = new SqlConnectionStringBuilder(connectionString);
var credential = GetPassword(builder.UserID);
builder.Password = credential.Password;
var connectionString = builder.ConnectionString;
using (var db = new SqlConnection(connectionString))
{
    // Execute our query
}

密码服务每隔几个月会更改一次密码，最近在更改密码后，我们开始收到下面的登录失败错误，并且帐户已被锁定.

The password service changes the password each few months and recently after a password change we started receiving login failed errors below and the account locked out.

Login failed for user 'OurUser'. Reason: Password did not match that for the login provided.

我的研究指出了连接池的问题，该连接池仍然具有开放的连接，这些连接尝试对数据库进行身份验证并锁定它.

My research points to problems with the connection pool which has still has open connections which try to authenticate against the db and lock it.

我们所有的数据库通信都是通过使用上面的语句完成的，该语句应在调用dispose时关闭连接.但是，在反编译System.Data.SqlClient.SqlConnection之后，似乎dispose方法不会关闭连接.

All of our db communication is done in using statements above which should close the connection when dispose is called. However, after decompiling System.Data.SqlClient.SqlConnection, it seems the dispose method doesn't close the connection.

public void Dispose() {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        // using CloseHandle and UnmapViewOfFile - no exposure
        [ResourceExposure(ResourceScope.None)]
        [ResourceConsumption(ResourceScope.Machine, ResourceScope.Machine)]
        private void Dispose(bool disposing) {
            if (disposing) {
               // Nothing to do here
                ;
            }
            if (pMemMap != IntPtr.Zero) {
                NativeMethods.UnmapViewOfFile(pMemMap);
                pMemMap = IntPtr.Zero;
            }
            if (hMemMap != IntPtr.Zero) {
                NativeMethods.CloseHandle(hMemMap);
                hMemMap = IntPtr.Zero;
            }
            active = false;
        }

        ~SqlDebugContext() {
                Dispose(false);
        }

有人遇到过此问题及其解决方案吗?目前，它指出必须按如下所示关闭连接，因为Dispose似乎没有这样做.

Has anyone come across this issue and a solution to it? At the moment it points to having to call connection close as follows as it doesn't seem Dispose does it.

using (var db = new SqlConnection(connectionString))
{
    // Execute our query
db.Close();
}

推荐答案

我现在实际上已经找到了答案.我有一个客户log4net附加程序，它获取连接字符串并从服务中添加密码.

I actually have now found the answer. I have a customer log4net appender which gets the connection string and adds the password from the service.

问题是我正在自定义追加程序的构造函数中检索密码，而该构造函数仅在应用程序start:D上调用.因此，我不得不使用重写的append方法来移动密码的检索.

The problem was I was retrieving the password in the constructor of our custom appender and the constructor is only called on app start :D. Therefore I had to move the retrieval of the password in the overridden append method.

<log4net>
    <root>
      <level value="ALL" />
      <appender-ref ref="RepositoryAppender" />
    </root>
    <appender name="RepositoryAppender" type="MyAppender, MyLibrary">
      <threshold value="ALL" />
      <bufferSize value="1" />
    </appender>
</log4net>

这篇关于SQL Server密码更改后导致登录失败的.Net连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！