Friday, July 25, 2008

Installing SQL Reporting Services uses wrong credentials

For a project I'm leading we were installing a SQL Reporting Services server, no simple feed I found out ...

Everything went fine, until I had to enter the credentials to connect to the SQL instance I had created on a different SQL server.

Little inside info: the reporting server I'm trying to configure is located in our DMZ zone and will be accessed by our external client.
First, the domain in the DMZ is different than the domain we use internally, so both servers were located in different domain.
Secondly, because the client connects to the reporting and thus also to the SQL server, we have removed the SQL permissions of the server administrator and created a specific SQL account for the client which has sysadmin & dbowner rights. That should be sufficient.

Now, coming to the credential step in the reporting wizard, I give up the account & password, but I receive an error message stating that "Login failed for user 'NT AUTHORITY\ADMINISTRATOR'".

Appearently this happens when you want to install SQL Reporting Services on a different server than the SQL server, which is recommended by the way ... (IIS should not be installed on a database server for performance reasons).
By default the credentials of the person logged into the server is what is used for installing the databases, only later is the account you specify in the installation wizard used for running Reporting Services ... GO FIGURE!!

Now, there are 2 things you can do (I had to use both in combination with eachother)
1. Locate the template.ini file and behind the values RSSETUPACCOUNT= and RSSETUPPASSWORD= specify the account and password that has the SQL priveledges.
2. Launch the setup.exe or .msi with the same parameters from a command screen, making it: “C:\RS\setup.exe“ RSSETUPACCOUNT=sa RSSETUPPASSWORD=sapwd.

To install services packs, it's the same procedure.

Hope this helps you out as well as it did for us ...

1 comment:

Anonymous said...

Good post and this fill someone in on helped me alot in my college assignement. Say thank you you for your information.