다음을 통해 공유

Using SAML Claims, SharePoint, WCF, Claims to Windows Token Service and Constrained Delegation to Access SQL Server

  • 아티클

Okay, this will hopefully be the longest titled post I ever write, but I wanted to make sure it covered all the relevant technologies being discussed. This is an area that I’ve heard a more rumbling about recently, which is really all about how can I take a SAML claims user and get a Windows context to access some other application. SharePoint 2010 has limited support for use of the Claims To Windows Token Service (hereafter referred to as c2wts), but only for Windows claims users with a small number of service applications. A common question is why can’t it use a SAML claims users with a valid UPN claim, and there really isn’t a technological reason why you cannot. So between the limitation in authentication types as well as the limitation in service apps that can use it, you may very well find yourself in a position where you need to build a way to connect SAML users to other applications as their underlying Windows account. This post will hopefully help you understand the basics of how it can be done.

The basic approach this scenario is going to take is to create a WCF Services Application that processes all the end user requests for data from the other application, which in our case is SQL Server. So I want to take a SAML user that is hitting the SharePoint site, and make a request as the Windows account for that SAML user when I retrieve data from SQL Server. NOTE: Even though this article is about SAML claims users, the same exact methodology can be used for Windows claims users; they get a UPN claim by default when they log in. Here’s a diagram of what the whole process looks like:

Configuring SQL Server

Let’s start on the SQL Server side. In my scenario, SQL Server is running on a server called “SQL2”. The SQL service itself is running as Network Service. This means that I do not need to create a SPN for it; if it was running as domain account then I would need to create a SPN for that service account for MSSQLSvc. For this particular scenario, I’m going to use the old Northwinds database to retrieve data. I want to easily demonstrate the identity of the user that is making the request, so I modified the Ten Most Expensive Products stored procedure so it looks like this:

 

CREATE procedure [dbo].[TenProductsAndUser] AS

SET ROWCOUNT 10

SELECT Products.ProductName AS TenMostExpensiveProducts, Products.UnitPrice, SYSTEM_USER As CurrentUser

FROM Products

ORDER BY Products.UnitPrice DESC

 

The key thing to note here is that I added SYSTEM_USER to the SELECT statement; all that does is return the current user in the column. That means when I execute a query and get the results back, I’ll see a column in my grid that contains the current user name so I’ll be able to easily see if the query executed as the current user’s identity or not. In this particular scenario I granted three Windows users rights to execute this stored procedure; any other user will not be able to do so (which will also be a useful example in the final output).

Creating the WCF Services Application

The next thing I did was to create a WCF Services Application that retrieved the data from SQL. I followed the guidelines I’ve described previously in the CASI Kit posting part 2 (https://blogs.technet.com/b/speschka/archive/2010/11/06/the-claims-azure-and-sharepoint-integration-toolkit-part-2.aspx); I did this to establish the trust between the SharePoint farm and the WCF application. That was necessary so that I could get the claims of the user making the request. You wouldn’t want to just pass the UPN claim value as a parameter, for example, because then anyone could spoof any other person’s identity by just passing in a different UPN claim value. Once the trust was configured correctly between the WCF and SharePoint, then I could go ahead and write my method that will:

  • Extract the UPN claim
  • Impersonate the user using the c2wts
  • Retrieve the data from SQL as that user

 

Here is the code that I used to do that:

 

//the following added for this code sample:

using Microsoft.IdentityModel;

using Microsoft.IdentityModel.Claims;

using System.Data;

using System.Data.SqlClient;

using System.Security.Principal;

using Microsoft.IdentityModel.WindowsTokenService;

using System.ServiceModel.Security;

 

 

public DataSet GetProducts()

{

 

   DataSet ds = null;

 

   try

   {

       string conStr = "Data Source=SQL2;Initial Catalog=

       Northwind;Integrated Security=True;";

 

       //ask for the current claims identity

       IClaimsIdentity ci =

          System.Threading.Thread.CurrentPrincipal.Identity as IClaimsIdentity;

 

       //make sure the request had a claims identity attached to it

       if (ci != null)

       {

          //see if there are claims present before running through this

          if (ci.Claims.Count > 0)

          {

              //look for the UPN claim

              var eClaim = from Microsoft.IdentityModel.Claims.Claim c in ci.Claims

              where c.ClaimType == System.IdentityModel.Claims.ClaimTypes.Upn

              select c;

 

              //if we got a match, then get the value for login

              if (eClaim.Count() > 0)

              {

                 //get the upn claim value

                 string upn = eClaim.First().Value;

 

                 //create the WindowsIdentity for impersonation

                 WindowsIdentity wid = null;

 

                 try

                 {

                     wid = S4UClient.UpnLogon(upn);

                 }

                 catch (SecurityAccessDeniedException adEx)

                 {

                           Debug.WriteLine("Could not map the upn claim to " +

                     "a valid windows identity: " + adEx.Message);

                 }

 

                 //see if we were able to successfully login

                 if (wid != null)

                 {

                        using (WindowsImpersonationContext ctx = wid.Impersonate())

                    {

                       //request the data from SQL Server

                        using (SqlConnection cn = new SqlConnection(conStr))

                        {

                           ds = new DataSet();

                           SqlDataAdapter da =

                               new SqlDataAdapter("TenProductsAndUser", cn);

                           da.SelectCommand.CommandType =

                               CommandType.StoredProcedure;

                           da.Fill(ds);

                        }

                     }

                 }

              }

          }

       }

   }

   catch (Exception ex)

   {

       Debug.WriteLine(ex.Message);

   }

 

   return ds;

}

 

Ultimately it’s really not very complicated code, so here’s a brief rundown on what’s going on. The first I do is make sure that we have a valid claims identity context, and if we do then I query the list of claims looking for the UPN claim. Assuming I find the UPN claim, I extract the value out of it and I make the call to the c2wts to do a S4U login as that user. If that login is successful, it returns a WindowsIdentity. I then take that WindowsIdentity and create an impersonation context. Once I’m impersonating the user, I then create my connection to SQL Server and retrieve the data. Here are a couple of quick troubleshooting tips to look out for:

  1. If you haven’t configured the c2wts to allow your app pool to use it, then you will get an error that’s trapped in the outer catch block. The error will be something like “WTS0003: The caller is not authorized to access the service.” I’ll give you details and a link for configuring the c2wts below.
  2. If Kerberos constrained delegation is not set up correctly, then when you try and execute the stored procedure with the da.Fill(ds); line of code, it will throw an exception that says anonymous user does not have rights to execute this stored procedure. I give a few tips on configuring constrained delegation for this scenario below.

Configuring the C2WTS

The c2wts is configured by default to a) start manually and b) not permit anyone to use it. I changed it so that a) it starts automatically and b) the application pool for my WCF Services Application is authorized to use it. Rather than go into the details of how to configure this authorization, I recommend that you read this article; the configuration information is at the end: https://msdn.microsoft.com/en-us/library/ee517258.aspx. That’s really all you need to do to get going. For more background information on c2wts I also recommend that you take a look at https://msdn.microsoft.com/en-us/library/ee517278.aspx.

 

