Revision as of 15:18, 29 June 2008

Overview

In this section there are some best practices concerning the security of SQL Server 2005. The operating system under SQL Server is Windows Server 2003.

Description

Installation of the Engine

The prerequisites for the installation are:

• .NET Framework 2.0
• Microsoft SQL Native Client
• Microsoft SQL Server 2005 Setup Support Files.

The installation consist of a large amount of services that are shortly descripted:

• SQL Server Database Services (install SQL Server database engine and tools for managing relational and XML data, replication and full text search)
• Analysis Services (install analysis services and tools used to support online analytical procession OLAP and data mining. Install also Integration Services)
• Notification Services (installs notification services a platform for developing and deploying applications that send personalized, timely notifications to a variety of devices or applications)
• Integration Services (install a set of tools and programmable objects for creating and managing packages that extract, transofrm and load data, as well perform task)
• Client Components (install management tools, development tools and legacy components)
• Documentation, samples and sample databases (installs books online documentation, sample databases and sample applications for all sql 2005 components)

During the installation the thing to remind is that from a security point of view, only what is strictly needed must be installed. To install a tipycal minimal configuration, the SQL Server Database Services and some Client Components (Connectivity components and Management Tools) can be installed.

Services

In SQL Server every service can run under a particular Windows account. The choices for the service's accounts are:

• Local user that is not a Windows administrator
• Domain user that is not a Windows administrator
• Local Service account
• Network Service account
• Local System account
• Local user that is a Windows administrator
• Domain user that is a Windows administrator

There is not only a solution to configure the service's account, but there are two rules to follow that enforce to behave in certain way:

• minimum privileges
• account isolation

Follow this, probably an administrator account (local or domain) has much more privileges than needed, indipendently of the service. The Local System account has to many privileges and it's not indicated for a service's account On the other hand Local Service and Network Service have not much privileges, but they are used for more Windows services, so there is no account isolation.

So the more secure solution for the Sql Server Service's Account is to use Local User or Domain User not Administrators. For example imagine that are installed the trhee main services:

• Sql Server
• Sql Server Agent
• Sql Server Browser

The task is to create three Windows Local User Account, belonging to User Group, protected with password, and assign them to the services. In this manner there are exactly two concepts: minimum privileges and account isolation.

Authentication Mode

Sql Server provides two kinds of authentication: SQL Server Authentication and Windows Authentication. During the installation is possible to enable both (Mixed Mode) or only the Windows Authentication (Windows Mode)

If there is an homogeneous Windows environment, the more secure solution is to enable the Windows mode Authentication, because the administration of the logins is made in the Windows Server and the credentials are not passed through the network, because Windows Authentication uses NTLM or Kerberos Protocols. If there is an heterogeneous environment, for example no domain controller or there are some legacy applications that have to connect to Sql Server, only the Mixed Mode solution is possible. In this second case the administration of the logins is made inside SQL Server and the credentials are necessarily passed through the network.

Is important to say that in a Windows Mode Authentication, the "sa" user (system administrator) is not enabled. In a mixed mode is enabled. So in an environment with Mixed Mode Authentication, to avoid the attacks against "sa" user, is better to:

• rename "sa" with another name
• use a strong password for the renamed "sa"

Processes

Every services that is installed in Sql Server could be administrated through the tool "Sql Server Configuration Manager" that is possible to install, enabling the client component of Sql Server. With this tool is possible to realize the two best practices for the account's services, assigning to every service a specific account protected with password, that authenticates against Windows. Every service could be started or stopped in a manual or automatic manner, like other Windows Services.

Configuration tools provided

Surface Area Reduction (services and connections)

The Surface Area Reduction is a powerful tool provided with Sql Server 2005 to configure:

• Services & Connections

Services

Every Service installed could be rapidly managed. It's posssible in every moment to:

• Manage the status of the service with the possibilities: Start/Stop & Pause/Resume
• Manage the action of the operating system on startup for that service: Automatic, Manual, Disabled.

The concept is to configure automatic start only for those services that are immediately needed, disabling or manually starting others services that are not necessary.

Connections

For every instance of SQL Server is possible to allow:

• Only local connection to the server
• Local and remote connections to the server

In a distributed environment probably it's necessary to allow both the connection's type, but it's easy to understand that allowing remote connections expose the server more easily. So for the remote connections Sql Server could allow two kind of protocols:

• TCP/IP
• Named Piped

For the normal use the better thing is to configure only TCP/IP, because Named Pipes need more open port to work. Additionally there are others two kinds of connections to the server:

• VIA
• Shared Memory

VIA (Virtual Interface Adapter protocol ) works with VIA hardware. Shared Memory is a protocol that is possible to use only by local connections. Using the Sql Server Configuration Manager the best solution is enable TCP/IP for remote connections and Shared Memory for the local connections.

Surface Area Reduction (features)

This is a series of interfaces for enabling or disabling many Database Engine, Analysis Services, and Reporting Services features. The most important are the features of the Database Engine:

• Ad hoc distributed queries
• Common language runtime (CLR) integration
• Dedicated administrator connection (DAC)
• Database Mail
• Native XML Web services
• OLE Automation stored procedures
• Service Broker
• SQL Mail
• Web Assistant stored procedures
• xp_cmdshell

Ad hoc distributed queries (default is disabled)

This feature enable the OPENROWSET and OPENDATASOURCE calls, to connect to an OLE DB data source. To use this feature launch:

EXEC sp_configure 'Ad Hoc Distributed Queries',1
GO
RECONFIGURE
GO

It's recommended to disable this feature, because in a case of Sql Injection, an attacker could use OPENROWSET to connect to a database.

Common language runtime (CLR) integration (default is disabled)

This feature give the possibility to write stored procedures, triggers, user defined functions using a .NET Framework language. So if the server is not used with this aim, or if all the databases object are written with T-SQL, there's no reason to enable this. To use this feature launch:

EXEC sp_configure 'clr enabled',1
GO
RECONFIGURE
GO

Dedicated administrator connection (DAC) (default is disabled)

This feature enables the possibility to execute diagnostics queries and troubleshoot problems when there are some problems to connect with standard mode to the server. The uses are tipically:

• Querying some dynamic management views of SQL Server to obtain informations about the "status health"
• Basic DBCC commands (for example DBCC SHRINKDATABASE to cut the log file)
• Kill the PID of processes

But for example is possible to do other works, like add logins with sysadmin privileges or other administrator's tasks This is an emergency type of connection, that SQL Server can permit every time, reserving an amount of resources during startup. It permits only a user a time, so if there is an user connected via DAC, no others users can connect. It's possible to use it with SQLCMD or Sql Server Management Studio, through the sintax:

Admin:<namepc>\<nameSqlInstance>

So DAC is only another type of connection, and in every time the credentials must be given to the server, but if it's not necessary, disable it. To use this feature launch:

EXEC sp_configure 'remote admin connections',1
GO
RECONFIGURE
GO

Database mail (default is disabled)

The database mail feature enables the possibility to use the Sql Server Instance to send email. If it's a powerful solution to give some advices to a sysadmin, from a security point of view it's a good practice to use the instance with the only aims that needed. To use this feature launch:

EXEC sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO

Native XML Web services (default no SOAP endpoints are created)

This feature basically gives the possibility to use SQL Server as a Web Services provider. It's possible to create HTTP Endpoints in Sql Server, associated for example to a result of stored procedure. A SOAP client could consume a service simply invoking the correct url provided by Sql Server, without others layers (tipically an IIS web server in wich the web services are hosted). Enabling this feature and create HTTP endpoints goes in the direction to increase the surface of attack. Every client could produce SOAP request, because SOAP grounds on its working to XML and HTTP, two standards.

To use this feature, first thing is to create an HTTP endpoint for SOAP, and then start or stop the service, with the Surface Area Reduction tool.

OLE Automation stored procedures (default is disabled)

This feature allows access properties and methods of an ActiveX object within SQL Server. If it's necessary to obtain informations inside a DLL that is not available inside SQL Server, it's possible to use some stored procedure to do the work, enabling this feature, but it's better to access the object with the right language, not with T-SQL. To use this feature launch:

EXEC sp_configure 'Ole Automation Procedures',1
GO
RECONFIGURE
GO

Service Broker (default no Service Broker endpoints is created)

Service Broker is a technology in which two or more entities accomplish a task, sending and receiving messages, in a asynchronous mode. As XML Web Services, to use this feature, a Service Broker endpoint must be created. A Service Broker endpoint listens on a specific TCP port numberm (tipically 4022, but could be configured during the endpoint's creation). The authentication against Service Broker could be BASIC, DIGEST, NTLM, KERBEROS, INTEGRATED.

To use this feature, first thing is to create a TCP endpoint for SERVICE_BROKER, and then start or stop the service, with the Surface Area Reduction tool.

SQL Mail (default is disabled)

SQL Mail is a feature for legacy applications, supported for backward compatibility, to send and receive email using MAPI protocol. This is replaced by Database Mail Feature. To use this feature launch:

EXEC sp_configure 'SQL Mail XPs',1
GO
RECONFIGURE
GO

Web Assistant stored procedures (default is disabled)

This feature enable stored procedures to generate HTML report from the data results. It's deprecated because there are others components (like Reporting Services) to present data in a browser. To use this feature launch:

EXEC sp_configure 'Web Assistant Procedures',1
GO
RECONFIGURE
GO

xp_cmdshell (default is disabled)

This feature enable the extended stored procedure "xp_cmdshell". By definition "executes a command string as an operating-system command shell and returns any output as rows of text". Enabling xp_cmdshell is potentially very dangerous, because it allows a complete interaction with the operating system, so it's possible to give every command. It's better to do administratives tasks not using SQL Server, so mantain disable xp_cmdshell. To use this feature launch:

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

Sql Server Configuration Manager (endpoints and protocols)

Sql Server Administrators

System Stored Procedure

Sql Server provides a lot of system stored procedures, to accomplish administrative tasks. Surface Configuration Area for features for example is a graphical interface that uses, as it's showed, the stored procedure sp_configure in conjunction with RECONFIGURE command to modify the server's configuration. So even if the server is well configurated avoiding every dangerous stored procedure (such as xp_cmdshell), if the application is vulnerable, a sql injection can cause the execution of the script:

EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO

Database Administration

Password Policies

Authorization

Roles and Schemas

Metadata Views

Linked Servers

Execution Context

Encryption

Symmetric

Asymmetric

Asymmetric with certificate

References