Surface Area Reduction
SQL Server 2012 installation minimizes the "attack surface" because by default, optional features are not installed. It is a best practice to review which product features you actually need and install only those features. Later, install additional features only as needed. It is easier to enable features when they are needed than it is to enable everything by default and then turn off features that you do not need. After the SQL Server is installed, you should turn off unneeded services by setting the service to either Manual startup or Disabled in the SQL Server Configuration Manager. Configure only those server network interfaces that you will actually use, mostly TCP/IP protocol only is enough.
Service Account Selection and Management
When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges. You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server.
Windows Server 2008 R2 and Windows 7 operating systems introduced two new types of service accounts: Virtual accounts and managed service accounts. SQL Server 2012 is the first version of SQL Server to permit these accounts to be used as service accounts. A managed service account is a special type of domain account that can be assigned to a single computer and used to manage a service. It must be provisioned by the domain administrator prior to being used. This type of account cannot be used to log in to a computer and provides automatic SPN and password management, once provisioned. Virtual accounts are managed local accounts that is automatically provisioned and managed. In SQL Server 2012, they are the default service account specified during setup. It can access the network. A virtual service account has a well-known name in the form of NT SERVICE\<SERVICENAME> and can access the network using the credentials <domain_name>\<computer_name>$. If the server that is running SQL Server is part of a domain and must access domain resources such as file shares or uses linked server connections to other computers running SQL Server, a managed service account is the best choice. If the server is not part of a domain (for example, a server running in the perimeter network (also known as the DMZ) in a Web application) and does not need to access domain resources, a virtual account is preferred. Using a local user or domain user that is not a Windows administrator is also a good choice.
The SQL Server Agent service account requires SQL Server sysadmin privilege in the SQL Server instance that it is associated with. In SQL Server 2005 and above, SQL Server Agent job steps can be configured to use proxies that encapsulate alternate credentials. A CREDENTIAL is simply a database object that is a symbolic name for a Windows user and password. To accommodate the principal of least privilege, do not give excessive privileges to the SQL Server Agent service account. Instead, use a proxy that corresponds to a CREDENTIAL that has just enough privilege to perform the required task.
Always use SQL Server Configuration Manager to change service accounts. Using Configuration Manager ensures that the new service account is placed in the appropriate Windows group, and is thus granted exactly the correct privileges to run the service.
SQL Server Best Practices Analyzer and other analysis utilities
You can download the SQL Server Best Practices Analyzer (BPA) from the Microsoft Download Center. BPA gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server recommendations and best practices to determine if there are potential security issues in the database environment.
When you configure your antivirus software settings, make sure that you exclude the following files or directories from virus scanning. Doing this improves the performance of the files and helps make sure that the files are not locked when the SQL Server service must use them.
- SQL Server data and transaction log files, usually have one of the following extensions:
.mdf
.ldf
.ndf
- SQL Server backup files, usually have one of the following extensions:
.bak
.trn
- Full-Text catalog files:
Default instance: Program Files\Microsoft SQL Server\MSSQL\FTDATA
Named instance: Program Files\Microsoft SQL Server\MSSQL$instancename\FTDATA
- Trace files, usually .trc, can be generated by configure profiler tracing manually or C2 auditing.
- SQL audit files (SQL 2008 or later), usually have the .sqlaudit extension.
- SQL query files, usually have the .sql extension.
- Filestream data files (SQL 2008 or later)
- Remote Blob Storage files (SQL 2008 or later)
- The directory that holds Reporting Services temporary files and Logs (RSTempFiles and LogFiles)
Processes to exclude from virus scanning
SQL Server 2012
%ProgramFiles%\Microsoft SQL Server\MSSQL11.
%ProgramFiles%\Microsoft SQL Server\MSRS11.
%ProgramFiles%\Microsoft SQL Server\MSAS11.
SQL Server 2008 R2
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.
%ProgramFiles%\Microsoft SQL Server\MSSQL10_50.
You can run antivirus software on a SQL Server cluster. However, you must make sure that the antivirus software is a cluster-aware version. Contact your antivirus vendor about cluster-aware versions and interoperability. If you are running antivirus software on a cluster, make sure that you also exclude these locations from virus scanning:
- Q:\ (Quorum drive)
- C:\Windows\Cluster
(Reference: KB 309422)
Service Pack and Hotfix
Download the latest service pack and security hotfix on SQL Server and Windows as soon as possible, but should be tested before they are applied to production systems.
Industry Compliance
SQL Server allows configuring an option that provides three elements required for Common Criteria compliance. You should enable Common Criteria compliance only if required. These elements can be configured by using an instance configuration option:
- Residual Information Protection, which overwrites memory with a known bit pattern before it is reallocated to a new resource.
- The ability to view login statistics.
- A column-level GRANT does not override table-level DENY.
You can configure an instance to provide these three elements for Common Criteria compliance by setting the configuration option common criteria compliance enabled as shown in the following code.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'common criteria compliance enabled', 1;
GO
RECONFIGURE;
GO
Data and Database Encryption
You can enable database-level data encryption (known as transparent data encryption or TDE) by running DDL statements. TDE is an Enterprise version-only feature. Enabling TDE on a database requires two keys: a database encryption key that uses symmetric key encryption to affect the encryption of the database and a certificate in the master database that protects the database encryption key. Because the certificate in the master database is needed to restore the database, care should be taken to back this certificate up. Below is the sample code:
--Check if the Database Master Key already present.
USE master;
GO
SELECT * FROM sys.symmetric_keys;
--Drop the existing Master Key.
USE master;
GO
BEGIN TRY
DROP MASTER KEY;
END TRY
BEGIN CATCH
PRINT 'Master Key NOT exists.';
END CATCH
GO
--Create Master Key in master database.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'master key password';
GO
--Create Server Certificate in the master database.
USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master key password';
CREATE CERTIFICATE SQL_TDE_CERT WITH SUBJECT = 'SQL TDE CERT';
GO
--Create User Database Encryption Key.
USE UserDB;
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE SQL_TDE_CERT;
GO
--Check Database Encryption Key created.
--The tempdb system database will also be encrypted
--if any other database on the instance of SQL Server is encrypted by using TDE.
SELECT DB_NAME(database_id) AS database_name, * FROM sys.dm_database_encryption_keys;
--Enabling Transparent Database Encryption for the User Database.
USE master;
GO
ALTER DATABASE UserDB SET ENCRYPTION ON;
GO
--Check User Database is encrypted.
SELECT name, is_encrypted FROM sys.databases;
--Backup Master Key to file.
USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master key password';
BACKUP MASTER KEY TO FILE = 'D:\MSSQL_TDE_KEYS\MasterKey.mtk' ENCRYPTION BY PASSWORD = 'master key password';
GO
--Backup Server Certificate.
USE master;
GO
BACKUP CERTIFICATE SQL_TDE_CERT TO FILE = 'D:\MSSQL_TDE_KEYS\ServerCert.cer'
WITH PRIVATE KEY ( FILE = 'D:\MSSQL_TDE_KEYS\PrivateKey.pvk', ENCRYPTION BY PASSWORD = 'master key password');
GO
TDE encrypts data as it in written to disk and decrypt data as it is read from disk. TDE encrypts data files, log files, tempdb, and database backups, but does not encrypt data stored using FILESTREAM storage feature. Use data encryption only when it is required or for very high-value sensitive data.
SSL Encryption
Microsoft SQL Server can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application. The SSL encryption is performed within the protocol layer and is available to most of the SQL Server clients. Enabling SSL encryption increases the security of data transmitted across networks between instances of SQL Server and applications. However, enabling SSL encryption does slow performance.
(Reference: KB 316898)
Administrator Privileges
Avoid relying on the Windows OS Administrators group and add only specific database administrators to the SQL Server sysadmin role. Minimizing the number of administrators who have sysadmin or CONTROL SERVER privilege, fewer logins with administrator privilege means fewer people to check with if things go wrong. The permission VIEW SERVER STATE is useful for allowing administrators and troubleshooters to view server information (dynamic management views) without granting full sysadmin or CONTROL SERVER permission.
Cross-Database Ownership Chaining
SQL Server can be configured to allow ownership chaining between specific databases or across all databases inside a single instance of SQL Server. Cross-database ownership chaining is disabled by default, and should not be enabled unless it is specifically required.
TRUSTWORTHY Database Property
You can use the TRUSTWORTHY database setting to indicate whether the instance of Microsoft SQL Server trusts the database and the contents within the database. By default, this setting is set to OFF. However, you can set it to ON by using the ALTER DATABASE statement. It's recommended to leave this setting set to OFF. If the TRUSTWORTHY setting is set to ON, and if the owner of the database is a member of a group that has administrative credentials, such as the sysadmin group, the database owner may be able to create and to run unsafe CLR assemblies that can compromise the instance of SQL Server.
System Extended Stored Procedures
System extended stored procedures, such as xp_cmdshell, are used to access resources outside the SQL Server instance. These procedures are off by default and should remain disabled. By the way, system stored procedures should not be dropped from the database; dropping these can cause problems when applying service packs. Removing the system stored procedures results in an unsupported configuration. It is usually unnecessary to completely DENY all users access to the system stored procedures, as these stored procedures have the appropriate permission checks internal to the procedure as well as external.
Schema
A schema is simply a named container for database objects. Each schema is a scope that fits into the hierarchy between database level and object level, and each schema has a specific owner. The owner of a schema can be a user, a database role, or an application role. Objects created in a schema are owned by the schema owner by default, not by the creator of the object. This makes it possible for a user to create tables in a known schema without the administrative problems that ensue when that user leaves the company or switches job assignments. It's suggested that like objects should be grouped together into the same schema, and manage database object security by using ownership and permissions at the schema level.
Authorization
In SQL Server, authorization is accomplished via Data Access Language (DAL). In addition to the two DAL verbs, GRANT and REVOKE, mandated by the ISO-ANSI standard, SQL Server also contains a DENY DAL verb. DENY differs from REVOKE when a user is a member of more than one database principal. If a user Fred is a member of three database roles A, B, and C and roles A and B are GRANTed permission to a securable, if the permission is REVOKEd from role C, Fred still can access the securable. If the securable is DENYed to role C, Fred cannot access the securable. The new granular permissions are also arranged in a hierarchy; some permissions imply other permissions. For example, CONTROL permission on a database object type implies ALTER permission on that object as well as all other object-level permissions. You can also grant permissions at the schema level, the user automatically has permissions on all new objects created in the schema; explicit grant after object creation is not needed.
A best practice for authorization is to encapsulate access through modules such as stored procedures and user-defined functions, rather than granting access permission directly on the underlying tables. Encapsulating access was dependent on a SQL Server feature known as ownership chains. In an ownership chain, if the owner of stored procedure A and the owner of table B that the stored procedure accesses are the same, no further permission check is required on table B when an user is granted to execute the stored procedure A.
A login can only be granted authorization to objects in a database if a database user has been mapped to the login. A special user, guest, exists to permit access to a database for logins that are not mapped to a specific database user. Because any login can use the database through the guest user, it is suggested that the guest user not be enabled except in the MSDB database. In order for some SQL Server features to work, the guest user must be enabled in MSDB.
System Catalog
Information about databases, tables, and other database objects is kept in the system catalog. The system metadata exists in tables in the master database and in user databases. These metadata tables are exposed through metadata views. The catalog views are secure by default. You can grant VIEW DEFINITION selectively at the object, schema, database, or server level to grant permission to view system metadata without conferring additional permissions.
Execution Context
SQL Server always executes SQL statements and procedural code as the currently logged on user. This behavior is a SQL Server-specific behavior and is made possible, in the case of procedural code, by the concept of ownership chains. EXECUTE AS CALLER is the default in the CREATE PROCEDURE, FUNCTION, and TRIGGER statements.
EXECUTE AS can also be used to set the execution context within an SQL batch. In this form, the SQL batch contains an EXECUTE AS USER='someuser' or EXECUTE AS LOGIN='somelogin' statement. This alternate execution context lasts until the REVERT statement is encountered.
When a procedure or batch uses an alternate execution context, the system functions normally used for auditing, such as SUSER_NAME(), return the name of the impersonated user rather than the name of the original user or original login. A new system function, ORIGINAL_LOGIN(), can be used to obtain the original login, regardless of the number of levels of impersonation used.
Authentication Modes and Logins
SQL Server has two authentication modes: Windows Authentication and Mixed Mode Authentication. In Windows Authentication mode, specific Windows user and group accounts are trusted to log in to SQL Server. Windows accounts use a series of encrypted messages to authenticate to SQL Server; no passwords are passed across the network during the authentication process. In Mixed Mode Authentication, both Windows accounts and SQL logins are permitted. When SQL logins are used, SQL login passwords are passed across the network for authentication. It is a best practice to use only Windows logins whenever possible. SQL logins should be confined to legacy applications, which include applications purchased from third-party vendor and the authentication cannot be changed, cross-platform client-server applications in which the non-Windows clients do not possess Windows logins, and applications that require logins from untrusted domains. In Mixed Mode Authentication, always use a strong password for the sa account and change the sa account password periodically. Rename the sa account to a different account name to prevent attacks on the sa account by name.
SQL Server contains some pre-defined logins such as NT AUTHORITY\SYSTEM and ##MS_PolicyEventProcessingLogin##. These logins are used for SQL Server built-in functionality and should not be deleted.
Windows logins can be based on Windows Groups in addition to being based on Windows Users. Using Windows User instead of Windows Group provides the ability to identify individual Windows Users for tracking purposes.
Password Policy
Windows logins abide by the login policies of the underlying operating system. These policies can be set using the Domain Security Policy or Local Security Policy. In SQL Server 2005 and above, SQL logins can also go by the login policies of the underlying operating system. You should mandate a strong password policy, including an expiration and a complexity policy for your organization. The CREATE LOGIN DDL statement has parameters to determine whether the login goes by the operating system policies. These parameters are: CHECK_POLICY, CHECK_EXPIRATION, and MUST_CHANGE.
Contained Database
A contained database is a database that is isolated from the instance of SQL Server that hosts the database. User authentication can be performed by the database, reducing the databases dependency on the logins of the instance of SQL Server, i.e. users can log directly into a contained database without a corresponding login. Contained databases support two types of users: Windows users and groups that can directly connect to the database and do not need logins, and users with a password where the password is authenticated by the database, not the instance. Contained databases allow users with the ALTER ANY USER permission, such as database owners, to authorize new users of the database. This reduces the access control of the sysadmin fixed server role. Contained database authentication is disabled by default and you should only turn this setting on if it is required. You should protect the backups of contained databases using passwords, and disable the guest account on databases that share an instance with contained databases.
Network Security
As part of SQL Server 2012 installation, a warning message will occur if Windows Firewall is not enabled on the server machine. It is a general network security best practice to enable Windows Firewall and restrict network protocols and ports to the minimum necessary for SQL Server operation. By default, Service Broker or database mirroring endpoints are off, you should enable them only when needed. The dedicated administrator connection (DAC) is available only locally by default, although it can be made available remotely. Access to database endpoints requires the login principal to have CONNECT permission. By default, no login account has CONNECT permission to Service Broker and mirroring endpoints. It is a best practice to enable only those protocols that are needed. For example, if TCP/IP is sufficient, there is no need to enable the Named Pipes protocol. You should configure named instances of SQL Server to use specific port assignments for TCP/IP rather than dynamic ports.
You can GRANT, REVOKE, or DENY permission to CONNECT to a specific endpoint on a per-login basis. By default, all logins are GRANTed permission on the Shared Memory, Named Pipes, and TCP/IP endpoints. You must specifically GRANT users CONNECT permission to other endpoints; no users are GRANTed this privilege by default. An example of granting this permission is:
GRANT CONNECT on ENDPOINT::Mirroring_Endpoint TO [Adomain\Otheruser];
Auditing
In SQL Server 2012, Server Audit Specification is available in any edition of SQL Server, but SQL Server Database Audit Specification is available as an enterprise-only feature. SQL Server Audit can use a file as an auditing target but can also audit to the Windows Security Log. The Windows Security log is considered to be resistant to tampering and nonrepudiation. Audit metadata is defined using DDL and therefore can be managed using standard SQL Server permissions. Changes to the audit metadata, as well as enabling and disabling audit sessions, are also audited. In SQL Server 2012, you can configure the audit object to fail the database operation associated with the audit object, but not shutdown the server. This allows maximum availability because un-audited operations continue to work.
SQL Server Audit uses the Extended Events feature to minimize impact on performance. Extended Events are events that are built into the SQL Server code to have minimal impact. Audits can be written synchronously or asynchronously, with a configurable queue delay to accommodate a trade-off between database performance and possible audit record loss. Another way to minimize performance impact is to use more granular auditing. This feature, using Database Audit Specifications, is an Enterprise-only feature.
Three new database objects are used to manage the audit feature: SERVER AUDITs, SERVER AUDIT SPECIFICATIONs, and DATABASE AUDIT SPECIFICATIONs. An AUDIT object resides in the master database and defines where the audit information will be stored, a file rollover policy (if using file targets), a queue delay, and whether or not to shut down the instance if audit records cannot be written. A SERVER AUDIT SPECIFICATION or DATABASE AUDIT SPECIFICATION specifies what accesses and what principals to audit at a server or database level. One or more specifications are associated with an AUDIT object to connect what is auditing to where the information is to be written. Auditing information is stored in binary when written to file targets and can be read with a table-valued function fn_get_audit_file(). Audit information written to the Windows log can be read using any of the Windows log-reading utilities, such as Windows Event Viewer. Both file and Windows log-based auditing information can also be read directly with SQL Server Management Studio.
No comments:
Post a Comment