OUR THOUGHTS ON:

Data Security - SQL Server Database Best Practices

Internal Audit/Risk

By Trudie Kozar

With the recent data breaches at Epsilon and Sony PlayStation, data security has once again become a focus. One of the best ways to heighten data security is to tighten security around the databases that house the sensitive data. SQL Server is one of the most widely used database systems. Does your company use SQL Server? Is it secure? Is it being used efficiently? Many companies, large and small, use an edition of SQL Server to interface with their applications. Maintaining the security and performance of your SQL Server databases is a continuous effort with many challenges.

Since the databases within SQL Server most likely contain confidential and/or sensitive information, the security around SQL Server should be a high priority. Some best practices that aid in the security against hackers include changing the default firewall port from port 1433 to another open port on your firewall. The file extension name on the database files is another default that can be changed. Keeping the default file extension names of .mdf, .ndf, and .ldf allows hackers to easily spot your database files. These file extension names can be changed to any name.

Another key area of focus for security should be limiting access to data to appropriate personnel. The most efficient way to grant and manage user access is by utilizing the role access available in SQL Server and then assigning users to certain roles that grant specific access. By default, all users created in SQL Server are assigned to the “public” role. This “public” role is a default role created by SQL Server during setup. The “public” role should be reviewed and monitored to ensure that limited access is granted, since all users will have access to the rights in this role. Periodic reviews should be completed on all roles and the users assigned to the roles to ensure that the access assigned remains accurate. This will help to detect any terminated employees that were not removed, as well as any employees that have changed job roles and need a change in access.

Some best-practice configurations that aid in performance include configuring SQL Server to limit the number of concurrent connections. Also, databases are set to auto growth by default, but can be disabled or customized. If auto growth is left enabled, it could result in performance degradation in the event of an automatic growth occurring during peak operating hours.

We can assist you in performing a user account assessment that consists of querying users with administrative rights to ensure appropriate access, querying last login dates and times to identify inactive accounts, and identifying generic/shared accounts through inspecting a listing of all user accounts and eliminating these high-risk accounts. If you have questions about SQL Server or would like to discuss ways to improve your SQL Server environment through a user account assessment, please contact Trudie Homonai at thomonai@schneiderdowns.com or Frank Dezort at fdezort@schneiderdowns.com.

© 2011 Schneider Downs. All rights-reserved. All content on this site is property of Schneider Downs unless otherwise noted and should not be used without written permission.

This advice is not intended or written to be used for, and it cannot be used for, the purpose of avoiding any federal tax penalties that may be imposed, or for promoting, marketing or recommending to another person any tax-related matter.

ask a question

comments