我被分配任务编写适用于一个遗留数据库的Web界面，其中所有用户都有数据库账户并根据角色进行分配（我们在许多地方设置了触发器来记录用户执行某些操作的时间，这些操作基于user_name()）。
为了使用现代技术并避免以明文形式存储用户密码，我连接了一个应用层级别的账户，并尝试运行Execute As User=@username和Revert来在运行任何 SQL 之前和之后设置和重置执行上下文。
不幸的是，连接池的reset_connection调用会对我的连接造成干扰，并且会出现一些关于物理连接无效的错误信息...
我可以通过不使用连接池来解决此问题。但是，我的应用程序用户需要大量特权才能执行模拟。此外，关闭连接池是一个烦人的事情...
在不牺牲安全性或性能的前提下，我该如何做？请记住，我不能更改用户具有数据库登录的事实，而且我真的不想以可检索的方式存储用户密码。我的唯一选择是绕过连接池，以便我可以模拟（并使用 sa 用户，以便我具有足够的权限来模拟某人）吗？
user_name()的调用替换为对dbo.fn_user_name()的调用。create function dbo.fn_user_name()
returns sysname
as
begin
declare @user sysname = rtrim(convert(nvarchar(64), context_info()))
if @user is null
return user_name()
return @user
end
go
这里有两个选项。或者您创建自己的 SqlConnection 类，或者创建一个工厂方法，该方法将返回一个打开的 SqlConnection，如下所示。工厂方法的问题是您运行的每个查询都将是 2 个数据库调用。不过这是最少要编写的代码。
public SqlConnection CreateConnection(string connectionString, string user)
{
var conn = new SqlConnection(connectionString);
using (var cmd = new SqlCommand(
@"declare @a varbinary(128) = convert(varbinary(128), @user + replicate(N' ', 64 - len(@user)))
set context_info @a", conn))
{
cmd.Parameters.Add("@user", SqlDbType.NVarChar, 64).Value = user;
conn.Open();
cmd.ExecuteNonQuery();
}
return conn;
}
using(var conn = CreateConnection(connectionString, user))
{
var cmd = new SqlCommand("select 1", conn);
return conn.ExecuteScalar()
}
declare @a varbinary(128) = convert(varbinary(128), @user + replicate(N' ', 64 - len(@user)))
set context_info @a
using(var conn = new SqlContextInfoConnection(connectionString, user))
{
var cmd = new SqlCommand("select 1", conn);
conn.open;
return conn.ExecuteScalar()
}
我知道这是老旧的，但这篇文章是我找到的唯一有用的资源，所以我想分享我们的解决方案，它基于Filip De Vos的答案。
我们还有一个使用sp_setapprole的传统VB6应用程序（我明白这与原帖不太相符）。我们的.NET组件与同一数据库共享（实质上是应用程序框架的一部分），它们主要基于Linq to SQL。
考虑到连接被打开和关闭的次数，为数据上下文连接设置approle变得棘手。
最终，我们采用了上面建议的简单包装器。唯一重写的方法是Open()和Close()，在这里设置和取消设置approle。
Public Class ManagedConnection
Inherits Common.DbConnection
Private mCookie As Byte()
Private mcnConnection As SqlClient.SqlConnection
Public Sub New()
mcnConnection = New SqlClient.SqlConnection()
End Sub
Public Sub New(connectionString As String)
mcnConnection = New SqlClient.SqlConnection(connectionString)
End Sub
Public Sub New(connectionString As String, credential As SqlClient.SqlCredential)
mcnConnection = New SqlClient.SqlConnection(connectionString, credential)
End Sub
Public Overrides Property ConnectionString As String
Get
Return mcnConnection.ConnectionString
End Get
Set(value As String)
mcnConnection.ConnectionString = value
End Set
End Property
Public Overrides ReadOnly Property Database As String
Get
Return mcnConnection.Database
End Get
End Property
Public Overrides ReadOnly Property DataSource As String
Get
Return mcnConnection.DataSource
End Get
End Property
Public Overrides ReadOnly Property ServerVersion As String
Get
Return mcnConnection.ServerVersion
End Get
End Property
Public Overrides ReadOnly Property State As ConnectionState
Get
Return mcnConnection.State
End Get
End Property
Public Overrides Sub ChangeDatabase(databaseName As String)
mcnConnection.ChangeDatabase(databaseName)
End Sub
Public Overrides Sub Close()
Using cm As New SqlClient.SqlCommand("sp_unsetapprole")
cm.Connection = mcnConnection
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.Add("@cookie", SqlDbType.VarBinary, 8000).Value = mCookie
cm.ExecuteNonQuery()
End Using
mcnConnection.Close()
End Sub
Public Overrides Sub Open()
mcnConnection.Open()
Using cm As New SqlClient.SqlCommand("sp_setapprole")
cm.Connection = mcnConnection
cm.CommandType = CommandType.StoredProcedure
cm.Parameters.Add("@rolename", SqlDbType.NVarChar, 128).Value = "UID"
cm.Parameters.Add("@password", SqlDbType.NVarChar, 128)Value = "PWD"
cm.Parameters.Add("@fCreateCookie", SqlDbType.Bit).Value = True
cm.Parameters.Add("@cookie", SqlDbType.VarBinary, 8000).Direction = ParameterDirection.InputOutput
cm.ExecuteNonQuery()
mCookie = cm.Parameters("@cookie").Value
End Using
End Sub
Protected Overrides Function BeginDbTransaction(isolationLevel As IsolationLevel) As DbTransaction
Return mcnConnection.BeginTransaction(isolationLevel)
End Function
Protected Overrides Function CreateDbCommand() As DbCommand
Return mcnConnection.CreateCommand()
End Function
End Class
之前：
Using dc As New SystemOptionDataContext(sConnectionString)
intOption= dc.GetIntegerValue("SomeDatabaseOption")
End Using
之后：
Using dc As New SystemOptionDataContext(New ManagedConnection(strConnectionString))
intOption= dc.GetIntegerValue("SomeDatabaseOption")
End Using
App_Name()或Host_Name()函数结合使用？虽然不是理想的解决方案，但考虑到限制，这可能是一个可接受的解决方法... - gvee