443 Chapter4.Designing Database Server Security Policies

Designing Database Server Security Policies
Lesson 1:  Enforcing SQL Server Security through Group Policy
1. What is Group Policy
(1) Group policy is a framework that administrators use to control the configuration of users and computers in an Active Directory domain.
 Security options affecting passwords policies
 Software settings affecting application availability
 Desktop configuration settings affecting the start menu appearance
(2) Group policy settings are contained in Group Policy Objects (GPOs), which in turn are associated with selected Active Directory objects: sites, domains, or organizational units.
(3) Local computer policy
Every computer running Windows 2000 or later includes a Local Computer policy, sometimes call a local GPO.
Command name: gpedit.msc; if the GPO, MMC—group policy editor—scope: Default domain policy
get the local GPO
(4) Order of policy processing
No Order Description
1 Local GPO, Each computer has exactly one GOP that is stored locally.
2 Site, Any GPOs that have been linked to the site are processed next. Processing of multiple site-linked GPOs is applied sequentially and in the order specified by the administrator
3 Domain, Processing of multiple domain-linked GPOs is applied sequentially and in the order specified by the administrator
4 OUs, GPOs that are linked to the OU that is highest in the Active Directory hierarchy are processed first, then GPOs that are linked to its child OU, and so on. Finally, the GPOs that are linked to the OU that contains the user or computer are processed.
(5) Administrative templates, enables user to control the registry settings for users and computers through Group Policy.
(6) Configuring Windows Server Update Services (WSUS)
2. Password Policy
(1) In Windows, we can configure password policies either in a local GPO or in a GPO in Active Directory
(2) Enforcing password policy
(3) Enforcing password expiration
(4) Password Complexity, password complexity requirements are designed to deter brute force attacks by eliminating common passwords and by increasing the number of possible passwords
 The password cannot contain all or part of the user’s account name
 The password is at least eight characters long
 The password contains characters from three of the following four categories
  Latin uppercase letters (A-Z)
  Latin lowercase letters (a-z)
  Base 10 digits (0-9)
  Non-alphanumeric characters (!, @, #, $, %)
(5) Password policies and Domain-level GPOs, policies take effect only when they are applied to domain-level GPOs.
3. Practice: Enforcing password policy
4. Practice: add the logon script in the Active Directory
(1) Create logon script file
set oNet = CreateObject("wscript.Network")
userid = oNet.UserName
domian = oNet.UserDomain
msgbox "hello: " & domian & "/" & userid

(2) Call the logon script when user logon
 
 
Lesson 2:  Encrypting SQL Server Traffic
1. Server-Level encryption
2. IPSec policies
(1) You can use IPSec policies encrypt traffic to and from Windows-based computers. IPSec requires no configuration within SQL server itself, but both endpoints of the encrypted channel must be authenticated by a common authentication system: a trusted CA or an Active Directory domain.
(2) Windows 2003 include the following three default IPSec policies, all of which you can deploy through local security policy or group policy
 Client (Response Only)
 Server (Request Security)
 Secure Server (Require Security)
3. Compare the Force Encryption Option and IPSec
(1) Deploy environment: Encryption – Outer of the company; IPSec – Active Directory

4. Practice: IIS SSL & Encrypting traffic to and from SQL Server

(1) IIS SSL
 
get a cert.txt file, and the request a cert from a cert publisher.
 
Issue the certification
 
Download the issued certification
 
Install the cert to IIS
 
After installation, you should set the request need the SSL
 
Then, you can visit the web site through https
 
(2) SQL Server SSL
on one SQL Server, you request a and install one certificate from the publisher
 
The last page presents you with a certificate to install hyperlink, click to install this certificate
Check whether the certificate is stalled or not?
 

Set the SSL for SQL server
 
 
To make SSL active, you please restart the SQL Server service

If you want to disable SSL: first, change the Force Encryption to false; then clear the value for the register key
LOCAL-MACHINE\SOFTWARE\MICROSOFT SQL ERVER\MSSQL.1\MSSQLSERVER\SUPERSOCKETNETLIB

Lesson 3:  Reducing the Attack Surface of SQL Server 2005
1. Disabling unneeded services
You can use the SQL Server Surface Configuration tool to enable, disable, or stop features, services, and remote connectivity of you SQL Server 2005 installations
Component Description
SQL Server Database Engine Database engine, the core service for sorting, processing, and securing data; replication; full-text search; and tools for managing relational and xml data
Analysis Services Includes the tools for creating and managing OLAP and data mining application
Reporting Services Includes server and client components for creating, managing, and deploying tabular, matrix, graphical, and free-form reports. Reporting services is also an extensible platform that you can use to develop report applications
Notification Services Notification services is a platform for developing and deploying application that send personalized, timely info to subscribers on a variety of devices
Integration Services A set of tools and programmable objects for moving, copying, and transforming data

Services Description
SQL Server SQL Server Database Engine
SQL Server Agent Executes jobs, monitors SQL Server, fires alerts, and enables automation of some administrative tasks
Analysis Services Provides OLAP and data mining functionality for BI application
Report Services Manages, executes, renders, schedules, and delivers reports
Integration Services Provide management support for integration services package storage and execution.
SQL Server Browser Provide SQL server connection info for client computers. If you have named instances, you must turn on this to resolve them. Otherwise, you can turn it off
Full-Text Engine for SQL Create full-text indexes on content and properties of structured and semi-structured data to allow fast linguistic searches on this data
SQL Server Active Directory Helper Publisher and manages SQL Server services in AD
SQL Writer Enables backup and restore app to operate in the Volume Shadow Copy Service (VSS) framework.

2. Service Accounts
According to the principle of lease privilege, user should be granted the minimum rights and permissions necessary to do their jobs.
(1) Using a domain user account. Using a domain user account as service account is preferable when the service in question needs to interact with other services on the network. The following services all require server-to-server connections and are handled by domain user accounts
 Remote procedure calls
 Replication
 Backing up to network drives
 Heterogeneous join that involve remote data access
(2) Using the local service account. The local service account is a special built-in account whose default rights and access permissions are equivalent to those of a member of the users group.
 The minimal privileges
 Can access network resources only as a null session with no credentials
(3) Using the network service account. The network service account is a special built-in account whose default rights and access permissions to local objects are equivalent to those of a member of the users group.
 Can access network resources by using the credentials of the local computers’ computer account
(4) Using the local system account. The local system account is a built-in user account with the most powerful set of rights and permissions on the system and is a common target for exploitation by attackers. For increased security, run SQL Server services under a Windows account with the lowest required privileges.
(5) Groups for SQL Service Accounts. After you specify an account for each SQL Server service, SQL Server Setup creates Windows group accounts for the different SQL Server services and adds the service accounts to these group accounts.
Services Group Account Privilege
SQL Server SQLServer2005MSSQLUser$WIN0301 Log on as a service.
Log on as a batch job
Replace a process-level token
Bypass traverse checking
Adjust memory quotas for a process
Permission to start SQL Server AD Helper
Permission to start SQL Writer
SQL Server Agent SQLServer2005SQLAgentUser$WIN0301$MSSQLSERVER Log on as a service
Log on as a batch job
Replace a process-level token
Bypass traverse checking
Adjust memory quotas for a process
Analysis Server SQLServer2005SQLAgentUser$WIN0301$MSSQLSERVER Log on as a service

Report Server SQLServer2005ReportServerUser$WIN0301$MSSQLSERVER Log on as a service

Notification Services SQLServer2005NotificationServicesUser$WIN0301 N/A
Integration Services SQLServer2005DTSUser$WIN0301 Log on as a service
Permission to write to application event log
Bypass traverse checking
Create global objects
Impersonate a client after authentication
Full-Text Search SQLServer2005MSFTEUser$WIN0301$MSSQLSERVER Log on as a service

SQL Server Browser SQLServer2005SQLBrowserUser$WIN0301 Log on as a service

SQL Server AD Helper SQLServer2005MSSQLServerADHelperUser$WIN0301 None
SQL Writer N/A none

3. Security beast practices
(1) Enhance physical security. You should consider the following recommendations:
 Place the server in a room that is inaccessible to unauthorized persons
 Place computers that host a database in a physically protected location – ideally a locked computer room with monitored flood detection and fire detection or suppression systems.
 Install database in the secure zone of the corporate internet and never directly connected to the internet.
 Back up all data regularly and store copies in a secure offsite location
(2) Use firewalls
 Put a firewall between the server and the internet
 Divide the network into security zones separated by firewalls. Block all traffic and then selectively admit only what is required
 Always block packets addressed to TCP 1433 and UDP port 1434 on your perimeter firewall. If named instances are listening on additional ports, block them, too.
 In a multitier environment, use multiple firewalls to create screened subnets.
 When you are installing the server inside a Windows domain, configure interior firewalls to permit Windows Authentication.
 Open ports used by Kerberos or NTLM authentication.
 If your application uses distributed transactions, you might have to configure the firewall to allow MS DTC traffic to flow between separate MS DTC instances, and between the MS DTC and resource managers such as SQL Server.
(3) Use antivirus software
(4) Isolate services
 Avoid installing SQL Server on a domain controller
 Run separate SQL Server services under separate Windows accounts
 In a multitier environment, run web logic and business logic on separate computers.
(5) Configure a secure file system
 Use NTFS
 Use a redundant array of inexpensive disks (RAID) for critical data files
(6) Disable NetBIOS and server message block
 

 
(7) Authentication settings
 Authentication mode: Require Windows authentication for connections to SQL Server.
 String password: Increase security by following these practices.
  Enforce password policies and require passwords to meet complexity requirements.
  Always assign a strong password to the SA account, even when using Windows authentication.
  Always use strong passwords for all SQL Server accounts.
4. Practice: Reducing the attack surface of a Server
(1) Using the surface area configuration tool
 
(2) Configuring a service account for SQL Server