Is it possible to have a CLR function in SQL take on a specific windows identity?

Question

I want to have a CLR function run as a specific windows identity when called from SQL Server Agent Jobs. To that end, the SQL statement calling the function has the form:

execute as user='domain\username'
select database.schema.function()
revert

The function itself, for arguments sake, could simply be something like:

        [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);
        }

This throws an exception:

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetIdentityInformationImpersonated": System.NullReferenceException: Object reference not set to an instance of an object. System.NullReferenceException: at WinIdentityDebugging.IdentityResults.GetIdentityInformationImpersonated()

Which implies that the SqlContext.WindowsIdentity is null. Now there are references across the internet claiming that such a thing is impossible, such as ...

...here...

Keep in mind that SQLCLR impersonation doesn't work with impersonated contexts (EXECUTE AS)

...and here...

attempting to retrieve a SqlContext.WindowsIdentity object is an impersonated security context returns null currently and is doc'd to do so.

...and here...

If the execution is happening in the context of an Execute As Login (statement directly or some module marked with it ) SqlContext.WindowsIdentity will be null unless the login is sysadmin. In this case SqlContext.WindowsIdentity will return the identity under which the server process is running.

But all of those references are quite old, and I cannot find any such restriction in the actual documentation. For example the SqlContext.WindowsIdentity page only refers to itself being null for SQL Auth callers:

A WindowsIdentity instance representing the Windows identity of the caller, or null if the client was authenticated using SQL Server Authentication.

So I'm hoping that there's a way to achieve this.

Is such a thing possible? Or am I out of luck?

Answer 1

The references that you found, even if old, appear to still be correct. Not only have I never seen an example of this working, but I just re-verified on SQL Server 2017 that even when impersonating a Windows login via EXECUTE AS LOGIN = 'machine_or_domain\account_mame';, the SqlContext.WindowsIdentity property still returns a null.

If you need to execute external commands as a specific Windows account, you need to authorize as that account in the .NET code. This requires setting the assembly to UNSAFE.

I remember trying it out years ago and will look to see if I still have that test code.

OR, perhaps this might be easy enough given that you are already using SQL Server Agent. As others have mentioned proxies, you could simply change the Job Step type to be "Operating system (CmdExec)" and handle this in one of two ways, depending on if you need the output in the T-SQL context or not.

1. Create a credential for the Windows account you want the function to execute as
2. Create a proxy for that Credential to be used for job steps of this type (in SSMS under "SQL Server Agent" -> "Proxies" -> "Operating System (CmdExec)" )
3. Change/create the SQL Agent job step to be "Operating system (CmdExec)"
4. In the "Run as:" drop-down, select the proxy that you just created
5. Depending on whether or not you need output from the .NET method returned to the T-SQL context:
   1. If you do not need any output from this method, then rather than doing this in SQLCLR, create a console app and execute that as the SQL Agent job step. AND, I suppose even if you do need to send data to this function from T-SQL, or do something with output, in many/most cases you can simply connect to SQL Server from the .NET code and execute the required queries from the console app.
   2. If you do need the output in the T-SQL context, or need to pass in data from the T-SQL context, and those cannot be easily done via queries executed within the .NET code, then you can simply execute SQLCMD.exe from the SQL Agent job step as it will be connecting as the desired Windows account. From SQLCMD, you execute the query containing the SQLCLR function/stored procedure, and the .NET impersonation will use that proxy account (via the credential) since the external process connected to SQL Server as that account. I have tested this and it does work.

Answer 2

You could switch the security context to "domain\username" from within the job by using a proxy&a powershell step or with execute as login and a new connection using OPENROWSET:

powershell:

--create credential
--#!!!!!!!!!!!!!!!!  set domain account and password !!!!!!!!!!!!!!!!!!
create credential credentialUserxyz with identity = '**domain\user**', 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

openrowset:

execute as login = 'domain\username';

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