Basically we have implemented custom security so that our logins to the
Report Server are using details in a custom database. They are not Windows
usernames/passwords. However when we use the web service api
(ListSubscriptions) to return user's subscriptions we get nothing? After
digging further with Reflector I found the following code in an RS assembly:

public ArrayList ListSubscriptions(string user, string report)
{
ArrayList list1 = new ArrayList();
string text1 = "select
S.[SubscriptionID],
S.[Report_OID],
S.[Locale],
S.[InactiveFlags],
S.[DeliveryExtension],
S.[ExtensionSettings],
SUSER_SNAME(Modified.[Sid]),
Modified.[UserName],
S.[ModifiedDate],
S.[Description],
S.[LastStatus],
S.[EventType],
S.[MatchData],
S.[Parameters],
S.[DataSettings],
A.[TotalNotifications],
A.[TotalSuccesses],
A.[TotalFailures],
SUSER_SNAME(Owner.[Sid]),
Owner.[UserName],
CAT.[Path],
S.[LastRunTime],
CAT.[Type],
SD.NtSecDescPrimary
from
[Subscriptions] S inner join [Catalog] CAT on S.[Report_OID] =
CAT.[ItemID]
inner join [Users] Owner on S.OwnerID = Owner.UserID
inner join [Users] Modified on S.ModifiedByID = Modified.UserID
left outer join [SecData] SD on CAT.[PolicyID] = SD.[PolicyID]
AND SD.AuthType = @AuthType
left outer join [ActiveSubscriptions] A with (NOLOCK) on
S.[SubscriptionID] = A.[SubscriptionID]";

InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
CommandType.Text, base.Connection, base.Transaction,
base.SqlCommandTimeout);
command1.Parameters.Add("@AuthType",
WebConfigUtil.AuthenticationType);
if ((user != null) && (user != ""))
{
this.AddClause(ref text1, ref flag1, "Owner.[Sid] = @UserSid");
command1.Parameters.Add("@UserSid", Native.NameToSid(user));
}

<removed code>
}

Now the interesting bit is:

this.AddClause(ref text1, ref flag1, "Owner.[Sid] = @UserSid");
command1.Parameters.Add("@UserSid", Native.NameToSid(user));


Why are they trying to lookup a SID for my custom username? Of course this
fails, which results in a WHERE clause that fails to grabs our
subscriptions.

Has anyone else found this? Am I completely wrong?

BTW: I'm using Reporting Services SP 2.

James Snape (for Ryan Stevens)

Hello James,

Based on my scope, if a report server user is not mapped to a Windows user
sid, it shall be NULL. I think this shall work properly in SQL query.

