SqlTypes.SqlXml and impersonation

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 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:

public static void LoadSomeXML(SqlXml thexml)
{
// impersonate
// do something here is:
   XmlDocument doc = new XmlDocument();
   doc.Load(thexml.Value);
// undo
}

interestingly, this code works:
public static void LoadSomeXML(SqlString thexml)
{
// impersonate
// do something here is:
   XmlDocument doc = new XmlDocument();
   doc.Load(thexml.Value);
// undo
}

because 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.

A 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.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.