SQL Server 2008 R2: Going Ninja to Mitigate SQL Server 2008 R2 Threats

  • Article

Properly securing your SQL Server 2008 R2 servers is more than just maintaining physical and network security and reducing attack surface. Here’s how to complete the task.

William Stanek

There are few issues as likely to polarize a room full of database administrators as security. This is especially true when it comes to best practices for mitigating threats and vulnerabilities. Traditional practices have DBAs focusing on physical security, network security and attack surface minimization.

Indeed, those are all valid aspects of SQL security. You must ensure the physical security of your SQL Server hardware. You must properly configure network security and networking protocols on your SQL Servers and your SQL Server clients. You must also reduce the attack surface to protect your data and your servers.

You can reduce the SQL Server overall attack surface and greatly improve security by enabling only the features your clients and applications need. This will limit the ways in which malicious users can exploit SQL Server. It will also close avenues of potential attack. The surface area features you can manage for the SQL Server Database Engine, Analysis Services and Reporting Services are as follows:

DATABASE ENGINE

AdHocRemoteQueriesEnabled

The OPENROWSET and OPENDATASOURCE functions can use ad hoc connections to work with remote data sources without specifically configuring linked or remote servers. You should enable this functionality if your applications or scripts use these functions. Otherwise, disable this feature.

ClrIntegrationEnabled

CLR integration lets you write stored procedures, triggers, user-defined types and user-defined functions using Microsoft Visual Basic, C# and any other Microsoft .NET Framework language. Enable this feature if your applications or scripts use .NET Framework languages; otherwise, disable this feature.

DatabaseMailEnabled

Database Mail replaces SQL Mail as the preferred technique for sending e-mail messages from SQL Server through SMTP. Enable this feature if you’ve created a mail host database and the necessary database mail profiles, and you want your applications and scripts to be able to use the sp_send_dbmail stored procedure to send e-mail messages from SQL Server. Otherwise, disable this feature.

RemoteDacEnabled

By default, SQL Server only permits local dedicated connections. If you want to authorize remote dedicated connections, enable this feature. Otherwise, disable it.

SoapEndpointsEnabled

SOAP messages contain text-based commands formatted in XML. If you plan to use SOAP for data exchange and have configured the necessary HTTP endpoints, you can enable this feature and configure the state of each endpoint.

OleAutomationEnabled

OLE Automation lets you use Transact-SQL batches, stored procedures and triggers to reference SQL DirectX Media Objects (DMOs) and custom OLE Automation objects. Enable this feature if you want to use OLE Automation; otherwise, disable it.

ServiceBrokerEndpointActive

Service Broker provides queuing and messaging for the Database Engine, which your applications can use to communicate across instances of SQL Server. If your applications use Service Broker and you’ve configured the necessary HTTP endpoints, you can enable this feature and configure the state of each endpoint.

SqlMailEnabled

You can use SQL Mail for sending e-mail messages from SQL Server to legacy applications using SMTP. Enable this feature if you want legacy applications and scripts to be able to use the xp_sendmail stored procedure to send e-mail messages from SQL Server. Otherwise, disable this feature.

XPCmdShellEnabled

The xp_cmdshell executes command strings using the OS command shell, and returns the results as rows of text. If you want applications and scripts to run OS commands, you’ll have to enable this feature.

WebAssistantEnabled

In earlier versions of SQL Server, Web Assistant stored procedures to generate HTML files from SQL Server data. In SQL Server 2005 and SQL Server 2008, Reporting Services takes the place of these stored procedures. If you have legacy applications or scripts that use Web Assistant, enable this feature. Otherwise, disable it.

ANALYSIS SERVICES

AdHocDataMiningQueriesEnabled

The Data Mining Extensions OPENROWSET function establishes connections with data source objects, which permit ad hoc connections to remote data sources without specifically configuring those linked or remote servers. Enable this feature if your applications or scripts use OPENROWSET with Data Mining; otherwise, disable it.

AnonymousConnectionsEnabled

With anonymous connections, unauthenticated users could establish connections with Analysis Services. Enable this feature if your applications and scripts require unauthenticated user access. Otherwise, disable this feature.

LinkedObjectsLinksFromOtherInstancesEnabled

With Analysis Services, you can use linked objects to link dimensions and measure groups between servers. If you want to link an instance to other servers, enable this feature. Otherwise, disable it.

ListenOnlyOnLocalConnections

Analysis Services works with remote resources, as well as local resources. If you want Analysis Services to work only with local resources, enable this feature. Otherwise, disable this feature.

UserDefinedFunctionsEnabled

Analysis Services is integrated with the .NET Framework. It can load assemblies containing user-defined functions. Enable this feature if your applications and scripts require user-defined COM functions. Otherwise, configure this feature to permit only CLR functions.

REPORTING SERVICES

ScheduledEventsAndReportDeliveryEnabled

With Reporting Services, you can use ad hoc, on-demand reports and scheduled reports. Typically, when you install Reporting Services, it enables both types of reports. If you don’t use scheduled reports, you can disable this aspect of report generation and delivery by disabling this feature.

WebServiceRequestsAndHTTPAccessEnabled

