SQL Server 2008 R2/2012/2014 Security Policies

After installing SQL Server and creating the instance you need to focus on the security features and options that are available to you. I have created a set of standards that all DBAs have to follow in my team after creating the instance. Following are the details.

Service Account

• Change the SQL Server Service account to a domain account. 
• This should not have admin rights on the server.
• Use one AD account for the database engine service and another one for the agent service.
• Use unique AD Account for each database engine but use the same account for all agent services in your environment. If you have jobs that access databases across different hosts then this is required. 

Port Number

• Change the port number on which you runt he SQL Server service. This prevents from attackers in attacking on the default port.

Authentication

• If possible do not enable SQL authentication. I prefer Windows authentication for all logins. 
• If you have to enable SQL Authentication then make sure that you disable the SA account.

Audit

• Enable both Successful and failed logins to be audited. You can do this under properties for the SQL Server Instance.

Password Policy

• If you are creating logins using SQL Authentication then you have to enable the  'Enforce Password Policy'. This will ensure that the passwords that users set will have to comply with the Windows password policies. This will typically require 8 or more characters and a combination of several types of characters.
• Enforce Password Expiration policy so that users are forced to change passwords every 90 days or so. You can check the policy in Windows to find out the expiration days.

Account types and roles

• As much as possible refrain from creating logins for individuals. 
• Create logins for AD groups so that you don't have to keep adding new logins. Once a new AD account is added to a group they will inherit the permissions for that group.
• Create roles that you would know will be used in the database and added logins to those roles. It is easier to modify roles and modify permissions for everyone in that group, rather than doing this for each login.

Monitoring

• Set up alerts to monitor failed logins.
• Set up jobs to check on all the accounts that have expired passwords or if the accounts are locked.

If possible script out all of these and run the script once after you finish the install. This way you will never forget any of the above points.