How to Get SQL Server Security Horribly Wrong or Failures

It is no good doing some or most of the aspects of SQL Server security right. You have to get them all right, because any effective penetration of your security is likely to spell disaster. If you fail in any of the ways that Robert Sheldon lists and describes, then you can't assume that your data is secure, and things are likely to go horribly wrong.

Failure 1: Not securing the physical environment

A database team might go through extraordinary measures to protect its SQL Server instances and the data they contain, but not necessarily extend that diligence to the physical environment, often because the assets themselves are not in the team’s control. Yet physical devices-such as servers, backup drives, or disks in a storage area network (SAN)-can be compromised just like any software or network component, so whoever is in charge of those devices better take heed.

All it takes is one disgruntled employee with the access to the physical assets to turn an organization upside down. Before anyone knows what happens, the individual can walk off with a backup drive full of credit card numbers, health records, company secrets, or other sensitive information, resulting in permanent damage to the organization’s reputation and financial well-being. Even if the data is encrypted, an employee with the necessary privileges, access to the security certificates, and a bit of know-how can make full use of that data on the open market.

Organizations serious about data security must implement defense in depth strategies that protect all layers of the data infrastructure, including the physical components, whether from inside threats or those coming from outside the organization. Every server and disk that hosts a SQL Server instance or data file must be physically protected so that only those who absolutely need access to those devices can get at them. That includes devices used for replications, mirrored databases, backups, or log files. The number of workers who have access to the physical devices should be strictly limited, with security measures scrutinized and updated regularly.

Another challenge, perhaps even more difficult to address, is trying to “enlighten” those wayward employees with privileged access who are careless with their desktops and laptops, leaving them unattended and unlocked for varying lengths of time. Anyone with less than honorable intentions can stroll along and change or copy sensitive data with a little bit of insight into the systems, not only making the careless user look bad, but again putting the company’s reputation and financial happiness on the line. If the imprudent user is a creature of habit, the miscreant thief has an even easier time getting at the sensitive data. Given the possibility of such a scenario, the only hope for the organization is employee education and IT policies that try to mitigate such risks as much as possible.

Failure 2: Not protecting the server environments

SQL Server can spread across environments far and wide, its impact reaching into machines and folders on the other side of the known network. Installation files might be located on a nearby server, log files on another device, data files on the new SAN down the hall, and backups in a remote data center. Even within the host, SQL Server also makes use of operating system (OS) files and the registry, spreading its impact even further.

Anything SQL Server touches should be protected to minimize the potential for a SQL Server instance or its data being compromised. For example, a database might contain sensitive data such as patient health records. Some of that data can end up in the log files at any given time, depending on the current operations. Even if all other files are carefully protected, a less-then-secure log file can have far reaching implications if the network or attached devices are compromised.

Database and IT teams should take every precaution to protect the files and folders that support SQL Server operations, including OS files, SQL Server installation files, certificate stores, data and log files-everything. Administrators should not assign elevated privileges to any related folder and should not share any of those folders on the network. Access to the files and folders should be limited to the greatest degree possible, granting access only to those accounts that need it. Teams should always keep in mind the principles of least privilege when it comes to NTFS permissions, that is, granting access only to the degree absolutely necessary and nothing more. The same goes for the machine as a whole. Don’t assign administrative access to a server when a user requires only limited privileges.

Failure 3: Implementing inadequate network security

When you consider all the ways a network can be hacked, it’s remarkable we can do anything safely. Weak network security can lead to viruses and network attacks and compromised data of unimaginable magnitude. Cyber-criminals able to exploit network flaws are not only able to inflict damage, but also download boatloads of data before being detected, whether credit card information, social security numbers, company strategies, or a host of other types of sensitive data.

An organization must do everything possible to protect its network and the way SQL Server connects with that network. One place to start is to ensure that you’re running the necessary antivirus software on all your systems and that the virus definitions are always up to date. Also be sure that firewalls are enabled on the servers and that the software is current. Firewalls provide network protection at the OS level and help to enforce an organization’s security policies.

In addition, configure SQL Server to use TCP/IP ports other than default one (such as 1433 or 1434). The default ports are well known and are common targets for hackers, viruses, and Trojan horses. In addition, configure each instance to use a static port, rather than allowing the number to be assigned dynamically. You might not be able to ward off all attacks, but this way you’re at least obscuring the pathways.

