Securing SQL Server

Article
12/03/2008

New: 12 December 2006

Securing SQL Server involves three areas: the platform and network, principals and securables, and applications that access the database. The following topics will guide you through creating and implementing an effective security plan.

You can find more information on SQL Server security at the SQL Server Web site, including 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, as well as the binary files 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 begins with keeping unauthorized users off the network. The following chart contains more information on networking security information.

For information about	See
Networking and SQL Server	Network Protocols, and TDS Endpoints
Specifying and restricting ports used for SQL Server	Configuring Server Network Protocols and Net-Libraries
Restricting network access to SQL Server	Restricting Network Access
SQL Server 2005 Compact Edition and network access to other SQL Server editions	"Configuring and Securing the Server Environment" in SQL Server 2005 Compact Edition Books Online
Backup and restore strategies	Security Considerations for Backup and Restore.

Operating System Security

Operating system service packs and upgrades include important security enhancements. Apply all patches and upgrades to the operating system after testing them with your 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. Using a firewall increases security at the operating system level by providing a chokepoint where your security measures can be focused. The following table contains more information on using a firewall with SQL Server.

For information about	See
Configuring a firewall to work with SQL Server	How to: Configure a Firewall for SQL Server Access
Configuring a firewall to work 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 allow 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 to 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 with "least privilege" by only granting services and users the appropriate rights. The following chart contains more information on 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 secure the surface of the platform. The following chart contains information on SQL Server and Internet Information Services.

For information about	See
IIS security with SQL Server 2005 Compact Edition	"IIS Security" in SQL Server 2005 Compact Edition Books Online
Using XML services in SQL Server and IIS	Best Practices for Using Native XML Web Services
Report servers and Internet access	Configuring a Report Server for Internet Access
Setting up forms security on a report server	Security Extension Sample
Reporting Services Authentication	Authentication in Reporting Services
SQL Server 2005 Compact Edition and IIS access	"Internet Information Services Security Flowchart" in SQL Server 2005 Compact Edition 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 chart contains information on these files.

For information about	See
SQL Server program files	File Locations for Default and Named Instances of SQL Server 2005
Database file security	Securing Data and Log Files
Notification Services file security	Securing Files and Folders
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 your 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 further minimize the SQL Server surface area. The following chart contains information on principals and securables.

For information about	See
Server and database users, roles and processes	Principals
Server and database objects security	Securables
The SQL Server security hierarchy	Permissions Hierarchy

For more information on database and application security, see Security Considerations for Databases and Database Applications.

Encryption and Certificates

Encryption does not solve access control problems. However, it enhances security by limiting data loss even in the normally rare occurrence that access controls are bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive data, such as credit card numbers, that stolen information is useless if it is encrypted. The following chart contains more information on 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 allow secure communications by way of strong authentication. You can create and use certificates in SQL Server to enhance object and connection security. The following chart contains information on using 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 certificates with SQL Server Service Broker	Certificates and Service Broker
Using certificates with database mirroring	Using Certificates for Database Mirroring

Application Security

SQL Server security best practices include writing secure client applications. For more information on server access and SQL Server client applications, see SQL Server Programming Overview.

For more information on securing client applications at the networking layer, see Client Network Configuration.

For more information on writing 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 chart contains information on 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 from the command prompt	sqlcmd Utility
Network configuration and control for SQL Server	SQL Server Configuration Manager
Configuring features and connectivity options	SQL Server Surface Area Configuration
Configuring features and connectivity options from the command prompt	sac Utility
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 chart contains information on 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 also 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, schemas, and so on.	Security Functions (Transact-SQL)

Share via