Reporting Services components use SOAP messaging over HTTP for communications and HTTP for URL access requests. Enable this feature if your client applications use the Report Server Web Service or if you use Report Manager, Report Designer or SQL Server Management Studio with this Reporting Services installation. Otherwise, disable it.

ReportManagerEnabled

Report Manager is a Web-based application for viewing reports, managing report server content and controlling access to report servers running in Native Mode. Enable this feature if you use Report Manager with this Reporting Services installation. Otherwise, disable this feature.

Always a Hot Topic

If ensuring physical security, network security and attack-surface minimization really was all there was to mitigating threats and vulnerabilities in SQL Server, security wouldn’t be such a hot-button issue. But security is a hot-button issue for SQL Server, and it’s likely to remain so indefinitely because your company’s data is one of its most valuable assets.

By default, all of the surface-area features listed above are disabled in SQL Server 2008. You can manage them using Policy-Based Management. To learn about SQL Server policies, look at the Administering Servers by Using Policy-Based Management guidelines.

There are a number of excellent resources to take you through the fundamentals. The “Physical Security at Microsoft” white paper provides a comprehensive look at physical security practices. For help configuring networking for servers and clients, you’ll find good resources in the Server Network Configuration and Client Network Configuration sections of the SQL Server 2008 R2 books online.

Art vs. Science

When it comes to database and data security, simply applying best practices alone doesn’t always get the job done. Threat and vulnerability mitigation is as much an art as it is a science. Sometimes it seems as if you have to be part ninja to get the job done right. To be a ninja means to try to think like the opposition so you can beat the opposition, but in real life, we’re unlikely to imagine all the sophisticated ways someone can attack a system.

Still, you need to be aware of current threats. You need to know the attack vectors hackers typically use. You need to know how to analyze your client-server environments with certain threats in mind and how to mitigate those threats using the available tools and techniques. Security education is as important as your mitigation efforts. Internal threats represent as much risk as external threats. For example, there’s a risk when a user—administrator or not—with database privileges leaves their computer unlocked and unattended.

Extend your security practices beyond server policy to your corporate policy and practices to enhance security. Combat all threats—internal and external—by approaching data and server security holistically. Just as products have a lifecycle, so to should your security program. You could call it the Security Operations Lifecycle (SOL).

As part of the SOL, everyone who works with SQL Server client applications and SQL Server should have annual formal security training. For every technical team member, this training should cover security testing, threat modeling, current vulnerabilities and exploits, and security response examination. Beyond formal training, technical team members need to make security part of their everyday work. You don’t “set it and forget it” when it comes to security. You bake security into your everyday processes by:

  • Periodically testing for vulnerabilities and exploits
  • Routinely monitoring for security problems
  • Modeling potential problems
  • Planning your response to inevitable incidents

Every technical member of your organization should be expected to have a solid understanding of the SOL. Anyone who works with SQL Server through client applications should also have a basic understanding of the SOL. The need for ongoing vigilance is critical. You really have to believe that secure operations require constant vigilance. There’s no such thing as zero vulnerability.

A World Full of Bad Guys

Hackers, crackers and malicious insiders are out there waiting and looking for the inevitable chink in your security armor. Don’t let unintended vulnerabilities negate or diminish the effectiveness of your security planning.

Use secure-by-default strategies to ensure that your SQL Servers are as secure as possible. Use defense-in-depth strategies to ensure that each layer from Web and client interfaces to your SQL Server databases and file systems are as secure as possible. These strategies will help you maintain effective barriers that provide more opportunities for detection along the way.

Using a secure-by-default and defense-in-depth approach does help, but the overarching goals of your security plan should be to reduce the number of vulnerabilities and their potential impact. Flaws are inevitable—how you react to their exploitation is paramount. You must have a prepared formal response and take appropriate action. You must believe that no incident is an isolated occurrence. Take the lessons you learn from each incident and disseminate them throughout your organization at the appropriate levels. Sharing this event data with the technical, development and operations teams will help to create an organizational security conscience.

Make it easy for your application users to report vulnerabilities and other issues. Use threat modeling to ensure features and functions are operated with security in mind. Perform threat modeling by asking what threats you’re worried about, given the client-server nature of SQL Server database operations. Determine the attack surfaces of your front-end applications and back-end databases and take appropriate action.

Fuzz testing is essential to protect your back-end databases from front-end application exploits. Fuzz tests are intentional submissions of malformed data, such as data and values outside the bounds of what an application was designed to accept. An application that crashes or overflows buffers as a result of fuzz testing may open a potential SQL Server exploit.

Your technical teams know how your front-end applications are written and how SQL Server queries work. Use that knowledge to perform deeper fuzz testing. Your insider knowledge can help you create fuzz tests that get past initial checks and, in this way, you can perform deeper testing.

Adopting a lifecycle approach to security makes security an ongoing process. Change is continuous and the emphasis on the SOL should be as well.

For additional threat and vulnerability mitigation step-by-step procedures by SQL Server 2008 R2 components, look here:

Joshua Hoffman

William R. Stanek*(williamstanek.com) is a leading technology expert, an instructional trainer, and the award-winning author of more than 100 books. Follow Stanek on Twitter at [https://twitter.com/WilliamStanek]https://(twitter.com/williamstanek).*

 

Related Content