To further obscure your SQL Server instance, consider disabling the Browser service to prevent snoopers from being able to search for it on the network. Clients can still access the instance as long as their connections specify the correct port number or named pipe. If disabling the Browser service is not an option, at least hide any instances you don’t want easily discovered, once again forcing any client connections to have to provide the port number or named pipe.

In addition, you should disable the network protocols that are not needed. For example, it’s unlikely you need to have both the Named Pipes and TCP/IP protocols enabled. In addition, you should grant the CONNECT permission only the endpoints or logins that require it; for all others, explicitly deny the permission. If possible, avoid exposing the server that’s running SQL Server to the Internet altogether.

Failure 4: Not updating and patching your systems

Some of you might recall what happened in 2003. The SQL Slammer computer worm infected over 75,000 servers running SQL Server. Within 10 minutes of deployment, the worm infected more than 90% of vulnerable computers and took down thousands of databases. Slammer exploited a buffer-overflow vulnerability in SQL Server to carry out an unprecedented denial-of-service (DoS) attack. The rest is cyber history.

The interesting part of this story is that the bug had actually been discovered long before the attack. In fact, Microsoft provided a fix six months in advance of the DoS onslaught. The organizations that got hit by Slammer had failed to patch their SQL Server instances.

Because of the onslaught of threats aimed at SQL Server and other systems, companies like Microsoft are regularly releasing service packs and patches and security fixes and other types of updates to address any new found vulnerabilities. What these vendors can’t do, however, is force their customers to apply the updates.

In all fairness, there’s good reason why many DBAs and other administrator types can be slow to getting around to patching their systems. Applying these fixes can be a big deal. They take time and testing and careful consideration. If you apply an update to a production server without first testing it you risk taking down the entire system. Overworked DBAs must have the time in their schedules and the resources necessary to apply these updates right.

Yet such incidents as the Slammer debacle demonstrate how critical it is to keep your SQL Server instances up to date. Not only can such attacks lead to excessive resource consumption, server downtime, and corrupt data, but also to even more serious concerns. The information gathered from a successful DoS attack can be used to launch subsequent attacks, some of which might be aimed at getting at the actual data.

Microsoft is constantly releasing critical updates and fixes for both Windows and SQL Server. You can manually download and install them, or set up your systems to update the software automatically, if you can afford to take such risks. More often than not, you’ll want to first test the update and schedule a time to implement it into a production environment. It is no small matter, certainly, but keeping Windows and SQL Server current is one of the most effective strategies you can employ for protecting your system’s data.

Failure 5: Maintaining a large surface attack area

We’re not talking rocket science here, just common sense. The more SQL Server features and services you have enabled, the larger the surface attack area and the more vulnerable your system is to potential attacks. When setting up SQL Server, you should install only those services you need and disable those features you don’t plan to use anytime soon. Only the services and features for which there is a credible business need should be running.

Cyber criminals are a diligent lot, and the more you give them to work with, the happier they are. Given the proliferation of zero-day exploits, the less you can leave yourself exposed, the better. Why install Analysis Services or Integration Services on a production server when all you need is the database engine?

Some features in particularly can raise the stakes on your system’s vulnerability. For example, xp_cmdshell let’s you execute a command against the Windows host. If that feature is enabled on a SQL Server instance and a hacker gains access, xp_cmdshell can open doors to the OS itself. Other features too should be enabled only if needed, such as the Active Directory Helper service or VSS Writer service. You even have to be cautious with OPENROWSET and OPENDATASOURCE because they can facilitate access to external systems. In addition, you should consider disabling mail-related features that are not needed, such as the Database Mail service or the sp_send_mail system stored procedure. And don’t install the sample databases or code on your production servers.

If you’re running SQL Server 2005, you can use the Surface Area Configuration (SAC) tool to enable or disable installed components. For whatever components SAC doesn’t address, you can use Configuration Manager, the sp_configure system stored procedure, or a tool such as Windows Management Instrumentation.

Starting with SQL Server 2008, the Policy-Based Management system replaces SAC, providing one location to address all your surface configuration needs, in addition to configuring other components. For example, the following dialog box shows a new policy named Surface area conditions, with the Remote queries disabled condition selected. The condition specifies that the OPENROWSETand OPENDATASOURCE functions should be disabled.

