SQL中的CLR函数是否可以采用特定的windows标识

Question

当从SQL Server代理作业调用时，我希望CLR函数作为特定的窗口标识运行。为此，调用函数的SQL语句具有以下形式：

execute as user='domainusername'
select database.schema.function()
revert

为了论证起见，函数本身可以简单地类似于：

[SqlFunction(DataAccess = DataAccessKind.Read)]
[return: SqlFacet(MaxSize = -1)]
public static SqlChars GetIdentityInformationImpersonated()
{
WindowsIdentity clientId;
WindowsImpersonationContext impersonatedUser;
string currentUser;
clientId = SqlContext.WindowsIdentity;
impersonatedUser = clientId.Impersonate();
currentUser = WindowsIdentity.GetCurrent().Name;
impersonatedUser.Undo();
return new SqlChars(currentUser);
}

这引发了一个异常：

在执行用户定义例程或聚合"GetIdentityInformationImpersonated"期间发生.NET Framework错误：System.NullReferenceException：对象引用未设置为对象实例。System.NullReferenceException：在WinIdentityDebugging.IdentityResults.GetIdentityInformationImpersonated((

这意味着SqlContext.WindowsIdentity为空。现在互联网上有人声称这样的事情是不可能的，比如。。。

在这里

请记住，SQLCLR模拟不适用于模拟的上下文(EXECUTE AS(

。。。在这里。。。

尝试检索SqlContext.WindowsIdentity对象是模拟的安全上下文当前返回null，并且文档会这样做。

。。。在这里。。。

如果执行是在Execute As Login(直接执行语句或用它标记的某个模块(的上下文中进行的，则除非登录名是sysadmin，否则SqlContext.WindowsIdentity将为null。在这种情况下，SqlContext.WindowsIdentity将返回用于运行服务器进程的标识。

但所有这些引用都很旧，我在实际文档中找不到任何这样的限制。例如，SqlContext.WindowsIdentity页仅将其自身引用为SQL Auth调用方的null：

表示调用方的Windows标识的WindowsIdentity实例，如果使用SQL Server身份验证客户端，则为null。

所以我希望有一种方法可以实现这一点。

这样的事情可能吗？还是我运气不好？

Answer 1

您找到的引用，即使是旧的，看起来仍然是正确的。我不仅从未见过这样的例子，而且我刚刚在SQL Server 2017上重新验证，即使通过EXECUTE AS LOGIN = 'machine_or_domainaccount_mame';模拟Windows登录，SqlContext.WindowsIdentity属性仍然返回null。

如果需要以特定Windows帐户的身份执行外部命令，则需要在.NET代码中以该帐户的身份进行授权。这需要将程序集设置为UNSAFE。

我记得几年前就试过了，我会看看我是否还有那个测试代码。

或者，考虑到您已经在使用SQL Server代理，这可能很容易。正如其他人提到的代理一样，您可以简单地将作业步骤类型更改为"操作系统(CmdExec("，并根据是否需要T-SQL上下文中的输出，以两种方式之一进行处理。

1. 为您希望函数执行的Windows帐户创建凭据
2. 为要用于此类型作业步骤的凭据创建代理(在SSMS中的"SQL Server Agent"->"Proxies"->"Operating System(CmdExec("下(
3. 将SQL代理作业步骤更改/创建为"操作系统(CmdExec(">
4. 在"运行方式："下拉列表中，选择刚刚创建的代理
5. 根据是否需要.NET方法的输出返回到T-SQL上下文：
   1. 如果您确实不需要此方法的任何输出，那么与其在SQLCLR中执行此操作，不如创建一个控制台应用程序并将其作为SQL代理作业步骤执行。而且，我想，即使您确实需要从T-SQL向该函数发送数据，或者对输出进行处理，在许多/大多数情况下，您也可以简单地从.NET代码连接到SQL Server，并从控制台应用程序执行所需的查询
   2. 如果确实需要T-SQL上下文中的输出，或者需要从T-SQL上下文传入数据，而这些数据无法通过在.NET代码中执行的查询轻松完成，那么您可以简单地从SQL代理作业步骤执行SQLCMD.exe，因为它将作为所需的Windows帐户进行连接。在SQLCMD中，您执行包含SQLCLR函数/存储过程的查询，.NET模拟将使用该代理帐户(通过凭据(，因为外部进程作为该帐户连接到SQL Server。我已经测试过了，它确实有效

Answer 2

您可以使用代理&一个powershell步骤，或者使用execute作为登录和使用OPENROWSET:的新连接

powershell：

--create credential
--#!!!!!!!!!!!!!!!!  set domain account and password !!!!!!!!!!!!!!!!!!
create credential credentialUserxyz with identity = '**domainuser**', secret = '**password_here**';
go
exec msdb.dbo.sp_add_proxy @proxy_name=N'proxyUserxyz',@credential_name=N'credentialUserxyz', @enabled=1;
go
--enable proxy for powershell
exec msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'proxyUserxyz', @subsystem_id=12;
go
--create a job with a powershell step
declare @jobid binary(16);
exec msdb.dbo.sp_add_job @job_name=N'thestclrwithproxy', @enabled=1, @category_name=N'[Uncategorized (Local)]', @job_id = @jobId OUTPUT;
exec msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)';
exec msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'step 1', 
@step_id=1, 
@subsystem=N'PowerShell', 
@command=N'
$conn = New-Object System.Data.Odbc.OdbcConnection("Driver={ODBC Driver 17 for SQL Server};Server=$(ESCAPE_DQUOTE(SRVR));Trusted_Connection=yes;");
$conn.open();
$cmd = New-Object data.Odbc.OdbcCommand;
$cmd.Connection = $conn;
#!!!!!!!!!!!!!!!!  change the clr function call !!!!!!!!!!!!!!!!!!
$cmd.CommandText = "select database.schema.function();";
$scalar = $cmd.ExecuteScalar();
#echo $scalar;
Write-Output "clr identity: $scalar";
$cmd.Dispose();
$conn.Dispose();', 
@database_name=N'master', 
@proxy_name=N'proxyUserxyz';
go
--execute the job
exec msdb.dbo.sp_start_job @job_name = 'thestclrwithproxy' 
go
waitfor delay '00:00:10';
go
--message, output of clr identity
select message, *
from msdb.dbo.sysjobhistory as h
join msdb.dbo.sysjobs as j on h.job_id = j.job_id
where j.name = 'thestclrwithproxy';
go
--delete the job
exec msdb.dbo.sp_delete_job @job_name = 'thestclrwithproxy';
go
--drop proxy
exec msdb.dbo.sp_delete_proxy @proxy_name = N'proxyUserxyz';
--drop credential
drop credential credentialUserxyz;
go

打开行集：

execute as login = 'domainusername';
select *
from OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 
'select database.schema.function() as functioncall;'
) ;