Much of the responsibility of a DBA is to keep the SQL Server environment stable and healthy, to ensure data integrity and availability, and to make sure the data is safe and secure. There are many vulnerabilities to SQL Server, and many available features to safeguard and protect the data. SQL Server security is a giant topic and there are many facets to consider. We’ll discuss several SQL security topics in later articles covering some of the following areas: physical security, OS security, SQL configuration, user management, and application security.
Data security is at the forefront of everyone’s concerns lately with hackers obtaining sensitive data from big retailers, financial institutions, and even government organizations. Some data is intentionally compromised, while in other situations the data is compromised accidentally. Some security threats are internal to the organization, while others are external threats.
To begin this journey into SQL server security, I’ll start with SQL Server installation.
Physical Environment
Let’s assume you have already taken precautions to put your SQL server(s), backup drives, storage devices, and any other physical devices into a secure environment. This means locked rooms and restricted access to those rooms and any other hardware and network devices. A room that protects and suppresses flooding or fire is preferable. And if you can, secure your database backups at an off-site location. A natural disaster can be detrimental, and a disgruntled or careless employee can be disastrous to an organization if given access to these mission critical assets.
Reduce Surface Area
During the installation of SQL Server, chose the least amount of features and install only the necessary components. In other words if you don’t need Analysis Services, Integration Services, or Reporting Services, don’t install it. You can always come back later and install features you may need in the future. The more components of SQL Server you have installed on your server, the more vulnerable your server is and the more opportunity for someone to attack.
If you do use Reporting Services, its best to install that on a separate server from your database server. Also it is not advised to install SQL Server on the same machine as IIS or your web server, as these have historically been vulnerable to attack.
Service Packs / Patches
Download and install the most up to date service pack and hotfixes available during installation. Also after initial installation check for and install these to make sure there are no holes in your security. Microsoft frequently releases security fixes, updates, service packs and hotfixes to resolve security threats and vulnerabilities, so be diligent in applying these to your environment. After SQL is installed, it is always prudent to download and install any updates to a test system prior to deploying to an existing production server, and make sure you backup your productions databases before applying updates.
Service Accounts
Its best to assign the service accounts running SQL Server a non-user domain account with least amount of privileges. Don’t use the built-in Windows accounts – Network Service or Local System. These built in accounts have access to Windows and a compromise into the SQL system could cause the OS to be compromised as well, giving the bad guys access to the machine and its resources.
The SQL Server Agent service account should be different from the other SQL Server service accounts because SQL Server Agent account requires permission in the system admin role. The other service accounts do not need this elevated privilege.
Only Enable Necessary Services
The only SQL Server services that should be enabled are the SQL Server service, and the SQL Server Agent service. Of course if you install the SSAS, SSIS, or SSRS features, you will enable the services associated with these installed features.
Use SQL Server Configuration Manager to disable the following services:
SQL Server VSS Writers service – used by Volume Shadow Copy service (VSS). Enable this if it is actively being used.
Active Directory Helper service – SQL will automatically enable and disable this as needed.
SQL Server Browser service (if you use fully qualified connection strings). The browser service is a Windows service and provides connection information to incoming requests.
If you are not installing SSRS, SSAS, or SSIS, these services should not appear in list of services in SQL Server Configuration Manager.
Don’t Use Default TCP/IP Ports
Default ports are entry points into SQL Server and are vulnerable to hacking attempts. Using a different port other than the well-known default (1433) conceals the entry point to SQL Server. This can be set in SQL Server Configuration Manager after installation. Changing the port number doesn’t prevent hackers from attacking, but will at least conceal the entryway.
Don’t Install SQL on Domain Controller
It is possible to install SQL on a domain controller, but for security reasons it is not advised. SQL server failover clusters as well as AlwaysOn groups are not supported on domain controllers. And you will not be able to create security groups or provision SQL service accounts on a domain controller…setup will fail.
Disable Unused Protocols
Shared Memory, Named Pipes, TCP/IP, and VIA (VIA protocol is deprecated and will be removed in a future version of SQL Server) – disable the network protocols that are not needed. Enable whichever network protocol you decide to use for your SQL Server, and disable the others.
Disable xp_cmdshell
It is best not to enable xp_command shell. This is disabled by default, and should remain disabled. Occasionally there will be legitimate reasons to use xp_command shell, but this should be done very carefully and sparingly. This feature lets you execute commands against the Windows host and can open access to the OS itself, if hacked. A better alternative might be to use command EXEC or use CLR instead. Click here for more info https://blogs.msdn.microsoft.com/sqlsecurity/2008/01/10/xp_cmdshell/ .
Increase Server Log Files
By default, SQL contains 7 error log files (one current, and six archives). Error logs contain critical information about your SQL server. A new log is created each time the SQL service starts, and the oldest archived log gets deleted. For security reasons it is best practice to keep a large number of error logs, depending on the space you have available. Someone with access to your SQL server could execute sp_cycle_errorlog and regenerate the logs at a rate that could ‘delete’ their malicious activity or cover up the fact that they have gained access to your system. If you have highly sensitive data or stringent auditing requirements you can increase the number of retained error logs up to 99, just be sure to monitor your disk space.
Conclusion
This article covers some basic best practices during installation to make your SQL Server secure. This is just the tip of the iceberg when it comes to securing your SQL environment. There are always exceptions to the rules, but these recommendations should help to give you guidelines in setting up your SQL server. Our next article will discuss controlling access to your SQL environment, including authentication, roles, permissions, and related topics that can help ensure your SQL server is protected.
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!