Archive

Archive for October, 2011

BISQL # 67 – SQL Server Denali Feature # 5–Security–SQL Server Denali Security & Compliance

October 30, 2011 Leave a comment

Hi all

As we already started learning about few thing in my previous post

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

image

Comparing Denali features with SQL Server 2008 R2

Following some recap for SQL Server 2008 R2

image

SQL Server Denali features

image

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 :

  1. Configurable Server Roles
  2. Server Level Principal
  3. Avoid using SA account
  4. Triggered can be used for more notification

image

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

image

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

image

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.

image

Audit Resilience

Delivers the ability to recover auditing data from temporary file and network issues.

image

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

image

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

Link Resource Website

Categories: Query, SQL
Follow

Get every new post delivered to your Inbox.

Join 1,115 other followers

%d bloggers like this: