July 26, 2012 at 11:53 am
we have :
1. report server - RS1 (contains both reportmanager and database)
2. db server - DB1 (used as datasource)
3. 2 report writers - NT logins : COMP\ben & COMP\jeff
4. AD group - COMP\reportwriters , containing above 2 windows logins
we want:
1. Both report writers should be Content Manager on RS1 (so they can deploy reports)
- this can be done by adding COMP\reportwriters as Content Manager by going to http://RS1/reports
2. Both should also have read-only access on DB1 (so they can test reports)
- this can be done in 2 ways:
----a. create sql login 'userRS' on DB1 as datareader and give them uname/pwd
----b. create new windows login 'COMP\winuserRS' on DB1 and give them uname/pwd, and choose 'Use as Windows credentials when connecting to the data source' when storing the credentials
- both these options can be used to connect to DB1 either via SSMS, or via report credentials when creating the report
3. Regular people (under group COMP\reportusers) should be able to run these reports, without being prompted for uname/pwd.
But they should not have direct read access to DB1 (This rules out creating NT login for every report user on DB1 as datareader) - Btw, even tried creating the logins, but report does NOT work, due to double-hop Kerberbos thingy - gives NT Authority\Anonymous Logon Failure.
Problems with above :
2a(sql logins) - we want avoid sql logins, and move towards windows logins
2b(windows login) - we want to avoid giving passwords to report writers because they are contractors - we don't want to reset pwd often.
Question:
can DBAs create (one-time) a Windows stored credential uname/pwd on RS1 (e.g. COMP\winuserRS), that the report writers can pass as credentials for whatever datasources/reports they create?
August 28, 2012 at 3:32 am
Hey,
Not sure I follow you 100% so I'll suggest something but feel free to provide more info or correct me.
Ok, so I'd suggest you use your existing AD Group (COMP\reportwriters). Give this group datareader access to the database DB1. You can then get the developers to use their windows AD accounts for datasource connections etc. Using the same group as contentManager in RS Report Manager too.
This should allow them to view data in the DB safely. If your datasources are then set to use Windows integrated security you just use the (COMP\reportusers) AD group in DB1 with read access to make sure regular users can read data.
Also I'm assuming RS1 is an instance of Reporting Services pointing to DB1 or does RS1 host the ReportServer DBs? (ie does RS1 have the DBEngine too?)
If so, I'd recommend creating a linked server on RS1 to DB1 and use an AD account to connect them (no one will need to know the credentials) Then create your groups as above on RS1. Any stored Procs created or queries will then reference DB1 through its linked server.
ie. DB1.DatabaseName.dbo.Tablename
Anything i'm missing? hope that helps
Ta
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply