SSRS : Cannot create a connection to data source .(rsErrorOpeningConnection)

Question

I am working with a windows server 2003,SQL SERVER 2005 and Reporting Services.

Basically, SQL Server is on my local machine and the Reporting Services Server is on another computer.

When I have tested my my reports on the local machine there is no error.

But when I'm deploying them on the new Reporting Services Server using another machine. I receive this error message :

"An error has occurred during report processing. (rsProcessingAborted).

Cannot create a connection to data source .(rsErrorOpeningConnection)

Login failed for user '(null)'.

So the reason is not associated with a trusted SQL Server connection. "

Generally this error occurs when I use the "windows authentication" with my connection string.

If I use the "SQL Server authentication" withe User Name and Password. I don't get this error message.

I've found some people talking about a "double HOP" problem. :ermm:

But I'm not an expert in Security or in Network Connection problems.

Is that someone already had the same error?

Answer 1

Whenever you want to access another resource off of the IIS server and certain fairly common situations occur.  So whenever you use Integrated Security, anonymous access is disabled, and impersonation is turned on, a security measure kicks in and doesn't allow your site to access resources on any network servers.  Generally it includes access to a UNC path directly from IIS or SQL Server using Windows authentication.

Basically, the reason behind this is because of a 'double hop' that authentication is doing.   Using Integrated Authentication whenever you authenticate to the IIS server, It uses up your first 'hop'.  When a network device is accessed by an IIS, that would be the double or second hop which is not allowed.  So IIS in turn generally can not pass on those credentials to the next network device, else your credentials could be abused by the administrator or developer and use them in different ways that the site visitor didn't anticipate.

Basically, this will not occur with impersonation off or anonymous access because in that case IIS takes care of authenticating you and then it uses a different user for local or network access which means that the app pool identity or anonymous user can make a network called as the first hop.

So basically your  SQL Server database cannot give you access based on the domain\username it receives, the reason being that for security reasons IIS is not allowed to pass them on.

You can learn ssrs from Intellipaat