Executing a Web Service in a CLR Based Table Valued Function and Representing its IEnumerable Type Output as a Table in SQL Server

• Download source - 32.58 KB

Introduction

In this article, I would describe how to invoke a web service from within a CLR based Table Valued Function (TVF) and then how to represent the IEnumerable output of that web service as a table directly in SQL Server 2005. I would present two methods of executing a web service to overcome the security restriction posed by the CLR based environment; One by passing explicit credentials to a web service within SQL Server user context: Second by impersonating an SQL Server user to a local service account user.

Background

A few weeks ago, I was assigned a task of finding some way to integrate the results of a third party Concept Search (CAAT) with search results coming from our database. We have created the CAAT indexes based on the search items text stored in the database, but the items meta data is stored in database not in the search indexes. So, every time we perform a CAAT search, we need to fetch the meta data of found items from the database. The CAAT search exposes all of its functionality in the form of web services and its search results are returned in the form of an array of SearchItem objects as an IEnumerable type. So, based on the output type we needed to find something that could make it easy to represent IEnumerable in SQL Server. Thanks to Microsoft for making CLR based table valued function output also in the form of IEnumerable, this provision enables us to represent the output of CAAT search directly as a table in SQL Server. Now, the solution is to execute the CAAT search web service within a CLR based TVF and traversing the result objects for ItemIds returned in a tabular form. When these ItemIds are returned, we could use them for further joining with other database tables to fetch the meta data of found items stored in the database.

The Code

The code is a Visual Studio 2008 solution containing two projects. One project is of type Database Projects-->Microsoft SQL Server-->SQL CLR and the second project is an ASP.NET Web Service to be called from a TVF written in the CLR project. The web service is not actual CAAT search web service but a sample web service just to illustrate the purpose.

The CLR Project

This project contains two CLR based table valued functions. Both of them call a web service and give similar output as an array of IEnumerable type, the only difference between them is to call the web service either by using explicit credentials or by impersonating the local service account of SQL Server as discussed earlier in the introduction. So, the first method with passing explicit credentials.

/// <summary>
/// The TVF calling the web service with explicit credentials.
/// </summary>
/// <returns>
[SqlFunction(FillRowMethodName = "FillRow",
       TableDefinition = "ItemID int")]
public static IEnumerable TVFWithCredentials()
{
    IEnumerable items;
    // Create the instance of a web service to be called. Remember this is not actual 
    // CAAT search web service but a similar fake web service just for testing.
    CLRTVFService service = new CLRTVFService();
    // Passing the credentials explicitly because the code is running within the 
    // context of a SQL Server user that cannot access some external resources.
    // There is possibility that we could access a web service deployed locally
    // without passing these credentials but when its deployed remotely then we 
    // need to pass the network credentials explicitly.
    ICredentials cr = new NetworkCredential("YourUserName", "YourPassword", 
	"YourDomainName");
    service.Credentials = cr;
    // Execute the web service and get the IEnumerable type results
    items = service.PerformConceptSearch();
    return items;
}
public static void FillRow(object row, out int ItemID)
{
    ItemID = ((SearchItem)row).ItemID;
}	

[Note:] The second method named "FillRow" in the above listing is an event handler of each row. It works when each and every row is traversed in the IEnumerable for sending the output to the caller.

The second method of calling a web service by impersonating the local service account user is.

/// <summary>
/// The TVF calling a web service by impersonating the local service account user.
/// </summary>
/// <returns />
[SqlFunction(FillRowMethodName = "FillRow",
        TableDefinition = "ItemID int")]
public static IEnumerable TVFWithImpersonation()
{
    WindowsIdentity windowsIdentity = null;
    WindowsImpersonationContext userImpersonated = null;
    windowsIdentity = SqlContext.WindowsIdentity;
    IEnumerable items = null;

    // Switch the context to local service account user (a domain user) 
    // by getting its identity in order to call the web service
    userImpersonated = windowsIdentity.Impersonate();
    if (userImpersonated != null)
    {
        // Create the instance of a web service to be called. 
        // Remember this is not actual 
        // CAAT search web service but a similar fake web service just for testing.
        CLRTVFService service = new CLRTVFService();
        // Execute the web service and get the IEnumerable type results
        items = service.PerformConceptSearch();
        // Switch the context back to the SQL Server
        userImpersonated.Undo();
    }
    return items;
} 

