An SQL Server provides a security architecture that has designed and allow database administrators and also developers to secure their database applications over a threat. In an Updated Version of SQL Server, it improved and new features and functionality. Authentication and Authorization has major role in SQL Server.
SQL Server is one of the Most Popularly used database domains. It carries broadly millions of databases. Each Organization prefer uses database based on their usage and data.
In order to protect and secure the data certain security has to be taken.
1.Physical Envirnoment.
2.Operating system.
3.Network.
4.Application.
5.Surface Area.
6.Server Level and Database Permissions.
7.Authentication and Authorization.
8.Password Policies.
In SQL Server Physical Environment maintenance plays a crucial role. Assume that having SQL Server the maximum-security level has to be done but by leaving all physical location of a database server comes to its security.
To ensure this a specific steps to be taken along with adequate control and authorized users has to give a physical access over the servers.
Securing the Operating System over the SQL Server Instance also validates the security.
To Access an SQL Server instance's both data and log files by gaining access to our data. The Following Steps to be taken for OS
(i) Everytime OS has to be updated with latest patches and service packs.
(ii) Always Follow the least privileage principle for all service accounts.
(iii) Access to SQL Server has to be for proper and particaular files only.
The Access of any data within an Organization can only be done with Network. Different Organization has usage of Network's ad Database servers, Application servers, Storage Area Network(SAN).
The Access of Network must be secure and proper restriction has to be done. Unauthorized sources should not allow flow to basic users.
The firewall has to blocked by placing firewall between database and Internet.
While if we consider SQL Server user have to follow certain recommendations to configure windows firewall.
As Database Security is considered it’s important to secure Your SQL Serve. It also states that a secure to the applications should be provided.
The Data flow takes plcae between Server and applications.So thes entites has to be secured.
(i) Mainly Database Server and Instance.
(ii) Client Server (or) Application Server.
(iii) Network connection provided.
The Above Discussed Factors come under all External Factors For SQL Server Security.
Surface are configuration in SQL Server allows by stopping or by disabling any components and features which are not secured.
When disabled these features user can limit surface of any SQL Server which is also subject to potential attacks.
The surface are configuration options can be done as:
sp_configure 'show advanced options',1;GO
RECONFIGURE;GO
sp_configure;GO
sp_configure 'show advanced options', 0;GO
RECONFIGURE;These Major Surface options has to be taken an disabled.
(i) Ad Hoc Distributed Queries.
(ii) Database Mail XPs.
(iii) Cross DB ownership chaining.
(iv) xp_cmdshell.
Server Level permissions has to granted permissions as on their instance-Level.
There are Different server roles provided in SQL Server 2016:
(i) serveradmin.
(ii) securityadmin.
(iii) processadmin.
(iv) setupadmin.
(v) bulkadmin.
(vi) public.
(vii) sysadmin.
Authentication and Authorization are the Main role in SQL Server.
While Installing SQL Server it can be Done Windows Authentication Mode and Mixed Mode.
SQL Server gives an option that authentication mode at any moment via SQL Server instance.
Other Security Considerations that come under authorization category:
(i)Public database roles
(ii)Orphaned users
(iii)Gust user permissions
Password policy enforcement i.e. complexity and strong password has to be done.
Password policy and Password Expiration are the both options provide by SQL Server 2016.
It is essential that to keep track up-to-date with latest service pack and critical cumulative patches .
All database has a store sensitive data. SQL Server has provided many encryption data options as
Ex: Always Encrypted.