Bug in CommentBelongsToUser stored procedure ReportServer database

[woptastic1]

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

[woptastic1]

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

[woptastic1]

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 

[v-luwang-msft]

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.

1. 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).

2. 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... 

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

Best Regards

Lucien