CODEWITHSUNDEEP
sqlsql-serverreporting-services
abs786123
abs786123

Reputation: 609

SSRS/ SQL security over client's site

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!

Upvotes: 1

Views: 52

Answers (1)

BIDeveloper
BIDeveloper

Reputation: 2638

For the purposes of this answer I am ignoring any ethical issue with this question.

Short Answer:

There is no way of doing this with SSRS and your restrictions on the client database.

Long Answer:

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:

  • The definition of the report, in order to be able to modify it.
  • The definition of the dataset, in order to modify it or apply it to another report they design.

Your only chance would have been to obfuscate your SQL on the actual SQL Server (WITH ENCRYPTION) however

  • You state you're not allowed to do it
  • The client may be able to either decrypt or using SQL Profiler detect the SQL you are running and therefore capture it for reuse.

Upvotes: 1

Related Questions