Security is a very important concept and no matter how many times we discuss this it is never enough. I have requested my friend Bharti who is expert in SQL Domain to help me write a simple post about beginning SQL Server security. I personally found it very easy read and good beginning on this subject. Bharti Batra is Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.
Today all organizations have important data that is confidential and mission-critical, and should not be disclosed to unauthorized persons. Hence the security of data is one of the prime aspects of a DBA’s job.
We will discuss about the SQL server security model and different features that SQL provides for data security.
Security means that only relevant data should be available to authenticated persons. It additionally includes appropriate restrictions on the use of data, as well as controls to prohibit data theft. In case data is stolen by unauthorized users, security systems do not allow them to make use of it.
However it is not sufficient to just secure the SQL server. The operating system’s network security is also important. In addition, physical access to the server must be restricted and the application which accesses SQL server must be authenticated.
The security model has five layers, which are Physical security, OS and Network security, Database security, Data security and Application Security. Below mentioned are the specifications for each one of them.
Physical security: Physical access to server should be restricted. This can be done by guards outside the server room, biometric locks like retina scan or finger print reader.
OS and Network Security: Provide only restrictive or no access to server over network. Use firewall to restrict unauthorized access from network. Use less privileged service accounts. And SQL server data files, logs are stored on windows file system. So restrict access to these file locations. Always keep your system up to date with service packs and patches.
SQL Server Security: Access to SQL Server must be restricted by authenticated users. SQL server supports two authentication mechanisms: – Windows authentication and SQL Server authentication.
Windows authentication is secure for most environments. With this we can grant login rights to the database to a Windows login or a Windows group.
In SQL Server authentication, the user has an SQL login that is not mapped with any Windows login.
There are some important terms in SQL server security, which include:
Principals are that require access to SQL server or database, to which we grant permissions for example: logins, roles etc.
Securables are items we secure or ones on which we grant permissions to principals. For example, database, schemas, table etc. The below figure gives you details about the principals and securables.
Refer the Permissions Hierarchy Image
Login: At server level we have logins, which give access to a user to SQL Server. Server level permissions are assigned to logins. Login can be windows login or SQL server login, depending upon the authentication mechanism you are using.
Example: Creating a login
To create an SQL login named SqlLogin with password ‘Password@123’ by T-SQL, the command is:
To create a windows authenticated login, the T-SQL command is:
To create a login from Management studio
Open object explorer and then go to security folder at instance level and then right click login to create a new login
Fill the form to create a new login
Users: The logins are mapped with users in database and the database level permissions are assigned to users. There are some predefined user present in every database which are dbo, guest. ‘dbo’ is the default user for sysadmin login.
Example: Creating a user
Create a user by T-SQL command:
Create a user by Management studio
In object explorer, go to the security folder inside the database folder and right click users for new users
Fill the form
Roles: There are some built-in roles present at server and database level. These roles contain predefined set of permissions so if you want to assign specific set of permissions to a login or user, make them the member of a role. There are two types of roles:
Fixed server roles: These server roles are predefined and you cannot create a new one.
To make a user member of Server Role, T-SQL command is:
Database Level Roles: At database level, we have some predefined database level roles. However users can create their own database roles.
To make a user member of Database Role, T-SQL command is
By these means we can restrict access to our database, however placing restrictions is not enough because data can also be stolen. Critical data must be encrypted for security so that even if it is stolen, it cannot be used. SQL server encryption mechanism to encrypt the data by using certificates, keys.
The SQL server provides hierarchical encryption structure, as shown in the image below:
Refer the Encryption Hierarchy Image
The top key, i.e. service master key is encrypted with windows Data Protection API (DPAPI). And at database level we have Database Master Key that is encrypted with the Service Master Key. So the encryption in SQL Server is layered where the upper layer encrypts the layer below it. Data is encrypted by keys, Symmetric (Public key) or Asymmetric (public, private key pair) and these keys get encrypted by certificates, which are encrypted by database master key. Data can also be directly encrypted by certificates that have more information than keys like the name of issuing authority, subject and expiry date.
Data can be encrypted, and the keys and certificates are in the database. But what if the backup file or mdf file gets stolen?
For that we have Transparent Database Encryption (TDE), which is a real-time IO encryption that uses certificates and Database encryption key. This is done for encrypting the database, so in case the backup or mdf file is stolen, it cannot be used fully without the certificate.
There are other important considerations too. For example, just applying the security is not enough. Auditing also holds huge significance. SQL server provides SQL Server Audit for auditing purpose. SQL Server Audit can track and log events that occur at the server level or the database level automatically. With SQL server audits, several server and database level actions can be tracked. These include failed login attempts, modification in structure of database or table etc. These can then be logged in Windows event log or a file system file.
The author of the article is Bharti Batra, Trainer, SQL Domain, Koenig Solutions. Koenig is a premier IT training firm that provides several IT certifications, such as Oracle 11g, Server+, RHCA, SQL Server Training, Prince2 Foundation etc.
Reference: Pinal Dave (http://blog.sqlauthority.com)