Impersonated Searching against SharePoint

by Vishal 5. November 2013 08:59
Like many typical deployments of SharePoint 2010 and FAST Search Server for SharePoint 2010, I’ve been dealing with one recently that has a search center that uses Kerberos authentication.
 
Consider the following scenario where you have a client web application that uses the SharePoint (with FAST) search service to perform searches and publish the results to users.
 
User -----------> Client Web App ------------> SharePoint
 
In a typical intranet scenario, the user authenticates to the Client Web App using windows authentication. The client then needs to pass the user identity through to the backend SharePoint search service in order to provide the security trimmed results back to the user.
 
The out-of-the-box way to do this with SharePoint, is to set up the Kerberos authentication for the Client Web App and for the SharePoint search center by setting up SPNs for the domain identity that is used as the app pool running the client web app and delegation rights. That way, the user can authenticate to the client web app and the app pool identity can then delegate that Kerberos ticket back to SharePoint when calling the search service. This works but setting up Kerberos is tricky. The other disadvantage that I’ve seen is that this works great when the User’s browser is IE since IE supports NTLM authentication for the intranet by default. For other users that are on Linux systems or Macs or using browsers like Firefox and Chrome, NTLM authentication is tricky and requires browser specific configuration to make it work.
 
Fortunately there’s another way we can make this scenario work but it does require custom development. We can make SharePoint impersonate a user when performing a search using a trusted identity. Here your client web application will be running as the trusted domain identity that will be allowed to impersonate a user.
 
On the SharePoint side, we can create a solution, that deploys a web service that can perform an impersonated search on behalf of a user using the object model. The client application can then call this new service instead of the out of the box search.asmx.
 
Here is the code for the web service that will do impersonated searches:
    class SearchService
    {
        private const string TRUSTED_ACCOUNT = @"domain.com\trustedserviceaccount";
        private const string SEARCH_SITECOLLECTION = "https://searchcenter.domain.com/";

        [WebMethod]
        public DataTable TrustedImpersonatedQuery(string UserName, string QueryText, string[] ReturnFields, SortProperty[] SortFields, int StartIndex, int PageSize)
        {
            DataTable dt = null;
            string NTAccount = HttpContext.Current.User.Identity.Name.ToLower();
            if (NTAccount.Equals(TRUSTED_ACCOUNT)) //only do impersonation if the search is requested by the trusted account
            {
                SPSecurity.RunWithElevatedPrivileges(delegate()
                {
                    using (WindowsIdentity impersonatedIdentity = new WindowsIdentity(UserName + "@domain.com"))
                    {
                        using (WindowsImpersonationContext wic = impersonatedIdentity.Impersonate())
                        {
                            dt = Query(QueryText, ReturnFields, SortFields, StartIndex, PageSize);
                        }
                    }
                });
            }
            else //else do an unimpersonated search
            {
                dt = Query(QueryText, ReturnFields, SortFields, StartIndex, PageSize);
            }
            return dt;
        }

        private DataTable Query(string QueryText, string[] ReturnFields, SortProperty[] SortFields, int StartIndex, int PageSize)
        {
            DataTable dt = null;
            using (SPSite SearchSiteCollection = new SPSite(SEARCH_SITECOLLECTION))
            {
                using (KeywordQuery kq = new KeywordQuery(SearchSiteCollection))
                {
                    kq.ResultsProvider = SearchProvider.FASTSearch;
                    kq.SelectProperties.AddRange(ReturnFields);
                    kq.EnableFQL = false;
                    
                    foreach (SortProperty s in SortFields)
                    {
                        kq.SortList.Add(s.Name, (Microsoft.Office.Server.Search.Query.SortDirection)Enum.Parse(typeof(Microsoft.Office.Server.Search.Query.SortDirection), s.Direction.ToString()));
                    }
                    
                    kq.RowLimit = PageSize;
                    kq.StartRow = StartIndex;
                    kq.QueryText = QueryText;
                    kq.ResultTypes |= ResultType.RelevantResults;
                    dt = kq.Execute()[ResultType.RelevantResults].Table;
                    dt.ExtendedProperties.Add("TotalResults", kq.QueryInfo.TotalResults);
                }
            }
            return dt;
        }
    }
    public enum SortDirection
    {
        Ascending,
        Descending
    }
    public class SortProperty
    {
        public string Name;
        public SortDirection Direction;
    }

Tags: , , , , , ,

.NET | ASP.Net | C# | MOSS | Sharepoint

Call a webservice from TSQL (Stored Procedure) using MSXML

by Vishal 22. December 2009 10:13

I am working on a n integration project that required me to call a webservice through a stored procedure in the SQL database. After proving my concept using the CLR integration features in Sql Server, I learned that the production database was actually running on Sql Server 2000 compatibility level. There went my proof of concept.

 

So now I have to resort to doing web service calls the old way, making post requests using MSXML. This is what my stored procedure looks like. 

