The addition of DTS to SQL Server meant that more and more of us could grab data from a variety of sources, easily, and move it to SQL Server systems. SSIS enhanced our capabilities and made it fairly simple for non-programmers to grab web service data, and other formats, and quickly decompose them into relational formats. This made it more likely that we would build data warehouses or reporting systems, and easily keep them up to date with data from our OLTP systems.
However the ease of using ETL (extraction, transformation and loading) processes to move data did not include the moving the same security controls and restrictions to these new systems. That can create a big security holes, especially when you have secondary systems used for decision support that might not be contained in the production network and not audited to the same level as other production systems.
Whenever you move data around, there ought to be some set of guidelines for the security protocols required. That can be hard to track when you are pulling data from a secure system and your connections look like every other connection. One of the dangers that I suspect we will run into with Powerpivot is that secure data will be downloaded to Excel worksheets on insecure systems, and all of the controls that we have in place for protecting sensitive information are ignored when we pull data for analysis elsewhere.
Our data security has gotten better, but we have a long way to go. If you are working with ETL processes, and especially with end-users, make sure that some sort of security policy is in place to help protect this data from being accidently disclosed. It’s not a great suggestion, but until we have better security tools to allow policies to follow the data, that is all we can do.
(originally published at http://www.sqlservercentral.com/articles/Editorial/72157/)