NOTE: There is one HUGE mistake in this last article; it recommends that you create a dependency for the c2wts by running this code: sc config c2wts depend=cryptosvc. DO NOT DO THIS!! This is a typo and “cryptosvc” is not a valid service name, at least not on Windows Server 2008 R2. If you do that, then your c2wts will no longer start because it will say the dependency is marked for deletion or cannot be found. I found myself in this situation and changed the dependency to be iisadmin (which is logical because in my case at least my WCF host has to be running for me to use c2wts); otherwise I was stuck.

Configuring Kerberos Constrained Delegation

Okay, before anyone gets too freaked out by this topic let me just say this:

  1. I’m not going into nitty gritty details on getting kerb constrained delegation working. There are tomes on the topic out there.
  2. For what it’s worth, this part actually worked pretty smoothly when I wrote this up.

 

So let’s walk through the things we need for delegation. First, as I mentioned above, my SQL Server service is running as Network Service, so I don’t need to do anything there. Second, my WCF application pool is running as a domain account called vbtoys\portal. So I need to do two things for it:

  1. Create an HTTP SPN for it, using both the NetBIOS name and fully qualified name of the server from which it will be delegating. In my case my WCF server is called AZ1, so I created two SPNs that looked like this: 
    1. setspn -A HTTP/az1 vbtoys\portal
    2. setspn -A HTTP/az1.vbtoys.com vbtoys\portal
  2. I need to configure my account to be trusted for Kerberos constrained delegation to the SQL Server services running on the server “SQL2”. To do that I went into my domain controller and opened up Active Directory Users and Computers. I double-clicked on the vbtoys\portal user then clicked on the Delegation tab to configure this trust. I set it up to trust delegation for specific services only, using any kind of authentication protocol. Here’s a link to a picture of what that delegation configuration looked like:

 

Third, I needed to configure my WCF application server to be trusted for constrained delegation. Fortunately, the process is exactly the same as I described above for the user; you just find the computer account in Active Directory Users and Computers and configure it in there. Here’s a link to a picture of what its configuration looked like:

 

 

And with that, all of the non-SharePoint stuff is setup, configured and ready to go. The last thing needed is a web part to test it.

Creating the SharePoint Web Part

