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 == ""]
=> issue(
store = "SQL",
types = (""),
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):

@BirthDate nvarchar(10)
RETURNS nvarchar(3)
DECLARE @Age int, @ReturnValue nvarchar(3)

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



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 == ""] 
=> issue(
store = "SQL",
types = (""),
query = "SELECT dbo.IsOver21({0})", param = c.Value);
Have fun!

Posted in: Claims | ADFS | Federation

Tags: ,