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.
