Securing SQL Server

Securing SQL Server can be viewed as a series of steps, involving four areas: the platform, authentication, objects (including data), and applications that access the system. The following topics will guide you through creating and implementing an effective security plan.

You can find more information about SQL Server security at the SQL Server Web site. This includes a best practice guide and a security checklist. This site also contains the latest service pack information and downloads.

Platform and Network Security

The platform for SQL Server includes the physical hardware and networking systems connecting clients to the database servers, and the binary files that are used to process database requests.

Physical Security

Best practices for physical security strictly limit access to the physical server and hardware components. For example, use locked rooms with restricted access for the database server hardware and networking devices. In addition, limit access to backup media by storing it at a secure offsite location.

Implementing physical network security starts with keeping unauthorized users off the network. The following table contains more information about networking security information.

For information about

See

Networking and SQL Server

Network Protocols and TDS Endpoints

Specifying and restricting ports that are used for SQL Server

Configuring Server Network Protocols and Net-Libraries

Restricting network access to SQL Server

Restricting Network Access

SQL Server Compact 3.5 SP1 and network access to other SQL Server editions

"Configuring and Securing the Server Environment" in SQL Server Compact 3.5 SP1 Books Online

Backup and restore strategies

Security Considerations for Backup and Restore (SQL Server).

Operating System Security

Operating system service packs and upgrades include important security enhancements. Apply all updates and upgrades to the operating system after you test them with the database applications.

Firewalls also provide effective ways to implement security. Logically, a firewall is a separator or restrictor of network traffic, which can be configured to enforce your organization's data security policy. If you use a firewall, you will increase security at the operating system level by providing a chokepoint where your security measures can be focused. The following table contains more information about how to use a firewall with SQL Server.

For information about

See

Configuring a firewall to work with SQL Server

How to: Configure a Windows Firewall for Database Engine Access

Configuring a firewall to work with Integration Services

Configuring a Windows Firewall for Integration Services Access

Configuring a firewall to work with Analysis Services

How to: Configure Windows Firewall for Analysis Services Access

Configuring a firewall to work with Reporting Services

Server Deployment Checklist

Opening specific ports on a firewall to enable access to SQL Server

Opening Ports in the Firewall

Surface area reduction is a security measure that involves stopping or disabling unused components. Surface area reduction helps improve security by providing fewer avenues for potential attacks on a system. The key to limiting the surface area of SQL Server includes running required services that have "least privilege" by granting services and users only the appropriate rights. The following table contains more information about services and system access.

For information about

See

Services required for SQL Server

Setting Up Windows Service Accounts

Restricting server logon access

Restricting Interactive Logon Access

Local administration rights

Granting Local Administrative Rights

If your SQL Server system uses Internet Information Services (IIS), additional steps are required to help secure the surface of the platform. The following table contains information about SQL Server and Internet Information Services.

For information about

See

IIS security with SQL Server Compact 3.5 SP1

"IIS Security" in SQL Server Compact 3.5 SP1 Books Online

Using Web services in SQL Server and IIS

Best Practices for Using Native XML Web Services

Report servers and Internet access

Planning for Extranet or Internet Deployment

Reporting Services Authentication

Authentication in Reporting Services

SQL Server Compact 3.5 SP1 and IIS access

"Internet Information Services Security Flowchart" in SQL Server Compact 3.5 SP1 Books Online

SQL Server Operating System Files Security

SQL Server uses operating system files for operation and data storage. Best practices for file security requires that you restrict access to these files. The following table contains information about these files.

For information about

See

SQL Server program files

File Locations for Default and Named Instances of SQL Server

Database file security

Securing Data and Log Files

Analysis Services file security

Securing Program Files, Common Components, and Data Files

SQL Server service packs and upgrades provide enhanced security. To determine the latest available service pack available for SQL Server, see the SQL Server Web site.

You can use the following script to determine the service pack installed on the system.

SELECT CONVERT(char(20), SERVERPROPERTY('productlevel'));
GO

Principals and Database Object Security

Principals are the individuals, groups, and processes granted access to SQL Server. “Securables” are the server, database, and objects the database contains. Each has a set of permissions that can be configured to help reduce the SQL Server surface area. The following table contains information about principals and securables.

For information about

See

Server and database users, roles, and processes

Principals (Database Engine)

Server and database objects security

Securables

The SQL Server security hierarchy

Permissions Hierarchy (Database Engine)

For more information about database and application security, see Identity and Access Control (Database Engine).

Encryption and Certificates

Encryption does not solve access control problems. However, it enhances security by limiting data loss even in the rare occurrence that access controls are bypassed. For example, if the database host computer is misconfigured and a malicious user obtains sensitive data, such as credit card numbers, that stolen information might be useless if it is encrypted. The following table contains more information about encryption in SQL Server.

For information about

See

The encryption hierarchy in SQL Server

Encryption Hierarchy

Encrypting SQL Server connections

Encrypting Connections to SQL Server

Implementing secure connections

How to: Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager)

Encryption functions

Cryptographic Functions (Transact-SQL)

Implementing encryption

Encryption How-to Topics

Setting up Analysis Services for data encryption

Requiring Data Encryption

Certificates are software "keys" shared between two servers that enable secure communications by way of strong authentication. You can create and use certificates in SQL Server to enhance object and connection security. The following table contains information about how to use certificates with SQL Server.

For information about

See

Using a certificate for secure connections

Configuring Certificate for Use by SSL

Creating a certificate for use by SQL Server

CREATE CERTIFICATE (Transact-SQL)

Using a certificate with SQL Server Service Broker

Certificates and Service Broker

Using a certificate with database mirroring

Using Certificates for Database Mirroring

Application Security

SQL Server security best practices include writing secure client applications. For more information about server access and SQL Server client applications, see Developer's Guide (Database Engine).

For more information about how to help secure client applications at the networking layer, see Client Network Configuration.

For more information about how to write applications that use native XML services, see Writing Client Applications.

SQL Server Security Tools, Utilities, Views, and Functions

SQL Server provides tools, utilities, views, and functions that can be used to configure and administer security.

SQL Server Security Tools and Utilities

The following table contains information about SQL Server tools and utilities that you can use to configure and administer security.

For information about

See

Connecting to, configuring, and controlling SQL Server

Introducing SQL Server Management Studio

Connecting to SQL Server and running queries at the command prompt

sqlcmd Utility

Network configuration and control for SQL Server

SQL Server Configuration Manager

Enabling and disabling features by using Policy-Based Management

Administering Servers by Using Policy-Based Management

Manipulating symmetric keys for a report server

rskeymgmt Utility

SQL Server Security Catalog Views and Functions

The Database Engine exposes security information in several views and functions that are optimized for performance and utility. The following table contains information about security views and functions.

For information about

See

SQL Server security catalog views, which return information about database-level and server-level permissions, principals, roles, and so on. In addition, there are catalog views that provide information about encryption keys, certificates, and credentials.

Security Catalog Views (Transact-SQL)

SQL Server security functions, which return information about the current user, permissions and schemas.

Security Functions (Transact-SQL)

SQL Server security dynamic management views. 

Security-Related Dynamic Management Views and Functions (Transact-SQL)