\nI've been working on a student question about using Impersonation inside of a stored procedure. This one's worth sharing.\n<\/p>\n
\nYou can do impersonation using the .NET SqlClient data provider using code roughly like this:\n<\/p>\n
\nWindowsIdentity w = SqlContext.WindowsIdentity;
\nWindowsImpersonationContext c = w.Impersonate();
\n\/\/ do something here
\nc.Undo();\n<\/p>\n
\nThe rule is that in the "do something here" part, I'm allowed to do things like access the file system and these happen using the correct identity. But I'm NOT allowed to do data access. I'd always thought that "data access" meant using the classes in System.Data.SqlClient to access database data. But using the System.Data.SqlTypes.SqlXml class (which uses XmlReader) is also considered data access. So this code fails:\n<\/p>\n
\npublic static void LoadSomeXML(SqlXml thexml)
\n{
\n\/\/ impersonate
\n\/\/ do something here is:
\n XmlDocument doc = new XmlDocument();
\n doc.Load(thexml.Value);
\n\/\/ undo
\n}\n<\/p>\n
\ninterestingly, this code works:
\npublic static void LoadSomeXML(SqlString thexml)
\n{
\n\/\/ impersonate
\n\/\/ do something here is:
\n XmlDocument doc = new XmlDocument();
\n doc.Load(thexml.Value);
\n\/\/ undo
\n}\n<\/p>\n
\nbecause it doesn't use XmlReader to do the load. So if you pass in a SqlXml type parameter and use this class inside an impersonation context, it will fail. The error message says "Can't revert thread token in UDF\/UDP…" so I wonder if this isn't related to some other threading issues reported using the impersonation context.\n<\/p>\n
\nA good rule of thumb is to only do the minimum number of operations required while in the impersonation context and revert back (Undo) as soon as possible. In this case, all I really wanted to do was call doc.Save("somefile.xml") to save to the filesystem. If I move the declaration of XmlDocument and doc.Load() outside the impersonation context, doc.Save() works perfectly.<\/p>\n","protected":false},"excerpt":{"rendered":"
I've been working on a student question about using Impersonation inside of a stored procedure. This one's worth sharing. You can do impersonation using the .NET SqlClient data provider using code roughly like this: WindowsIdentity w = SqlContext.WindowsIdentity; WindowsImpersonationContext c = w.Impersonate(); \/\/ do something here c.Undo(); The rule is that in the "do something […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,37,38],"tags":[],"class_list":["post-921","post","type-post","status-publish","format-standard","hentry","category-sql-server-2005","category-sql-server-xml","category-sqlclr"],"yoast_head":"\n