- Physical Security
It’s important for physical and logical securing the server. Secure the physical access to the server by allowing only the authorized persons to the place of the server.
The servers should not be directly connected to the internet.
No matter how much secure you make the SQL Server if it is not physically secure than everything else is of no use.
- Securing the data and log files.
OS needs to be installed with latest patches and security updates as they help in fixing the loopholes and bugs which are exploited to reach the important files.
Its important to follow the least privilege account for service accounts and any other account as well.
Provide the permissions to the Data and log files only to the users or groups which require access to these. Such as SQL Server service accounts. Service account used by any third party tool for backing up the database files.
Use the NTFS file system instead of FAT32, and is preferred for the installation of SQL Server. As its more secure.
Use RAID for securing the data files against any HDD related hardware failure.
Firewalls are important to secure the network. Database servers, application servers, Clients, SAN and many things more are all on network so it is important to limit the access to the network by firewall.
Only allow the defined ports for SQL Server through firewall. Such as 1433,1434 and so on.
- Service Accounts
Isolate the service accounts used in SQL Server and provide only those permissions and access rights to the accounts that are required i.e. least privilege required.
- Authentication mode\ Strong passwords.
Its preferred to use Windows authentication for connecting to SQL Server.
When using SQL Server authentication use strong password. Check Enforce password policy and Enforce password expiration.
Note: It’s not preferred to enforce password expiration for the logins which are used as service accounts
- Surface Area Configuration.
Using sp_configure disable the features and components that are not required in the environment. Lesser the features enabled lesser the area to secure. Some features that needs to be disabled if not used are: Database Mail XPs, cross db ownership, xp_cmdshell Ad hoc distributed queries and more.
- Server and Database Roles
Assign appropriate Server roles to logins as they provide access on instance level and thus should be assigned to the logins carefully.
To control the access and permissions on database level use database roles. Provide appropriate permissions to users so that they limited permissions based upon there requirement.
SQL Server provides various encryption options to choose from like TDE, always encrypted, column level encryption.