Security Structure For Dummies
-
Hi,
OK, I admit it, I know nothing... Nothing nothing nothing nothing nothing. At least, that's how I feel when I try and troubleshoot SQL Reporting Services Security.
So first question - is there any decent tutorial out there that tells you how SQL Server, IIS and Report services security actually work together in terms of what is requesting what from what and when? Sure I can find tutorials about one, but not all three?
My problem seems pretty basic, I can build and deploy reports but I can't access data sources from the report server machine. I can create reports for the local database no problem, but can't seem to hop from the report server to any other DB. For testing I've built three data sources, Native, OLEDB and ODBC, here's the results from each one :
Native - The 'SQLNCLI' provider is not registered on the local machine.
(note I've changed the provider name from SQLNCLI.1)
OLEDB - 'DATABASEPROPERTYEX' is not a recognized function name.
(OK, this is because I'm accessing an SQL 7.0 server and I'm thinking OLEDB isn't going to work full stop as DATABASEPROPERTYEX is 2000 functionality)
ODBC - OK, this kind of works, but the parameter passing doesn't, I get the error ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'sp_lt_REPORT_Daily_Production_Report_by_Laser_DAILY_Optimised' expects parameter '@DateFrom', which was not supplied.
But the parameter passing does work internally in the report, because it gives me an error if I don't put a parameter in, telling me @DateFrom is not a valid type.
OK, so everything works from Visual Studio for both native and ODBC clients on my XP laptop. I have to admit I don't really understand how/why SQLNCLI isn't advertised on the report server machine, but I know the correct ODBC name is advertised as a System DSN.
I've tried upping the security on the IWAM and ASPNET users to admin to get rid of them as issues (don't worry, I have a very meaty firewall) and frankly I've run out of ideas.
If only I had a simple diagram showing me how all the parts of this solution interact..........
Any Help Appreciated.
Thanks
Rich
-
This was removed by the editor as SPAM
-
Hi,
during an analysis about setting up connections from Business Objects to MSAS I came across the following links helping me to get a clearer picture about security and autjorization.
http://support.microsoft.com/kb/828280/en-us
Concerning your data sources, you might try to dig deeper into impersonation or the use of database account information stored within Reporting Services. Which one you will be able or want to use depends on other security criteria. I have developed a solution where the access rights were checked only on a per report basis and we were able to use one distinguished account for all our database connections. This one we stored in SSRS and thus circimvented the 'more than one hop'-problem.
I remember the Reporting-Services step by Step book to contain some very valuable information about that topic as well, but cannot provide any link to that apparently.
Regards,
Michael
-
Thanks for that Michael, very useful.
As it happens I managed to work it out in the end (didn't post because I felt a bit dumb and it had been a while with no replies).
In case anyone has the same problem (I doubt anyone is as dumb as me) but here goes :
The only direct connection I could get to SQL 7.0 was ODBC
But the parameters wouldn't work with this, for reasons I forget.
So I set up a linked server on my SQL 2000 server.
Here's the dumb part - I made the incorrect assumption that I would need to use an ODBC connection to this server. Wrong.
In reality the linked server has an OLEDB link between SQL 2000 and SQL 7.0.
ODBC requires unnamed parameters, OLEDB requires named parameters, therefore I was going through an ODBC datasource to SQL2000, which was then making an OLEDB call to my SQL 7.0 server. Of course it couldn't translate the parameters...
Setting up an OLEDB datasource to the server sorted that out.
As it happens I'm not making two hops anyway, so the Microsoft article is irrelevant in this case (although still useful reading). I'm actually making a call to the SQL 2000 server, then the 2000 server is using the linked server security to make the call itself (I built a low rights user just for the link then hardcoded it into the link).
Thanks for thinking of me anyway
Rich
-
Hello All,
Don't know if this information on SSRS security is still needed, but here you go. I am hoping this will help someone somewhere and don't have to go through the same pain I been through.
I will try to make it very simple as I don’t need to impress anybody here and I personally don’t like when people try to make things complicated just so they look more intelligent 🙂
Here are few things/steps you should follow to successfully setup security on you SSRS server:
1 – Setup a DNS alias (e.g. http://contosoreports)
2 – Setup a service account/ID (Domain user account). This account will run SSRS service as well as your web app pools under IIS
3 – Make sure this ID can run as a service on the SSRS BOX (some companies block this via group policy)
4 – Make sure this ID can run as batch process (some companies block this via group policy)
5 – Make sure this ID has interactive logon right on the BOX (don’t ask why **Microsoft** hint hint)
6 – Make sure this ID is setup for “Delegation” in Active Directory
7 – Setup the following SPNs
SetSPN –A HTTP /contosoreports.domainname.com \userID
SetSPN –A HTTP /servername.domainname.com \userID
SetSPN –A HTTP /servername \userID
NOTE: YOU WILL ALSO NEED AN SPN SETUP ON EVERY DATASOURCE YOU ARE GOING AFTER
8 – Make sure “Integrated Windows Authentication” checkbox under IE advance is checked (This is necessary for Kerberos to work)
Following these steps should successfully deploy your SSRS security with Kerberos.
Murad Akram
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply