Why you wanna treat me this way? (Or Extra Special Super Secret Security in SS 2008 Reporting Services)

I agree with Audrey.  I too, heart Microsoft.  For me Microsoft’s SQL Server has been the cornerstone of a very rewarding career.  But in every relationship there are moments of great disappointment.  I will now share one of those moments in this thing me and SQL Server got going. 

I have a lot of SSIS experience, a bit of SSAS and practically no SSRS experience.    I have written many reports in “Access”, and have “heard” that of the three tiers in the BIDS stack SSRS is the easiest, but I wanted more than theory and hearsay.  Since no “real” projects came my way, I decided to do the genuine geek thing and just play with it myself.  The simple plan:  I would set up a reporting server on my laptop, a lovely ASUS Notebook U80V loaded with Windows Vista Home Edition.

I got started.  I did an install of SSRS. It’s just a series of clicks through the SQL Server Install wizard and I would be on my way to deploying reports on my very own server.  I took all the defaults.  Everything I looked up online said that would be ok to do.  Once I had my green success icon on the install I knew what to next (now that I had looked up what to do next).  I went to internet explorer, typed in http://myservername/reports to open up the Report Manager.  This is where I would be greeted with a GUI which would gracefully guide me through deploying some reports.  Everything in windows is intuitive, right?  Now that I knew how to open the report manager  , I would be able to figure out the rest easily.  (I’m pretty good with these computer thingies).  (Stop laughing at me). 

Here is what I was greeted with:

Awesome!  No errors! 

Now I just upload an RDL (report) file from the Contents Tab using the Upload File button and page.   …….   Um …… just , wait, it’s got to be here somewhere…… let me check the links —-Home.  (already home).  Um, My subscriptions ——(don’t have any).  Help——  (too much help). 

Well that’s weird.  I can’t “do” anything.  This doesn’t look like all those pictures of “functional” pages of Report Manager I’ve seen online:

Looking at it, it seems like a permissions problem but ….it can’t be a permission problem because this is my personal, stand alone laptop.  There is only me as the user and I’m an administrator.  The server is installed under my user (an administrator btw).  Every user, every service, every application I touch should have absolute God rights on this machine.  Can’t be permissions.

I took a deep breath.  I knew what I was in for now.  Dedicating a portion of my life to fishing online for the (probably annoying) solution.  From topics on MSDN/BOL  I started with “report manager” .   I was optimistic that the answer was close.  And it was about halfway down the page I saw this note:

 Note:
If you are using Windows Vista or Windows Server 2008, you must configure the report server for local administration before you can use Report Manager to manage a local report server instance. For instructions on how to configure the server, see How to: Configure a Report Server for Local Administration on Windows Vista and Windows Server 2008.

 

Hmm.  I was indeed using Windows Vista.  But wasn’t I already configured for local administration?   After all, this is my personal, stand alone laptop.  There is only me as the user and I’m an administrator.  The server is installed under my user (an administrator btw).  Every user, every service, every application I touch should have absolute God rights on this machine.  Can’t be permissions.

What the heck, I’m only ten minutes in at this point.  I followed the link.  

There was the answer.  Right at the top. 

——————————————————————————

Deploying Reporting Services on Windows Vista and Windows Server 2008 requires additional configuration steps if you want to administer a report server instance locally. Both Windows Vista and Windows Server 2008 limit the overuse of elevated permissions by removing administrator permissions when accessing applications. Because the operating system removes permissions, members of the local Administrators group run most applications as if they using the Standard User account.

While this practice improves the overall security of your system, it prevents you from using the predefined, built-in role assignments that Reporting Services creates for local administrators. However, with additional configuration on your part, you can effectively manage report server content and operations using standard user permissions if you do the following:

———————————————————————————–

Folks,  I have to say that the verbiage above is so unbelievable to me as to not need any embellishment for comic effect.  It’s like the SNL skit when Tina Fey played Sarah Palin in the Katie Couric interview and all she did was read from the transcript of the actual interview.

My personal favorite tidbits from the above:

  • “limit the overuse of elevated permissions”  you mean like permissions to execute all designed functionality?  Without a warning, flag, or tool tip in sight?
  • “requires additional configuration steps IF you want to administer report server instance locally”.  Maybe just assume that if a user installs it then that user may want to use it, “locally” being a popular choice for “how”.

Anyway I got my server working, using the below instructions.  I post them here now from MSDN/BOL for the sake of the children everywhere:

————————–

  To configure local report server administration on Windows Vista and Windows Server 2008

  1. Open a browser window with Run as administrator permissions. From the Start menu, click All Programs, right-click Internet Explorer, and select Run as administrator.
  2. Click Allow to continue.
  3. In the URL address, enter the Report Manager URL. For instructions, see How to: Start Report Manager.
  4. Click Tools.
  5. Click Internet Options.
  6. Click Security.
  7. Click Trusted Sites.
  8. Click Sites.
  9. Add http://<your-server-name>.
  10. Clear the check box Require server certification (https:) for all sites in this zone if you are not using HTTPS for the default site.
  11. Click Add.
  12.  Click OK.
  13.  In Report Manager, on the Home page, click Properties.
  14. Click New Role Assignment.
  15. Type your Windows user account in this format: <domain>\<user>.
  16. Select Content Manager.
  17. Click OK.
  18.  Click Site Settings in the upper corner of the Home page.
  19. Click Configure Site-wide security.
  20. Click New Role Assignment.
  21. Type your Windows user account in this format: <domain>\<user>
  22. Select System Administrator.
  23. Click OK.
  24. Close Report Manager.
  25. Re-open Report Manager in Internet Explorer, without using Run as administrator.

  ————————— 

So, even though the server was my personal, stand alone laptop, even though there was only me as the user and I was an administrator, even though the server was installed under my user (an administrator btw) and even though every user, every service, every application I touched should have had absolute God rights on the machine, it was the Permissions.

But I’m over it now.  Things are good again between me and SQL Server.  If anything, I think it’s made our relationship stronger. 😉

Published by

Julie Smith

One half of the Datachix.com. ETL person. This picture is the best of me ever taken. Don't be too sad when you meet me for real :)

7 thoughts on “Why you wanna treat me this way? (Or Extra Special Super Secret Security in SS 2008 Reporting Services)”

  1. Agreed, microsoft has plenty of room for improvement when it comes to making their UIs user friendly…

    But what’s the deal with the “Etch-A-Sketch” circles? You may have heard of another microsoft program called “Paint”, you might find their elipse tool handy for improving them circles, hehe 😉

  2. I agree the circles are very artsy and I like the colors chosen and the way they were drawn. Thank you for the info Datachix!

  3. We have SQL Server 2008 Enterprise edition on Windows Server 2003 R2.

    I installed SQL Server 2008 as myself as I am admin on the server.

    As per microsoft all the services should be running as least privilege user, so I changed all database, analysis services using SQL Server Configuration Manager. I changed Report services using Reporting Services Configuration Manager.
    If I run report service account on my id, everything runs fine, but if I change the service account to a user with least privilege, and go the report manager link, I get a blank page, the log error says “ui!ReportManager_0-1!aa8!05/10/2010-08:53:11:: e ERROR: Requested registry access is not allowed.
    ui!ReportManager_0-1!aa8!05/10/2010-08:53:11:: e ERROR: HTTP status code –> 500
    ——-Details——–
    System.Security.SecurityException: Requested registry access is not allowed.”

    I also have added this user on reporting website as shown by Julie, but I get a blank page.

    1. I followed steps on this chain and gave System Admin rights to the user:
      # In Report Manager, on the Home page, click Properties.
      # Click New Role Assignment.
      # Type your Windows user account in this format: \.
      # Select Content Manager.
      # Click OK.
      # Click Site Settings in the upper corner of the Home page.
      # Click Configure Site-wide security.
      # Click New Role Assignment.
      # Type your Windows user account in this format: \
      # Select System Administrator.

Leave a Reply