August 12, 2015 at 4:11 am
Hi,
I've installed SQL Server on 3 x 2014 servers for a BI solution (VM environment).
Server A is a warehouse from where data is extracted (read), and Server B is a Reporting Server with SSRS configured (Server C is Integration Services and not part of this problem).
The Data Source (pointing to DW Server A) on the Reporting Server employs Windows Integrated Security (see attached) but is using the NT AUTHORITY\ANONYMOUS LOGON service account when running the report instead. I am an administrator on the ReportManager site as well as on all SQL Servers (so no need to explicitly map account to particular database). Additionally the Report Server uses the NT Service\ReportServer service account (this is standard) - this wouldn't influence any authentication occurring on Server A though.
So I'd expect Reporting Services to pick up my Windows account and authenticate on Server A to read the reporting data.
Obviously, the main reason for specifying Windows is that many users will eventually be using this platform and I'd want them to be Active Directory - mapped and then I could map the AD group accounts in SQL Server and manage security within SSRS at a granular folder level.
It's easy when the report server and source data are all on the same server but moving to multiple servers is a better solution for enterprise.
So, I've seen this issue before but can't recall if the route I took was relating to SPN or Kerberos/NTLM, if that makes sense.
Probably a simple thing that I've missed.
I've attached a couple of screenshots to show some of the setup.
Would really appreciate some guidance.
Thanks.
August 12, 2015 at 8:37 am
DuncEduardo (8/12/2015)
Hi,I've installed SQL Server on 3 x 2014 servers for a BI solution (VM environment).
Server A is a warehouse from where data is extracted (read), and Server B is a Reporting Server with SSRS configured (Server C is Integration Services and not part of this problem).
The Data Source (pointing to DW Server A) on the Reporting Server employs Windows Integrated Security (see attached) but is using the NT AUTHORITY\ANONYMOUS LOGON service account when running the report instead. I am an administrator on the ReportManager site as well as on all SQL Servers (so no need to explicitly map account to particular database). Additionally the Report Server uses the NT Service\ReportServer service account (this is standard) - this wouldn't influence any authentication occurring on Server A though.
So I'd expect Reporting Services to pick up my Windows account and authenticate on Server A to read the reporting data.
Obviously, the main reason for specifying Windows is that many users will eventually be using this platform and I'd want them to be Active Directory - mapped and then I could map the AD group accounts in SQL Server and manage security within SSRS at a granular folder level.
It's easy when the report server and source data are all on the same server but moving to multiple servers is a better solution for enterprise.
So, I've seen this issue before but can't recall if the route I took was relating to SPN or Kerberos/NTLM, if that makes sense.
Probably a simple thing that I've missed.
I've attached a couple of screenshots to show some of the setup.
Would really appreciate some guidance.
Thanks.
You'll need to check any SPNs are correctly registered. Use domain accounts for the services and ensure the SPNs are bound otherwise authentication issues are a hazard you will face
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 12, 2015 at 8:46 am
Perry,
There are no SPNs set up and Kerberos is not enabled on SQL Server. The auth_scheme = SQL.
I've never had to use a SPN, so my thinking was it is more simple and fundamental than this.
August 12, 2015 at 9:53 am
the issues you have are common with double hop auth between the reporting server and the database engine. There will likely be an spn of some kind created (theyre auto created when the service has permission), whether it's the correct spn or not is another story.
When authenticating as a local service the auth impersonates the computer account, setup a low privilege domain account for the sql server services and check the SPNs are registered correctly (which theyre usually not when using a low priv account)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 13, 2015 at 11:40 pm
There's a simple answer to this: The message about NT AUTHORITY\ANONYMOUS LOGON is because you're asking the Report Server web service to check the permissions using its account, which runs under NT AUTHORITY\ANONYMOUS LOGON. If you want to pursue AD Authentication for the data source, what you want to do is select "Credentials supplied by the user..." and then "Use as Windows credentials...".
There's a StackOverflow question about this error: https://stackoverflow.com/questions/8852399/login-failed-for-user-nt-authority-anonymous-logon
If I may make a suggestion about your set up: As Perry suggested, run your ReportServer instance to run under a domain service account rather than a local account. If you don't need to audit user connections to the DW server or apply row-level security based on logins, create a SQL login for a reports server user (RS_ReadOnly as an example so it's easily identifiable), and use that user/pass combination in the Data Source(s). Then, create your AD groups for reporting, assign users to them, and then assign those groups to the reports.
August 14, 2015 at 7:19 am
Hi,
So, an update and a little clarification.
Firstly, I think SPN is a red herring here - servers are configured correctly with no comm issue; no additional changes made.
Secondly, regarding auditing, I'll be using the [ExecutionLogStorage] as always to track report usage within the Reporting Server, so no requirement as such to monitor DW access.
Thirdly, set up of Reporting Server (Configuration Manager) was to define (standard) {NT Service\ReportServer} service account. Execution Account left blank.
Contrary to what you've mentioned, I have defined a reporting user login RS_Read within the Data Source, mapped in DW.
So this is "Credentials Stored Securely in Report Server". This is limited to database/objects required for data retrieval. Of course, the usual problem is when stored procedures are specified within the SSRS report; then privileges need to be elevated, or at least db_executor minimum.
So, when report is run the user's windows account will still register against the Report Server but the predefined SQL report account will access data in DW. I will test this with multiple users.
Basically, I got an error when configuring Data Source as "Credentials Supplied by User running Report"
August 25, 2015 at 4:33 am
All works ok now. Basically in a 3 server BI environment, we have a non-expiring non-service domain account which provides the integration service access to data in warehouse, and AD windows accounts will hit the report server and register usage in the execution log in there (and authenticate against PDC of course)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply