Sql-server – Reporting Services: Cannot connect to Oracle using Integrated Security

You didn't indicate what version of SQL Server. Keep in mind SSRS 2005 requires IIS installed on the box as well. SSRS 2008 does not. It uses HTTP.SYS natively. The optimal answer is #3, however, it really depends on the existing load on the physical server as well as the type of reports you would be running. #1/#2 are essentially the same option with respect to performance.

The truth is, without knowing the performance characteristics, no one here can tell you the best option. Your best path is to get a good idea of how your SQL Servers are performing today, to set up a small dev SSRS environment and profile it as you test the reports, and then make the decision for there. If you're not sure where to start on getting a performance profile on your SQL Servers, allow me to recommend:

Brent Ozar's posts on Performance Tuning

SPNs should be configured on the computer or user object representing the identity of the service. if the service in question on server01 is running under network service, then you would ensure there are correct SPNs configured on the computer account representing server01. You can check it by running "setspn -l server01". The command itself can be run from anywhere as it will talk to a DC and examine the serviceprincipalname LDAP attribute of the object. So you can run it from dc01 or server01 or anywhere else, provided you have the setspn.exe binary. You can see all syntax by running "setspn /?"

If they aren't configured (as verified by above command) you would add them using the same binary but with different syntax. Example syntax for the "-A" switch which adds the values by running "setspn -A http/server01 server01". The example assumes the port the service is running under is 80 or 443.

You have to be careful in ensuring the SPN isnt registered anywhere else in the AD forest as the SPN has to be unique. the "-Q" switch can check for the presence of the SPN anywhere when you do something like "setspn -F -Q http/server01".

In Wireshark or any other network tracing tool, you will only see the kerberos traffic provided that the client does not already have a cached ticket for the resource and provided that it doesnt have a negative cache indicating the SPN as unavailable. If you tried this several times and then launched Wireshark to see whats happening, its likely a negative cache is involved if the SPN is completely missing. Else if you already have a ticket cached, this will be reused and no further tickets will be requested.

Always fush dns cache on client "ipconfig /flushdns" and kerberos cache "klist purge" before doing network traces to troubleshoot. This will show name resolution traffic for KDC location and attempts to obtain a ticket if not locally cached.

http://msdn.microsoft.com/en-us/library/cc281253.aspx looks like a good resource on the SSRS side of things. For kerberos help in troubleshooting, please see the askds blog posts targetted kerberos starting with http://blogs.technet.com/b/askds/archive/2008/06/13/understanding-kerberos-double-hop.aspx. Then review http://blogs.technet.com/b/askds/archive/2008/05/29/kerberos-authentication-problems-service-principal-name-spn-issues-part-1.aspx and other articles as you see fit.

DC01 will issue tickets provided that the KDC service is running and it can find the relevant SPn registered in its database and provided that it is not duplicated elsewhere in the forest. The value must be unique. "setspn -x -f" can be reviewed to check if the value of interest is a duplicate in the forest.