I ran into a weird problem that I figured I would share the solution to since it seems to be floating around on the internet. Basically the scenario is that if I connect to my MSSQL Reporting Server using the IP or shortname it works fine but once I use the FQDN I get prompted for credentials and it never lets me authenticate. The root cause of this is Windows Loopback Check Functionality due to a DNS and Domain mismatch. Here’s what I mean:
My Domain = domain.com
My FQDN = servere1.sub.domain.com
If you do not correctly set the “Primary DNS Suffix for this Computer” under System Properties > Change > More as seen below you will experience this issue.
If you are interested there is a MS KB http://support.microsoft.com/kb/926642 that has more details but does not necessarily address this specific cause.
Scenario: I have a new SQL Server Reporting Services 2008 R2 x64 instance that works if you are the local administrator. Once I switch to another user who is an implied administrator via an AD group I get the following error when attempting to access SSRS in a browser:
“User ‘TESTDOMuser’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.”
The user is a member of the domain admins group which is obviously a member of the local admins. Also if I right click on IE8 and say “Run As Administrator” it works. The quick fix is to disable the UAC. If you don’t want to do that then just do the below:
1. Launch IE8 using “Run As Administrator” and in SSRS (http://servername/Reports) click on Site Settings. In Site Settings navigate to “Security” and add your user “TESTDOMuser” as a System Administrator.
2. Click on “Home” and then “Folder Settings”. Now add your account again this time assigning yourself all the roles (Browser, Content Manager, My Reports, Publisher, Report Builder).
3. Close Administrative IE session and open a normal IE8 window and your SSRS should work.