Security in SQL Server – Part 1

The estimated reading time for this post is 3 minutes

Microsoft SQL Server is a Relational Database Management System (RDBMS) that contains few components and subcomponent. Each component in SQL Server has its own security mechanism which is almost separated from each other. Database Engine security includes Service Account, Instance and databases security.

Most of developers and IT professionals only think encryption as only mechanism to secure their data or application. Security in SQL Server environment is not limited to encryption; SQL Server should have physical security to protect whole server rack as well against any vulnerable issue and also SQL Server instance root security. Security in SQL Server can be Authentication and Authorization of users against data and SQL Server instance, Always remember to make a secure environment for your SQL Server, you should have security in Design, Develop and Deployment phases of your software and databases. Most of security issues come out from Design and Development phase.

SQL Server Service Account

Microsoft SQL Server is a user mode application even though it runs under windows service. Service Account is completely a windows user account and it provides security privileges for the SQL Server service to access Windows Platform resources such as File System, Network and Registry. The most important point is SQL Server Service Account should not be shared with other SQL Server Instances and it is preferred to be in a Domain Controller (DC).

SQL Server Security Layers

Microsoft SQL Server has multiple security layers, the first layer is the communication channel security which is applied by TSL or SSL protocol, The second layer is in the instance level which is protected by Windows Data Protection API (DPAPI), DPAPI is a built-in encryption function in windows platform to encrypt and decrypt data and the algorithm is different in every single machine. Logins, Server roles and Credentials are instance level security objects in SQL Server and User, Certificate, Roles, Schemas and Encryption Keys are database level security objects. Below figure shows the SQL Server security layers.

SQL Server Service Key is the base encryption key in SQL Server platform and it is protected by DPAPI, Service key is always created by SQL Server process during the first startup and it cannot be created by user, User has ability to backup and restore it in the same instance or other instances. Master key is an optional key in every database and it can be protected by Service key or a strong password which is provided by user. Master key can be backed up by user and be restored in the same database or other database. Regenerating Service key will regenerate all sub keys such as Master and Encryption keys (Symmetric or Asymmetric).

SQL Server Encryption Algorithms and Mechanism

Encryption algorithm is vary in SQL Server and depends on the encryption key, not every encryption key supports all algorithms in SQL Server. SQL Server can support encryption algorithms such as AES, AES_128, AES_192, AES_256, DES, Triple_DES, RSA_2048, MD5, SHA1, SHA2_512 (Introduced in SQL Server 2012) and RC4.

SQL Server provides a feature to encrypt database for preventing stolen database to be read. Database encryption is an optional option in every database. SQL Server does not encrypt whole database at same time when the feature is enabled on a database, SQL Server will encrypt every page when the “Lazywriter” process needs to write the page into the disk and decrypt it when it reads the page from disk. Database encryption feature called “Transparent Database Encryption” and it is only available in “Enterprise” editions and it introduced in SQL Server 2005.

In the next series of blog posts, I will explain more about different aspects of SQL Server security features. 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

Be the First to Comment!

Notify of
avatar
1000
wpDiscuz