Summary

The goal of this chapter is to highlight the most important security issues for a DW/BI system, and to help you figure out where and when to secure data. We couldnt possibly cover all the security features of SQL Server, nor all the details of how to implement those features. We hope merely to have provided you with the tools to develop a security plan and security test plan.

One of the most important steps you can take for a secure DW/BI system is to identify which DW/BI team member is in charge of security. That security manager will drive the development and implementation of the security plan.

The easiest pieces of the security plan have to do with physical security and operating system security. Theres lots of information available about how to secure servers and the Windows operating system. You just have to commit to doing it.

The harder question, and the one to which we devoted most of this chapter, has to do with securing the data. As we described, securing predefined reports in Reporting Services is easy, painless, and effective. Securing data for ad hoc analysis is a harder problem. Youll definitely find it easier, and the user experience much better, to define security rules in Analysis Services than in the relational database. Analysis Services security features make this a fairly straightforward task for a wide range of requirements. And because security rules are defined in MDX, even very complicated scenarios are feasible .

Finally, we struggled with how to set up the relational database security to support direct ad hoc access. Its possiblepeople have been doing it for years but its hardly as easy or satisfactory as wed like.

The SQL Server documentation in Books Online has a strong emphasis on security features. Its as if Microsoft executive management made security a priority. But the security documentation is scattered across the different components , and it isnt always easy to find the information you need. You might be able to cut corners in some aspects of system development, especially if your data volumes are small, but everyone needs to be careful about security.