Step 8: Configure the Contoso federation server to get values from a SQL data store

  • Article

Applies To: Active Directory Federation Services (AD FS) 2.0

Step 8: Configure the Contoso federation server to get values from a SQL data store

In this step, we configure the Contoso federation server to pull role information from a SQL database (HOL Doctors Role) based on the e-mail address for each user. In this database, we have three tables for sourcing the roles that we want to use here. We use the e-mail address of the user who is trying to access the SharePoint site, and we use that e-mail address to look up in the database what role the user should have.

Table 1(dbo.URT) contains a list of e-mail addresses of doctors, the role that they have, and the drug trial that they belong to.

Table 2(dbo.TS) contains information about which SharePoint site belongs to which drug trial.

Table 3(dbo.RS) maps the roles in the database to the roles in the Contoso SharePoint site.

To begin using these roles, we must first add these roles to the SharePoint site and give them the correct access permissions.

To provide access for the SQL-based roles to the SharePoint site

  1. Log on to the CONTOSOSRV01 computer as CONTOSO\Administrator with "demo!23" as the user password.

  2. Navigate to the SharePoint site by going to https://docs.contoso.com/.

  3. The site redirects you to the STS login page and asks you to authenticate to the STS. On the STS login page, click Sign in using your account at this identity provider, and then click Sign In. On the next page, sign in using the credentials of administrator as username contoso\administrator and password demo!23.

  4. On the SharePoint site, click Site Actions, click Site Setting, and then click People and Groups.

  5. To add the sp_admins group, in the left pane, click Home Owners, click New, and then click Add Users.

  6. On the new screen, type Role#sp_admin in the text box, and then click OK.

  7. Delete the previously added administrator role. Select the Role#DrugTrial1Admins check box. On the Actions menu, click Remove Users from Group, and then click OK in the confirmation dialog box.

  8. To add the sp_visitor, under Groups, click Home Visitors, click New, and then click Add Users.

  9. On the next screen, type Role#sp_visitor in the text box, and then click OK.

  10. Delete the previously added role. Select Role#DrugTrial1Auditors. In the Actions pane, click Remove Users from Group, and then click OK in the confirmation dialog box.

Now, we update the Contoso federation server to also pull role claim values from the SQL database on this computer.

To add a local SQL database as an attribute store for the Contoso federation server

  1. Log on (if you are not still logged on) to the CONTOSOSRV01 computer as CONTOSO\Administrator with "demo!23" as the user password.

  2. Open the AD FS 2.0 Management console (if it is not still open).

    On the Start menu, click All Programs, point to Administrative Tools, and then click AD FS 2.0 Management.

  3. In the console tree, expand Trust Relationships, and then click Attribute Stores.

  4. In the Actions pane, click Add Attribute Store.

  5. Clicking the link opens the Add an Attribute Store dialog box. Type HOL Doctors Role as the display name. For Attribute Store Type, select SQL, type the following connection string, and then click OK to finish. For your convenience, this command is in a text file on the desktop, called DataBase Connect:
    Data Source=CONTOSOSRV01;Initial Catalog=HOL Doctors Role;Integrated Security=True

Now that we have connected to the database, we must update the SharePoint rules in the Contoso federation server regarding where to get role claim values:

To update policy to pull role claim values from the SQL attribute store

  1. In the console tree of the AD FS 2.0 Management console, under AD FS 2.0 and Trust Relationships, click Relying Party Trusts. In the Replying Party Trusts list, click SharePoint Docs Site on Contoso, and then in the Actions pane, click Edit Claim Rules.

  2. The Rules Editor opens. To create a new custom rule, click Add Rule.

  3. In the new window that appears, click Send Claims Using a Custom Rule, and then click Next.

  4. In the first rule, we see which trial the https://docs.contoso.com/ site belongs to. The custom rule is presented here. For the Claim rule name, type Trial Lookup and for Custom rule, type the following, and then click Finish. (For convenience, this role is saved in a file called Custom Rule1 on the desktop. You can copy and paste it from there.)
    => add(store = "HOL Doctors Role", types = ("https://schemas.microsoft.com/ws/2008/06/identity/claims/trial"), query = "select trial from dbo.TS where dbo.TS.SharePointSite = {0}", param = "https://docs.contoso.com/");

  5. Add a second custom rule. In this rule, we use the previously queried trial information with the user’s e-mail address and discover which role the user belongs to. To add another custom rule, click Add Rule, and then select Send Claims Using a Custom Rule, and then click Next. For Claim rule name, type User Role and for Custom rule, type the following presented here. (For convenience, this role is saved in a file called Custom Rule2 on the desktop. You can copy and paste it from there.)
    c1:[Type == "https://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress"] && c2:[Type == "https://schemas.microsoft.com/ws/2008/06/identity/claims/trial"] => add(store = "HOL Doctors Role", types = ("https://schemas.microsoft.com/ws/2008/06/identity/claims/incomingtrialrole"), query = "select role from dbo.URT where dbo.URT.Trial = {1} and dbo.URT.UserName={0}", param = c1.Value, param = c2.Value);

  6. Now we create a third custom rule. In the third rule, we use a previously queried role claim to query the SharePoint role claim and assign the value to the outgoing role claim. To add another custom rule, click Add Rule, select Send Claims Using a Custom Rule, and then click Next. For Claim rule name, type SharePoint Role and for Custom rule, type the following presented here. (For convenience, this role is saved in a file called Custom Rule3 on the desktop. You can copy and paste it from there.)
    c:[Type == "https://schemas.microsoft.com/ws/2008/06/identity/claims/incomingtrialrole"] => issue(store = "HOL Doctors Role", types = ("https://schemas.microsoft.com/ws/2008/06/identity/claims/role"), query = "select dbo.RS.SharePointGroup from dbo.RS where dbo.RS.Role = {0}", param = c.Value);

  7. Click OK to save these new rules and exit the Rules Editor.

Now that the issuance rules are in place to pull claims from the SQL-based attribute store, we can test the new policy by accessing the SharePoint site. First, we access the site from within Contoso.

To verify revisions in access policy to the SharePoint site from within Contoso

  1. Log on to the CONTOSOSRV01 computer as CONTOSO\administrator with "demo!23" as the user password.

  2. Navigate to https://docs.contoso.com. (Make sure that you opened a new browser window and that there were no browser windows already open.)

  3. When you are redirected to the STS login page, you will see sts1.contoso.com in the drop-down menu. Click Continue to Sign In.

  4. On the Username and password logon page, type the following information, and then click Sign In. If you are prompted to save credentials, click No.
    Username: contoso\danielw
    Password: demo!23

  5. When you are logged in to the site, you see that Daniel has full access to the SharePoint site because he belongs to the Admin group in the SQL database. The Admin group maps to the sp_admin group on the SharePoint site with full site access.

Now that you have verified that Daniel from the Contoso domain has write access, try logging in to the SharePoint site from a computer in the Fabrikam domain with Frank’s account.

To verify revisions in access policy to the SharePoint site from within Fabrikam

  1. Log on to the FABRIKAMSRV02 computer as FABRIKAM\frankm with "demo!23" as the user password.

  2. When you are logged in, open Internet Explorer, and navigate to https://docs.contoso.com.

    Because of the Auto Card policy changes that we implemented earlier, your Fabrikam Information Card will be automatically selected and used to sign you in to the Contoso SharePoint site. You will be logged into the site with read-only access. This is because the user FrankM belongs to the Auditors group, that group maps to the sp_visitor group on the SharePoint site, and that group has read-only access to the site.