I’ve just got round to trying out Reporting Services on my laptop, having rebuilt it with Windows 7. I’m fairly comfortable with Reporting on XP but I’ve clearly got some reading to do because there was some baffling security weirdness that I never would have fixed without help. Once again, a great product is let down by stuff that you have to be pretty advanced to understand. With the installation problems I had and this one, if I was a newbie I would probably by now have written off SQL Server up as a pile of crap and started looking at a competitor’s product.
This is SQL 2008 Developer installed on Windows 7 Pro on a standalone laptop. I have no domain. I defaulted everything during installation: Report Server is in Native mode and the Reporting Services service is running under the Local System account. When I browsed to http://<machine-name>/Reports it asked me for my login and password. Odd. I thought it would default to Windows authentication and just let me in. Then, although I’m a member of the local Administrators group in Windows, I couldn’t do anything in Report Manager. I could see the Home page but not the links to administer security etc. Only My Subscriptions. So I went into SQL Management Studio and connected to Reporting Services. I could see some stuff but I couldn’t view details of anything. Those options were greyed out.
I read the Books Online stuff about authentication modes etc. but my brain started to hurt. I’m not a web security guru! So I asked the question on StackOverflow (http://stackoverflow.com/questions/1540800/sql-2008-reporting-services-i-have-no-rights) and got the answer. Thanks, Mozy.
To cut a long story short, when you go into IE in Windows 7 UAC ensures that, even though your account is an Administrator, once you log in you are no longer running with those privileges. So, when you get to the Report Manager you are a nobody. So you have to add http://<machine-name> to your trusted sites. Then you have to use Run As Administrator on IE to get into Report Manager and then add your own login as a System Administrator in Site Security. Finally you can actually try the technology out.
I just don’t think I was trying to do anything exotic here, Microsoft. I’m a reasonably accomplished .NET developer, DBA, and Reporting administrator and developer in SQL 2005. And I’ve got completely stuck twice now using two of your flagship new products in their ‘out of the box’ configuration. There may be perfectly sound ‘secure by default’ reasons for these things but please make it easier to discover how to switch the stuff on. With PowerShell you can’t even run your own scripts on your own machine without making some changes but that was quite well publicised and it’s easy to find the instructions. But I haven’t found it like that with SQL Server 2008. Maybe I missed some excellent advice about all this somewhere in Books Online. I don’t think so but, if I did, that’s the point: it was easily missable! Please don’t assume that, just because it’s a server product, it’s going to be set up by geek gods who instinctively understand a wide range of network and security details. I suspect it usually won’t be. And it certainly wasn’t this time!