Lesson 1: Assessing database security needs for biz

1. What is a security policy

(1) A security policy is a document or set of documents containing rules that define the security framework of an organization, which provides high-level courses of action and guiding principals

(2) Benefits of a security policy

 It ensures that the security measures are enforced in a consistent manner throughout the organization

 It serves as reminder of management’s commitment to information security

 It can help reduce legal liability

 It serves to define the security responsibilities of each employee, along with the nature of a security violation

 It serves as a detailed set of rules from which to create simplified instructions or checklists of security practices.

2. Customizing a security policy

(1) Gather requirements

 Interview biz owners and company management

 Review regulatory requirements: SOX, HIPAA.

 Gather security variations: different user with different security privileges.

 Simplify biz security needs: simplify your list of stated biz security requirements

(2) Evaluate requirements

 Consider the various possible ways to meet the requirements, choose the way which are  not only meeting the security requirements but also without overwhelming your organization’s administrative resources.

 Evaluate the risk associated with security requirements

(3) Choose policies and exceptions

 Keep exceptions to a minimum

 Consider the ramifications of security exceptions

 Document security exceptions

3. Protecting SQL Server form network attacks

(1) Virus and worm attacks

 Deploy antivirus software on the database server and update the virus definition frequently.

 Apply the latest service packs and security packs for both SQL Server 2005 and Windows.

 Use database mail with caution. Allow only select users or groups to use this feature.

 Never expose your database server directly to the Internet: firewall + allowed port

 Require strong passwords for all user and service account.

(2) Denial of Service attacks (DoS): A DoS attack is a coordinate flood of service requests that attempts to overwhelm a server’s resources and cause it either to crash or to provide unacceptable performance to all other requests.

To spot a DoS attack, you need to recognize its symptoms, which typically include the following:

 A sudden and unexpected spike in logins

 Many connections originating from a common address or set of addresses

 A drop in SQL Server performance

The guideline to mitigate the damage of a DoS attack and prevent repeat attacks:

 Restart the SQL Service

 Restart the OS

 Block the IP address of the attacker

 Change the name or IP address of the server

(3) SQL Injection attacks

 Validate application input

 Do not use dynamic T-SQL

 Do not run service though highly privileged accounts

Lesson 2: Overview of SQL Server security

1. Security principals

(1) Security principals in SQL Server 2005 are entities such as users, logins, groups and roles that can request the use of database, server, or schema resources, which exist at three levels

(2) Window level: windows domain logins, local logins, and groups

(3) SQL Server level: include SQL Server logins and server roles

 SQL Server logins are generally reserved for users outside of the company; All SQL Server have the built-in sa login ID and password and might also have NETWORK SERVICE and SYSTEM logins

 Server roles provide administrative capabilities to role members, which are

Role Name Role description

bulkadmin Designed for domain accounts that need to perform bulk inserts into database

dbcreator Designed for users who need to create, modify, drop, and restore database

diskadmin Designed for users who need to manage disk files

processadmin Designed for user who want to control SQL Server processes

Securityadmin Designed for users who need to manage logins, create database permissions, and read error logs.

serveradmin Designed for users who need to set server-wide configuration options and shut down the server

setupadmin Designed for users who need to manage linked servers and control startup procedures

Sysadmin Designed for users who need complete control over SQL Server and installed database. Members of this role can perform any activity in SQL Server.

(4) Database level: include database users, application roles, and database roles.

• Database users are entities that are associated with Windows or SQL Server logins and that are assigned a configured set of permission and privilege to specific objects in the database. The default, or built-in, users include guest, dbo, INFORMATION_SCHEMA, and sys

Role Name Role description

guest Enable anyone with a valid SQL Server login to access the database

dbo Database owner, which is granted all permissions and privileges on the database

INFORMATION_SCHEMA Used by system internally to reference views of metadata in a database.

sys 

• Application roles enable user to create password-protected roles for specific applications.

• Database roles, it can be used to assign permissions at the database level. SELECT, INSERT, UPDATE and DELETE, which are the folloing:

Role Name Role description

public The default role assigned to all database users

db_accessadmin Add or remove logins in a database

db_backupoperator  Back up a database

db_datareader View data in a database. Can select all data from user tables

