Security Features (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions

  • Article

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

There are many features that make up the security features of Microsoft SQL Server, including but not limited to auditing, policy-based management, and Transparent Data Encryption (TDE). But security of the database is only one aspect of securing the platform and the application as a whole. Security features beyond the database range from firewall, anti-malware, antivirus programs, and Windows security updates to end-to-end application auditing and packet sniffing. A very distinguishing capability is that Microsoft builds many (if not all) of these listed systems listed—the implication is that if we coordinate more closely with other teams, we can build more end-to-end secure environments by using each other’s mechanisms.

Best Practices

The following resources provide some general information about compliance and security features.

  • Data warehouse applications often require special consideration when implementing security measures. A popular topic is Row Level Security,1 wherein a more complex layer of security beyond authentication is needed to restrict access to specific rows of data. This is an excellent step-by-step guide for how to set up row-level security in SQL Server.

  • The web site SQL Server 2008: Compliance2 is the main site for information about SQL Server compliance, including an overview of governance. The following sections on the site are of particular interest:

    • Encrypting database data. Guidance and references for protecting sensitive data using encryption.

    • Auditing sensitive information. Guidance and references for monitoring database events.

    • Securing the platform. Guidance and references for securing the platform, end to end.

    • Using policy-based management to define, deploy, and validate policies. Guidance and references for using policy-based management to address compliance requirements.

    • Controlling identity and separation of duties. Guidance and references about the basics of identity and access control in addition to the policies surrounding the separation of duties.

  • The white paper Reaching Compliance: SQL Server 2008 ComplianceGuide3 includes a deep dive into understanding compliance and its impact through regulatory requirements and organization policies.

  • The Enterprise Policy Management Framework4 (EPM) is a CodePlex project that provides an end-to-end working framework for using SQL Server Policy-Based Management features to reach compliance goals. A key contribution of the EPM is that it allows the inclusion of SQL Server 2000 and 2005 servers into the framework.

  • The Centralized Auditing Framework5 is a CodePlex project that provides an end-to-end working framework for using SQL Server XEvents-based auditing feature to reach compliance goals.

Case Studies and References

Industry standards compliance examples include:

Questions and Considerations

  • Clearly understand how data warehouse security is implemented, especially with regards to the topic of row-level security.

  • As noted in Governance #39, the regulations and organization policies ultimately help define what IT security features are deployed and required for tier-one enterprises to be compliant.

  • Understanding compliance governance requirements allows you to determine the necessary IT features. It is important to research the specific local requirements in each location that the organization operates in.

  • An important consideration is how does Microsoft get them to all work together? A potential solution is to work with outside vendors to provide end-to-end compliance solutions using SQL Server security features.

  • Note that to truly secure the database, the entire platform must be secure.

  • This leads to the question that will require Microsoft-wide executive commitment—since Microsoft creates the software and platforms for these systems end-to-end, shouldn’t we be able to create an end-to-end security solution for the entire platform? This would provide tier-one enterprises with a regulatory incentive to use the Microsoft stack.

Appendix

Following are the full URLs for the hyperlinked text:

1 Row Level Security http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

2 SQL Server 2008: Compliance https://www.microsoft.com/sqlserver/2008/en/us/compliance.aspx

3 Reaching Compliance: SQL Server 2008 Compliance Guide https://msdn.microsoft.com/library/bb326717.aspx

4 Enterprise Policy Management Framework https://www.codeplex.com/EPMFramework

5 Centralized Auditing Framework http://sqlcat.codeplex.com/wikipage?title=sqlauditcentral&referringTitle=Home

6 ParenteBeard: Deploying SQL Server 2008 Based on Payment Card Industry Data Security Standards (PCI DSS). http://www.parentebeard.com/Uploads/Files/Deploying_SQL_Server_2008_Based_on_PCI_DSS.PDF (If link does not open when clicked, copy the following URL into a browser. http://www.parentebeard.com/Uploads/Files/Deploying_SQL_Server_2008_Based_on_PCI_DSS.PDF)

7 TechNet Webcast: SQL Server 2008 Capabilities for Meeting PCI Compliance Needs http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032404174&CountryCode=US

8 Beth Israel Deaconess Medical Center: Major Hospital Enhances Auditing Infrastructure using SQL Server 2008 https://www.microsoft.com/canada/casestudies/Case_Study_Detail.aspx?casestudyid=4000003892

9 TechNet Webcast: Supporting HIPAA Compliance with SQL Server 2008 http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032441700&Culture=en-US

10 Jefferson Wells: Supporting HIPAA Compliance with Microsoft SQL Server 2008 http://www.jeffersonwells.com/mssql2008hipaa