Reporting Services: Cannot connect to Oracle using Integrated Security

sql-server oracle reporting-services integrated-security
Ucodia picture Ucodia · Apr 10, 2012 · Viewed 8k times · Source

Context

I have an Oracle 11g database instance and a SQL Server 2012 instance installed on 2 different servers in the same domain. The Oracle database is configured to accept connection with Integrated Security (ie. Active Directory authentication). SQL Server has the Reporting Services feature installed and configured. Reporting Services will connect to Oracle database using the Oracle .NET data provider from Microsoft.


Issue

When creating a data connection to the database in Visual Studio I have no problem to connect to the Oracle database using Integrated Security. I configured the connection with this connection string:

Data Source=INSTANCE_NAME;Integrated Security=True;Unicode=True

But whenever I try to create a data source in Reporting Services from the SSRS website, from Report Builder or from BIDS, I am getting a login error:

ORA-01017: invalid username/password; logon denied

I tried copying the exact same connection string but the result is always the same. It looks like Reporting Services is ignoring the Integrated Security=True option for an unknown reason.


Notes

The same issue occurs with SQL Server 2008 R2.

Oracle .NET data provider from Oracle cannot be used with Reporting Services.

Answer

Ucodia picture Ucodia · Apr 25, 2012

I finally found what was the problem. When you need to pass credentials that needs to be transmitted to another server (Client -> SSRS -> DB), the default authentication system (NTLM) won't be enough because it can't perform double hop authentication. You need to give the rights to the report server to delegate authentication through Kerberos on the DC. So that means that you also have to enable RSWindowsNegotiate as the default authentication system for your report server and that you define SPNs for SSRS and its service account.

More details can be found here: https://serverfault.com/q/378096/85935

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
Can a foreign key be NULL and/or duplicate?

Please clarify two things for me: Can a Foreign key be NULL? Can a Foreign key be duplicate? As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to input …

Read More