SQL Server Reporting, are you having fun yet?

If you are visiting my blog today in search of help because you are configuring a SQL Server Reporting server, I hope this helps. I have had to setup the same reporting server about 3 times now. This last time because I bought a new dev PC and the client decided to use a custom configuration instead of the kinda standard setup that Reporting Services has.

Setting up a Report Server requires careful detail to permissions. Yes, love, love, love permissions.

Here are a few pointers:

Say you are setting your own local copy for dev purposes, and you get some sort of pop-up 'Access Denied' permission, right as you try to 'Start' you ReportServer, you may want to check your folder permissions on the 'Reporting Services', for myself I was using the 'Network Service' account to run the show and somehow the permission on the folder was all of the sudden gone after working for some time. IDK but make sure you go to the highest folder 'Reporting Services' and allow your selected user 'Full Control' on that folder!

Sometimes you may need to create a Login to run reports, so go into SQL Server Management Studio, create a Login with a SQL Server authentication and a password you can remember, give it permissions to the database you are trying to get a report to run on. Sometimes you may need to give it 'Execute' permissions on Store Procs too! This user may even come in handy as the user that a 'Shared Data Source' can use. Since I am just doing this for dev purposes, I have found this is the easiest way to get passed permission errors....

Now, let say you got the ReportServer to start but it is still acting funny. You get a 500 Service error when you try to visit the ReportServer URL. Go to the log files and check into errors there.

If you see this error:

ERROR: AppDomain ReportServer_MSSQLSERVER_0 failed to start. Error: Invalid syntax on line 166 - 'Expected > character.'.

Please inspect your config files, make sure you have all begin and end tags correct (and double check all those attributes!). I was actually missing an end tag and this error kept coming up. I was editing the config files in TextPad so it was hard to spot. Once I opened them in Visual Studio, it was much easier to identify!

Ok. So you have stuff running, and you are trying to run as the 'Integrated Security' for stuff, you visit the Report Server URL, it keeps prompting you for a login or maybe permission, try this. Open IE Explorer as 'Run as Administrator' then try again. This is one case where you will have to drop FireFox/Chrome. IE plays better here. I suppose since it is a MS product....

Hope that helps! It is always a headache setting up a report server! I feel for you. Just do little steps at a time, DOUBLE-CHECK! It is very finicky one little thing and it doesn't work!