[Note:] The local service account user is listed as "NT AUTHORITY\SYSTEM" in the SQL Server Logins. Its a domain user having local administrator rights on the machine where SQL Server is installed and its the same domain user with which the SQL Server is installed on that particular machine. In order to call an external web service, we need to impersonate that very user. Please also note that this user must be mapped to the database in which it needed to be impersonated by using the following T-SQL command.

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'NT AUTHORITY\SYSTEM')
CREATE USER [NT AUTHORITY\SYSTEM] FOR LOGIN [NT AUTHORITY\SYSTEM] _
	WITH DEFAULT_SCHEMA=[db_owner]

The Web Service Project

This project is an ASP.NET Web Service and contains only one method named "PerformConceptSearch" in order to make it similar to actual CAAT search. The method just creates an IEnumerable type array of 5 hard coded objects of a fake SearchItem class defined also within the same project. Here is the code of PerformConceptSearch method.

/// <summary>
/// The method returns a hard coded IEnumerable array of 5 SearchItem class objects 
/// just to illustrate the CAAT search concept because we don't 
/// have actual CAAT search here.
/// </summary>
/// <returns />
[WebMethod]
public SearchItem[] PerformConceptSearch()
{
    SearchItem[] items = new SearchItem[5];
    for (int i = 0; i <= 4; i++)
    {
        items[i] = new SearchItem();
        items[i].ItemID = i + 1;
    }
    return items;
}

/// <summary>
/// A fake SearchItem class.
/// </summary>
public class SearchItem
{
    public int ItemID { get; set; }
}

Deployment and Execution

Before deploying the CLR based assembly to SQL Server, we need to satisfy the following prerequisites.

1. Enable the CLR execution on the target database that is disabled by default. The T-SQL command to enable CLR execution is as follows:
   SQL

   sp_configure 'clr enabled', 1
   reconfigure
   GO

2. Make the database TRUSTWORTHY. This is required because our CLR project is going to be deployed with permission set = EXTERNAL_ACCESS as its calling an external web service, and for an assembly having EXTERNAL_ACCESS permissions we need to make database TRUSTWORTHY. The command for making database TRUSTWORTHY is:
   SQL

   ALTER DATABASE [CLRTVF] SET TRUSTWORTHY ON
   GO

Finally the deployment comes in. To deploy the web service, just open the solution attached and right click the "CLRTVFWebService" project and click the properties. In project properties page, select the Web tab as shown below:

Please check the "Use Local IIS Web Server" check box and press the button "Create Virtual Directory" it will create a virtual directory automatically on your machine having the web service. Please don't change the name of virtual directory created because this default address is used within the CLR project web reference and if it's changed, we need to update the web reference in the CLR project and re-compile it.

For deploying the CLR project, I have created two database scripts in the folder "Database Scripts" available in the main folder of attached solution. First execute the scripts named "CLRTVF.sql" in order to create the test database and then execute the "CLRTVF_Deployment.sql" to deploy the CLR project. Remember one thing within the deployment script - I have used "LOCATE_YOUR_BIN_PATH_HERE" string for a path on your machine. It will point to the location of your downloaded project named "CLRTVF" i.e. you might download the attached project at "E:\CLRTVF". Now the complete path in your case would be like this "E:\CLRTVF\CLRTVF\bin\Debug\" that will point to the DLL of assembly to be deployed to the SQL Server.

At last, the execution. We can test any of the TVF as SQL statements as given below:

SELECT ItemID FROM [TVFWithCredentials]()
-- OR 
SELECT ItemID FROM [TVFWithImpersonation]()

Our intention for fetching meta data of search items by joining with Items table was like this:

SELECT Items.ItemID, Items.ItemName, Items.ItemMetaData FROM [Items] _
	INNER JOIN [TVFWithCredentials]() AS TVF ON Items.ItemID = TVF.ItemID

Conclusion

The IEnumerable type output of a CLR based Table Valued Function enables us to represent any IEnumerable type array as a table directly in the SQL Server, that makes life easy when we wish to integrate an external array with SQL Server.