May 20, 2009 at 7:23 pm
Hello all,
I'm not that knowledgeable with SQL reporting services so I'm hoping my problem will be easy to resolve.
On a server running SQL Server 2005 SP2 32bit (windows server 2003 sp2 r2 32bit o/s); i have installed the base instance (MSSQLSERVER) and a named instance (MSSQLSERVER$NAME). When i built the server i chose to install reporting services for both instances.
After a failed install for an app that required reporting services, i found that the 2 db's that reporting services uses were not present within the MSSQLSERVER instance and have been trying to fix the issue since. The named instance, MSSQLSERVER$NAME, does have the two db's and seems to be configured properly.
For the MSSQLSERVER$NAME instance; within the configuration manager it shows all the config areas are green and the config is as follows:
Server status:
Instance name: NAME
Instance ID: MSSQL.6
Initialized: Yes
Server Status: Running
Report Server Virtual Directory:
Name: ReportServer$NAME
Website: Default Web Site
Report Manager Virtual Directory:
Name: Reports$NAME
Website: Default Web Site
Windows Server Identity:
Service Name: ReportServer$NAME
Service Account: domain\sqlaccount
Web Service Identity:
ASP .NET Service Account: NT Authority\NETWORK SERVICE
Report Server: DefaultAppPool
Report Manager: ReportServer$NAME
Database Setup:
Server Name: server\NAME
Database name: ReportServer$NAME
Database version: C.0.8.54
Server mode: native
Credentials Type: Service Credentials
The URL, http://localhost/reportserver$NAME, returns the following;
MS SQL Server Reporting Services Version 9.00.3073.00
and the URL, http://localhost/reports$NAME, returns the SQL Server Reporting Services Home page which all looks functional
IIS config is as follows:
Application Pools\
--DefaultAppPool\
----DefaultApplication
----ReportServer
--ReportServer\
----Report Server Interface
----Report Server
--ReportServer$NAME\
----Report Server Interface
Default Web Site\
--Reports
--Reports$NAME
--ReportServer
--ReportServer$NAME
For a couple of weeks i have been reading through tutorials and forums posts for installing / configuring reporting services, but as of yet have not been able to successfully configure reporting services for the MSSQLSERVER instance and am hoping someone can guide me step by step for doing so with the info provided above. I have removed and re-installed reporting services for the MSSQLSERVER instance several times and tried to configure it using the configuration tool with little success - i suspect its a bit of a dog's breakfast at the moment, so happy to start from scratch (i.e remove reporting services from that instance and start from there...)
Assistance is greatly appreciated and much thanks in advance!
Cheers
May 21, 2009 at 4:14 am
rms (5/20/2009)
Hello all,I'm not that knowledgeable with SQL reporting services so I'm hoping my problem will be easy to resolve.
On a server running SQL Server 2005 SP2 32bit (windows server 2003 sp2 r2 32bit o/s); i have installed the base instance (MSSQLSERVER) and a named instance (MSSQLSERVER$NAME). When i built the server i chose to install reporting services for both instances.
After a failed install for an app that required reporting services, i found that the 2 db's that reporting services uses were not present within the MSSQLSERVER instance and have been trying to fix the issue since. The named instance, MSSQLSERVER$NAME, does have the two db's and seems to be configured properly.
For the MSSQLSERVER$NAME instance; within the configuration manager it shows all the config areas are green and the config is as follows:
Server status:
Instance name: NAME
Instance ID: MSSQL.6
Initialized: Yes
Server Status: Running
Report Server Virtual Directory:
Name: ReportServer$NAME
Website: Default Web Site
Report Manager Virtual Directory:
Name: Reports$NAME
Website: Default Web Site
Windows Server Identity:
Service Name: ReportServer$NAME
Service Account: domain\sqlaccount
Web Service Identity:
ASP .NET Service Account: NT Authority\NETWORK SERVICE
Report Server: DefaultAppPool
Report Manager: ReportServer$NAME
Database Setup:
Server Name: server\NAME
Database name: ReportServer$NAME
Database version: C.0.8.54
Server mode: native
Credentials Type: Service Credentials
The URL, http://localhost/reportserver$NAME, returns the following;
MS SQL Server Reporting Services Version 9.00.3073.00
and the URL, http://localhost/reports$NAME, returns the SQL Server Reporting Services Home page which all looks functional
IIS config is as follows:
Application Pools
--DefaultAppPool
----DefaultApplication
----ReportServer
--ReportServer
----Report Server Interface
----Report Server
--ReportServer$NAME
----Report Server Interface
Default Web Site
--Reports
--Reports$NAME
--ReportServer
--ReportServer$NAME
For a couple of weeks i have been reading through tutorials and forums posts for installing / configuring reporting services, but as of yet have not been able to successfully configure reporting services for the MSSQLSERVER instance and am hoping someone can guide me step by step for doing so with the info provided above. I have removed and re-installed reporting services for the MSSQLSERVER instance several times and tried to configure it using the configuration tool with little success - i suspect its a bit of a dog's breakfast at the moment, so happy to start from scratch (i.e remove reporting services from that instance and start from there...)
Assistance is greatly appreciated and much thanks in advance!
Cheers
Don't know if this helps - but did you apply the service pack 2 to the default instance of reporting service after you rolled it out? Also, when you configure the reporting service, you may wish to log in with an account with sysadmin privilege in the SQL Server where the RS databases are going to be residing.
May 21, 2009 at 5:52 pm
Hello Sadequl,
Thanks for your response, since i originally installed reporting services with the database instance, it was patched to SP2 - it's not patched now because i have un-installed and re-installed reporting services a few times trying to configure it. I realize i will have to patch it once its configured before i use it in production - I'm just stuck using the configuration tool to properly configure reporting services for that instance. I'm logged on using an account with sufficient privileges... after i install reporting services i am able to create and start configuring using the configuration tool (i.e create the report db's and specify virtual directories etc...) - i just cant finish it satisfactorily compared to what i see with the named instance
May 21, 2009 at 6:40 pm
rms (5/21/2009)
Hello Sadequl,Thanks for your response, since i originally installed reporting services with the database instance, it was patched to SP2 - it's not patched now because i have un-installed and re-installed reporting services a few times trying to configure it. I realize i will have to patch it once its configured before i use it in production - I'm just stuck using the configuration tool to properly configure reporting services for that instance. I'm logged on using an account with sufficient privileges... after i install reporting services i am able to create and start configuring using the configuration tool (i.e create the report db's and specify virtual directories etc...) - i just cant finish it satisfactorily compared to what i see with the named instance
Hi rms, I would strongly suggest instgalling SP2 before starting to configure. I had one such occassion where the configuration would not even recognise the reporting databases - because the SQL Server was in SP2 and thew RS was not.
Also, you may want to ensure IIS related side of things - like ASP.NET configs, virtual directories etc - if you have not already done so.
May 23, 2009 at 4:07 am
Hi, not exactly an answer to your question, but I think you should give some thought to what you are trying to achieve with this architecture (2 instances of SSRS running on the same server).
You may have a specific requirement which necessitates this configuration, in which case fair enough, but it's difficult to immediately see why you would want to run 2 instances of SSRS on the same server.
If you wish to generate reports from databases on both instances of SQL Server on this host (or indeed any other host), then you can still do that with one instance of SSRS.
2 SSRS instances aren't going to give you any more fault tolerance, they will probably confuse IIS, and the double whammy of 2 SSRS windows services instead of 1 is going to eat up more server resources.
How about just uninstalling the SSRS instance which is not working and using the single instance that is working?
May 24, 2009 at 4:07 pm
Sadequl Hussain (5/21/2009)
Hi rms, I would strongly suggest instgalling SP2 before starting to configure. I had one such occassion where the configuration would not even recognise the reporting databases - because the SQL Server was in SP2 and thew RS was not.
Also, you may want to ensure IIS related side of things - like ASP.NET configs, virtual directories etc - if you have not already done so.
Hello Sadequl, thanks again for your prompt reply - i will raise a change management and patch RS to SP2 as you have suggested. I am almost certain the area where i'm having problems is IIS; i suspect i have muddled things up in trying to repeatedly get it working; hence why i posted the IIS setup above hoping someone could guide me to remove what's not needed and start from scratch successfully. I will update further...
May 24, 2009 at 4:23 pm
Andy Hogg (5/23/2009)
Hi, not exactly an answer to yoru question, but I think you should give some thought to what you are trying to achieve with this architecture (2 instances of SSRS running on the same server).You may have a specific requirement which necessitates this configuration, in which case fair enough, but it's difficult to immediately see why you would want to run 2 instances of SSRS on the same server.
If you wish to generate reports from databases on both instances of SQL Server on this host (or indeed any other host), then you can still do that with one instance of SSRS.
2 SSRS instances aren't going to give you any more fault tolerance, they will probably confuse IIS, and the double whammy of 2 SSRS windows services instead of 1 is going to eat up more server resources.
How about just uninstalling the SSRS instance which is not working and using the single instance that is working?
Hello Andy, thanks for the reply - i completely understand & agree with your sentiments; when i initially noticed that instance of RS wasn't working i wasn't too concerned with fixing it; and had preferred to just un-install but after a little researching about some of the apps that have hosted db's on the server and their requirements (i.e some apps require RS) - i probably should fix it; as it will almost definitely be needed (the named instance i was hoping to leave for testing etc...); we're a small place and i'm it as far as a DBA goes - which isn't saying much, hehehe... can you skim your eyes over the stuff i've posted above and see if you can tell me which virtual directories / app pools are for the named instance (which is working) and which i can remove and give me a hand creating the report db's from scratch and configuring using the configuration tool?
May 24, 2009 at 4:26 pm
Update: After some more fidldling around, i'm able to get most areas in the SSRS Configuration Tool to show up in green - except for the Web Service Identity area; the ASP .NET Service Account field is blank (and greyed out) - should this be populated with an account?
May 26, 2009 at 7:49 am
Ah well if you have existing applications already coded to access this second SSRS instance then yes I see your point about getting it working and leaving both instances running on the same box.
Glad to hear that you have managed to work through some of the issues that you had rms.
Yup, you will need something configured for the ASP.NET Service Account.
If this is greyed out then you should go into the IIS Manager application, find the relevant Application Pool for the non-functioning instance, bring up the properties, look on the "Identity" tab, and set the account to be used there.
Once you have done this I suggest restarting IIS, the command "IISRESET" from the command prompt will do it, and then restarting the Reporting Services Service.
Go back into the SSRS Configuration Manager, and hopefully the field should now be populated.
Good luck!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply