Vaughan Harper's blog IBM Security Privilege Vault, IBM Security Secret Server, Thycotic Secret Server Secret Server – Custom reports (and the annoying ‘SQL that was entered is not valid for reporting’ error)
Secret Server – Custom reports (and the annoying ‘SQL that was entered is not valid for reporting’ error)

IBM Security Privilege Vault IBM Security Secret Server Thycotic Secret Server

Secret Server – Custom reports (and the annoying ‘SQL that was entered is not valid for reporting’ error)

Secret Server is a brilliant Privileged Access Management (PAM) product. I have being doing some work recently on its custom reports – it’s great that you have the flexibility to create your own custom reports without needing to resort to Professional Services, so I thought would be worth sharing some of my observations on the subject:

  1. As the documentation says, a good starting point for creating custom reports is to look at the SQL scripts behind the ‘out of the box’ reports and then modify them to meet your own specific requirements.

2. To investigate the data available for reporting, if you are running the ‘on premise’ Secret Server and have access to the database the best thing to do is to use SQL Server Management Studio to view the tables and the data stored within them:

Using SQL Server Management Studio

Otherwise you can go into the report editor and see what tables exist and see what columns exist within them:

Viewing tables and columns in the Secret Server Report Editor

3. It is also useful to know that if you have access to the database you can test running queries within the SQL Server Management Studio:

Running queries within the SQL Server Management Studio

4. If there is a problem with the SQL query you sometimes get a meaningful error message from the SQL engine (such as The following SQL Exception occurred… Incorrect syntax near the keyword 'SELECT'). But Secret Server seems to first run a pre-processor to perform its own validation of the query, and if that fails it just returns a bland response The SQL that was entered is not valid for reporting:

'SQL that was entered is not valid for reporting' error

I have even had the unhelpful not valid for reporting error message when I copied the SQL from the standard ‘Secret Activity’ report into a new report. Why? Surely this SQL should be valid! The problem appears to be the AS [User] statement (on line 3). If you change it to AS [Username] then it works fine. It seems that the column header name needs to be anything other than Use or User.

One other gotcha: make sure that you don’t have a ; anywhere in the query.

5. You can’t create temporary tables and the reports need to be constructed from a single SQL query (which means that you may end up using multiple subqueries, etc). I’m sure that this is heaven for the DBAs but it can be challenging for us lesser mortals..!

Vaughan

Written by Vaughan

2 thoughts on “Secret Server – Custom reports (and the annoying ‘SQL that was entered is not valid for reporting’ error)

  1. I just wanted to leave a comment saying thanks! I had this exact issue and couldn’t figure out why, even if I used the same query from a pre-built report. Your suggestion did the trick!

Leave a Reply

Your email address will not be published. Required fields are marked *