SQL Server Best Practices Security

In New England we always notice how small the Database community is.  How many times when we meet someone how likely it is we will know them though some connection. Then as I walk the Internet looking for information, I am always amazed how how big the Database community is. My latest blog entry comes to us from Atif Shehzad. He is located in Islamabad Pakistan. He clearly has a passion for database administration. His blog is called DBDigger.

The blog entry I include her is “DBA Best Practises for SQL Server Security”. Here is what his blog said…

Following best practices may be implemented as base line for standard security of SQL Server
1.    Ensure the physical security of each SQL Server, preventing any unauthorized users to physically accessing your servers.
Comment: How important physical security is. If someone can get at the physical server they can easily mess with it.

2.    Only install required network libraries and network protocols on your SQL Server instances.
Comment: The less network protocols you have on the server, the less ways for someone to hack in..

3.    Minimize the number of sysadmins allowed to access SQL Server.
Comment: I would argue that each Sysadmin should be a named account.

4.    As a DBA, log on with sysadmin privileges only when needed. Create separate accounts for DBAs to access SQL Server when sysadmin privileges are not needed.
Comment: Its nice to have privileges, but how easy it makes it to delete something or drop something you did not mean to drop. This is great advice.
5.    Assign the SA account a very obscure password, and never use it to log onto SQL Server. Use a Windows Authentication account to access SQL Server as a sysadmin instead.
Comment: Take it one step further. Make it at least 6-8 characters long. Make sure it has both uppercase, Lower, Numbers and characters. It will make it so much harder to hack.

6.    Give users the least amount of permissions they need to perform their job.
Comment: Great idea, but so hard to do in actual practice. Thank god for roles.

7.    Use stored procedures or views to allow users to access data instead of letting them directly access tables.
Comment: Amen.
8.    When possible, use Windows Authentication logins instead of SQL Server logins.
9.    Use strong passwords for all SQL Server login accounts.
Comment. Same as above. 6-8 character long. Number and letters, etc.
10.    Don’t grant permissions to the public database role.
11.    Remove user login IDs who no longer need access to SQL Server.
Comment: An absolute must,
12.    Remove the guest user account from each user database.
Comment: An absolute must
13.    Disable cross database ownership chaining if not required.
14.    Never grant permission to the xp_cmdshell to non-sysadmins.
15.    Remove sample databases from all production SQL Server instances.
16.    Use Windows Global Groups, or SQL Server Roles to manage groups of users that need similar permissions.
17.    Avoid creating network shares on any SQL Server.
18.    Turn on login auditing so you can see who has succeeded, and failed, to login.
19.    Don’t use the SA account, or login IDs who are members of the Sysadmin group, as accounts used to access SQL Server from applications.
20.    Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
21.    Remove the BUILTIN/Administrators group to prevent local server administrators from being able to access SQL Server. Before you do this on a clustered SQL Server, check Books Online for more information.
22.    Run each separate SQL Server service under a different Windows domain account.
23.    Only give SQL Server service accounts the minimum rights and permissions needed to run the service. In most cases, local administrator rights are not required, and domain administrator rights are never needed. SQL Server setup will automatically configure service accounts with the necessary permissions for them to run correctly, you don’t have to do anything.
24.    When using distributed queries, use linked servers instead of remote servers.
25.    Do not browse the web from a SQL Server.
26.    Instead of installing virus protection on a SQL Server, perform virus scans from a remote server during a part of the day when user activity is less.
27.    Add operating system and SQL Server service packs and hot fixes soon after they are released and tested, as they often include security enhancements. 28. Encrypt all SQL Server backups with a third-party backup tool, such as SQL Backup Pro.

Comment: Easiest way to protect a database, keep the patch levels current.
28.    Only enable C2 auditing or Common Criteria compliance if required.
29.    Consider running a SQL Server security scanner against your SQL servers to identify security holes.
30.    Consider adding a certificate to your SQL Server instances and enable SSL or IPSEC for connections to clients.
31.    If using SQL Server 2005, enable password policy checking.
32.    If using SQL Server 2005, implement database encryption to protect confidential data.
33.    If using SQL Server 2005, don’t use the SQL Server Surface Area Configuration tool to unlock features you don’t absolutely need.
34.    If using SQL Server 2005 and you create endpoints, only grant CONNECT permissions to the logins that need access to them. Explicitly deny CONNECT permissions to endpoints that are not needed by users.

To read the original blog entry without my comments….
dbdigger blog

I enjoyed what Atif had to say. I thought it was well thought out and good advice

Posted by Michael Corey



Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.