SQL CLR File System Access

Question

Is there some reason why Impersonation would NOT work from a CLR-created stored procedure?  I think I have implemented the SqlContext Impersonation code correctly as I successfully get the SqlContext.WindowsIdentity.Name (the caller), but the code that is run after I call the Impersonate() function does not run as the impersonated user.  I'm trying to read the properties of a file out on the network that the caller has permissions to.  Is there a setting on the SQL side that could prevent this from working?  I'm using SQL Server and Windows Authentication mode.  The Permissional Level is set to External.  Here is a code snippet...

Dim clientId As WindowsIdentity
clientId = SqlContext.WindowsIdentity
Dim impersonatedUser As WindowsImpersonationContext = clientId.Impersonate()
Dim filePerm As New FileIOPermission(FileIOPermissionAccess.Read, path)
filePerm.Assert()
Dim fi As New FileInfo(path)
       
If fi.Exists Then
   'populate the record.
   record.SetString(0, fi.FullName)...

In the code above, the "fi.Exists" check fails.

Thank you in advance for any insight.

Answer 1

Are you actually getting a permissions error? What’s the exact error message? If it’s a permission error, shouldn’t the code fail on Assert rather than fi.Exists?

 

Also, remember that you’re trying to write to the file system from the machine SQL Server is on, rather than the machine you are developing on. Does that machine have access to that part of the network?

 

Finally, if you remove the impersonation and  give access to the SQL Server service account, does it work then?

 

Cheers, Bob

Answer 2

Hi Bob.  I appreciate your help.

I'll start from the bottom...  Yes, when the SQL Server service account has access it works fine.  The machine does have access to that part of the network.  The error message I get if I try to read a property of the file is:

"Msg 6522, Level 16, State 1, Procedure spGetFileDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "spGetFileDetails":

System.UnauthorizedAccessException: Access to the path '...' is denied."

Here is the rest of the code...

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub spGetFileDetails(ByVal path As String)

        Dim record As New SqlDataRecord( _
            New SqlMetaData("Alternate Name", SqlDbType.NVarChar, 255),
            New SqlMetaData("Size", SqlDbType.NVarChar, 255),
            New SqlMetaData("Creation Date", SqlDbType.NVarChar, 255),
            New SqlMetaData("Creation Time", SqlDbType.NVarChar, 8),
            New SqlMetaData("Last Written Date", SqlDbType.NVarChar, 8),
            New SqlMetaData("Last Written Time", SqlDbType.NVarChar, 8),
            New SqlMetaData("Last Accessed Date", SqlDbType.NVarChar, 8),
            New SqlMetaData("Last Accessed Time", SqlDbType.NVarChar, 8),
            New SqlMetaData("Attributes", SqlDbType.NVarChar, 255) _
        )

        'impersonate the caller
        Dim clientId As WindowsIdentity
        clientId = SqlContext.WindowsIdentity
        Dim impersonatedUser As WindowsImpersonationContext = clientId.Impersonate()
        ' access the file
        Dim filePerm As New FileIOPermission(FileIOPermissionAccess.Read, path)
        filePerm.Assert()
        Dim fi As New FileInfo(path)

        Try
            Try

                If Not fi.Exists Then
                    'populate the record.
                    record.SetString(0, fi.FullName)
                    record.SetString(1, fi.Length.ToString)
                    record.SetString(2, fi.CreationTime.Date.ToString("yyyyMMdd"))
                    record.SetString(3, fi.CreationTime.TimeOfDay.ToString)
                    record.SetString(4, fi.LastWriteTime.Date.ToString("yyyyMMdd"))
                    record.SetString(5, fi.LastWriteTime.TimeOfDay.ToString)
                    record.SetString(6, fi.LastAccessTime.Date.ToString("yyyyMMdd"))
                    record.SetString(7, fi.LastAccessTime.TimeOfDay.ToString)
                    record.SetString(8, fi.Attributes.ToString)
                    impersonatedUser.Undo()
                    'Send the record to the client
                    SqlContext.Pipe.Send(record)
                Else
                    record.SetString(0, clientId.Name)
                    record.SetString(1, clientId.IsAuthenticated)
                    record.SetString(2, fi.Exists)
                    record.SetString(3, Date.Now.TimeOfDay.ToString)
                    record.SetString(3, Date.Now.Date.ToString("yyyyMMdd"))
                    impersonatedUser.Undo()
                    SqlContext.Pipe.Send(record)
                End If

            Finally
                ' Undo impersonation.
                If impersonatedUser IsNot Nothing Then
                    impersonatedUser.Undo()
                End If

            End Try

        Catch e As Exception
            Throw e
        End Try

    End Sub

