Using ADFS 2.0 SQL Attribute Store for “advanced” claims

November 30, 2011 at 11:20 PMHenrik Nilsson

It’s getting late so I’ll just briefly describe this unless you figured this out already…

It started out with MSFTie Ken St. Cyr published a blog post  about a Powershell Attribute Store, a really great idea except he pointed out this could be used for provisioning which is not such a great idea so I made a comment on it. He replied and complained that the ADFS Claims Rule Language lacked more advanced functionality so I just had to show you how the SQL Attribute store can be used for this. Sorry Ken, I just had to make this blog post and I hope you don’t mind me mentioning your great blog and our conversation!?

First of all make sure you have a working connection to a SQL database from ADFS using SQL Attribute store!

Here’s a simple one just to make a claim (Given Name in this case) upper case, other function could be used as well:

c:[Type == "http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname"]
=> issue(
store = "SQL",
types = ("http://schemas.xmlsoap.org/ws/2005/05/identity/claims/givenname"),
query = "SELECT UPPER({0})", param = c.Value
);

And here’s how the famous IsOver21 claim can be created as a scalar valued function in SQL
(far from perfect especially date conversion but it works with Swedish date format like 1979-12-23):

CREATE FUNCTION IsOver21 
(
@BirthDate nvarchar(10)
)
RETURNS nvarchar(3)
BEGIN
DECLARE @Age int, @ReturnValue nvarchar(3)
SET @Age = DATEDIFF(year, CONVERT(DATETIME,@BirthDate,20), GETDATE())

IF @Age >= 21
SET @ReturnValue = 'Yes'
ELSE
SET @ReturnValue = 'No'

RETURN(@ReturnValue)

END

You can then use it like this in ADFS (please use more properly named claim types though) and note how the function needs to be prefixed with dbo:

c:[Type == "http://OddClaims.org/ws/2011/11/identity/claims/birthdate"] 
=> issue(
store = "SQL",
types = ("http://TheCrazyClaimsFactory.com/ws/2011/11/identity/claims/AreYouReallyOver21Punk"),
query = "SELECT dbo.IsOver21({0})", param = c.Value);
 
Have fun!

Posted in: Claims | ADFS | Federation

Tags: ,

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading