How To Change Xp_cmdshell From Service Account

SQL Server Security Checklist

By: | Updated: 2014-02-06 | Comments (4) | Related: > Security

Problem

I have to install a new Microsoft SQL Server box and configure it as a production server. I know that there are many security related settings and I do not desire anyone to hack the system on the first day on the network. Which are the basic steps to harden the security of my SQL Server? Do you lot have a checklist to start with? Check out this tip to learn more.

Solution

At that place are many security related settings in the Microsoft SQL Server and y'all should also consider setting up processes to ensure that the security is maintained in the hereafter. The security related tasks tin can be divided into four main categories: concrete security, operating system level security, SQL Server configuration and user direction. You should protect your server physically, have a secure Bone and then you tin starting time thinking near your SQL Server.

Physical Security

The first line of your security is the physical security of your on premise hardware. Y'all have to protect your server from existence tampered with. Here are the bones items I would recommend:

Limit the number of employees who have access to the physical hardware. Yous can limit access with admission codes, entry cards or even with armed security guards. The well-nigh important indicate is to have a minimal number of people who have access and you lot should take written procedures to access the server, preferably with managerial approval for concrete access rights.
Store the backup media at a secure offsite location. To increase security, it is recommended to have one location for the production server and a separate location for the fill-in files.
Configure alerts for hardware warnings. To be proactive, you can configure e-mail alerts for example with HP iLO or Dell DRAC, just yous tin also use VMware vSphere.

Operating System Security

We live in the era of the cyberspace, so physical security is non enough. You should build your protection plan to include Windows. The operating system should be secured to reduce the vulnerability of your organization. Here are the basic items to consider:

Install all service packs and critical fixes for Windows (and for VMware if applicable). It is necessary to implement all disquisitional fixes to ensure that at that place are no known holes on your security. Consider creating a procedure to use the latest Windows security patches on a regular ground.
Configure a firewall. You need a protective firewall on your server to defend your system. If at that place is no other firewall installed on your server, then configure Windows Firewall to work with the Database Engine, Integration Services and Analysis Services components of SQL Server.
Limit the number of employees who have Windows Administrator access on the SQL Server. Equally a best do, you should limit the authoritative access only to those who actually require it. This should include the VMware panel every bit well as Remote Desktop Services. Maybe you lot can consider limiting the Bone level admin access to the SQL Server administrators and Network administrators.

SQL Server Installation

Now we reached the point when y'all can finally work with your beloved SQL Server. There are a number of security related tasks which should be completed earlier you lot tin can start to utilise a production SQL Server. Here is what I recommend as a starting point:

Install only the required components. The less installed components, the less security issues tin occur.
Install all service packs and critical fixes for SQL Server. Information technology is necessary to implement all critical fixes to ensure that at that place are no known holes on your security.
Disable unnecessary features and services. You can use the SQL Server Configuration Manager to disable unused SQL Server services.
Disable the unused SQL Server protocols. SQL Server supports four type of protocols: Shared Retentivity, Named Pipes, TCP/IP and VIA. You should utilise the bare minimum of these protocols and disable the others using the SQL Server Configuration Manager.
Change the default SQL Server ports. The SQL Server gets installed with the default TCP port 1433 which is well known by admins and attackers also. You can avert some targeted SQL attacks if you do not use the default ports. Yous tin change the port in SQL Server Configuration Director.
Hibernate the SQL Server instance and/or turn off the SQL Server Browser Service. You tin configure the SQL Server case as subconscious using the SQL Server Configuration Director. This volition forbid advertisement of your server past the SQL Server Browser service. If you use fully qualified connection strings then you tin disable the SQL Server Browser.
Restrict the access to the SQL Server configuration and database file. Apart from the database level access, you should also protect the file system to forestall unauthorized file deletion, copying or alteration of data.
Restrict the access to the SQL Server fill-in folders. Read this tip to learn how to protect your SQL Server Backup folder.
Utilize Transparent Data Encryption whenever it is an option. Yous tin can secure your data, logs and backup with TDE in Evaluation, Developer, Enterprise and Datacenter versions of SQL Server 2008, 2008 R2 and 2012.
Create only the required databases. Practice non create demo or test databases on production servers, keep it clean and safe.
Run the SQL Server Best Practice Analyzer to verify your installation. The Microsoft SQL Server 2012 All-time Practice Analyzer can quickly identify if your server is configured according to industry best practices or not.
Revoke execute rights to 'PUBLIC' on extended stored procedures. Extended stored procedures volition be removed in a hereafter version of SQL Server and it is not recommended to utilise them. You tin use CLR Integration instead. The following extended stored procedures should not exist executed past your applications: xp_availablemedia, xp_dirtree, xp_enumgroups, xp_fixeddrives, xp_regaddmultistring, xp_regdeletekey, xp_regdeletevalue, xp_regenumvalues, xp_regremovemultistring, xp_regwrite, xp_regread, xp_servicecontrol, xp_subdirs.
Disable the xp_cmdshell option. It is highly recommended to disable the xp_cmdshell stored process even if other administrators tin enable it again.

User Accounts

Later on your bones SQL Server security is configured, you tin can commencement to address the traditional user access and security topics. The administrators and the service accounts require extra attention. Here is how to go started:

Rename and disable the SA account if your applications let it. You can employ the sp_SetAutoSAPasswordAndDisable stored process to disable the SA business relationship as described in this tip. This will forestall the attacker from trying to login with the default admin account.
Remove the BUILTIN\Administrators grouping from the SQL Server Logins. You tin read more than about the security problems with the SQL Server BUILTIN\Administrators group in this tip.
Use Windows Authentication fashion. You can cheque and alter the authentication style in three different ways: using SQL Server Direction Studio, with T-SQL or in the Windows registry.
Every administrator should accept a named login, shared logins should not be allowed. This is required in order to be able to place the people behind each and every database change. It is also disquisitional to have an upward-to-date listing of all the accounts.
All accounts for named user access should be controlled past Active Directory. Employ Active Directory and exercise not create SQL Server logins. It makes the assistants easier if you grant access rights through Active Directory groups or Group Policy.
Use service accounts for applications. It is a best do to create a different service account with a descriptive name for every service. You tin utilise SQL Server logins, only a complex password is a must. Restrict the access only to data required: if an application updates only ane-two tables and then information technology does not require full control of every object in the database.
Configure service accounts with the to the lowest degree privileges. Yous tin can read this tip if you would like to know how to decide service related privileges for SQL Server service account. Practise not grant more rights than required.
The user privileges should be minimized. Endeavour to assign the minimum sufficient rights to every user. It is a best exercise to document any elevated user permission and asking managerial approving.
All administrator accounts should accept a complex password and password change should exist enforced. You should identify blank and weak passwords and configure password enforcement options.
Configure SQL Server login auditing to log both failed and successful logins. Details of the login audit configuration can be constitute in this tip and there is also a tip about SYSADMIN login auditing.

The higher up checklist can be used to ensure that the minimum requirements are fulfilled. Every company should have an information security policy and you should apply those requirements to your SQL Server as well. In case that policy does not exist then y'all can use this checklist as the basic checklist.