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!