topic Re: Bug in CommentBelongsToUser stored procedure ReportServer database in Report Server

Bug in CommentBelongsToUser stored procedure ReportServer database

woptastic1 — Tue, 12 Jul 2022 18:58:52 GMT

Have May 2022 installed using custom authentication, user can create comments but then if they try to delete a comment, error pops up stating An error has occurred. An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. When reviewing RSPortal log file it adds in System.Data.SqlClient.SqlException: Procedure or function 'CommentBelongsToUser' expects parameter '@UserSid', which was not supplied. Since this is custom authentication their is no @UserSid. If I modify stored procedure to set a default of NULL to the @UserSid parameter the issue is resolved, though this will likely get ovewrote on any SQL Server or Report Server patching. Would think this should be addressed

Re: Bug in CommentBelongsToUser stored procedure ReportServer database

v-luwang-msft — Fri, 15 Jul 2022 07:41:18 GMT

Hi woptastic1 ,

Sadly, SqlCommand objects ignore any SqlParameter object in their .Parameters collection with a value of null.In my opinion, this is by design.

There are two solutions to this problem that I found.

In our stored procedures, give a default value to each parameter, so from @Parameter int to @Parameter int = NULL (or some other default value as required).
In our code that generates the individual SqlParameter objects, assigning DBNull.Value instead of null where the intended value is a SQL NULL does the trick.

refer:

https://stackoverflow.com/questions/19703653/stored-procedure-or-function-expects-parameter-which-is-not-supplied

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

Re: Bug in CommentBelongsToUser stored procedure ReportServer database

woptastic1 — Fri, 15 Jul 2022 11:35:42 GMT

Thanks, this is more or less what I ended up doing however this is Microsofts own stored procedure within their ReportServer database, so any changes to their schema have a high likelihood of being overwritten with updates. It's also their own code that is executing, just seems they didn't think through custom authentication all the way

Re: Bug in CommentBelongsToUser stored procedure ReportServer database

woptastic1 — Mon, 18 Jul 2022 11:39:05 GMT

No, since I had already done that prior to posting. Since this is code being fired by Microsoft on deleting a comment from Power BI we can't modify it and since the stored procedure itself can be overwritten on upgrades it's not really a feasible solution