SSRS reports work on the server but not on the clients

SSRS reports work on the server but not on the clients

Symptom: reports work from the server. They do not work from client machines. “An error has occurred during report processing. (rsProcessingAborted). Cannot create a connection to data source ‘MyData’. (rsErrorOpeningConnection). For more information about this error navigate to the report server on the local server machine, or enable remote errors.”

Step 1: Set the server as trusted for delegation

  • Active Directory Users and Computers
  • Right-click SSRS Server, Properties
  • Delegation tab
  • (o) Trust this computer for delegation to any service (Kerberos only)

Step 2: Enable the service account to impersonate

  • Check the service account
  • Microsoft SQL Server 2005 > Configuration Tools > Reporting Services Configuration
  • Windows Service Identity
  • Web Service Identity
  • SSRS is generally running under Network Service (NT Authority\NetworkService)
  • Set the permissions
  • Administrative Tools > Local Security Policy
  • Add Network Service to: Act as part of the operating system; Impersonate a client after authentication

 Step 3: Add the service principal name (SPN) for web services

  •  Install Support Tools
  • List the active SPN
  • CD C:\Program Files\Support Tools
  • Setspn -L SSRSServer
  • If HTTP SPNs do not exist, add them.
  • Setspn -A HTTP/SSRSServer SSRSServer
  • Setspn -A HTTP/ssrsserver.mydomain.com SSRSServer

 Step 4: Add the service principal name (SPN) for databases

  •  Check the service account
  • Check to see what service is running “SQL Server (Instance)”
  • Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager
  • SQL Server 2005 Network Configuration
  • Protocols for Instance: TCP/IP
  • Scroll to the bottom, IPALL
  • TCP Dynamic Ports: 3418
  • setspn -L DOMAIN\serviceaccount
  • If MSSQL SPNs do not exist, add them
  • setspn -A MSSQLsvc/SSRSServer:port DOMAIN\serviceaccount
  • setspn -A MSSQLsvc/ssrsserver.mydomain.com DOMAIN\serviceaccount

 Step 5: Modify the Data Sources

  • http://ssrsserver.mydomain.com/Reports
  • Data Sources
  • Edit the data source
  • Connection string: Data Source=ssrsserver\instance;Initial Catalog=MyDBname;Integrated Security=SSPI
  • (o) Windows integrated security

From the desktop, open SQL Server Management Studio. Create a new connection

  • Server type: Reporting Services
  • Server name: http://ssrsserver.mydomain.com/ReportServer
  • Authentication: Windows Authentication
  • Click Connect and it will now open up reporting services.
Posted by Wolf Goerlich