Create a string from report parameters, return checksum

Hi,
We want to add an extra checksum parameter to our RS report, and make the
report work only if the correct checksum is entered/passed based on all the
other parameter fields, as a simple security check when rendering reports
from a system with its own security system (users not in AD/domains).
1. How do I construct a stored procedure that creates a string consisting of
all but the last parameter and returns a checksum ? (checksums we know how to
create from strings)
2. How do I filter my report based on that ?
"where ... and @.checksum = checksum_proc.checksum" ?
Report and parameters ex.
report1: product_string, checksum
report2: customer_string, department_string, date_string, checksum
Or is there a better way ?
/JeromeOn Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
wrote:
> Hi,
> We want to add an extra checksum parameter to our RS report, and make the
> report work only if the correct checksum is entered/passed based on all the
> other parameter fields, as a simple security check when rendering reports
> from a system with its own security system (users not in AD/domains).
> 1. How do I construct a stored procedure that creates a string consisting of
> all but the last parameter and returns a checksum ? (checksums we know how to
> create from strings)
> 2. How do I filter my report based on that ?
> "where ... and @.checksum = checksum_proc.checksum" ?
> Report and parameters ex.
> report1: product_string, checksum
> report2: customer_string, department_string, date_string, checksum
> Or is there a better way ?
> /Jerome
Here is another option you might want to consider. You might want to
have a report parameter that has a string datatype and is used as a
password. You might pass the password entered by the user back to the
stored procedure and if the password matches one in a list somewhere
(table, etc) you send the complete dataset back to the report;
otherwise, you send back no data or a single line of all nulls or a
text message of "you do not have correct permissions to access this
report" to the report. Also, to enforce security, you might create the
stored procedure using "with encryption" that way noone can look at
the logic -or- you could add a certain number of characters to the
true password in a table and just remove them when you do the table
lookup for the passwords. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks,
The report must be started with a URL (cant render reports from our system)
and should only be allowed for a certain combinations of parameters set by
our program, ex product 100 with department A. The user must not seconds
later go to the reportserver and manually enter product 100 with department B
with the same password.
If using a "one-time" password parameter, will this mean we should store all
parameters in a table as well ? If we delete the stored password in the
procedure, is it possible for the user to re-render the report to Excel etc
?
A last checksum question: Is there a function to be used in a stored
procedure that gets current report's parameter 1, 2 ... ?
/Jerome k
"EMartinez" wrote:
> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
> wrote:
> > Hi,
> >
> > We want to add an extra checksum parameter to our RS report, and make the
> > report work only if the correct checksum is entered/passed based on all the
> > other parameter fields, as a simple security check when rendering reports
> > from a system with its own security system (users not in AD/domains).
> >
> > 1. How do I construct a stored procedure that creates a string consisting of
> > all but the last parameter and returns a checksum ? (checksums we know how to
> > create from strings)
> >
> > 2. How do I filter my report based on that ?
> > "where ... and @.checksum = checksum_proc.checksum" ?
> >
> > Report and parameters ex.
> > report1: product_string, checksum
> > report2: customer_string, department_string, date_string, checksum
> >
> > Or is there a better way ?
> >
> > /Jerome
>
> Here is another option you might want to consider. You might want to
> have a report parameter that has a string datatype and is used as a
> password. You might pass the password entered by the user back to the
> stored procedure and if the password matches one in a list somewhere
> (table, etc) you send the complete dataset back to the report;
> otherwise, you send back no data or a single line of all nulls or a
> text message of "you do not have correct permissions to access this
> report" to the report. Also, to enforce security, you might create the
> stored procedure using "with encryption" that way noone can look at
> the logic -or- you could add a certain number of characters to the
> true password in a table and just remove them when you do the table
> lookup for the passwords. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||What do you propose to do to reject the user's request if it is invalid by
the rules (see thread: "Am I crazy or is there no form validation" in this
forum). I'm not arguing with you, just wondering what you think is the best
strategy here.
IAC, if I were faced with your requirement, I would probably have the users
submit their reporting URL to a small proxy web application that did the
validation you require against the user's credentials, etc. If the tests
passed (whatever they are) then the proxy would submit the request to the
report server and return the server's response.
By "small" I mean that you could probably get away with a simple APX page
for this, and in addition you would have the ability to return whatever type
of user feedback you wanted if the tests failed.
>L<
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:8A257C82-B978-446C-A2B7-0FFAC3773B32@.microsoft.com...
> Thanks,
> The report must be started with a URL (cant render reports from our
> system)
> and should only be allowed for a certain combinations of parameters set by
> our program, ex product 100 with department A. The user must not seconds
> later go to the reportserver and manually enter product 100 with
> department B
> with the same password.
> If using a "one-time" password parameter, will this mean we should store
> all
> parameters in a table as well ? If we delete the stored password in the
> procedure, is it possible for the user to re-render the report to Excel
> etc
> ?
> A last checksum question: Is there a function to be used in a stored
> procedure that gets current report's parameter 1, 2 ... ?
> /Jerome k
> "EMartinez" wrote:
>> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
>> wrote:
>> > Hi,
>> >
>> > We want to add an extra checksum parameter to our RS report, and make
>> > the
>> > report work only if the correct checksum is entered/passed based on all
>> > the
>> > other parameter fields, as a simple security check when rendering
>> > reports
>> > from a system with its own security system (users not in AD/domains).
>> >
>> > 1. How do I construct a stored procedure that creates a string
>> > consisting of
>> > all but the last parameter and returns a checksum ? (checksums we know
>> > how to
>> > create from strings)
>> >
>> > 2. How do I filter my report based on that ?
>> > "where ... and @.checksum = checksum_proc.checksum" ?
>> >
>> > Report and parameters ex.
>> > report1: product_string, checksum
>> > report2: customer_string, department_string, date_string, checksum
>> >
>> > Or is there a better way ?
>> >
>> > /Jerome
>>
>> Here is another option you might want to consider. You might want to
>> have a report parameter that has a string datatype and is used as a
>> password. You might pass the password entered by the user back to the
>> stored procedure and if the password matches one in a list somewhere
>> (table, etc) you send the complete dataset back to the report;
>> otherwise, you send back no data or a single line of all nulls or a
>> text message of "you do not have correct permissions to access this
>> report" to the report. Also, to enforce security, you might create the
>> stored procedure using "with encryption" that way noone can look at
>> the logic -or- you could add a certain number of characters to the
>> true password in a table and just remove them when you do the table
>> lookup for the passwords. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>