Error "There was an unexpected error while getting the report parameters" and allowing Non-Domain Admin users to run reports (SL3158)

A user that is not a Domain Admin, but who does have Super User rights to Desktop Authority attempt to run reports and receives the following error:

Title: Unexpected error

Body: There was an unexpected error while getting the report parameters

Error: Login failed for user “Domain\User”

This error could occur if the firewall is enabled on the server where the SQL instance resides. Either disable the firewall or add a program or port exception to allow communications through the firewall.

A secondary error message may appear after clicking OK on the above message:

Unexpected error. There was a problem running this report. It is possible that the server did not have enough memory to run this report, the report timed out, or the server was unavailable. It is also possible that there was an error in the data or there was a problem with a hard-coded data comparison in the SQL statement cursor. Try running the report with a tighter parameter (i.e. fewer users, fewer workstations, smaller time spans, etc.) or check the report’s SQL statement.

Error message: object reference not set to an instance of an object.

This error occurs as a result of the user trying to run the report without possessing the requisite permissions to the database.

In past versions, when a user attempted to run a report, the connection to the database was made through the Desktop Authority OpsMaster Service. Since the OpsMaster Service account is a member of the Domain Admins group, it has rights to the Databases. In version 7.8.1 and newer, the report is run in the context of the logged on user instead of utilizing OpsMaster credentials.

For the example below an Active Directory group will be used. You can follow the same steps if you want to add a user.

Open Microsoft SQL Server Management Studio, expand Security, right click on Logins and chose New Login:

In the Login à New Login dialog, click on search. In the Select User or Group window click on object type and make sure the Groups box is checked and click OK.

In the Select User or Group dialog, click on Locations and chose Entire Directory then click OK.

Enter the object name, click on Check Names, and then click OK.

Do not change the Default database and Default language.

Under Logins you should now see the new login that you just created.

The following steps will be taken on the following Databases:

DAConfiguration

DAReporting

Tempdb (found under System Databases)

Expand the database, expand Security, right click on Users and click on New User.

Enter in a Username and then browse out to the login name that you created above.

Under Database role membership, chose db_owner and click OK.