Problem solve Get help with specific problems with your technologies, process and projects.

Why SQL Server security matters: A case study

With a DBA's multiple responsibilities, it's easy to neglect SQL Server security. This case study by Basit Farooq explains why this is so dangerous.

Editor's note: This is the first of a four-part series on SQL Server security. Please check back next week for the next chapter.

Database backups are stolen. Viruses attack servers. The database has unauthorized changes. This is what happens when SQL Server security is lax.

Database technologies are an essential component of many information systems because they store a large amount of sensitive corporate data such as customer information and other confidential business data. Because of this, it is not wrong to say that databases such as SQL Server are the most valuable asset for any organization, and that is why they must be secured from internal and external attacks.

Unfortunately, this is not the case, and many organizations still find database security to be an unattractive topic. As a result, they do not have a comprehensive database security policy that can defend their database infrastructure against sophisticated internal and external attacks. Many are even unable to meet regulatory compliance requirements such as the Gramm-Leach-Bliley Act (GLBA), the European Union Data Protection Directive (EUDPD), the Health Insurance Portability and Accountability Act (HIPAA), the Payment Card Industry Data Security Standard (PCI DSS) and Sarbanes-Oxley (SOX).

Case study of a company lacking SQL Server security

Company XYZ is a large pharmaceutical company that markets various medical products to smaller pharmaceutical companies worldwide. As SQL Server has a good track record with few vulnerabilities and weak points, Company XYZ uses SQL Server as a main database technology. Its SQL Server databases store the company's confidential data such as customer information, card details and employee information. This data is originally ported from Excel spreadsheets. In recent years, however, Company XYZ has experienced significant growth in all areas of its business. Over the last year, the business has been in the press for the wrong reasons: leaked customer data, unauthorized access and other reasons caused by its unsecure SQL Server infrastructure.

Here's a list of security vulnerabilities identified in Company XYZ's SQL Server infrastructure:

Unauthorized changes. In the last 12 months, unauthorized changes to the databases are the main factor in the failure of its SQL Server infrastructure. This is because Company XYZ currently has no change management processes in place. Unauthorized changes to databases represent a much greater chance of external threats, such as attacks from external hackers or malicious code attacks.

Well-documented and robust change management process is needed for Company XYZ SQL Server database code deployment because it ensures that authorized changes are deployed to production databases and eliminates unwanted risks.

Unauthorized users. On few occasions, it has been observed that unauthorized users are frequently accessing Company XYZ's SQL Server databases. This mainly happens when a SQL Server instance is configured to use Mixed Mode Authentication or BUILTIN\Administrators are a member of the "sysadmin" role. After investigation, it was found that most SQL logins, including logins with sysadmin (sa) privileges, had weak or blank passwords and users are not forced to change their passwords periodically.

Microsoft SQL Server security best practices suggest that you use Windows authentication over mixed-mode authentication because SQL Server stores passwords for SQL logins in plain text. If mixed-mode authentication is required for backward compatibility reasons, then ensure that complex passwords are used for sysadmin and all other SQL login accounts. This can be achieved by checking the "Enforce password expiration" and "Enforce password policy" options for sysadmin and all other SQL logins.

For more on SQL Server security

Ten common SQL Server security blunders

Five quick steps to improved SQL Server security

SQL Server security best practices also recommend renaming these accounts because sysadmin is a powerful account that is well known to many hackers, and the name change will make it more difficult for hackers to take control. It is also advised not to manage your SQL Servers using sysadmin. Also, ensure that BUILTIN\Administrator is not a member of the sysadmin role; otherwise it grants sysadmin permissions to any account in the local administrators group.

SQL injection. SQL injection is another reason for unauthorized access to databases. That's because the SQL code within the application allows direct updates to tables, and most of the SQL code parameters are not validated. Validate code parameters and perform all updates to databases using SQL Server store procedures.

Guest users. Logins who do not have an associated database user inside user databases are still able to access the sensitive data inside these user databases. This is because a guest user is enabled within these databases. Disable guest users. This will ensure that members with public server roles cannot access user databases in SQL Server, unless you have access explicitly assigned to the database.

Computer virus attack. Company XYZ's SQL Servers have been attacked a few times in the past year by computer viruses. Most of the SQL Server security patches, hotfixes and updates have not been applied on these affected SQL Servers. Security updates and patches are constantly released by Microsoft companies should apply these to protect against the aforementioned problems.

Using common TCP/IP ports. It has been noted that most of the servers attacked by hackers were using common TCIP\IP ports such as 1433 and 1432. Change from standard TCP/IP ports, if possible, to ensure SQL Server security.

Database backups stolen. In the past few months, it has been noted that database backups have been stolen due to weak network security. Also, data has been stolen and copied from tables to files on operating systems. Company XYZ is advised to secure the database backups by enabling Transparent Data Encryption. Also, make sure xp_cmdshell and OPENROWSET have been disabled on the instance of SQL Server and only systems administrators have access to these SQL Server features.

Lack of monitoring. Currently, SQL Server users' activities are not monitored on Company XYZ's SQL Servers. Obviously, this opened the doors for hackers to access the company's servers without fear. SQL Server provides a variety of features such as login auditing or logon triggers, common compliance criteria, ddl triggers, even notification and audit specification to track malicious activities on SQL Server.

Dig Deeper on SQL Server Security