I recently have been asked by an interviewer about how we are securing our SQL Server. SQL Server security is one of the important concern considering the importance of the data that it holds. It is very important keep any server secure from unauthorized access from internal and external hackers.
To keep the data on SQL Server secured, we need to tighten the security both at windows server level and SQL Server level.
At Windows Server Level: It is important to secure the operating system secured to make the system less vulnerable to the unauthorized gain of access.
Service packs and critical fixes for Windows: We need to make sure all the OS related latest security fixes\patches are properly installed, it is one of the best way to fill any holes on the security. Windows team need to roll out all the up to date releases.
Configure a firewall: Configuring the firewalls will keep the systems behind an additional protective layer. We can setup the external facing servers in DMZ (demilitarized zone), it is a physical or logical sub-network that separates an internal local area network (LAN) from other untrusted networks, usually the Internet. This provides an additional layer of security to the LAN as it restricts the ability of hackers to directly access internal servers and data via the Internet.
Restrict the Windows Administrator Access: As a best practice, we should limit the administrative access even Remote Desktop Service only to those who really require it.
At SQL Server Level: Its time to do few SQL Server level tasks to add additional strength.
Install only the needed components: Install only needed SQL Server features\components. Though you installed all the features try to stop and disable unused features. The less installed components, the less security problems can occur.
Install service\Cumulative packs and critical fixes for SQL Server: It is important to implement all critical Service packs\fixes to ensure that there are no known holes on your security.
Disable the unused SQL Server protocols: though SQL server supports four different type of protocols, enable only the needed protocols and disable any unused protocols using the SQL Server configuration Manager.
Don't use the default TCP Port 1433: The default port number for SQL Server is well known to the hackers and other admins so there is fair chances hackers can target the 1433 port. Always change the default port number to something else.
Restrict the access to the SQL Server configuration\Binaries and database files: There is chance to make changes to the database files to corrupt the databases or modifying the data using hexeditor so it important to restrict access to other users on the SQL Server related folders on local drives, It protects the file system to prevent unauthorized file deletion, copying. Restrict access to the SQL Server backup folders as well, which will protect the backup files from stealing.
Use Transparent Data Encryption: If you have an option to use the Transparent Data Encryption use it, It will provide an extra layer of protection in securing data, logs and backup. But TDE is only available with Enterprise and Datacenter editions.
Disable the xp_cmdshell option: It is highly recommended to disable the xp_cmdshell stored procedure and do proper alerting\monitoring to get an alert when someone enables it. As members with sysadmin rights can enable it again.
Run the SQL Server Best Practice Analyzer to validate the installation: The Microsoft SQL Server 2012 Best Practice Analyzer can quickly identify if server is configured according to industry best practices or not.
User Account standards: It is important to restrict user account with required permissions only than granting them with much elevated permissions.
Rename and disable the SA account: Where it is feasible change the SA password to some random GUID and disable it. Use undocumented sp_SetAutoSAPasswordAndDisable stored procedure to disable the SA account. This will prevent the attackers from trying to login with the default admin account.
Remove the BUILTIN\Administrators group: If you don't remove the BUILTIN\Administrators of the from the SQL Server Logins, you are opening a window for Windows Admins to gain unauthorized access to the SQL Server. But in latest versions of SQL Server Microsoft has discontinued the BUILTIN\Administrators login.
Avoid Mixed mode and Use Windows Authentication mode only: If possible try to use the windows authentication mode only.
Use service accounts for applications: Try to create a dedicated (AD account) Service account for applications to use with only the required\limited permissions. We can use SQL Server logins with complex password. Try to avoid using the shared service accounts\logins for different applications.
Configure service accounts with the least privileges: Do not grant more rights than required for SQL Server Service accounts.
The user privileges should be minimized: Try to assign the minimum sufficient rights to every user. It is a best practice to document any elevated user permission and request managerial approval.
All Logins (Windows\SQL Server) should have enforced with Password Policy: This is the best way to keep the weak and blank passwords away.
Configure SQL Server login and Server role auditing: Setup auditing for both login failures and success. And get the list of logins with elevated permissions. The gathered audit log need to be reviewed on weekly\monthly basis.
References:
https://www.pythian.com/blog/how-to-secure-microsoft-sql-server/
https://www.mssqltips.com/sqlservertip/3159/sql-server-security-checklist/
