KNF:ADFS-SQL

SQL QUERY attributen aanmaken

Andere attributen die niet vanuit LDAP beschikbaar zijn, kunnen ook via een ‘SQL attribute’ store gegenereerd worden. Hiervoor wordt een extra ‘Attribute Store’ voor SQL type aangemaakt. Zie bijvoorbeeld: SQL Attribute Stores

• ILM_staging
  • Deze gaat via de connectie string: Server=WVFIMP01;Database=ILM_staging;Integrated Security = True

Het domein account dat de ADFS service draait (Zie onder service AD FS 2.0 Windows Service naar de Log On As) moet als login aangemaakt worden op de database server met leesrecht op de database (in dit geval op Drenthe College de ILM_staging database op de server WVFIMP01).

In de tabel dbo.studenten zijn uiteraard de volgende velden aanwezig:

ObjectId, lokatie, klas, cohort, crebo, opleidingsnaam, geboortedatum, (en nog vele andere).

Deze worden elk uur bij ons up-to-date gehouden vanuit de deelnemers administratie.

De attributen worden dan als volgt opgebouwd

• Open de ‘relying Party Trust’ en selecteer de trust waarvoor je de attributen wilt aanpassen.
• Kies voor ‘Edit Claim Rules’ en selecteer ‘Send Claims Using a Custom Rule’

nlEduPersonDepartment

In het venster voor de ‘Custom rule’ wordt het volgende ingevuld:

c:[Type == "DC_SamId"]
  => issue(store = "ILM_staging",
    types = ("nlEduPersonDepartment"),
    query = "SELECT lokatie as nlEduPersonDepartment
            from dbo.studenten  where ObjectID = {0}",
    param = C.Value);

PS1: De regels moeten als een regel (‘oneliner’) zonder regeleinde ingevoerd worden in het veld Custom rule.

PS2: as <attributename> kan weggelaten worden in de query's. Door de types = (“<attribute>”), wordt bepaald aan welk attribuut de gevonden waarde(n) gekoppeld moet worden.

Bij het op [ OK ] klikken wordt op de achtergrond de syntax gecontroleerd.

Direct hierna kan via de Referentie Service Provider (in dit geval staging) gecontroleerd worden of het correct is.

De URL hiervoor is: https://referentie-s.entree.kennisnet.nl/referentiesp

Als het goed gaat komt het standaard overzicht waarbij het attribuut netjes aanwezig is.

Als de query niets oplevert, wordt het attribuut ook niet getoond in het overzicht.

Als er iets fout gaat, bijvoorbeeld er wordt een date of numeriek gegeven teruggestuurd, komt er een foutmelding 'Authenticatie Mislukt' met o.a. gebruikersnaam of wachtwoord verkeerd. (Kennisnet gaat er bij de meeste attributen vanuit dat het een string is)

Het is mij nog niet gelukt een logfile te vinden of in te stellen waarbij ik iets meer kan terugvinden. Het blijft dan ook gissen naar de oorzaak.

Als de syntax van de claim rule verkeerd is komt daar wel een foutmelding van, bij het opslaan van de claim rule.

Hierbij is de “ vergeten in …ObjectID = {0}", .

nlEduPersonUnit

c:[Type == "DC_SamId"]
  => issue(store = "ILM_staging",
     types = ("nlEduPersonUnit"),
     query = "SELECT klas as nlEduPersonUnit from dbo.studenten
             where ObjectID = {0}",
     param = C.Value);

nlEduPersonCohort

c:[Type == "DC_SamId"]
  => issue(store = "ILM_staging", 
     types = ("nlEduPersonCohort"),
     query = "SELECT COALESCE('20'+ COHORT,'2014') as nlEduPersonCohort from dbo.studenten
             where ObjectID = {0}", 
     param = C.Value);

nlEduPersonProfile

c:[Type == "DC_SamId"]
  => issue(store = "ILM_staging",
     types = ("nlEduPersonProfile"),
     query = "SELECT crebo + ' ' + Opleiding_naam as nlEduPersonProfile from dbo.studenten  
             where ObjectID = {0}",
     param = C.Value);

nlEduPersonBirthDate

c:[Type == "DC_SamId"]
  => issue(store = "ILM_staging",
     types = ("nlEduPersonBirthDate"),
     query = "SELECT geboortedatum as nlEduPersonBirthDate from dbo.studenten
             where ObjectID = {0}", 
     param = C.Value);

Geboortedatum

Kennisnet wil ‘JJJJMMDD’ ontvangen (Zie Overzicht van Federatieve attributen voor Identity Providers).

Geboortedatum is namelijk een string geworden in onze databaseview met opmaak ‘DD-MM-JJJJ’.

Dus de select moet worden: "SELECT CONVERT(NVARCHAR(10), cast(geboortedatum as date), 112)…

Auteur: Bert Barskè| Drenthe College