Beginning of SQL Server Security

Security! This is the word comes in mind of every concerned person when it comes to store, access and share the data and database or database server. At times when applications are run in the geographically restricted area, there were less chance of its exploitation and with spread of internet and the availability of application over the internet; security of data, databases and database servers has become vital.

SQL Server Security has been so much important for every organization or institute or company or government has their data that is vital to them, and not to be accessed by the authorized person or entity only. And that is vital to set some security standards for such a critical data so that you can prevent your data as much as you can with proper security mechanism and set of standards within organization and in your scope for MS SQL Server Security.

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.

Securable 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 securable.

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:

Create Login SqlLogin with password='password@123'

To create a windows authenticated login, the T-SQL command is:

Create Login [<DomainName>/<UserName>] From Windows

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 User UserLogin From Login SqlLogin

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:

EXEC sp_addsrvrolember  SqlLogin, ServerAdmin

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

EXEC sp_addsrvrolember 'db_datareader','SqlUser

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.

Encryption:

The SQL server provides hierarchical encryption structure, as shown in the image below:

 

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.