db_datawriter Add or modify any data in any user table in the database

db_ddladmin Perform tasks related to the data definition language for SQL Server. Members of this role can issue any DDL statement except GRANT, REVOKE, or DENY

db_denydataeader Designed to restrict access to data in a database by login. Cannot read any data

db_denydatewriter Restrict modifications permission in a database by login.

db_owner Complete control over all aspects of the database

db_securityadmin Manage permissions, object ownership, and roles

(5) What are SQL Server Securables?

Securables are entities within SQL Server to which you can assign permissions. The three top-level securables are server, database, and schema.

Securables scope Description

Server Server instance, databases, endpoints, logins, and server roles

Database Application roles, database roles, schemas, and users

Schema  Functions, procedures, tables, and views

(6) Verifying permission and privileges of security principals

function Description

IS_SRVROLEMEMBER Return 1 if the current login is a member of the specified fixed server role

HAS_PERMS_BY_NAME Evaluates the effective permission of the current user on a securable.

2. SQL Server 2005 authentication modes

(1) Windows authentication mode. Windows authentication mode leverages existing Windows user and group accounts for SQL Server

(2) SQL Server and Windows authentication mode

(3) Impact of authentication modes on service uptime: there must be one DC for Windows authentication

3. Integrating SQL Server into a Windows domain infrastructure

(1) What is a windows domain: A windows domain is a Windows network with a centralized authentication and security system

(2) What is active directory: : Active Directory is the directory service that has provided the basic structure and features of Windows domains since Windows 2000, which include domains, OUs, and forests.

(3) Active directory structures

4. Active directory authentication and SQL Server

(1) Kerberos(Windows 2000 or later), NTLM(NT or Windows 98)

(2) Service Principal Names for Kerberos.

 A service principal name (SPN) is the name by which a client uniquely identifies an instance of a service.

 When you configure SQL Server to run under the local system account, this SPN is automatically created. However, if you configure SQL Server to run under a service account, you should create the SPN manually. To configure an SPN for SQL Server, use the SETSPN utility available on the Windows Server 2003 CD.

 When you configure a domain account for use as a service account with SQL Server, be sure enable the account is trusted for delegation option in the properties of the user account.

5. Authentication guidelines for high-availability solutions

(1) Clustering service accounts security

 The account used to start SQL Server in the cluster must be a domain account.

 You should not change the passwords for any of the SQL Server service accounts when a failover cluster node is disabled.

 If the service account for SQL Server is not an administrator account in the cluster, the administrative share cannot be deleted on any node of the cluster.

 On Windows Server 2003-based clusters, you can use Kerberos authentication against SQL Server virtual servers

(2) Replication security

 Run each replication agent under a different Windows account, and use Windows authentication for all replication for all agent connections

 Add a local Windows account, which is not a domain account, for each agent on the appropriate nodes. You should use the same user name and password on each node.

 Ensure that an agent runs under the same account on each computer

 If you change the password for an account used by a replication agent, you need to execute the stored procedure sp_changereplicationserverpasswords to change the passwords on all replication servers.

(3) Mirroring endpoints and service accounts

 If the server instance uses the same domain user account for Database Mirroring sessions with Windows authentication, the correct logins exist automatically, and you do not need to create a login. However, if the server instances use different user accounts, you must create a login on each instance for the startup service account of each of the other instances.

 If the server instances are not in trusted domains, or is SQL server is running as a local service, Windows authentication is unavailable. You must configure the mirroring endpoint of each instance with its own locally created certificate.

(4) Security of log shipping

 You can select either Windows authentication or SQL authentication by the primary and secondary servers to connect to the monitoring server and update the monitoring tables

 For a backup job to succeed, you need to configure the SQL Server service account on primary server instance and the proxy account of the backup job to have read/write permissions to the backup directory

 For a copy job to be successful, you need to configure the proxy account of the copy job to have read permissions to the backup directory and write permission to the copy directory

 For a restore join to e successful, you need to configure the SQL Server service account on the secondary server instance and the proxy account of the restore job to have read/write permissions to the copy directory.

6. Practice: create a windows group for SQL Managers in AD

(1) Create account

(2) Create group 

(3) Adding administrative privileges