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