End Class

In order to generate the error, I changed the "If fi.Exists" to "If Not fi.Exists" since that check was returning false.  The "Else" is just for my own debugging.

Answer 3

Hmmm....

If you log on to the SQL Server machine as the user you are trying to impersonate, can you access that file using exactly that filename? If it can, perhaps a tracing tool like Process Monitor http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx can tell you where the permissions are failing. The only other thing, if a direct login to the SQL Server machine can access the file, I can think of offhand is a Kerberos double-hop problem (http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx).

Is the person you are impersonating logging on from a remote machine? That would produce the double-hop problem.

Machine A –> SQL Server Machine –> Remote Machine would be the double hop.

One last thing... if you're not using Kerberos but instead you're using NTLM, NTLM can't do a double-hop (known as delegated authentication) at all. You'll need to come up with another strategy.

Hope this helps,

Cheers, Bob

Answer 4

Hi Bob..  Yes, if I log on to the SQL Server machine (the user I am trying to impersonate), I can access that file using exactly that filename.  We're using Kerberos.  I am not logging on to a remote device.

If the SQL server service account is me (the user I'm impersonating.. I verified it's definitely impersonating me by looking at the 'clinetId.Name') everything works fine.  My main concern was that the code was screwed up, but I think have it right based on everything I've read and it sounds like you didn't find a problem with it either.

I appreciate your help!  - Don

---

Don

Answer 5

Hi

    Could you answer few question:

1. Kerberos case: I assume that you are testing using SMO so can you run SMO on the same server as your SQL is and do the same tests as before? Does it pass?

2. If it is not kerberos case can you paste here creation script of your procedure? Also what user executes this procedure (sql user or domain user)? Can you paste what query like SELECT USER_NAME() AS [USER_NAME], SUSER_SNAME() AS [SUSER_SNAME], ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN], SESSION_USER AS [SESSION_USER] returns if it executes inside you procedure?

---

everything is a matter of probability...

Answer 6

Hi..

I'm not using SMO... didn't actually know what it was until I looked it up.

I execute this procedure while logged into SSMS using my Windows credentials.  The SQL server is running under the SQL server service account which does not have access to the files.  If that account is given access to the file share, it works correctly.  However, I don't want to do this.

USER_NAME: dbo, SUSER_SNAME: HQ\dbugbee, SESSION_USER: HQ\dbugbee, ORIGINAL_LOGIN: dbo

It's a CLR procedure, so there an assembly as well.  Unfortunately, it does not paste into here very well.

---

Don

Answer 7

Hi Don,

You might try a using a tool like Sysinternals Process Monitor http://technet.microsoft.com/en-us/sysinternals/bb896645 to see who is actually trying to access the file. Trying tracing an identity that works (you said it works with the SQL Server service account) and one that doesn't work. Process Monitor can monitor file access.

Cheers, Bob

Answer 8

My bad...i meant SSMS. Could you run your test when SSMS is running locallly on SQL server? Also could you check how your CLR proc is created? Is it like this:

CREATE PROCEDURE [dbo].[spname]
	@someparam [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [someproject].[StoredProcedures].[spname]

Most important part is 'WITH EXECUTE AS CALLER' - is it excacly like in your proc?

---

everything is a matter of probability...

Answer 9

Yes, it's "WITH EXECUTE AS CALLER".

---

Don

Answer 10

Hi Bob..

I used the tool but it captures so much information that it wasn't useful for me.  I grabbed all of the data while the sp was executing but I didn't see anything in there that was useful to me.  I could see the ssms executing the sp but I didn't see anything that looked like something was trying to access the file.

---

Don

Answer 11

The tool has a filtering capability, so you can look at only what you want to.

 

Cheers, Bob