BISQL # 67 – SQL Server Denali Feature # 5–Security–SQL Server Denali Security & Compliance
Hi all
As we already started learning about few thing in my previous post
- BISQL # 49 : SQL Server Denali Feature # 1–FileTables #1 – Introduction & Theory
- BISQL # 50 : SQL Server Denali Feature # 1- FileTables # 2 – Demo and Scripting
- BISQL # 51 : SQL Server Denali Feature # 2 – Sequence number Explanation !!
- BISQL # 53 : SQL Server Denali Feature # 3 – AlwaysOn (Everything)
- BISQL # 66 : SQL Server Denali Feature # 4 – Columnstore Indexes –Explanation Columnstore Indexes for Fast Data Warehouse Query
Now lets dig into Security and Compliance w.r.t SQL Server Denali
We are going to cover following points in this article
1.Introduction to new Security feature
2.Comparing Denali features with SQL Server 2008 R2
3.SQL Server Denali features
4.PROTECT DATA
- Crypto Enhancement
5.CONTROL ACCESS
- User Defined Server Roles
- Default schema for group
- Contained Database Authentication
- SharePoint Active Directory
6.Ensure compliance
- SQL Server Audit for All Editions
- Audit Resilience
- Crypto Enhancement
- Audit Filtering
- User-Defined Audit
Introduction to new Security feature
In this article we are going to cover things related to Denali Security
As we know bit of old story regarding SQL Server 2008 R2
Comparing Denali features with SQL Server 2008 R2
Following some recap for SQL Server 2008 R2
SQL Server Denali features
Various Feature related to Denali are
-
PROTECT DATA
-
CONTROL ACCESS
-
ENSURE COMPLIENCE
Now lets see one by one in detailed explanation and features
PROTECT DATA
Crypto Enhancement
They have greatly enhance SQL Server cryptography such as the ability to create certificates from bytes, default for Server Master Key (SMK), Database Master Key (DMK), backups key using AES256, new support for SHA2 (256 and 512), and usage of SHA512 for password hashes.
It is built on top of great SQL Server features to achieve the following:
- Create certificate from byte
- 4K Certificate support
- Password hashes use SHA512
CONTROL ACCESS
User Defined Server Roles
User-Defined Server Roles increase flexibility, manageability, and facilitate compliance towards better separation of duties. It allows creation of new server roles to suit different organizations that separate multiple administrators according to roles. Roles can also be nested to allow more flexibility in mapping to hierarchical structures in organizations. It also helps prevent organizations to use sysadmin for database administration.
Benefits from this feature :
- Configurable Server Roles
- Server Level Principal
- Avoid using SA account
- Triggered can be used for more notification
Default schema for group
Database schema can now be tied to Windows Group rather than individual users to increase database compliance. It eases administration of database schema, decreases the complexity of database schema management through individual Windows users, prevent errors of assigning schema to the wrong users when users changes groups, avoids unnecessary implicit schema creation, and greatly reduces the chance of query errors when wrong schema being used.
Benefits from this feature :
- Schema tied to group rather than individual
- Prevent error from wrong accessing user
- Prevent unnecessary schema creation
- Reduce chances of wrong schema used in query
Contained Database Authentication
Contained Database Authentication increases compliance by allowing users to be authenticated directly into user databases without logins. User information for login (username and password) is not stored inside master database but user databases directly. It is very secure because users can only perform DML operations inside the user databases and not database instance level operations. It also reduces the need to login to the database instance and avoid orphaned or unused logins in the database instance. This feature is used in AlwaysOn to facilitate better portability of user databases among servers in the case of server failover without the need to configure logins for all database servers in the cluster.
Benefits from this feature :
- Easier to deploy
- Tightly scope under security
- Better Separation between administration and user
- Reduced orphaned
SharePoint Active Directory
Help secure end user data analytics with built-in IT controls, including new SharePoint and Active Directory security models for end user reports published and shared in SharePoint. Enhanced security models provide control at row and column levels.
All are built on top of great SQL Server features to achieve the following:
- Enforce password policies
- Use roles and proxy accounts
- Provide security enhanced metadata access
- Enhance security features with execution context
Ensure compliance
Ensure compliance with company policies and/or government regulations like HIPAA and PCI.
SQL Server Audit for All Editions
Allows organizations to expand the benefits of SQL Server Audit from Enterprise edition to all editions for more thorough auditing practices across SQL Server databases enabling audit standardization, better performance and richer features.
Audit Resilience
Delivers the ability to recover auditing data from temporary file and network issues.
Crypto Enhancement
They have greatly enhance SQL Server cryptography such as the ability to create certificates from bytes, default for Server Master Key (SMK), Database Master Key (DMK), backups key using AES256, new support for SHA2 (256 and 512), and usage of SHA512 for password hashes.
It is built on top of great SQL Server features to achieve the following:
- Create certificate from byte
- 4K Certificate support
- Password hashes use SHA512
Audit Filtering
Provides greater flexibility to filter unwanted events into an audit log.
All are built on top of great SQL Server features to achieve the following:
- Automatically apply software updates
- Configure the surface area with automated Policy-Based Management
- Enhanced auditing with the SQL Serve Audit
- Create custom auditing solutions with DDL triggers
User-Defined Audit
Allows application to write custom events into the audit log to allow more flexibility to store audit information.
So we can say ..SQL Server Denali brings additional flexibility, usability for auditing and security manageability across SQL Server environment to help making it even easier for organizations to meet compliance policies
Hope this explanation is useful for you !!
Thanks for visiting my blog !!
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
SQL Server Mentalist 