Whichever tools you use, the important point is to install and enable only those components you need. You can always add or enable other features later. Just don’t give hackers any more ammunition than they already have.

Failure 6: Using improper authentication

SQL Server supports two authentication modes: Windows Authentication and Mixed Mode. Windows Authentication, the default authentication type, leverages Windows local accounts and Active Directory network accounts to facilitate access to the SQL Server instance and its databases. Mixed mode authentication supports both Windows accounts and SQL Server accounts (SQL logins). You can specify the authentication mode when installing SQL Server or change it after SQL Server has been installed by updating the server properties, as shown in the following figure.

Microsoft (and just about everyone else) recommends that you use Windows Authentication whenever possible, falling back on Mixed mode only to support backward compatibility, access to SQL Server instances outside the domain, and legacy applications. Windows Authentication is more secure because it can take advantage of the Windows and Active Directory mechanisms in place to protect user credentials, such as the Kerberos protocol or Windows NT LAN Manager (NTLM). Windows Authentication also uses encrypted messages to authorize access to SQL Server, rather than passing passwords across the network. In addition, because Windows accounts are trusted, SQL Server can take advantage of Active Directory’s group and user account administrative and password management capabilities.

SQL Server accounts and passwords are stored and managed within SQL Server, with passwords being passed across the network to facilitate authentication. Not only does this make the authentication process less secure, but it also means you can’t take advantage of the password controls available to Active Directory. In addition, you must be diligent about managing the passwords within SQL Server, such as mandating strong passwords and expiration dates as well as requiring new logins to change their passwords. You must also guard against accounts being created with blank passwords, including accounts created by third-party products. Weak passwords leave your database open to brute force attacks and other nefarious activities. Blank passwords open up your databases to everyone and everything.

Also with Mixed Mode authentication, you have to be particularly wary of that powerful built-in SA account. At one time, this had actually been created with a blank password. Fortunately, that has not been the case since SQL Server 2005. Many database pros recommend that you rename the SA account. Others suggest you disable it and never use it. Still others would have you do all three, not a bad strategy, given how well known the account is and its history of abuse. At the very least, you should assign a complex password to the account and keep it disabled.

It should also be noted the SQL Server creates the SA account even if Windows Authentication is the selected mode, assigning it a randomly generated password while disabling the account. You might consider applying a complex password in this case as well, and certainly don’t enable the account.

Failure 7: Assigning the wrong service accounts

Each SQL Server service requires a Windows account to run so the service can access resources such as networks and file folders. Because orchestrating this access can be somewhat cumbersome, services are often assigned high-privileged built-in accounts such as Local System or Network Service, thus preventing those mysterious SQL Server error messages that can pop up at peculiar moments, providing information that is so obscure that even the Google gods cannot offer an answer.

The problem with using these accounts is that, if SQL Server were to be compromised, the OS too could be compromised-to the degree that the service account has access. These built-in accounts can actually inherit elevated privileges in Active Directory, which are not required in SQL Server. For example, you can easily assign the Local System account to a SQL Server service, as shown in the following figure.

The Local System account is a very high-privileged account with extensive access to the local system. It is like granting a user administrative privileges to the server. If SQL Server were to be compromised, the would-be hacker could potentially have unrestricted access to the machine and its resources.

Despite the convenience that these built-in accounts bring, you should be following the principles of least privilege when assigning accounts to SQL Server services. The accounts should have exactly the permissions necessary for the service to do its job. You should also use a different account for each service, with the permissions set up specifically to meet the needs of that account.

For example, SQL Server Agent will likely require a different set of permissions from Integration Services. Also, avoid assigning accounts being used by other services on the same server. In addition, you must take into consideration whether the service will need access to domain resources, such as a data file on a network share. Keep in mind too that service accounts should also be governed by good password management policies, such as enforcing complex passwords and expiration dates.

Failure 8: Failing to control access to SQL Server resources

Like all aspects of SQL Server access, account access should adhere to the principles of least privilege, assigning only the permissions necessary to perform specific functions. Along with this principle, we can add another-separation of duties. This ensures against conflicts of interest and the inadvertent combination of privileges that lead to excessive access.