Creating the web part is a fairly straight-forward; I just followed the pattern I described previously for making WCF calls to SharePoint and passing the current user’s identity (https://blogs.technet.com/b/speschka/archive/2010/09/08/calling-a-claims-aware-wcf-service-from-a-sharepoint-2010-claims-site.aspx). I could have also used the CASI Kit to make the connection and call the WCF, but I decided to do it manually so to speak to make things easier to illustrate. The basic steps for creating the web part were:

  1. Create a new SharePoint 2010 project in Visual Studio 2010.
  2. Create a Service Reference to my WCF Services Application.
  3. Add a new web part
  4. Add the code to the web part to retrieve the data from the WCF and display it in a grid.
  5. Add all the information in the app.config that is generated in the Visual Studio project to the <system.ServiceModel> section of the web.config file for the web application in which my web part is going to be hosted.

NOTE: The app.config will have an attribute in it called decompressionEnabled; you MUST DELETE THAT BEFORE ADDING IT TO THE WEB.CONFIG FILE. If you leave it in there your web part will throw an error when trying to create an instance of your service reference proxy.

In terms of the steps above, all of them should be pretty self-evident other than #4, so I won’t cover the others in any detail. Here is the code for the web part however:

private DataGrid dataGrd = null;

private Label statusLbl = null;

 

 

protected override void CreateChildControls()

{

   try

   {

       //create the connection to the WCF and try retrieving the data

       SqlDataSvc.SqlDataClient sqlDC = new SqlDataSvc.SqlDataClient();

 

       //configure the channel so we can call it with FederatedClientCredentials

       SPChannelFactoryOperations.ConfigureCredentials<SqlDataSvc.ISqlData>(

       sqlDC.ChannelFactory, Microsoft.SharePoint.SPServiceAuthenticationMode.Claims);

 

       //create the endpoint to connect to

       EndpointAddress svcEndPt =

          new EndpointAddress("https://az1.vbtoys.com/ClaimsToSqlWCF/SqlData.svc");

 

       //create a channel to the WCF endpoint using the

       //token and claims of the current user

       SqlDataSvc.ISqlData sqlData =

          SPChannelFactoryOperations.CreateChannelActingAsLoggedOnUser

          <SqlDataSvc.ISqlData>(sqlDC.ChannelFactory, svcEndPt);

 

       //request the data

       DataSet ds = sqlData.GetProducts();

 

       if ((ds == null) || (ds.Tables.Count == 0))

       {

          statusLbl = new Label();

          statusLbl.Text = "No data was returned at " + DateTime.Now.ToString();

          statusLbl.ForeColor = System.Drawing.Color.Red;

          this.Controls.Add(statusLbl);

       }

       else

       {

          dataGrd = new DataGrid();

          dataGrd.AutoGenerateColumns = true;

          dataGrd.DataSource = ds.Tables[0];

          dataGrd.DataBind();

          this.Controls.Add(dataGrd);

       }

   }

   catch (Exception ex)

   {

       Debug.WriteLine(ex.Message);

   }

}

 

Again, I think this is pretty self-explanatory. The first part is about making the connection to the WCF service in a way that will pass along the current user’s claims; for more details see the link above to my previous blog post on this topic. The rest of it is just getting a dataset back and binding it to a grid if there’s data, or showing a label that says there’s no data if it fails. To illustrate all of these pieces working together, below are three screenshots: the first two show it working for two different users, which you can see in the CurrentUser column. The third shows it for a user who was not granted rights to execute the stored procedure.

 

 

That pretty much wraps it up; I’ve attached the code for the WCF Service Application and web part to this posting, along with the original Word document in which I wrote this up since the formatting of these posts so routinely stinks.

C2wtsPost.zip

Comments

  • Anonymous
    January 01, 2003
    The comment has been removed

  • Anonymous
    January 01, 2003
    thanks

  • Anonymous
    September 01, 2011
    Great article! Thank you, Steve. But real problem around here that if you need get for PerformancePoint Dashboard or other BI staff to work with C2WTS - it can't be done without Windows authentication. So if we have ADFS + FBA (and AD users) it can't be resolved that way. Any suggestions?

  • Anonymous
    December 05, 2013
    So couple of questions here ...

  1. Does this work with ANY saml claims provider? (I could make up the usernames and passwords, and as long as I use a valid UPN then this will auto-magically create a Windows Token for that user ... this allows me to impersonate pretty much anyone I like?)
  2. Is this natively supported at all by OOTB Service Apps using the C2WTS? e.g. Reporting Services 2012 / Excel Services 2013?
  • Anonymous
    January 08, 2015
    m88 : http://m88en.com
    M88.com offer online sports games Asia, Sports Betting Asia, Sports Betting Sites Asia.
    m88asia : http://m88en.net
    Link to M88BET phone: m88en.com. – Register and Open Betting Account and Membership M88BET.
    m88bet : http://www.linkm88vip.com
    MANSION88 the house is one of the largest and most prestigious. Appeared quite early in the Asian market, the so-MANSION88 currently attracts more players.
    link m88 : http://m88wiki.com
    Home the M88 is the official sponsor of the football club in the Premier League
    Wish you happy with the new M88
    m88 casino online : http://m88free.com
  • Anonymous
    March 01, 2015
    NOTE: Please download the attachment to get a Word doc with this posting in human readable format.