1

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!

abs786123
  • 581
  • 2
  • 11
  • 24

1 Answers1

1

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.
Community
  • 1
  • 1
BIDeveloper
  • 2,628
  • 4
  • 36
  • 51
  • Thanks BIDeveloper, I will have a word with my boss and see if we could at least create a stored procedure if possible as this is the only way possible and to use the with encryption. Is it very difficult to crack the encryption or view the content of the query using SQL Profiler? – abs786123 Jul 20 '16 at 11:55
  • It's not going to be difficult for someone who knows what they are doing - so, whether it's worth it or not is up to you. If you're happy, can you mark the answer as accepted. Thanks. – BIDeveloper Jul 20 '16 at 11:57