Any good whitepapers on security/deployment for entire SQL Server BI solutions?

At my current employer we are struggling with the best way to manage security and deployment of a project that contains databases, SSIS, SSAS and SSRS components, using configurations.

Environment (Dev):

3 SQL Server databases, all using mixed-mode security, using SQL Server security credentials.

12 SSIS packages; one master package, eleven child packages, 3 shared data sources

1 SSAS database; one cube, 15 dimensions, three referenced data sources from the SSIS project (in same solution)

6 SSRS reports, one data source to cube (not shared- doesn't appear SSRS can share datasources among other projects in the solution? Why?)

Everything runs fine in development. Now comes the tricky part.

Deploying SSIS and SSAS into production environments:

-Packages use XML config files for connection strings to three relational data sources.

-Deploy to SQL Server storage. Deploy wizard copies package dependencies (including XML config files) to default location set in INI file. When I do this, no config file shows up in remote server (remote server not set up identical to local, so directory does not exist. Need UNC path?) So, being a developer with no "special" permissions on the PROD server, what security permissions is allowing the deployment wizard from copying files to this location on a production server?

-Using a deploy script using dtutil doesn't copy the SSIS dependencies. Is this matter of using COPY or XCOPY to copy the configuration files to the dependency location? Again, in real-world practice, do developers typically change this location in the INI file to another location, or stick with the default. In either case, how does security work that allows files to get copied to the remote folder? (i.e. manual, or SQL Server manages this file folder permission through some other magic)

When using SSMS and running the package after being deployed on the remote server, if the config path is the default (e.g. C:\program files\Microsoft SQL Server\90\DTS\Packages\...) it appears to be read from the local machines directory rather than the remote machines directory path (do I need to use UNC paths? The wizard doesn't give this option it seems)

-When scheduling the job from SQL Agent, does the proxy account need permissions to the folder the config files sit in?

-What about the roles security on the packages themselves? Where does the server roles come into play (dtsltuser, dtsadmin)

-Because the SSAS project uses connection references to the SSIS project in BIDS, and SSIS project uses configurations, will SSAS pick up on these connections?

-What about impersonation levels for SSAS? Leave all data sources set to default, and set the database impersonation level to "UseServiceAccount"? What if the developer is not the same as the OLAP administrator on the production server? In this case, Use Service Account isn't an option, and neither is the current users credentials.

-SSAS database also has security for Full Control, but still doesn't prevent security at the data source level within the database (talking about impersonation level, not source db credentials)

-How can SSRS connections leverage other shared connections?

As you can see, there are a ton of security considerations, none of which are intuitive and can be configured multiple ways and actually work (and a ton of ways that won't work).

I need a simple cheat-sheet about each step to take to configure this so multiple developers can work without interruption, hot-deploying SSIS, SSAS, and SSRS changes into different environments (QA, PROD).

-Kory

It is still on my list to read so I'm not sure it contains the information you need but check http://www.microsoft.com/technet/prodtechnol/sql/2005/sql2005secbestpract.mspx

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

I've already read this- not much on the BI tools side, mostly for the RDBMS.

-Kory