All too often, database administrators (or someone) will assign one or two sets of generic privileges to all users, without thought to the consequences of such action. For example, an assistant bookkeeper might need access to sales totals and quantities, but is granted access to the entire database, which includes sensitive customer information. That employee could abuse that position by modifying records or stealing data. Together, the principles of least privilege and separation of duties help to ensure that database owners maintain control over who can access SQL Server and its data and what levels of access they have.

This also goes for the applications and services accessing the database. They too should be using low-privileged accounts that grant only the access needed. In addition, be sure to remove unused default accounts created by third-party apps (as well as any other accounts not in use).

When possible, avoid granting individual accounts access to SQL Server and instead grant access to the security groups that contain these accounts, granting them the specific access they need. This helps to think in terms of separation of duties and makes it easier to manage large numbers of accounts. Also consider disabling the guest accounts in your databases so that members of the public server role can’t access those databases, unless they’ve been specifically granted the permissions.

Pay particular attention to how you’re assigning administrate permissions to users. For example, choose widely which accounts are added to the sysadmin fixed server role. Group members can do just about anything they want in SQL Server. And don’t use the SA admin account to manage SQL Server, as mentioned earlier.

You should also avoid assigning the CONTROL SERVER permission to individual accounts. It provides full administrative privileges over SQL Server and is already granted to the sysadmin group. Many database pros also recommend that you remove the Windows BUILTIN/Administrators group from the sysadmin role if it has been added. (It was added by default prior to SQL Server 2008.) Although removing the group lets you better control SQL Server access, you have to be careful doing so because it can impact your SQL Server installations.

Make use of SQL Server’s hierarchical permission structure in which the principals (users and roles) can be granted or denied to the securables (the databases and its objects). For example, you can grant a principal access at the database, schema, or table level. In the following figure, the database role has been granted SELECT and INSERT privileges to the ZipCodes table, but denied UPDATE and DELETE privileges.

By taking this approach, you can ensure that users perform only specific tasks and not perform others. Again, the key is to follow the principles of least privilege and the separation of duties. This applies to administrative accounts as well as other types of accounts.

Failure 9: Failing to encrypt sensitive data

An organization less diligent about security might assume that, because SQL Server is a backend system, the databases are inherently more secure than public-facing components and be satisfied that the data is ensconced in a protective layer. But SQL Server still relies on network access and is consequently exposed enough to warrant protection at all levels. Add to this the possibility that physical components such as backup drives can be lost or stolen (most likely the latter), and you can’t help but realize that no protection should be overlooked.

Encryption is one such protection. Although it cannot prevent malicious attempts to access or intercept data, no more than it can prevent a drive from being stolen, it offers another safeguard for protecting your data, especially that super-sensitive stuff such as credit card information and social security numbers. That way, if the data has been accessed for less than ethical reasons, it is at least protected from prying eyes.

SQL Server supports the ability to encrypt data at rest and in motion. On the at-rest side, you have two options: cell-level encryption and Transparent Data Encryption (TDE). Cell-level has been around for a while and lets you encrypt individual columns. SQL Server encrypts the data before storing it and can retain the encrypted state in memory.

Introduced in SQL Server 2008, TDE encrypts the entire database, including the log files. The data is encrypted when it is written to disk and decrypted when being read from disk. The entire process is transparent to the clients and requires no special coding. TDE is generally recommended for its performance benefits and ease of implementation. If you need a more granular approach or are working with SQL Server 2005 or earlier, then go with cell-level encryption.

SQL Server can also use the Secure Sockets Layer (SSL) protocol to encrypt data transmitted over the network whether between SQL Server instances or between SQL Server and a client application. In this way, data can be protected throughout a session, making it possible to pass sensitive information over a network. Of course, SSL doesn’t protect data at rest, but when combined with TDE or cell-level encryption, data can be protected at every stage.

An important component of any encryption strategy is key management. Without going into all the gritty details of SQL Server key hierarchies, master keys, and symmetric and asymmetric keys, let’s just say you need to ensure these keys (or certificates) are fully protected. One strategy is to use symmetric keys to encrypt data and asymmetric keys to protect the symmetric keys. You should also password-protect keys, and always back up the master keys and certificates. Also back up your database to maintain copies of your symmetric and asymmetric keys, and be sure those backups are secure.

