March 22, 2007 at 2:13 am
For the moment I am busy ith creating a lot of reports against out business system database. I am using Reporting Services. When setting connections to the database I am using Windows authentication mode. It works to run the report in Business Intelligent Studio and also to run the report in Internet Explorer on my computer. But when running the report in Internet Explorer from another computer it fails to connect to the database.
For the moment Reporting Services is installed on my computer where I test the reports before I will set them in production on the production server.
Niklas
March 23, 2007 at 4:08 am
It's the Double-Hop problem.
If the Reports server and SQL server are on different machines then the authentication is not passed from the Report server to the SQL server. If you check the SQL logs you should see an failed login attempt (assuming you have logging turned on).
Using AD groups was how I had to go. It does involve the end user having to log into the browser but then theyt are restricted via the reporting security.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
March 23, 2007 at 10:48 pm
Yes it's a Double-Hop problem
Basically within windows authentication we have two types of authentications Kerberos and NTLM. In your scenario i guess it is using Kerberos authentication. You might have to check couple of things before we confirm on that
KB articles
http://support.microsoft.com/kb/887682/en-us
March 26, 2007 at 4:41 am
Can you describe in more detail what I have to do? I have some basic knowledge about AD and authentication but this is beyond my knowledge.
Niklas
March 26, 2007 at 5:24 am
This is the setup I've used
Create global group gg_SQL_xxxx in Active Directory Users and Groups
On the Reporting Services database
Create login gg_SQL_xxxx Default database ReportServer
On the DataWarehouse database
Create login gg_SQL_xxxx Default database RequiredDatabase
User Mapping RequiredDatabase ddlreader & ddlwriter
User Mapping ReferencedDatabase ddlreader & ddlwriter
In database
RequiredDatabase Securables Stored Procedures required Execute Grant
ReferencedDatabase No Change
On Report Server web
Site Settings
Item-level-roles SQL Users only view folders
Home page
Create New Role Asignment gg_SQL_xxxx SQL Users
Reports page
Create New Role Asignment gg_SQL_xxxx SQL Users
Required Report Group(s)
Create New Role Asignment gg_SQL_xxxx Browser
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
March 26, 2007 at 6:01 am
Thanks for the answer. Just some more questions regarding your setup.
What do you mean with ReferencedDatabase?
With this solution you have suggested, will I still be able to set which reports the user can render? To decide which reports a user can render I have created usergroups in our AD which then are set to "Browser" of the reports.
Is it neccessary to have gg_SQL_xxxxx connected to the role ddwriter (I think you mean role db_writer)
March 26, 2007 at 6:45 am
The ReferencedDatabase is any other database to which the stored procedure used to view the report needs access.
The access given to the global group(s) only them to access the reports in the folder to which they have browser rights.
db_writer is required in some of our reports as they are using several databases / temporary tables at present.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply