Data Security - SQL Server Database Best Practices

Risk Advisory/Internal Audit

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.

You’ve heard our thoughts… We’d like to hear yours

The Schneider Downs Our Thoughts On blog exists to create a dialogue on issues that are important to organizations and individuals. While we enjoy sharing our ideas and insights, we’re especially interested in what you may have to say. If you have a question or a comment about this article – or any article from the Our Thoughts On blog – we hope you’ll share it with us. After all, a dialogue is an exchange of ideas, and we’d like to hear from you. Email us at contactSD@schneiderdowns.com.

Material discussed is meant for informational purposes only, and it is not to be construed as investment, tax, or legal advice. Please note that individual situations can vary. Therefore, this information should be relied upon when coordinated with individual professional advice.

© 2019 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.