SQLCLR Impersonation keeps using the service identity

I have the following code:

Public Shared Function MyTest() As SqlString

    Dim rc As String = Nothing
    Dim impersonatedUser As WindowsImpersonationContext = Nothing
    If SqlContext.IsAvailable Then
        If SqlContext.WindowsIdentity IsNot Nothing Then
            impersonatedUser = SqlContext.WindowsIdentity.Impersonate
        End If
    End If
    Try
        rc = System.IO.File.Exists("C:\Data Files\Test\42.txt").ToString
    Catch ex As Exception
        Return ex.Message
    Finally
        If impersonatedUser IsNot Nothing Then
            impersonatedUser.Undo()
        End If
    End Try

    Return rc
End Function

In SQL the declation of the assembly is as follows:

CREATE ASYMMETRIC KEY aKeyCLR FROM EXECUTABLE FILE = '$(BASE)CLR.dll'
CREATE LOGIN CLRLogin FROM ASYMMETRIC KEY aKeyCLR
GRANT UNSAFE ASSEMBLY TO CLRLogin

The create function:

CREATE FUNCTION dbo.Test()
RETURNS NVARCHAR(4000)
AS
    EXTERNAL NAME myStuff.[CLR.FileFunctions].[MyTest]
GO

When I execute SELECT Test() the file access is still done by account "NT SERVICE\MSSQLSERVER". I am logged on to SQL server with Windows Authentication and would expect that user to do the file access.

What am I missing here?

Solution

Your code appears to be correct. You can test by allowing read access only to your Windows Login and then changing the Exists() into string _Test = ReadAllText() or something like that.

What you are initially seeing is the main identity of the process. Impersonating another account is not a replacement of the original security context, it is simply a new SID (Security ID) to run the process as. In fact, impersonating an OS account does not change the "active" User in the Registry (i.e. HKEY_CURRENT_USER), nor does it reset the environment variables (i.e. PATH, TEMP, etc); those actions happen upon a full login.

Within SQL Server, when impersonating via EXECUTE AS, you can see the main identity of the Session via the ORIGINAL_LOGIN() built-in function.