Hello
I am receiving the following error for subscriptions we create:
ReportingServicesService!library!c24!10/22/2004-21:27:04:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, AuthzInitializeContextFromSid: Win32 error: 1355;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
IMPORTANT: These subscriptions were working last week but after applying
ALL the outsanding Windows 2003 patches the problem showed up. Also, it
doesn't matter which delivery extension (including custom) we choose.. we get
the same 1355 error. We can run the reports interactively through report
manager so the issue is definetly subscription related.
We are running the following software:
- Windows 2003 including ALL patches released to date (latest monthly
patches appear to be causing this issue)
- SQL Server 2000 SP3a
- SQL Reporting Services Standard Addition SP1
- Exchange 2000. I only mention this as other posts have suggested this is
Lotus notes issue. In our case it doesn't matter which delivery extension we
choose.
I can get the subscriptions to work using a god like account (ie has domain
admin rights) so the issue appears to center around which user account
actually owns the subscription. If I modify the subscription with any other
account the subscription continues to function properly.
My question is how is the OwnerID found in Subscription table actualy used
becuase this appear to be the key to resolving this issue?
Regards
Kevin WeirA follow-up to my own post:
Here is sample eror log messages containing error:
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:03:: EventPolling
processing item cb787fc0-b06b-4bc0-aa65-386991109995
ReportingServicesService!dbpolling!7dc!10/22/2004-21:27:03:: EventPolling
processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!library!c24!10/22/2004-21:27:03:: Schedule
90015ab7-ec96-4891-b51f-c3ff202da46b executed at 10/22/2004 9:27:03 PM.
ReportingServicesService!schedule!c24!10/22/2004-21:27:03:: Creating Time
based subscription notification for subscription:
bb790a12-151c-4074-a79b-61c158088965
ReportingServicesService!library!c24!10/22/2004-21:27:04:: Schedule
90015ab7-ec96-4891-b51f-c3ff202da46b execution completed at 10/22/2004
9:27:04 PM.
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:04:: EventPolling
finished processing item cb787fc0-b06b-4bc0-aa65-386991109995
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:04::
NotificationPolling processing item a7712007-71d4-44cc-81c0-5a68b9bbdf6a
ReportingServicesService!dbpolling!7dc!10/22/2004-21:27:04::
NotificationPolling processing 1 more items. 1 Total items in internal queue.
ReportingServicesService!library!c24!10/22/2004-21:27:04:: i INFO: Call to
RenderFirst( '/Test' )
ReportingServicesService!library!c24!10/22/2004-21:27:04:: e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files, AuthzInitializeContextFromSid: Win32 error: 1355;
Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
ReportingServicesService!library!c24!10/22/2004-21:27:04:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in Server system
properties.
ReportingServicesService!emailextension!c24!10/22/2004-21:27:04:: Error
sending email. Microsoft.ReportingServices.Diagnostics.Utilities.RSException:
The Report Server has encountered a configuration error; more details in the
log files -->
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The Report Server has encountered a configuration error; more details in the
log files
at
Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntPtr userSid)
at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String
userName)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(String
userName, IntPtr userToken)
at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String
userName, IntPtr userToken, Byte[] secDesc, ReportOperation requiredOperation)
at Microsoft.ReportingServices.Library.Security.CheckAccess(ItemType
catItemType, Byte[] secDesc, ReportOperation rptOper)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext
reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32&
executionOption, String& savedParametersXml, ReportSnapshot&
compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime&
historyDate)
at
Microsoft.ReportingServices.Library.RSService._GetReportParameters(String
report, String historyID, Boolean forRendering, NameValueCollection values,
DatasourceCredentialsCollection credentials)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobTypeEnum
type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters,
String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.ReportImpl.Render(String
renderFormat, String deviceInfo)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage message, Notification notification, SubscriptionData data)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification)
at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
ReportingServicesService!notification!c24!10/22/2004-21:27:04:: Notification
a7712007-71d4-44cc-81c0-5a68b9bbdf6a completed. Success: False, Status:
Failure sending mail: The Report Server has encountered a configuration
error; more details in the log files, DeliveryExtension: Report Server Email,
Report: Test, Attempt 0
ReportingServicesService!dbpolling!c24!10/22/2004-21:27:04::
NotificationPolling finished processing item
a7712007-71d4-44cc-81c0-5a68b9bbdf6a
ReportingServicesService!library!c24!10/22/2004-21:27:47:: i INFO: Cleaned 0
batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0
chunks, 0 running jobs|||If anyone else reads this and has same problem I applied Windows 2003 hotfix
834859 which resolved the problem
http://support.microsoft.com/default.aspx?scid=kb;en-us;834859
"Kevin Weir" wrote:
> Hello
> I am receiving the following error for subscriptions we create:
> ReportingServicesService!library!c24!10/22/2004-21:27:04:: e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in the
> log files, AuthzInitializeContextFromSid: Win32 error: 1355;
> Info:
> Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
> The Report Server has encountered a configuration error; more details in the
> log files
> IMPORTANT: These subscriptions were working last week but after applying
> ALL the outsanding Windows 2003 patches the problem showed up. Also, it
> doesn't matter which delivery extension (including custom) we choose.. we get
> the same 1355 error. We can run the reports interactively through report
> manager so the issue is definetly subscription related.
> We are running the following software:
> - Windows 2003 including ALL patches released to date (latest monthly
> patches appear to be causing this issue)
> - SQL Server 2000 SP3a
> - SQL Reporting Services Standard Addition SP1
> - Exchange 2000. I only mention this as other posts have suggested this is
> Lotus notes issue. In our case it doesn't matter which delivery extension we
> choose.
> I can get the subscriptions to work using a god like account (ie has domain
> admin rights) so the issue appears to center around which user account
> actually owns the subscription. If I modify the subscription with any other
> account the subscription continues to function properly.
> My question is how is the OwnerID found in Subscription table actualy used
> becuase this appear to be the key to resolving this issue?
> Regards
> Kevin Weir
>
Showing posts with label subscriptions. Show all posts
Showing posts with label subscriptions. Show all posts
Tuesday, March 20, 2012
BUG: Reporting Services Custom Security and Subscriptions?
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
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> 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)
> |
> |
> |
>|||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
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > 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
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
> 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
> |
> | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> | > 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)
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>|||Hello James,
Please rest assured that your feedback on this issue is routed to the
proper channel. Again, thank you for taking time to report this.
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>
<uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
<r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Tue, 11 Oct 2005 15:54:54 +0100
| Lines: 219
| 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: <upkv$OnzFHA.1264@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: exony-ltd-02.altohiway.com 213.83.66.226
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp1
3.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:61002
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| 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
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
| > 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
| > |
| > | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > | > 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)
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|
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
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> 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)
> |
> |
> |
>|||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
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > 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
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
> 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
> |
> | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
> | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
> | > 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)
> | > |
> | > |
> | > |
> | >
> |
> |
> |
>|||Hello James,
Please rest assured that your feedback on this issue is routed to the
proper channel. Again, thank you for taking time to report this.
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>
<uBkwSGzyFHA.2644@.TK2MSFTNGP09.phx.gbl>
<r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl>
| Subject: Re: BUG: Reporting Services Custom Security and Subscriptions?
| Date: Tue, 11 Oct 2005 15:54:54 +0100
| Lines: 219
| 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: <upkv$OnzFHA.1264@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: exony-ltd-02.altohiway.com 213.83.66.226
| Path:
TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp1
3.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.reportingsvcs:61002
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| 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
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:r7qTJWizFHA.3472@.TK2MSFTNGXA02.phx.gbl...
| > 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
| > |
| > | "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| > | news:te%23MlbyyFHA.768@.TK2MSFTNGXA01.phx.gbl...
| > | > 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)
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|
Sunday, February 19, 2012
Broken Subscriptions Due to Empty Table
Hi,
I was wondering what the best way was to deal with subscriptions breaking due to an empty table. I have subscriptions that people have scheduled to go out daily, but on certain days the table may be empty, in this case the subscription doesn't read the parameters for the report and then the subscription breaks.
My original solution involved creating a #temp table with the same columns as the original table and inserting one row into it which I'd union with the original table, this row in the temp table had all its values set to 0. The solution worked when I ran it in SQL Server Management Studio but it seems SRS doesn't like the INSERT INTO statement, which is the error I get, but I've read on these forums that it doesn't like #temp tables either. I proceeded to use a stored procedure with all the code in it, but I might have trouble filtering on multi value parameters, because at times these parameter lists get real big, plus I have to do this for multiple reports and don't want to get into creating stored procedures for each report.
Following is what the code I used look something like that executes and does the job in Management Studio but not SRS. I'm mainly just looking for the easiest and cleanest way to do this, since it'll have to be done across multiple reports, so disregard the code if there's an easier way to do it. Thanks in advance.
create table #dummytable
(
name varchar(35),
country varchar(35),
idnumber (int)
)
GO
insert into #dummytable (name),values('0');
select name, country, idnumber
from originaltable
where name in (@.name)
union
select name = 0, country = 0, idnumber = 0
from #dummytable
drop #dummytable
Another solution might be to do a union with a single known row that will always be delivered.
If the delivery query returns 0 rows, what would you want the subscription to do?
Thanks,
-Lukasz
|||>> SRS doesn't like the INSERT
Instead of creating a temp table and INSERTing, why not just do a SELECT of literals?
Code Snippet
SELECT Name, Country, idnumber FROM
OriginalTable WHERE Name IN (@.Name)
UNION
SELECT SPACE(0),SPACE(0), 0
>L<
|||Thank you Lisa, that does get the job done, much appreciated.
Broken hyperlinks in subscription emails
Users are receiving link-only subscriptions with broken links where the break
always starts with an exclamation mark. It looks like this in email source
view:
<table><tr><td><span>My comment text added to
subscription</span></td></tr></table><table><tr><td><span>The report is
accessible at the following address:</span></td><tr><td><a
href="http://links.10026.com/?link=http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&r!
s%3aParameterLanguage=en-US">http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&rs%3aParameterLanguage=en-US</a></td></tr></table>
Any suggestions how to fix or work around this?I have updated Reporting Services to version 9.00.2040.00 but I am still
getting broken hyperlinks in subscription emails.
"PeterG" wrote:
> Users are receiving link-only subscriptions with broken links where the break
> always starts with an exclamation mark. It looks like this in email source
> view:
> <table><tr><td><span>My comment text added to
> subscription</span></td></tr></table><table><tr><td><span>The report is
> accessible at the following address:</span></td><tr><td><a
> href="http://links.10026.com/?link=http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&r!
> s%3aParameterLanguage=en-US">http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&rs%3aParameterLanguage=en-US</a></td></tr></table>
> Any suggestions how to fix or work around this?
>
always starts with an exclamation mark. It looks like this in email source
view:
<table><tr><td><span>My comment text added to
subscription</span></td></tr></table><table><tr><td><span>The report is
accessible at the following address:</span></td><tr><td><a
href="http://links.10026.com/?link=http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&r!
s%3aParameterLanguage=en-US">http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&rs%3aParameterLanguage=en-US</a></td></tr></table>
Any suggestions how to fix or work around this?I have updated Reporting Services to version 9.00.2040.00 but I am still
getting broken hyperlinks in subscription emails.
"PeterG" wrote:
> Users are receiving link-only subscriptions with broken links where the break
> always starts with an exclamation mark. It looks like this in email source
> view:
> <table><tr><td><span>My comment text added to
> subscription</span></td></tr></table><table><tr><td><span>The report is
> accessible at the following address:</span></td><tr><td><a
> href="http://links.10026.com/?link=http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&r!
> s%3aParameterLanguage=en-US">http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&rs%3aParameterLanguage=en-US</a></td></tr></table>
> Any suggestions how to fix or work around this?
>
Subscribe to:
Posts (Atom)