locked
Query SQL Attribute Store - Not Working RRS feed

  • Question

  • Hello,

    I need to issue a claim from an  attribute store.

    It is almost working.

    My claim rule is

    c:[Type == "http://schemas.xmlsoap.org/claims/CommonName"]
     => issue(store = "SQLEXPRESS", types = ("http://contoso.com/edupers"), query = "SELECT EDUPERSONSCOPEDAFFILIATION FROM [ADFSATTRIBUTE].[dbo].[BZ_EDUPERSONSCOPEDAFFILIATION] WHERE [USER_NAME] = {0}", param = c.Value);

    I use ClaimsXray, but the claim isn't being emitted.

    Using XEvent Profiler, I see that the query that SQL Server Express recieves is...

    exec sp_executesql N'SELECT EDUPERSONSCOPEDAFFILIATION FROM [ADFSATTRIBUTE].[dbo].[BZ_EDUPERSONSCOPEDAFFILIATION] WHERE [USER_NAME] = @PARAMETER0',N'@PARAMETER0 nvarchar(10)',@PARAMETER0=N'FFLINTSTONE'


    If I run this query directly in SQL Server Management Studio, I get 0 rows.

    Now, if modify this query to 'deparameterize' it, it works;

    SELECT EDUPERSONSCOPEDAFFILIATION FROM [ADFSATTRIBUTE].[dbo].[BZ_EDUPERSONSCOPEDAFFILIATION] WHERE [USER_NAME] = 'FFLINTSTONE'

    I get the column and row back that I was expecting.

    I've followed one of the best guides out there for this activity;

    https://journeyofthegeek.com/2017/05/28/active-directory-federation-services-sql-attribute-store/

    My query and his query looks more-or-less the same.

    My attribute store is SQL Server Express 2017 (14.1805.4072.1), query is interrogating a view ([ADFSATTRIBUTE].[dbo].[BZ_EDUPERSONSCOPEDAFFILIATION]), the view is for a linked table, the linked table is Oracle.

    How can I fix this?

    Technically, SQL Server 2017 [Express] isn't in the list of supported SQL attribute stores, but I can't believe that's the problem.  Is it?
    • Edited by Anwar Mahmood Tuesday, March 26, 2019 10:59 AM added sql version
    Tuesday, March 26, 2019 10:26 AM

All replies

  • If I create a table in the database, add a row for 'FFlintstone', then update the ClaimsXray Issuance Transofrm Rules, it works; I get the expected claim back in ClaimsXray.

    c:[Type == "http://schemas.xmlsoap.org/claims/CommonName"]
     => issue(store = "SQLEXPRESS", types = ("http://contoso.com/edupers"), query = "SELECT [EDUPERSONSCOPEDAFFILIATION] FROM [ADFSATTRIBUTE].[dbo].[MANUALTABLE] WHERE [USER_NAME] = {0}", param = c.Value);

    XEvent Profiler reveals  a practically identical statement;

    exec sp_executesql N'SELECT [EDUPERSONSCOPEDAFFILIATION] FROM [ADFSATTRIBUTE].[dbo].[MANUALTABLE] WHERE [USER_NAME] = @PARAMETER0',N'@PARAMETER0 nvarchar(10)',@PARAMETER0=N'FFLINTSTONE'

    Weird!

    Tuesday, March 26, 2019 12:52 PM
  • If I remove the parameter from the issuance transform rule, and use a hard-coded value, it works;

    c:[Type == "http://schemas.xmlsoap.org/claims/CommonName"]
     => issue(store = "SQLEXPRESS", types = ("http://contoso.com/edupers"), query = "SELECT [EDUPERSONSCOPEDAFFILIATION] FROM [ADFSATTRIBUTE].[dbo].[BZ_EDUPERSONSCOPEDAFFILIATION] WHERE [USER_NAME] = 'FFLINTSTONE'");

    Hence, I can query a view based on a linked server, I just can't pass a parameter to it.

    Tuesday, March 26, 2019 2:21 PM
  • I am confused. Which one doesn't work now?


    Note: Posts are provided “AS IS” without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Sunday, March 31, 2019 11:00 PM
  • Using an issuance transform rule that queries an SQL Server, where the query is for a linked table to an Oracle database, and the query uses parameters.

    • querying a SQL Server table is OK
    • querying a linked [Oracle] table without parameters is OK
    • querying a linked [Oracle] table with parameters in the issuance transform rule doesn't work

    Monday, April 1, 2019 8:15 AM