27. července 2012 13:20
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.
29. července 2012 15:21ModerátorAre 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
31. července 2012 15:03
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.
- Upravený dbug2 31. července 2012 18:36
1. srpna 2012 2:45ModerátorHmmm....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
- Upravený Bob BeaucheminMVP, Moderator 1. srpna 2012 2:50
1. srpna 2012 13:50
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
10. září 2012 13:04
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...
10. září 2012 13:39
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.
11. září 2012 4:57Moderátor
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.
11. září 2012 6:51
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...
- Upravený pen_2 11. září 2012 6:53
11. září 2012 10:51Yes, it's "WITH EXECUTE AS CALLER".
11. září 2012 12:35
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.
11. září 2012 14:21ModerátorThe tool has a filtering capability, so you can look at only what you want to.Cheers, Bob