I was wondering if anyone can help. I have started a new job for a firm where they are looking to create BI reports for clients. The clients have the server on their site. We have access to only query the data, but not allowed to create tables, stored procedure etc.However we are allowed to create temporary tables.
I am looking to create an SSRS report for the client but at the same time want hide the SQL from them to prevent them knowing how the reports are built.
Is there a way to create a self service bespoken report which I can deploy for them but keep the SQL hidden from them, despite them having the server on their site and them having admin access to the sql server box and SSRS reporting server?
Any advice on best handling this tough situation will be greatly appreciated!
For the purposes of this answer I am ignoring any ethical issue with this question.
There is no way of doing this with SSRS and your restrictions on the client database.
SSRS reports are stored as a
.rdl file on your clients reporting server. This is basically easily readable XML which will allow anyone with an interest to view the report definition.
If the report definition also contains the dataset SQL (
SELECT secret_sql FROM table) then the client would have:
Your only chance would have been to obfuscate your SQL on the actual SQL Server (WITH ENCRYPTION) however