Securing a SQL Server instance begins with a properly configured operating system. Using windows (integrated) authentication to control access to the database, ensures that access will only be granted to users that have been authenticated against a domain controller.

The various features that make SQL Server 2008 useful, also expose opportunities to attack the server - ensure only those features that you are using are enabled.

Cross Database Ownership Chaining allows users to be granted elevated permissions via inheritance from another object. SQL Server checks the permissions on a top-level object, such as a stored procedure, trigger, view or function. If the top level object references objects with the same ownership, SQL Server does not re-check permissions on the referenced objects. CDOC provides a means to grant access to objects via ownership chaining, when permissions may not otherwise be granted.

CLR assemblies can be loaded into SQL Server by a sysadmin allowing CLR procedures to be available via the instance. When loading a CLR assembly, three levels of Code Access Security are available:

1. SAFE - no access to external resources, eg files, shares, network
2. EXTERNAL ACCESS - access to external resources allowed
3. UNSAFE - perform any operation, access any resource

Endpoints

Endpoints can be defined on SQL Server 2008 to filter network traffic on the instance. The Endpoint definition determines the server's response to connection attempts, and whether authentication is required for the requested service. Response to network connections are determined by Endpoints status definition:

STARTED

Endpoint responds to network connections

STOPPED

Endpoint listens for connections, but returns a connection error message

DISABLED

Endpoint does not listen or respond to connection attempts

Use CREATE ENDPOINT to define Endpoints:

CREATE ENDPOINT endpoint_name
[AUTHORIZATION login_name ]
[STATE = [ STARTED | STOPPED | DISABLED ] ]
AS [HTTP | TCP] (<protocol_specific_option>)
FOR [SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING]
    <language_specific_options>

Encryption can be enabled for endpoints, and is only used when data is transmitted outside of the SQL Server instance

Database Principles

Database Principles are the objects used to authenticate and authorise access on a SQL Server instance. Principals are either logins, users or groups, and can be defined at both instance and database levels. Principals are granted access to securable objects.

Instance-Level Principals

At the instance level, principals are either logins or fixed server roles. There are five types of logins available:

1. Standard SQL Server login
2. Windows login
3. Windows group
4. Certificate
5. Asymmetric Key

Logins are created using the following general syntax:

CREATE LOGIN login_name
[WITH PASSWORD = 'password' [HASHED] [MUST CHANGE]
  <login_options>
FROM <source_specification>

Available login_options for the CREATE LOGIN command are:

• SID = sid
• DEFAULT_DATABASE = database_name
• DEFAULT_LANGUAGE = language_name
• CHECK_EXPIRATION = [ ON | OFF ]
• CHECK_POLICY = [ ON | OFF ]
• CREDENTIAL = credential_name

The CHECK_POLICY option allows you to apply the operating systems password policy to SQL Server logins. The CHECK_EXPIRATION option applies the operation systems password lockout policy. With <source_specification> set to 'WINDOWS', SQL Logins can be mapped to windows user or group accounts, allowing for single sign-on. Certificates and Asymmetric keys are not used to enable connections to be made to the server, but instead are for internal security structures within the instance.

Instance-level 'Fixed server roles' are read-only roles that can be assigned to logins. New instance-level roles cannot be created.

Logins can also be setup using the GUI tools in SQL Server Management Studio. First create the account in Windows via 'User Manager', then navigate to the Security node in Management Studio and select 'New Login'.

The 'sa' acount has sysadmin authority and can't be locked out. The account should be protected by renaming it.

Database-Level Principals

Database-level principals are created with the CREATE USER command:

CREATE USER user_name
[FOR | FROM] 
  [LOGIN login_name | CERTIFICATE cert_name | 
	ASYMMETRIC KEY key_name | WITHOUT LOGIN ]
[WITH DEFAULT_SCHEMA = schema_name]

Users are normally mapped to Logins, Certificates or Asymmetric Keys and can be mapped to any of the built-in or user-defined roles

Members of the db_owner role are administrators of the database. Members of the sysadmin role are administrators of the instance and also db_owners for all databases. You cannot remove authority from sysadmin or db_owner roles. SQL server does not therefore need to check authorisations for sysadmins or db_owners

Users can be created without an associated login. Normally this is used for applications. Users of the application will have to authenticate with their own login, and then the application executes code under their login credentials.

User-defined roles are created with the CREATE ROLE statement:

CREATE ROLE role_name AUTHORIZATION authorisation_name

One principal can impersonate another by calling the EXECUTE AS:

[EXEC | EXECUTE] AS [LOGIN | USER | CALLER ]

To end impersonation, use REVERT

Users can be granted permissions to individual objects in a database. Alternatively, objects in a database can be assigned to security groups or schemas. Users can be granted permissions to schemas and those permissions propagate to all objects within the schema. Permissions granted at the database level propagate to all schemas, and therefore all objects in the database.

Permissions are granted using:

[DENY | GRANT] [PERMISSION] ON schema.object TO [role | user ]