CREATE proc [dbo].[spHTTPRequest] 
      @URI varchar(2000) = '',      
      @methodName varchar(50) = '', 
      @requestBody varchar(8000) = '', 
      @SoapAction varchar(255), 
      @UserName nvarchar(100), -- Domain\UserName or UserName 
      @Password nvarchar(100), 
      @responseText varchar(8000) output 
as 
SET NOCOUNT ON 
IF    @methodName = '' 
BEGIN 
      select FailPoint = 'Method Name must be set' 
      return 
END 
set   @responseText = 'FAILED' 
DECLARE @objectID int 
DECLARE @hResult int 
DECLARE @source varchar(255), @desc varchar(255) 
EXEC @hResult = sp_OACreate 'MSXML2.ServerXMLHTTP', @objectID OUT 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
                  source = @source, 
                  description = @desc, 
                  FailPoint = 'Create failed', 
                  MedthodName = @methodName 
      goto destroy 
      return 
END 
-- open the destination URI with Specified method 
EXEC @hResult = sp_OAMethod @objectID, 'open', null, @methodName, @URI, 'false', @UserName, @Password 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'Open failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
-- set request headers 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Type', 'text/xml;charset=UTF-8' 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
-- set soap action 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'SOAPAction', @SoapAction 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
declare @len int 
set @len = len(@requestBody) 
EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, 'Content-Length', @len 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'SetRequestHeader failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
/* 
-- if you have headers in a table called RequestHeader you can go through them with this 
DECLARE @HeaderKey varchar(500), @HeaderValue varchar(500) 
DECLARE RequestHeader CURSOR 
LOCAL FAST_FORWARD 
FOR 
      SELECT      HeaderKey, HeaderValue 
      FROM RequestHeaders 
      WHERE       Method = @methodName 
OPEN RequestHeader 
FETCH NEXT FROM RequestHeader 
INTO @HeaderKey, @HeaderValue 
WHILE @@FETCH_STATUS = 0 
BEGIN 
      --select @HeaderKey, @HeaderValue, @methodName 
      EXEC @hResult = sp_OAMethod @objectID, 'setRequestHeader', null, @HeaderKey, @HeaderValue 
      IF @hResult <> 0 
      BEGIN 
            EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
            SELECT      hResult = convert(varbinary(4), @hResult), 
                  source = @source, 
                  description = @desc, 
                  FailPoint = 'SetRequestHeader failed', 
                  MedthodName = @methodName 
            goto destroy 
            return 
      END 
      FETCH NEXT FROM RequestHeader 
      INTO @HeaderKey, @HeaderValue 
END 
CLOSE RequestHeader 
DEALLOCATE RequestHeader 
*/ 
-- send the request 
EXEC @hResult = sp_OAMethod @objectID, 'send', null, @requestBody 
IF    @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'Send failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
declare @statusText varchar(1000), @status varchar(1000) 
-- Get status text 
exec sp_OAGetProperty @objectID, 'StatusText', @statusText out 
exec sp_OAGetProperty @objectID, 'Status', @status out 
select @status, @statusText, @methodName 
-- Get response text 
exec sp_OAGetProperty @objectID, 'responseText', @responseText out 
IF @hResult <> 0 
BEGIN 
      EXEC sp_OAGetErrorInfo @objectID, @source OUT, @desc OUT 
      SELECT      hResult = convert(varbinary(4), @hResult), 
            source = @source, 
            description = @desc, 
            FailPoint = 'ResponseText failed', 
            MedthodName = @methodName 
      goto destroy 
      return 
END 
destroy: 
      exec sp_OADestroy @objectID 
SET NOCOUNT OFF 

GO

 

The stored procedure takes the following parameters:

  1.  @URI: the URI of the web service
  2.  @MethodName: this would be ‘GET’ or ‘POST’
  3.  @RequestBody: this is your SOAP xml that you want to send
  4. @SoapAction: this the operation that you want to call on your service
  5. @UserName: NT UserName if your web service requires authentication
  6. @Password: the password if using NT Authentication on the web service
  7. @ResponseText: this is an out parameter that contains the response from the web service

 

Here is a sample call to my service 

declare @xmlOut varchar(8000)
Declare @RequestText as varchar(8000);
set @RequestText=
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/">
   <soapenv:Header/>
   <soapenv:Body>
      <tem:CreateOrder>
         <!--Optional:-->
         <tem:OrderRequest>
            <tem:OrderId>200</tem:OrderId>
            <!--Optional:-->
            <tem:OrderName>something</tem:OrderName>
         </tem:OrderRequest>
      </tem:CreateOrder>
   </soapenv:Body>
</soapenv:Envelope>'
exec spHTTPRequest 
'http://localhost/testwebservices/helloworldservice.asmx', 
'POST', 
@RequestText,
'http://tempuri.org/CreateOrderForMe',
'', '', @xmlOut out
select @xmlOut 