select * from users where sid=NULL

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "James Snape" <jim_snape.at.hotmail.com@online.nospam>
| Subject: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Fri, 7 Oct 2005 09:25:13 +0100
| Lines: 75
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| Message-ID: <OH8poixyFHA.2540@TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net 213.92.131.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53918
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Basically we have implemented custom security so that our logins to the
| Report Server are using details in a custom database. They are not
Windows
| usernames/passwords. However when we use the web service api
| (ListSubscriptions) to return user's subscriptions we get nothing? After
| digging further with Reflector I found the following code in an RS
assembly:
|
| public ArrayList ListSubscriptions(string user, string report)
| {
| ArrayList list1 = new ArrayList();
| string text1 = "select
| S.[SubscriptionID],
| S.[Report_OID],
| S.[Locale],
| S.[InactiveFlags],
| S.[DeliveryExtension],
| S.[ExtensionSettings],
| SUSER_SNAME(Modified.[Sid]),
| Modified.[UserName],
| S.[ModifiedDate],
| S.[Description],
| S.[LastStatus],
| S.[EventType],
| S.[MatchData],
| S.[Parameters],
| S.[DataSettings],
| A.[TotalNotifications],
| A.[TotalSuccesses],
| A.[TotalFailures],
| SUSER_SNAME(Owner.[Sid]),
| Owner.[UserName],
| CAT.[Path],
| S.[LastRunTime],
| CAT.[Type],
| SD.NtSecDescPrimary
| from
| [Subscriptions] S inner join [Catalog] CAT on S.[Report_OID]
=
| CAT.[ItemID]
| inner join [Users] Owner on S.OwnerID = Owner.UserID
| inner join [Users] Modified on S.ModifiedByID =
Modified.UserID
| left outer join [SecData] SD on CAT.[PolicyID] =
SD.[PolicyID]
| AND SD.AuthType = @AuthType
| left outer join [ActiveSubscriptions] A with (NOLOCK) on
| S.[SubscriptionID] = A.[SubscriptionID]";
|
| InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
| CommandType.Text, base.Connection, base.Transaction,
| base.SqlCommandTimeout);
| command1.Parameters.Add("@AuthType",
| WebConfigUtil.AuthenticationType);
| if ((user != null) && (user != ""))
| {
| this.AddClause(ref text1, ref flag1, "Owner.[Sid] =
@UserSid");
| command1.Parameters.Add("@UserSid", Native.NameToSid(user));
| }
|
| <removed code>
| }
|
| Now the interesting bit is:
|
| this.AddClause(ref text1, ref flag1, "Owner.[Sid] =
@UserSid");
| command1.Parameters.Add("@UserSid", Native.NameToSid(user));
|
|
| Why are they trying to lookup a SID for my custom username? Of course
this
| fails, which results in a WHERE clause that fails to grabs our
| subscriptions.
|
| Has anyone else found this? Am I completely wrong?
|
| BTW: I'm using Reporting Services SP 2.
|
| James Snape (for Ryan Stevens)
|
|
|

Hi Peter,

A comparison against NULL is always false so your query below never returns
any results.

Regards,
James Snape

[quoted text, click to view]

Hello James,

If ANSI_NULLS is off, it shall return all users with NULL SID. I have
reported this issue to the proper channel but there is no feedback yet. If
we have any update on this, we will let you know.

Also, since the issue relates to source code evaluation, I recommend that
you open a Support incident with Microsoft Product Support Services so that
a dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.

For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================



This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------
| From: "James Snape" <jim_snape.at.hotmail.com@online.nospam>
| References: <OH8poixyFHA.2540@TK2MSFTNGP09.phx.gbl>
<te#MlbyyFHA.768@TK2MSFTNGXA01.phx.gbl>
| Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Fri, 7 Oct 2005 12:23:31 +0100
| Lines: 140
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| Message-ID: <uBkwSGzyFHA.2644@TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net 213.92.131.1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53926
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi Peter,
|
| A comparison against NULL is always false so your query below never
returns
| any results.
|
| Regards,
| James Snape
|
[quoted text, click to view]
| > Hello James,
| >
| > Based on my scope, if a report server user is not mapped to a Windows
user
| > sid, it shall be NULL. I think this shall work properly in SQL query.
| >
| > select * from users where sid=NULL
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > =====================================================
| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| > --------------------
| > | From: "James Snape" <jim_snape.at.hotmail.com@online.nospam>
| > | Subject: BUG: Reporting Services Custom Security and Subscriptions?
| > | Date: Fri, 7 Oct 2005 09:25:13 +0100
| > | Lines: 75
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2670
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2670
| > | X-RFC2646: Format=Flowed; Original
| > | Message-ID: <OH8poixyFHA.2540@TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.reportingsvcs
| > | NNTP-Posting-Host: ip-213-92-131-1.aramiska-arc.aramiska.net
| > 213.92.131.1
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl
| > microsoft.public.sqlserver.reportingsvcs:53918
| > | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
| > |
| > | Basically we have implemented custom security so that our logins to
the
| > | Report Server are using details in a custom database. They are not
| > Windows
| > | usernames/passwords. However when we use the web service api
| > | (ListSubscriptions) to return user's subscriptions we get nothing?
After
| > | digging further with Reflector I found the following code in an RS
| > assembly:
| > |
| > | public ArrayList ListSubscriptions(string user, string report)
| > | {
| > | ArrayList list1 = new ArrayList();
| > | string text1 = "select
| > | S.[SubscriptionID],
| > | S.[Report_OID],
| > | S.[Locale],
| > | S.[InactiveFlags],
| > | S.[DeliveryExtension],
| > | S.[ExtensionSettings],
| > | SUSER_SNAME(Modified.[Sid]),
| > | Modified.[UserName],
| > | S.[ModifiedDate],
| > | S.[Description],
| > | S.[LastStatus],
| > | S.[EventType],
| > | S.[MatchData],
| > | S.[Parameters],
| > | S.[DataSettings],
| > | A.[TotalNotifications],
| > | A.[TotalSuccesses],
| > | A.[TotalFailures],
| > | SUSER_SNAME(Owner.[Sid]),
| > | Owner.[UserName],
| > | CAT.[Path],
| > | S.[LastRunTime],
| > | CAT.[Type],
| > | SD.NtSecDescPrimary
| > | from
| > | [Subscriptions] S inner join [Catalog] CAT on
| > S.[Report_OID]
| > =
| > | CAT.[ItemID]
| > | inner join [Users] Owner on S.OwnerID = Owner.UserID
| > | inner join [Users] Modified on S.ModifiedByID =
| > Modified.UserID
| > | left outer join [SecData] SD on CAT.[PolicyID] =
| > SD.[PolicyID]
| > | AND SD.AuthType = @AuthType
| > | left outer join [ActiveSubscriptions] A with (NOLOCK) on
| > | S.[SubscriptionID] = A.[SubscriptionID]";
| > |
| > | InstrumentedSqlCommand command1 = Storage.NewSqlCommand(text1,
| > | CommandType.Text, base.Connection, base.Transaction,
| > | base.SqlCommandTimeout);
| > | command1.Parameters.Add("@AuthType",
| > | WebConfigUtil.AuthenticationType);
| > | if ((user != null) && (user != ""))
| > | {
| > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =
| > @UserSid");
| > | command1.Parameters.Add("@UserSid",
Native.NameToSid(user));
| > | }
| > |
| > | <removed code>
| > | }
| > |
| > | Now the interesting bit is:
| > |
| > | this.AddClause(ref text1, ref flag1, "Owner.[Sid] =
| > @UserSid");
| > | command1.Parameters.Add("@UserSid",
Native.NameToSid(user));
| > |
| > |
| > | Why are they trying to lookup a SID for my custom username? Of course
| > this
| > | fails, which results in a WHERE clause that fails to grabs our
| > | subscriptions.
| > |
| > | Has anyone else found this? Am I completely wrong?
| > |
| > | BTW: I'm using Reporting Services SP 2.
| > |
| > | James Snape (for Ryan Stevens)
| > |
| > |
| > |
| >
|
|
|

It's OK Peter, this issue is not troubling us because the workaround we are
using is to list all subscriptions and filter in our app server. Not great
but it works. I just thought you would like to know of the bug for inclusion
in your next SP.

Regards,
James Snape

[quoted text, click to view]