[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL 2008 R2 Reporting Services Double Hop SPN setup

Posted on 2011-10-19
5
Medium Priority
?
542 Views
Last Modified: 2012-05-12
I'm a system admin and know just a little about SQL.  We just setup a SQL 2008 R2 reporting server that will connect to other SQL 2005/2008 databases to pull from.  I understand the concept of the "double hop slash kerberos" issue that exists, but I'm not 100% sure how to configure it.

Some articles say that you need to do the setspn command for the service accounts AND also change the attribute on the computer account.  Others say just the service account or just the computer account.  Also, do I need to do this for the service accounts just on the reporting server?  Or do I  need to also do this for the database servers that the reporting server will connect to?  My guess is that I just need to do this on the reporting server since that is the only server doing delegation or impersonation.

So my guess as to what I should do it this:

•setspn -a MSSQLSvc/SQL01:1433 acme\SqlReportingServices
•setspn -a MSSQLSvc/SQL01.acme.com:1433 acme\SqlReportingServices
•setspn -a http/RPTS01 acme\SqlReportingServices
setspn -a http/RPTS01.acme.com acme\SqlReportingServices

Do I also need to set the computer account to "Trust this computer for delegation to any service"?  I've read that if you do it by machine you can't do load balancing, but we aren't doing that anyway.  
0
Comment
Question by:jpletcher1
  • 3
  • 2
5 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 36995080
You need to do it for data server. And yes you need to do setspn add. It is essential for this.
Regarding Trust this computer for delegation to any service option, I am not aware of. I will talk with other folks, if someone else has any idea regarding it.
0
 

Author Comment

by:jpletcher1
ID: 36995098
So it needs to be done both on the reporting server and and on the other database servers?  These articles make it sound like just the reporting server needs it..

http://callumhibbert.blogspot.com/2009/02/kerberos-delegation-and-sql-reporting.html

http://redmondmag.com/Articles/2010/08/23/Reporting-Services-Double-Hop-Authentication.aspx?Page=1
0
 
LVL 25

Accepted Solution

by:
TempDBA earned 2000 total points
ID: 36995148
From the first link itself its clear that you require it in dataserver:

Domain Functional Level is "Windows 2003".
SQL Reporting Services and SQL Server (the database engine) are installed to different machines.
Your domain is "acme.com".
The account running SQL Reporting Services is "acme\SQLReportingServices" (it does not matter which account SQL Server is running under).
SQL Reporting Services should NOT be run under a system account ("Local System" or "Network Service").
Your SQL Server machine name is <b> "SQL01". </b>
Your SQL Reporting Services machine name is <b> "RPTS01". </b>
The SQL Server instance holding the database is running on the default port (1433).


so the add spn you are doing is for the SQL01 not RPTS01
0
 

Author Comment

by:jpletcher1
ID: 36995200
I'm sorry, you are correct.  Thanks for pointing that out.  

If someone could verify if setting the computer account as well is necessary, and if so, is that on just the reporting service server or the database server or both?  Thanks!
0
 

Author Closing Comment

by:jpletcher1
ID: 37085124
Thanks for the help.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Screencast - Getting to Know the Pipeline

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question