如何将SQL Server获取的角色作为Claims添加至AD FS信赖方信任？

Let's work through your problem step by step to get those legacy SQL roles showing up in your ASP.NET MVC app's claims collection, and fix the parameter passing logic.

First: Fix Your Custom Claim Rule Syntax & Logic #

Your current rule has a few issues that are preventing the SQL roles from being issued to your relying party:

1. Replace add with issue #

The add action only adds claims to ADFS's internal rule engine collection, but doesn't explicitly send them to your relying party. While your catch-all rule (c:[] => issue(claim = c);) should include these added claims, using issue directly in the SQL rule is more reliable and avoids potential order/processing quirks.

2. Remove the Hard-Coded OR Condition #

Your rule includes a hard-coded username check, which could either return the wrong role (if the hard-coded user exists) or no role at all (if you're testing with a different user). Since your goal is to use the user's UPN, this hard-coded clause is unnecessary and problematic.

3. Clean Up the Query & Parameter Mapping #

ADFS automatically handles parameter escaping for SQL queries, so you don't need to wrap {0} in single quotes.

Here's the corrected custom rule:

c:[Type == "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/upn"] => issue(store = "SQLServer", types = ("http://schemas.microsoft.com/ws/2008/06/identity/claims/role"), query = "SELECT r.RoleName FROM dbo.aspnet_Roles r INNER JOIN dbo.aspnet_UsersInRoles uir ON r.RoleId = uir.RoleId INNER JOIN dbo.aspnet_Users u ON uir.UserId = u.UserId WHERE u.UserName = {0}", param = c.Value);

Step-by-Step Troubleshooting Checks #

If the corrected rule still doesn't work, verify these points:

• Confirm SQL Query Returns Results: Run the query directly in SQL Server using the UPN of the user you're testing with. For example:

  SELECT r.RoleName FROM dbo.aspnet_Roles r 
  INNER JOIN dbo.aspnet_UsersInRoles uir ON r.RoleId = uir.RoleId 
  INNER JOIN dbo.aspnet_Users u ON uir.UserId = u.UserId 
  WHERE u.UserName = 'your-test-user@domain.com'
  

  If this returns no rows, ADFS won't have any roles to issue.

• Check ADFS Event Logs: On your ADFS server, open Event Viewer → Applications and Services Logs > AD FS > Admin. Look for warnings/errors related to claim rule execution or SQL attribute store access. Even if the query runs without syntax errors, logs might show issues like empty result sets or parameter mismatches.

• Verify Rule Order: Ensure your SQL claim rule runs before the catch-all issue(claim = c) rule. While order shouldn't break functionality here, running the SQL rule first ensures the roles are included in the full claim set that gets passed to your app.

• Confirm UPN vs. Legacy Username: Double-check that your legacy aspnet_Users.UserName field stores UPNs (matching the AD upn claim). If your legacy system uses samAccountName instead, adjust the rule to target the windowsaccountname claim instead:

  c:[Type == "http://schemas.microsoft.com/ws/2008/06/identity/claims/windowsaccountname"] => issue(store = "SQLServer", types = ("http://schemas.microsoft.com/ws/2008/06/identity/claims/role"), query = "SELECT r.RoleName FROM dbo.aspnet_Roles r INNER JOIN dbo.aspnet_UsersInRoles uir ON r.RoleId = uir.RoleId INNER JOIN dbo.aspnet_Users u ON uir.UserId = u.UserId WHERE u.UserName = {0}", param = c.Value);
  

Parameter Passing Explanation #

Once your rule is corrected, the parameter logic works as intended:

• c:[Type == "upn"] targets the user's UPN claim from AD
• param = c.Value passes the UPN string to the SQL query's {0} placeholder
• ADFS automatically handles escaping the parameter to prevent SQL injection, so you don't need to add single quotes around {0}

After updating the rule, sign out of your app and sign back in (to get a fresh token from ADFS), then check your claims collection again—your SQL roles should now appear.

内容的提问来源于stack exchange，提问作者Tom Regan