Error code 40 when running SSRS reports from Internet Explorer (run as administrator)

Question

We deployed a VB.Net application on a customer's computer that contains SSRS reports.

The application connects to the SQL Server database in the app without any problems. We installed SQL Server Data Tools so we could deploy the reports (rdl) and data source (rdl) files up to the report server. These deploy without any problems.

In SQL Server Data Tools we can "Preview" the reports without any problems as well.

We do run into a problem when attempting to view the report from Internet Explorer (run as an administrator).

We get the following error:

Cannot create a connection to data source 'DataSourceReports' 
(this is the name we used for the TargetDataSourceFolder) 
error:40 - Could not open a connection to SQL Server

We also get the same error when the app we deployed runs the reports.

Please let us know what is not set up correctly on the SQL Server side.

Stacia Stacia · Accepted Answer · 2012-06-10T14:44:32

A likely possibility is that you are experiencing a double hop authentication problem. It's not clear from your explanation, but is the SQL Server database on a separate server from the report server? If so, then your credentials allow you to connect to the report server but Windows integrated security does not pass those credentials on to the SQL Server database if you are using NTLM on the report server. The report server tries to use Kerberos on your network to authenticate by way of ticketing to the SQL Server database, but you must have this configured correctly on your network. See this article if you want to use Kerberos: http://technet.microsoft.com/en-us/library/ff679930(v=sql.100).aspx.

Another (easier) solution is to open the data source on the report server and change the authentication to use stored credentials. Make sure the credentials you use have read permission on the SQL Server database. The downside of this approach is that you cannot use row-level security in your report by user unless you design your report to capture user information and set up the query or a filter on the dataset to restrict data by user. If that's not a concern, the stored credentials are easy to set up and maintain - and you're going to have to do this anyway if you want to use caching, snapshots, or subscriptions. For more information on stored credentials, see http://msdn.microsoft.com/en-us/library/ms159736.aspx.

Error code 40 when running SSRS reports from Internet Explorer (run as administrator)

1 Answers