sp_setapproxle在线程处理时出错:";模拟会话安全上下文”;无法在此批处理中调用,因为同时进行的批处

本文关键字:调用 批处理 因为 上下文 处理 出错 线程 setapproxle 安全 sp 会话 | 更新日期: 2023-09-27 18:26:52

从线程应用程序在SQL Server 2014上调用sp_setapprole时,我们会收到错误:无法在此批处理中调用"模拟会话安全上下文",因为同时有一个批处理调用了它。据我所知,连接未被池化,每次调用都会创建一个新连接,因此我不确定冲突发生在哪里。以下是创建错误的测试应用程序:

    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Threading.Tasks;
  namespace PleaseBreak
 {
class Program
{
    static string ConnectionString = "Data Source=<SQL DB SERVER>; Initial Catalog=<SQL DB NAME>; user id=<USER NAME>; pwd=<PASSWORD>; Enlist=false; Persist Security Info=True; Pooling=false; MultipleActiveResultSets=True; APP=<APPNAME>;";
    static int ThreadCount = 3;
    static int LoopCount = 100000;
    static void Main(string[] args)
    {
        var tasks = new List<Task>();            
        for (var i = 0; i < ThreadCount; i++)
        {
            tasks.Add(Task.Factory.StartNew(Work));
        }
        Task.WaitAll(tasks.ToArray());
    }
    static void Work()
    {
        for (var i = 0; i < LoopCount; i++)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                connection.Open();
                try
                {
                    SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, "sp_setapprole", new SqlParameter[] 
                    { 
                        new SqlParameter { ParameterName = "@RETURN_VALUE", Direction = ParameterDirection.ReturnValue, DbType = DbType.Int32 },
                        new SqlParameter { ParameterName = "@rolename", Direction = ParameterDirection.Input, DbType = DbType.AnsiString, Value = <APPROLE NAME> },
                        new SqlParameter { ParameterName = "@password", Direction = ParameterDirection.Input, DbType = DbType.AnsiString, Value = <APP ROLE PASSWORD> },
                    });
                }
                catch (Exception ex)
                {
                }
            }
        }
    }
}
}

sp_setapproxle在线程处理时出错:";模拟会话安全上下文”;无法在此批处理中调用,因为同时进行的批处

问题似乎是MultipleActiveResultSets。一旦我们将MultipleActiveResultSets=False添加到连接中,问题就消失了。