Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite
1

Before I just go ahead and put a really awful check constraint on the Catalog table, I wanted to solicit some better ideas first.

I want to ensure that all shared data sources on our report server are deployed to "/Data Sources". Every now and then, we get one mistakenly deployed to some other directory (particularly if it's a report upgraded from SSRS 2000, which didn't allow specifying a different data source deployment location).

I can put an ugly check constraint on Catalog (Type != 5 OR ParentID = 'GUID of /Data Sources directory', or similar) if it comes down to it, but if there's a better option, I'd rather use that.

share|improve this question
2  
+1 Personally I've used constraints in the past and they seem to work, but I'd love to see what others have done too. – OliverAsmus Apr 11 '12 at 13:27

3 Answers

up vote 4 down vote accepted

Why not change permissions so that folk can't deploy datasources to anything other than this folder?

So, remove "Manage data sources" from all folders except for /Data Sources. This can be done at root level and then set custom permissions on /Data Sources

You may need to setup a custom role for this if you can't change the existing ones.

share|improve this answer
Yup, use Reporting Service's built in security management. – DForck42 Apr 12 '12 at 16:09
Ah ha, I always forget I can customize the roles, since you do it from SSMS, and it's not right in front of me in Report Manager. I took "Manage Data Sources" out of the "Content Manager" role, and put it in a new "Data Source Manager" role. Then I only granted that role at the data source directory. I think that'll do the trick... ("Manage Resources" doesn't imply "Manage Data Sources", does it?) – db2 Apr 13 '12 at 12:27
@db2: IIRC, "Resources" are images and things: not reports and not data sources and not folders (which are explicitly mentioned) – gbn Apr 16 '12 at 6:53
up vote 1 down vote

Just to throw an idea out there - in our environment, the report developers have access to the source database but the report users do not. All of our data sources are deployed to a single folder and a service account is used for authentication of that data source.

The report developers do not know the service account password.

So with this set up, if a report developer deploys a data source somewhere else, the report won't work for report consumers, just other developers. (Technically, they must always use "do not overwrite data source" or all the reports are messed up until the administrator modify the "new" data source with the service account.)

Hope this helps!

share|improve this answer
1  
That's actually very close to our setup. All the production data sources use a predefined account (usually "ReportingServicesData", with very specific read-only permissions), whereas development is done with Windows authentication. This protects you from trojan horse reports being deployed and then run by somebody with administrative rights. (Slip CREATE LOGIN secret_admin ... into a report query, then trick somebody with sysadmin into running it...) – db2 Apr 11 '12 at 16:45
So what's the danger if a data source is deployed to the wrong place? That is, why the need to be proactive instead of reactive? Or is that just a natural lean towards the "best practice"? :) – JHFB Apr 11 '12 at 16:57
Mostly just to keep the report server from becoming a god-awful mess. Over the years, I've noticed heavy entropy if our server is left unchecked (especially with regards to directory and report permissions). The only danger in this case would be reduced manageability. – db2 Apr 11 '12 at 17:44
up vote 0 down vote

I don't think MS actually guarantee anything about the underlying SSRS database, so you're getting into unsupported territory by actually frigging with that. Generally this sort of thing is best avoided on production servers.

You can query data sources and other report server metadata programatically through a web service API exposed by SSRS. The API would allow you to tree-walk the folders hunting for shared data sources in places they aren't supposed to be.

Hunting and exception reporting is probably the best way to deal with this, rather than attempting to prevent it at the source. You can also query the report metadata to hunt for reports with references to sources outside the designated areas. This allows you to educate the authors.

MS provide a utility called rs.exe that lets you write scripts to do this in VB.NET. Later versions might also support C# but I haven't done this with anything more recent than SSRS 2005. Essentially it tops and tails your script with a bit of boilerplate, compiles it and then executes it.

Some documentation about the rs.exe utility and SSRS web service API can be found here, here and here.

share|improve this answer
1  
Not a bad idea, but I'd prefer more of a proactive than reactive approach. Though I like the idea of searching for reports with broken data source references. I'll have to look into trying that - I just cleaned up a bunch by hand. – db2 Apr 11 '12 at 14:03
@db2 you could set up a job and run it on a nightly basis to pick this up. Note that you're enforcing a policy, not an invariant with hard-coded dependencies. – ConcernedOfTunbridgeWells Apr 11 '12 at 14:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.