SQL Server Security Tutorial | SQL Server DBA Tutorial

SQL Server Security Tutorial from Coding compiler | SQL Server DBA Tutorial – Part 5, this blog is the fifth tutorial in the series, here you are going to learn about how SQL server security works. Before going to learn this tutorial, we recommend you to go through the previous SQL Server Restoration DBA Tutorial – Part 4.

Table of Contents

Topics Covered in this SQL Server DBA Tutorial

Security Basics
Levels of Security
- Server level
- Database level
- Object level
Security Processes
- Authentication
- Authorization
- Encryption

SQL Server Security

Security is the most important tasks for a DBA. we need control the access to sql server as well as provide security to the datal.

Levels of SQL Server Security

There are mainly 3 levels of security.

Server level
Database level
Object level

Server level – Is the topmost level in connecting to sql server.
Database level – Once connected to the server. We need to provide security to user at database level.
Object level – Once connected to database, we need to provide security on database object like tables stored procedures etc.,

SQL Server Security Processes

For providing security we have 3 processes

Authentication
Authorization
Encryption

Authentication, validating credentials on server authorization, permission to perform their operations.

1. Authentication:

Validating credentials (username and password) on server called authentication. For any user who are connecting to server will have login name and password.

There are mainly 3 levels of security.

Server level
Database level
Object level

Domain user, Database Administrators, other admins, developers will have windows login for connect to server. These user use same account for connecting to windows and sql server. Windows login store in windows component called Active Directory. These logins provides high security.

Sql login, for the users who are connecting externally from front end we are providing sql login. They first connect to windows and then connect to sql server. These logins stores system logins table of master database.
We need to provide security to sql server.

We have to validate both windows and sql logins. This process of checking login name and password is authentication.

There are 2 types of authentication in sql server:

Windows mode authentication
Mixed mode authentication

1. Windows mode

It allows only window login users in this mode. It enforces only windows user to connect and access the databases. When we use highly restrict database we go with windows login. We do not provide access to external users if the data is highly restricted. This mode provides best security, it validates logins in active directory.

2. Mixed mode

This mode allows both windows and sql logins. Whenever any login tries to establish connection first it validate username and password in active directory. If login exists, establishes connection otherwise validation goes to syslogins of master database and verifies there. If logins exists it will establish a connection.

Whenever any request comes first we need to verify the login active directory. If it is not exist we need to create (or) ask active directory admins to create command for windows.

Create login [domain\login name] from windows

Authentication at database level:

We require another account called user account to access the databases inside the server. This is a database level access. We need to grant access for these user account on each database separately.

User mapping:

Every login account will be mapped with the user account. Wevery login will have a security is (SID). It’s a unique security number inside sql server to avoid duplication of logins using the SID of login account we’ll map to SID of user account. Now it establishes relationship between login account and user account. This is user mapping, one login account will mapped to one user account only we call this as one to one mapping.

Once the user mapping is created between login and user account, sql server allows to connect to server and then to access the databases inside the sql server.

Creating windows login:
Creating login loginname from windows
Creating sql server login:

For creating any login, it requires 4 properties

Login name
Password
Default database
Default language (optional)

Using T-sql:

sp-add login ‘login name’ ‘password’ ‘default database’
Example: sp-add login ‘sql 2012 admin’ ‘sql@12345’ ‘master’

Using GUI:

Go to security folder under management studio → Go to login → Right click on login → Select new login

Domain users will set these password policies. These policies will be enabled at window server level. We are using same policies for sql login also. Once we provide require properties to login we can enable password policy. What is minimum password length and complexity.

Enforce password policy

We are using password policies which created at domain level

Enforce password expiration

When this login is to be expire. It expires after specified days

User must change at next login

Domain users create login and provides password we need to change the password in logging first time.
When user login next time sql server prompt a message to change the password.
Once all these details provided login will be created.

Authorization:

Once user connected to server, they should have permissions to perform / operating certain tasks. As a DBA we need to restrict the permissions by granting the roles, roles are set of privileges for connection server and to access the database.

Types of Authorization Roles

We have 2 types of roles

Server role
Database role

Server role – Server role to perform server side operations. We have 8 server roles and by default it will be public role.
Sysadmin: Top must privilege on the server, capable of doing any thing on the server. DBA’s will have this role.
Server admin: Allows users to manage configurations on the server capable of start and stop services from configuration manager, server settings, memory configurations and shutdown of sql server.
Setup admin: Setup admin capable of creating linked server. It can run any setup (utilities) like replication, mirroring
Security admin: capable of creating new logins, delete logins and reset passwords. It has ability to grant, revoke and deny permissions.
Process admin: Monitors the process running under task bar, capable of killing problematic process
Disk admin: Manages storage, if any new device need to add, it has permission to add new drive to the server.
Bulk admin: capable to performing bulk inserts and updates.
DB creator: This role is capable of creating databases. It can alter and drop. Taking backups and restore.
Public: The default role that server role consists of
Using T-sql: sp-add srv role member ‘loginname’ ‘privilege’ Example: sp-add server role member ‘em padmin’ ‘sysadmin’

Database roles:

DB – owner: DB owner is capable of doing anything on particular database. Highest privilege at database level. DB – security admin – He is responsible for creating/deleting/modifying roles to users and manages permissions.
DB – data reader – He can run select queries on all tables, only read activities.
DB – data writer – He can perform insert/update/delete data in the database.
DB – deny datareader – To restrict the read access on particular confidential data even though user has server level (or) high privileges at database level we can prevent that user in accessing particular database using deny role.
DB – deny data writer: He cannot able to write data on restricted databases.
DB – DDL admin: He can able to create/after/drop the tables and other objects.
DB – access admin: He cannot able to create (or) modify any user role. He can grant only roles and remove access to other users.

Backup operator: He can able to take backup of databases

Public: This is default role on database level.

Using T-sql command – sp-add role member ‘username’ ‘rolename’

Example: sp-add role member ‘Testadmin’ ‘DB-owner’

Using GUI:

Flexible roles are the roles to create group of users in terms of functionality.

Sql server 2012 introduces contained databases,

There are 2 types of Authentication in this,

Contained databases users with passwords, stores all the passwords with in contained database.
Contained databases windows is similar to windows login.

Object level:

We need to identify 3 things in providing object level access.

Principles are login accounts to whom we are rating permissions. Login accounts and user account comes under principals.
Privileges, how we granting permissions to access objects.
Securables, on which object we are granting tables, stored procedures and views.

Through T-SQL

Grant/revoke permissions on securable to principals.

Example: Grant insert on dbo.sqltest to empadmin

Through Users:

Go to users under database → go to properties → go to securables → select objects from search → object types provides list of objects tables, views etc.

Select them  → provide permissions and click OK.

Encryption:

Using encryption we can protect the confidential data while transferring data through networks. It restricts hackers to access data.

Encryption using certificate.
Encryption using asymmetric keys.
Encryption using symmetric keys.

Certificate encryption is used to identify users, devices and organization certification authorities generate these certificates to provide the securing sql service will use these certificates and encrypt data.

Asymmetric keys uses a public key that encrypt data before sending data private decrypts that message after receiving. Here we have 2 keys in encryption.

Symmetric key uses same key to encrypt and decrypt the data.

Transparent data encryption(TDE):

TDE is a sql server 2012 enhanced feature that allows encryption of data in better way.

SQL Server Security Tutorial – Part 5