Failure 10: Following careless coding practices

After all these years, after all the press, after all the countless articles and recommendations and best practices, SQL injection still remains a critical issue for SQL Server installations. Whether because of sloppy coding within SQL Server or within the web application passing in SQL, the problem persists. Hackers are able to insert malicious code into a string value passed to the database and in the process do all sorts of damage-deleting rows, corrupting data, retrieving sensitive information. Any code that accesses SQL Server data should be vetted for potential SQL injection and fixed before going into production. Fortunately, those fixes are often quite straightforward, such as validating user input or delimiting identifiers.

But SQL injection is not the only risk. Issues can arise if the execution context within procedures, functions, or triggers is not explicitly called out. By default, the code executes as the caller, but this can lead to problems if an account with elevated privileges has been compromised. However, as of SQL Server 2005, you have more control over the execution context (using EXECUTE AS) and can specify the account to use as the execution context, thus ensuring full control over a procedure’s capabilitiy.

Another way coders can protect their databases is to create procedures, functions, and views to present the data without providing access to the base tables. This helps to abstract the actual schema and allows access to be controlled at the abstraction layer, restricting access altogether to the tables themselves. This approach also has the benefit of more easily accommodating changes to the application as well as to the underlying data structure. In some cases, the database team won’t have this option because of application requirements or the technologies being used, such as a data abstraction layer, but when it is possible, providing this extra layer of protection can be well worth the effort.

There are, of course, plenty of other coding practices that can result in compromised data. The key is to make certain that all code is reviewed and tested for vulnerabilities, before it is implemented in production.

Failure 11: Not verifying SQL Server implementations

Regardless of the safeguards you’ve implemented to protect your SQL Server databases and their data, security can be breached without you being aware that something is wrong. The only way you can fully protect your data is to monitor and verify your SQL Server installations. Monitoring in this sense does not refer to auditing (which we’ll discuss shortly), but to the process of assuring that everything is running as it should, the data is intact, and nothing too strange is going on.

For example, you should be monitoring CPU, memory, and disk usage, not only for performance considerations, but also to ensure you’re not seeing any anomalies that might point to such issues as illegal data downloads or malware accessing or modifying the databases and their data. Activity monitoring can also be a useful stopgap measure until you get a chance to apply the latest security patches. You should take whatever steps might help you expose abuses that would otherwise go unnoticed-until it’s too late.

You can also make use of such tools as DBCC CHECKDB, which can help uncover data corruption as a result of a cyber-attack or illegal access. Spot-checking security-related configuration settings can also be useful in discovering if a rogue user has gained access and is wreaking havoc on the permissions or other settings. In general, you want to check your databases to make sure they’re doing only what they’re supposed to be doing and that data is in the state you expect it to be.

You can also make use of such tools as SQL Server Best Practices Analyzer. The Analyzer is free and can help you gather information about security settings and identify vulnerabilities. The tool uses SQL Server recommendations and best practices to uncover potential security risks. And don’t forget the server hosting SQL Server. For example, you (or an IT administrator) can use Microsoft Security Compliance Manager to enhance the server’s security via Group Policy.

Failure 12: Failing to audit your SQL Server instances

Auditing is a big topic, too big to cover in just a few paragraphs, but it is a topic too important not to mention. One of the most beneficial steps you can take as part of a complete security strategy is to implement auditing. If you’re not monitoring user activity, you might be opening yourself up to severe consequences in the form of corrupt or compromised data.

You have several options for auditing your systems. You can use SQL Server’s built-in features (which have greatly improved since SQL Server 2008), or you can implement one of many available third-party solutions, which usually come in the form of host-based agents or network-based monitors. There are pros and cons to any solution, with performance and cost being two of the main considerations. The important point is to maintain an audit trail of all access to sensitive data. Without such an audit trail, you risk being out of compliance as well as having your data compromised.

You will, of course, have to balance the overhead that comes to auditing against the need to protect sensitive information. You’ll have to take this on a case-by-case basis. Determine the level of granularity needed to ensure a credible audit as well as which operations and data should be audited. At the very least, you should audit both successful and failed login attempts. Also keep in mind any compliance-related requirements that might govern your auditing strategy.