Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Deep Dive Into SSRS and Data Connection Permissions

  • 16 January 2014
  • Author: CraigLove
  • Number of views: 15166
  • 0 Comments

A couple weeks ago I ran across a problem when setting up a shared data connection for a SQL Server Reporting Services in a test environment.  After beating my head against the desk for a few hours I finally solved the problem.  I wanted to pass this information along because I know that someone out there is also screaming in frustration!  I am concerned that once I provide some of the background details of this situation, many of you may move along.  However, I want to encourage you to hang with me because the discovery I made applies to all SQL Server Reporting Services installations.

My problem presented itself when I was setting up a virtual environment to support experimenting with SharePoint 2013.  In my virtual environment, I created 3 machines a domain controller, a server for SQL Server 2012 and a Windows 7 workstation.  Since the virtual machines are running on my laptop the domain controller needed to pull triple duty and so it also hosted the SharePoint installation.  I also installed SSRS in SharePoint integrated mode to this server.

Before I continue, I want you to know that the guidance in this blog entry-

  • Is not limited to SSRS in SharePoint Integrated mode.  The issue I found can also be a problem with SSRS in Native Mode.
  • Is not limited to SSRS installed on Domain Controllers.

Now, all seemed well in my world.  To test the SSRS setup, I created a very simple report and a shared data connection.  I used a high privilege account as the connecting account.  Success!

Successful connection using Adminsitrator

 

One of my goals for this environment was to use minimal privileges for all parts of the installation.  My next step then was to replace the administrator account with an account dedicated for SSRS data connections.  Big failure!!  I confirmed that the zymurgy\SP_DataAccess_RO account had permissions to access the data source.

Read-Only Account Failure

What the heck!  (Actually, the language I used was more colorful than that.)  I won’t drag you through the broken glass I crawled through to solve this problem.  Suffice it to say that I spent a lot of time poring over various log entries and attempting to test the issue in different ways.

And the winner is… Any domain account used for a SQL Server Reporting Services connection MUST have the ability to log on locally.  On a non-domain controller server, this is (normally*) not an issue.  The ability to log on locally is set in the group policy setting “Allow log on locally”.  To check this you can access the Local Group Policy Editor by executing gpedit.msc from the command line.  In a non-domain controller server the Log On Locally policy includes the local groups Administrators, Users and Backup Operators.  The standard Users groups includes all Domain Users and Authenticated Users.  Therefore, any domain account can be used as an SSRS Shared Data Connection account.

Standard Server Log On Locally

 

Standard Server Users Group

 

As I mentioned at the start of this entry, this information applies to SSRS installations on a standard server.  If your organization’s domain administrators manipulate group policies to lock down servers and workstations, you may find that the Log On Locally privilege has been restricted.  The Log On Locally privilege is also required on an SSRS installation working in Native Mode! If you find that this account is not included directly or within a group, you need to add the group/account by clicking on the "Add..." button.  If you do not have permissions to edit this setting on a local server you need to buy your server administrator a doughnut and get some help.

The remainder of this entry provides guidance for adding users to the Log On Locally policy on a domain controller.  This will be helpful for those of you who are setting test environments where the domain controller must perform more than one task.

This user accounts included in a standard server's Log In Locally policy does not apply to Domain Controllers.  The local Users Group is not included and therefore a simple account such as I used will not work as an SSRS data connection account.  It’s possible to add the read only account to one of the groups listed but that would not adhere to a least privilege approach!

Domain Controller Log On Locally

 

 

Normally, adding users to the Log On Locally policy is as easy as clicking on the “Add User or Group” and then selected the correct user.  Unfortunately, this button is disabled on the domain controller.   To add the user, you need to modify the Default Domain Controllers Policy.  Getting to this setting is not as simple as using GPEdit.msc. 

 

Not able to add users on Domain Controller

The screen shots for doing this are included in the following images.

1.  Open an empty Management Console by running “mmc” at the command line.  In the window that opens, select (menu) File > Add/Remove Snap-in…

Adding Group Policy Management

 

2.  Add “Group Policy Management”

Add Group Policy Management

3.  Navigate to the “Default Domain Controllers Policy” in the left pane.  Then, in the right pane, select the Settings tab and navigate to “Local Policies/User Rights Assignments”.  Here you see what accounts can log in locally on Domain Controllers.

Domain Controller Default Login In Locally BEFORE

 

4.  To edit the policy, right-click on the “Default Domain Controller Policy” and select “Edit…”.

Edit Policy

5. A new window will open.  Navigate to the Policies> Windows Settings> Security Settings> Local Policies> User Rights Assignment node and locate the “Allow log on locally” entry.  Double-click on this entry and a dialog box will open where you can add the groups or user accounts.

Adding User to Domain Control poilicy

 

6.  When you check the Default Domain Controllers Policy again, the group(s) and user account(s) you added should now appear.  (You may need to refresh the window.)

Domain Controller Default Login In Locally AFTER

 

7.  Now the read-only account I set up works!!

Successful connection using Read-Only Account

Print
Categories: Analysis Services
Tags:
Rate this article:
5.0

CraigLoveCraigLove

Other posts by CraigLove

Please login or register to post comments.