The stored procedure runs and selects the response from my service call which in my case is:

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <soap:Body>
  <CreateOrderForMeResponse xmlns="http://tempuri.org/">
   <CreateOrderForMeResult>
    <Message>Order Created for me</Message>
   </CreateOrderForMeResult>
  </CreateOrderForMeResponse>
 </soap:Body>
</soap:Envelope>

Note that the stored procedure automatically adds the appropriate http headers for Content-Type, SoapAction and Content-Length. If you need to pass additional http headers with your request, refer the section in the center of the stored procedure which is commented out. Basically you need a table in your database called RequestHeaders with 3 columns; HeaderKey, HeaderValue, Method. Populate this table with the keys and values for the additional headers. The Method column will contain GET or POST depending on what method you are using.

Another thing to note here is that I tried the above calls with MSXML2.XMLHTTP and Microsoft.XMLHTTP but did not succeed with either. I kept getting an error from msxml3.dll or msxml6.dll with message "The parameter is incorrect". I believe this to be a bug with msxml that requires the request body text to be in a specific format that is not available through a sql data type and hence cannot be used via TSQL. THese will hoever work fine when performing a simple http request (non- SOAP) or a SOAP request that does not need a request body. for performing SOAP requests from within TSQL the only component that worked for me was MSXML2.ServerXMLHTTP. There are some third-party components that I came across, some paid, but MSXML2.ServerXMLHTTP worked just fine for me.

The next step is to actually take the SOAP response that I recieved and extract the appropriate data from it - that will be another post.

Tags: , , ,

.NET | C# | SQL | SQL Server Management Studio

Passing JavaScript Objects to the CLR

by Vishal 16. December 2009 08:19

Passing JavaScript Objects to the CLR

Asp.Net 3.5 makes it easy to pass JavaScript Objects to the CLR for AJAX calls. Here it is...

For this example, I created an ASP.Net Web Service Application which gave me a simple web service to start off with called Service1.asmx. The sample service has a single sample web method called HelloWorld() which just returns the text “Hello World”. For this example, since we want to pass something to the service method, I modify the service method a little bit so it looks like below:

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Web; 
using System.Web.Services; 
namespace JSAjaxTest 
{ 
    /// <summary> 
    /// Summary description for Service1 
    /// </summary> 
    [WebService(Namespace = "http://tempuri.org/")] 
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] 
    [System.ComponentModel.ToolboxItem(false)] 
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService] 
    public class Service1 : System.Web.Services.WebService 
    { 
        [WebMethod] 
        public string HelloWorld(string text) 
        { 
            return text; 
        } 
    } 
}

 Basically I just modified the method to accept a string and return that same string back.

Now add an application page to the project, default.aspx, which will contain the javascript that makes the AJAX call. The default.aspx page contains a script manager, a html button and a piece of javascript that gets called when the button is clicked to make the AJAX call. This is what the page looks like:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="default.aspx.cs" Inherits="JSAjaxTest._default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title></title>

    <script type="text/javascript">

        function CallService() {

            JSAjaxTest.Service1.HelloWorld("Hello World", function(response) { alert(response); });

        }

    </script>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        &nbsp;<asp:ScriptManager ID="ScriptManager1" runat="server">

            <Services>

                <asp:ServiceReference Path="/Service1.asmx" />

            </Services>

        </asp:ScriptManager>

        <input type="submit" value="hitme" onclick="javascript:CallService();" /></div>

    </form>

</body>

</html>

 

Looking at the javascript on the page, the call is really made here:

JSAjaxTest.Service1.HelloWorld("Hello World", function(response) { alert(response); });

The call to the webservice through javascript uses the script manager to make the call, pass in the javascript argument and specifies the inline call back function which gets called when the AJAX call is complete. The inline function simply has an alert statement that displays the response.

When you run the project, clicking on the button will pass the text, “Hello World” to the “HelloWorld()” method of the web service via AJAX. There is no postback.

Tags: ,

.NET | C#

ssms tools pack - Auto Generate CRUD stored Procedures for your database and more

by Vishal 1. June 2009 19:53

Just found a great tool that will hopefuly save me tons of work...

It can auto-generate the CRUD stored Procedures for your tables - that was the most useful to me. Plus there are a lot of other useful features for the IDE.

  • Window Connection Coloring.
  • Query Execution History (Soft Source Control) and Current Window History.
  • Search Table or Database Data.
  • Uppercase/Lowercase keywords and proper case Database Object Names.
  • Run one script on multiple databases.
  • Copy execution plan bitmaps to clipboard.
  • Search Results in Grid Mode and Execution Plans.
  • Generate Insert statements for a single table, the whole database or current resultsets in grids.
  • Text document Regions and Debug sections.
  • Running custom scripts from Object explorer's Context menu.
  • CRUD (Create, Read, Update, Delete) stored procedure generation.
  • New query template.

http://www.ssmstoolspack.com/