Home / server / Questions / 81344
Accepted
jgardner04
Asked: 2009-11-05 06:26:58 +0800 CST

SQL 2008 Reporting Remote Datasource Windows Authentication

  • 772

I am trying to use Windows Authentication on a SQL Server 2008 Report Server that connects to a 'remote' SQL Server 2008 Enterprise server. If I store the login credentials with the datasource everything works fine. The reports limit what information can be selected based on the user's account. I need to be able to use Windows Authentication. If I set the data source to Windows authentication when I try to go to the report it prompts me for my us/pw but every time I enter this information the box pops up again. Both servers are on the same subnet. If I put a copy of the database on the report server it will run without issue.

Environment: Data Server SQL 2008 Enterprise SP1 Windows 2008 Enterprise Physical Server

Report Server SQL Server 2008 Enterprise SP1 Windows 2008 Enterprise VMware ESX 3.5 Virtual

Any Assistance would be greatly appreciated

sql-server

1 Answers

  1. Best Answer

    Definitely sounds like a double-hop issue. You will need to set your environment to use Kerberos authentication between your clients, the report server, and the data server.

    For Kerberos to work, you'll need the reporting service and SQL database service to be running as domain accounts rather than local accounts (Local system/Network service), and you'll need to create SPNs for the reporting service account in Active Directory. See this article for information about the SPNs that you need to create. Google will also turn up a bunch more information on how to configure things.

    edit: System no longer thinks I'm a spammer so I can post multiple links :P

    • 2