- Programming with SqlClient
- Context: The SqlContext Class
- Connections
- Commands: Making Things Happen
- Obtaining Results
- Transactions
- Pipe
- Creating and Sending New Rowsets
- Using the WindowsIdentity
- Calling a Web Service from SQLCLR
- Exception Handling
- SqlTriggerContext
- SqlClient Classes That You Can’t Use on the Server
- Where Are We?
This chapter is from the book
This chapter is from the book
This chapter is from the book
Using the WindowsIdentity
Procedural code executes SQL in the context of the caller by default. In SQL Server 2005, you can also specify alternative execution contexts by using the new WITH EXECUTE AS clause. (We’ll talk more about this clause in Chapter 6.) When you’re using an assembly created with the EXTERNAL_ACCESS or UNSAFE permission sets, however, you’re allowing to access resources outside SQL Server. These resources can include other database instances, the file system, and external Web Services. Some of these resources may require authentication using a Windows security principal. If you’re running the stored procedure as a SQL Server user associated with a Windows login, you can choose to impersonate the logged-in user. If you don’t choose to impersonate the logged-in user (or if you’re running the procedure while logged in as a SQL login), all external access takes place under the identity of the Windows principal running the SQL Server process. Under the principal of least privilege, this most likely should be a user with only the privileges required to run SQL Server. This principal probably won’t have access to the external resources that you want.
You can get the Windows identity of the user running the procedure by using the SqlContext.WindowsIdentity property, as shown in Listing 4-18.
Listing 4-18: Using impersonation in a SQLCLR stored procedure
// other using statements elided for clarity
using System.Security.Principal;
using Microsoft.SqlServer.Server
public static void GetFile(string filename)
{
string s;
using (WindowsIdentity id = SqlContext.WindowsIdentity)
{
WindowsImpersonationContext c = id.Impersonate();
StreamReader sr = new StreamReader(filename);
s = sr.ReadLine();
sr.Close();
c.Undo();
}
SqlContext.Pipe.Send(s);
}
When you have the WindowsIdentity instance, you can call its Impersonate method. Be sure to save the WindowsImpersonationContext that is returned, because you’ll have to call its Undo method to undo the impersonation. Note that you can do data access only before calling Impersonate or after calling Undo. Attempting to use an impersonated context for data access will fail. Also, bear in mind that if you’re executing the stored procedure as a SQL login rather than a Windows login, the WindowsIdentity instance will be null.