无法在 nunit 测试中打开 sqlconnection [英] Can't open sqlconnection within nunit test

问题描述

我遇到了一个我无法弄清楚的奇怪问题.我正在尝试围绕某些数据库代码编写一些集成测试，但我的单元测试因奇怪的异常而失败.在控制台应用程序下正常运行代码工作正常.

I'm having a weird issue that I can't figure out. I'm trying to write some integration tests around some database code and my unit tests fail with a weird exception. Running the code normally under a console application work just fine.

public static class DatabaseManager
{
    public static VerifyServceConnectionResult VerifyServerConnection(Server server)
    {
        try
        {

            using (var conn = new SqlConnection(BuildConnectionString(server)))
            {
                conn.Open();
            }

            return new VerifyServceConnectionResult { ConnectionSuccessful = true };
        }
        catch (SqlException ex)
        {
            return new VerifyServceConnectionResult
            {
                ConnectionSuccessful = false, 
                ErrorMessage = ex.Message
            };
        }
        catch (Exception ex)
        {
            return new VerifyServceConnectionResult
            {
                ConnectionSuccessful = false,
                ErrorMessage = "General Exception: " + ex.Message
            };
        }
    }

    private static string BuildConnectionString(Server server)
    {
        var builder = new SqlConnectionStringBuilder();

        builder.DataSource = server.DataSource;
        builder.IntegratedSecurity = server.UseIntegratedSecurity;

        return builder.ConnectionString;
    }

}

只要在 Nunit 测试中调用 conn.open，我就会收到异常算术运算导致溢出".关于为什么这样一个简单的测试会在 nunit 中失败并出现如此奇怪的错误的任何想法.同样，代码在单元测试的上下文之外也能正常工作.

as soon as conn.open gets called within the Nunit Test I get an exception "Arithmetic operation resulted in an overflow." Any ideas on why such a simple test would be failing in nunit with such a weird error. Again, the code works just fine outside of the context of the unit test.

谢谢

添加堆栈跟踪

at SNIOpenSyncExWrapper(SNI_CLIENT_CONSUMER_INFO* , SNI_ConnWrapper** )
at SNINativeMethodWrapper.SNIOpenSyncEx(ConsumerInfo consumerInfo, String constring, IntPtr& pConn, Byte[] spnBuffer, Byte[] instanceName, Boolean fOverrideCache, Boolean fSync, Int32 timeout, Boolean fParallel)
at System.Data.SqlClient.SNIHandle..ctor(ConsumerInfo myInfo, String serverName, Byte[] spnBuffer, Boolean ignoreSniOpenTimeout, Int32 timeout, Byte[]& instanceName, Boolean flushCache, Boolean fSync, Boolean fParallel)
at System.Data.SqlClient.TdsParserStateObject.CreatePhysicalSNIHandle(String serverName, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Byte[]& instanceName, Byte[] spnBuffer, Boolean flushCache, Boolean async, Boolean fParallel)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
at System.Data.SqlClient.SqlConnection.Open()
at xxx.Database.DatabaseManager.VerifyServerConnection(Server server) in d:\projects\TFS\xxx.visualstudio.com\SnydJK.DatabaseUtility\Database\DatabaseManager.cs:line 17

推荐答案

最近，我遇到了相同调用堆栈的异常.

Recently, I experienced an exception with the same call stack.

环境

我在具有默认 UAC 设置的 Windows 8.1 64 位(不在域中)上使用 LocalDB 服务器，并且用户是本地管理员，Framework v4.0.2(请参阅为什么重要).遗憾的是，该问题无法在具有相同环境的其他计算机上重现.

I was using LocalDB server on Windows 8.1 64-bit (not in domain) with default UAC settings and the user is local admin, Framework v4.0.2 (see why it's important). Sadly, the issue can't be reproduced on other computers with the same enviroment.

问题描述

发现异常发生在以下情况:

Found out that the exception occurs in the following conditions:

• 连接字符串中有 Integrated Security 参数，用于创建新数据库.
• 同一进程在创建后打开与新创建的数据库的连接.

• There is Integrated Security parameter in the connection string which is used to create a new database.
• The same process opens a connection to the newly created DB after it got created.

打破这两个条件之一可以无一例外地通过.因此，省略 Integrated Security 是最简单的方法.作为一个选项 - 不要在同一进程中打开与新创建的数据库的连接.但是，如果我们仍然在一个进程的范围内工作，那么在创建数据库后添加暂停并没有帮助.

Breaking one of these two conditions allows to get through without exceptions. So omitting the Integrated Security is the easiest way to go. As an option - don't open connection to the newly created DB in the same process. But just adding a pause after creating a DB wouldn’t help if we’re still working in the scope of one process.

解决方案

当与LocalDB有交易时，去掉连接字符串中的Integrated Security参数.

Chuck out the Integrated Security parameter in the connection string when have a deal with LocalDB.

一般评论

Integrated Security 是一个选项，它指定使用 Windows 帐户凭据进行身份验证(请参阅 MSDN 和这里).建议 SQL Server 使用此选项(值 True 或 SSPI).

Integrated Security is an option, which specifies that Windows account credentials are used for authentication (see details on MSDN and here). Having this option (value True or SSPI) is recommended for SQL Servers.

同时，此选项对 LocalDB 服务器没有任何影响.无论此设置如何，LocalDB 仍将是一个用户实例，在启动它的帐户下运行.基本上，它会因为 Integrated Security 被设置为 True 或 SSPI.LocalDB 没有办法阻止用户访问他们数据库中的数据(例如，通过使用 SQL 授权).一旦用户有权访问数据库文件，他们就可以启动自己的LocalDB 实例并打开数据库.

Meanwhile this option doesn’t make any difference for LocalDB servers. Regardless of this setting, LocalDB will still be a user instance, running under the account that started it. Basically, it’ll be as Integrated Security was set to True or SSPI. There is no way in LocalDB to prevent the user from accessing data in their database (e.g. by using SQL authorisation). Once the user has access to the database file, they can spin up their own LocalDB instance and open the database.

因此，在连接到 LocalDB 时指定 Integrated Security 的所有建议都没有根据.

Hence all recommendations to specify the Integrated Security when connecting to LocalDB have no grounds.

这篇关于无法在 nunit 测试中打开 sqlconnection的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！