Sql server lesson11

TCP End Point
 Endpoints control the capability to connect to an instance of SQL
Server as well as to dictate the communications methods that
are acceptable.
 Endpoints are a layer of security at the border between
applications and your SQL Server instance.
 An endpoint has two basic parts: a transport and a payload
 Endpoints can be of two different :
 Transports: TCP and HTTP
 Payload : defines the basic category of traffic that is allowed.
Transport Payload
TCP TSQL
TCP SERVICE_BROKER
TCP DATABASE_MIRRORING
HTTP SOAP

TCP End Point (continue)
 By combining an endpoint transport and payload, SQL Server
can filter acceptable traffic before a command even reaches the
SQL Server instance.
 Example, suppose you have an endpoint defined as TCP with a
payload of TSQL. If any application attempted to send HTTP,
SERVICE_BROKER, or DATABASE_MIRRORING traffic
through the endpoint, the connection would be denied without
needing to authenticate the request.
 Endpoints reject requests that are not properly formatted based
on the endpoint definition.

Endpoint Access
 If traffic going to the endpoint matches the correct transport and payload, a
connection is still not allowed unless access has been granted on the
endpoint.
 Endpoint Security Layers:
 Endpoint State.
 STARTED : The endpoint is actively listening for connections
and will reply to an Application
 STOPPED : The endpoint is actively listening but returns a
connection error to an application
 DISABLED: The endpoint does not listen and does not respond to
any connection that is attempted.
 Permission to connect to the Endpoint.
 An application must have a login created in SQL Server that has the
CONNECT permission granted on the endpoint before the
connection is allowed through the endpoint.

Endpoint Access
 SQL Server 2008 ensures that only valid requests can be submitted by a
valid user before a request is scheduled within the engine.
 TCP ENDPOINTS
You can configure TCP endpoints to listen on specific Internet Protocol (IP)
addresses and port numbers.
There are two arguments:
 LISTENER_PORT is required : The TCP for TSQL endpoint that is
created for each instance during installation is already configured for
TCP port 1433 or the alternate port number for the instance
 LISTENER_IP is an optional : argument is an optional argument that
can provide a very powerful security layer for some types of
applications.

Endpoint Access
 SQL Server 2008 ensures that only valid requests can be submitted by a
valid user before a request is scheduled within the engine.
 TCP ENDPOINTS
 You can specify a specific IP address for the endpoint to listen on.
 The default setting is ALL, which means that the endpoint listens for
connections sent to any valid IP address configured on the machine.
 If you want to limit connection requests to a specific network interface
card (NIC), you can specify a LISTENER_IP argument.
 When you specify an IP address, the endpoint listens for requests sent
only to the IP address specified.
 TSQL endpoints do not have any additional configuration options beyond
the universal TCP settings.

Database Mirroring and Service Broker Common Arguments
 Database Mirroring and Service Broker endpoints provide options to
specify the authentication method and the encryption setting.
 You can use either Microsoft Windows–based authentication or
certificates.
 You specify Windows-based authentication by selecting the NTLM,
KERBEROS, or NEGOTIATE option.
 The NEGOTIATE option causes the instances to select the
authentication method dynamically.
 You can set up certificate-based authentication by using a certificate
from a trusted authority or by generating your own Windows
certificate.
 When all Database Mirroring and Service Broker instances reside
within a single domain or across trusted domains, you should use
Windows authentication.
 When instances span non-trusted domains, you should use
certificate-based authentication.

 SQL Server can encrypt all communications between endpoints, and
you can specify which encryption algorithm to use for the
communications.
 The default algorithm is RC4, but you can specify the much stronger
Advanced Encryption Standard (AES) algorithm.
 Database Mirroring endpoints include a third argument related to the
role within the Database Mirroring session.
 You can specify only one TCP endpoint with a payload of
DATABASE_MIRRORING for each instance of SQL Server.
 You can specify that an endpoint is a PARTNER, WITNESS, or ALL.
 An endpoint specified as PARTNER can participate only as the
principal or as the mirror. An endpoint specified as WITNESS can
participate only as a witness. An endpoint specified as ALL can function
in any role.
 SQL Server 2008 Express, it supports only a role of WITNESS

 The following T-SQL example shows how to create a Database
Mirroring endpoint, it responding to requests from all valid IP
addresses.
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING
(ROLE = PARTNER, ENCRYPTION = REQUIRED);
ALTER ENDPOINT [Mirroring] STATE = STARTED;

 The ROLE = PARTNER option specifies that the endpoint allows only
databases hosted on this SQL Server instance to participate as a
principal or mirror using the RC4 encryption algorithm.
 Service Broker Arguments
 Service Broker endpoints implement arguments related to
message forwarding.
 The MESSAGE_FORWARDING option enables messages
destined for a different broker instance to be forwarded to a
specified forwarding address, The options are ENABLED
and DISABLED.
 If the MESSAGE_FORWARDING option is set to ENABLED,
you can also specify
 The MESSAGE_FORWARDING_SIZE, which specifies the
maximum amount of storage to allocate for forwarded
messages

 Service Broker instances process messages by executing stored
procedures to perform work in an asynchronous manner.
 Each Service Broker instance is configured to process messages of
a particular format.
 It is possible to have many Service Broker instances configured in
an environment, each of which processes different types of
messages.
 By employing message forwarding, administrators can balance the
load on Service Broker instances more easily, without requiring
changes to applications.
Service Broker Arguments

 Encryption
 The communication encryption for endpoints is coded to
understand the source and destination of the traffic.
 If the communication occurs entirely within the SQL Server
instance, the traffic is not encrypted because it would introduce
unnecessary overhead in the communications.
 This is especially important with Service Broker, in which many
messages are exchanged between queues within a single
instance.
 Traffic is encrypted only when data will be transmitted outside
the SQL Server instance

 The purpose of security is to provide enough barriers such that
the effort required to break into a system exceeds the benefit
received.
 You can decide to force users to authenticate to an instance using
only Windows credentials.
 If the authentication mode for your instance is set to Windows
only, you have disabled users’ ability to use SQL Server native
logins.
 The first attacking SQL Server is Slammer Trojan.
 SQL Server now disables every feature not required for the
operation of the database engine.
 The biggest potential risk to an instance is through the use of
features that expose an external interface or ad hoc execution
capability.
 Configuring the SQL Server Surface Area

 The biggest potential risk to an instance is through the use of
features that expose an external interface or ad hoc execution
capability.
 The two features with the greatest risk are
OPENROWSET/OPENDATASOURCE and OLE Automation
procedures.
 OPENROWSET /OPENDATASOURCE expose you to attack by
allowing applications to embed security credentials into code that
spawns a connection to another instance from within SQL
Server.
 If you need the ability to execute queries across instances, you
should be using linked servers which allow Windows credentials
to be passed between machines.
 Configuring the SQL Server Surface Area (continue…)

 Configuring the SQL Server Surface Area (continue…)
 You enable and disable SQL Server features by using sp_configure.
 Ad Hoc Distributed Queries
 CLR Enabled
 Cross Database Ownership Chaining (CDOC)
 Database Mail
 External Key Management
 Filestream Access Level
 OLE Automation Procedures
 Remote Admin Connections
 SQL Mail extended stored procedures (XPs)
 xp_cmdshell
 The main advantage of a CLR routine is that the routine runs within a
protected memory space and cannot corrupt the SQL Server memory
stack.
 CDOC allows you to transfer execution authority across databases.
When enabled, the owner of the database containing the object being
called effectively cedes control to another database owner.

 Creating Principals
 Principals are the means by which you authenticate and are identified
within an instance or database.
 Principals are broken down into two major categories: logins/users and
groups that exist at both an instance and database level.
 Logins
 To gain access to an instance, a user has to authenticate by supplying
credentials for SQL Server to validate.
 You create logins for an instance to allow a user to authenticate.
 Logins within SQL Server 2008 can be five different types:
 Standard SQL Server login
 Windows login
 Windows group
 Certificate
 Asymmetric key

 A standard SQL Server login is created by a database administrator
(DBA) and configured with a name and password which must be
supplied by a user to authenticate successfully.
 The login is stored inside the master database and assigned a local
security identifier (SID) within SQL Server.
 When adding a Windows login or Windows group, SQL Server stores
the name of the login or group along with the corresponding Windows
SID.
 When a user logs in to the instance using Windows credentials, SQL
Server makes a call to the Windows security application programming
interface (API) to validate the account, retrieve the SID, and then
compare the SID to those stored within the master database to verify
whether the Windows account has access to the instance.

 The generic syntax for creating a login is:
CREATE LOGIN loginName { WITH <option_list1> | FROM
<sources> }
<option_list1> ::=
PASSWORD = { 'password' | hashed_password HASHED }
[ MUST_CHANGE ]
[ , <option_list2> [ ,... ] ]
<option_list2> ::=
SID = sid
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| CHECK_EXPIRATION = { ON | OFF}
| CHECK_POLICY = { ON | OFF}
| CREDENTIAL = credential_name
<sources> ::=
WINDOWS [ WITH <windows_options> [ ,... ] ]
| CERTIFICATE certname
| ASYMMETRIC KEY asym_key_name
<windows_options> ::=
DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language

 CHECK_POLICY :
 When the option (the default and recommended setting) is enabled,
SQL Server 2008 enforces the Windows password policy settings
when you create a SQL Server login.
 CHECK_EXPIRATION :
 Is used to prevent brute force attacks against a login.
 each time the login is used to authenticate to an instance, SQL
Server checks whether the password has expired and prompts the
user to change the password if necessary
 Using Windows groups provides the greatest flexibility for managing
security access.
 You simply add or remove accounts from the group to control access
to a SQL Server instance.
 When you create a SQL Server login, you can specify a SID for the
account explicitly.
 when you need to copy SQL Server logins from one instance to
another, being able to specify the SID allows you to map logins
appropriately to any restored databases

 SA:
 SA account is administrator account, it cannot be locked out due to
failed login attempts.
 Making the SA account a prime target for brute force attacks.
 You should rename the SA account to protect an instance from
attacks.
 When you are performing maintenance on a database, such as
deploying new code or changing the database structure, you need to
ensure that users are not accessing the database in the meantime, One
way to prevent access is to DISABLE permissions from a login
ALTER LOGIN <loginname> DISABLE

 Roles in SQL Server provide the same functionality as groups within
Windows.
 Roles provide a convenient way to group multiple users with the same
permissions.
 Permissions are assigned to the role, instead of individual users.
 Users then gain the required set of permissions by having their account
added to the appropriate role.
 SQL Server ships with a set of instance-level roles.
 The instance-level roles are referred to as fixed server roles, because you
cannot modify the permissions on the role.
 You also cannot create additional roles at an instance level
 Fixed Server Roles

 Fixed Server Roles
Fixed Server Role Server - Level Permission
bulkadmin
Administer BCP and Bulk Insert operations
ADMINISTER BULK OPERATIONS
dbcreator CREATE DATABASE
diskadmin
Manage disk resources
ALTER RESOURCES
processadmin
Manage connections and start or pause an instance
ALTER ANY CONNECTION, ALTER SERVER STATE
Securityadmin
Create, alter, and drop logins, but can’t change passwords
ALTER ANY LOGIN
serveradmin
Perform the same actions as diskadminand processadmin,plus manage
endpoints, change instance settings, and shut down the instance
ALTER ANY ENDPOINT, ALTER RESOURCES, ALTER SERVER STATE,
ALTER SETTINGS, SHUTDOWN, VIEW SERVER STATE
setupadmin
Manage linked servers
ALTER ANY LINKED SERVER
sysadmin
Perform any action within the instance. Members cannot be
prevented from accessing any object or performing any action
CONTROL SERVER

 Database Users
 SQL Server security works on the principle of “no access by default.”
 If you haven’t explicitly been granted permission, you cannot perform an
action.
 You grant access to a database by adding a login to the database as a user.
CREATE USER user_name
[ { { FOR | FROM }
{ LOGIN login_name
| CERTIFICATE cert_name
| ASYMMETRIC KEY asym_key_name}
| WITHOUT LOGIN ]
[ WITH DEFAULT_SCHEMA = schema_name ]

 Database Users
 The SID of the login is mapped to the database user to provide an access
path after a user has authenticated to the instance.
 When a user changes context to a database, SQL Server looks up the SID
for the login and if the SID has been added to the database, the user is
allowed to access the database.
 just because a user can access a database, that does not mean that any
objects within the database can be accessed since the user still needs
permissions granted to database objects.
 You can create a database user mapped to a certificate or asymmetric key.
 Database users mapped to certificates or asymmetric keys do not provide
access to the database for any login.
 Certificate- and asymmetric key–mapped users are a security structure
internal to the database.

 Loginless Users
 It is possible to create a user in the database that is not associated to a
login, referred to as a Loginless User.
 Loginless users are designed to replace application roles in SQL Server
2005.
 Loginless users also provide a much better audit trail than an application
role because each user must authenticate to the instance using their own
credentials instead of using a generic account.
 Users still authenticate to the instance using their own credentials.

Fixed database role Database-level permission
Server-level
permission
db_accessadmin ALTER ANY USERl CREATE SCHEMA
Granted with GRANT option: CONNECT
VIEW ANY
DATABASE
db_backupoperator BACKUP DATABASElBACKUP LOGl CHECKPOINT VIEW ANY
DATABASE
db_datareader SELECT VIEW ANY
DATABASE
db_datawriter DELETEl INSERTl UPDATE VIEW ANY
DATABASE
db_ddladmin l ALTER ANY ASSEMBLYl ALTER ANY ASYMMETRIC KEYl ALTER ANY
CERTIFICATEl ALTER ANY CONTRACTlALTER ANY DATABASE DDL
TRIGGERl ALTER ANY DATABASE EVENTl NOTIFICATIONl ALTER ANY
DATASPACEl ALTER ANY FULLTEXT CATALOGl ALTER ANY MESSAGE
TYPEl ALTER ANY REMOTE SERVICE BINDINGl ALTER ANY ROUTEl
ALTER ANY SCHEMAl ALTER ANY SERVICEl ALTER ANY SYMMETRIC
KEYl CHECKPOINTl CREATE AGGREGATEl CREATE DEFAULTl
CREATE FUNCTIONlCREATE PROCEDUREl CREATE QUEUEl CREATE
RULEl CREATE SYNONYMl CREATE TABLEl CREATE TYPEl CREATE
VIEWl CREATE XML SCHEMA COLLECTIONl REFERENCES
VIEW ANY
DATABASE
db_denydatareader Denied: SELECT VIEW ANY
DATABASE
db_denydatawriter Denied: DELETEl INSERTl UPDATE
db_owner Granted with GRANT option: CONTROL VIEW ANY
DATABASE
db_securityadmin ALTER ANY APPLICATION ROLEl ALTER ANY ROLEl CREATE SCHEMAl
VIEW DEFINITION
VIEW ANY
DATABASE
dbm_monitor VIEW most recent status in Database Mirroring Monitor VIEW ANY
DATABASE
 Fixed Database Roles
 SQL Server provides a set of fixed roles at a database level.

 User Database Roles
 Instead of managing permissions for each account, all modern
operating systems allow you to define groups of users that all have the
same permissions.
 SQL Server uses the same security management principles that
administrators have applied to Windows domains by providing the ability
to create database roles.
 A database role is a principal within the database that contains one or
more database users.
 Permissions are assigned to the database role.
 Although you can assign permissions directly to a user.
 It is recommended that you create database roles, add users to a role,
and then grant permissions to the role.

 Managing Permissions
 SQL Server denies access by default. To access any object or perform
any action, you must be granted permission.
 Administrative accounts are :
 Members of the sysadmin role fixed server role.
 Members of the db_owner fixed database role.
 The SA account.
Note :
Members of the sysadmin role are members of the db_owner role in every database
within the instance.
 You cannot limit the permissions of an administrative account.
 The Public Role is a special database role to which each database user
belongs, cannot be dropped.

 Securables
 Permissions work in concert with securables and principals.
 You GRANT | REVOKE | DENY <permissions> ON <securables> TO
<principals>
 Securables are the objects on which you grant permissions .
 Every object within SQL Server, including the entire instance, is a securable.
 Securables also can be nested inside other securables.
For example: an instance contains databases;
databases contain schemas;
schemas contain tables, views, procedures, functions.

 Schemas
 Schemas provide the containers that own all objects within a database.
 A schema is the first layer of security within a database.
 A schema should represent a functional grouping within an application, such
as Customers, Products, Inventory, and HumanResources.
 A schema is owned by a database user.
 Every object created within a database cannot exist without an owner (user
with the authority to manage permissions on an object).
 If database users directly owned objects, it would not be possible to drop
a user unless you reassigned the objects to a different owner.
 Reassigning an object to a different owner would change the name of the
object.
 By introducing a schema between users and objects, you can drop a user
from the database without affecting the name of an object or applications
that use the object.
 Schemas are the only objects that are directly owned by a database user,
so to drop a user that owns a schema, you must first change the ownership
of the schema to another user.
 The ownership can be transferred later after the object has been created.

 Permissions
 Permissions provide the authority for principals to perform actions within an
instance or database.
 To access an object, permission must be granted explicitly.
 Permissions apply to :
 A statement such as INSERT, UPDATE and SELECT.
 An action such as ALTER TRACE
 Broad scope of authority such as CONTROL.
 You add permissions to an object with the GRANT, and places an entry in a
security table for permission granted.
 Prevented permissions with the DENY statement, and placed in a security
table for the DENY.
 The REVOKE statement removes permission entries for the object referenced.
 For example:
 If you issue a GRANT SELECT ON Person.Address TO Test.
You can remove the access by executing REVOKE SELECT ON Person.Address
FROM Test
 If you issue DENY SELECT ON Person.Address TO Test,.
you can remove the DENY by executing REVOKE SELECT ON Person.Address
FROM Test.

 Permissions (continue….)
 You can also grant permissions at multiple levels;
 For Example:
You might grant SELECT permission on:
* The AdventureWorks database,
* The Person schema,
* The Person.Address table.
To prevent the user from accessing the Person.Address table, you can then
issue three REVOKE statements—database, schema, and table—to
remove the SELECT access on the table
 Permission Scope
 A securable can be a database, schema, or an object.
 Because you grant permissions on a securable, you can assign
permissions to a securable at any scope.
 Granting permission on a database causes the permission to be
granted implicitly to all schemas within the database and thereby to all
objects within all schemas.
 Granting permission on a schema causes the permission to be granted
implicitly to all objects within a schema.

 Metadata Security
 SQL Server follows the same principle of “out of sight, out of mind.”
 SQL Server secures all the metadata within the system such that you can view
only the objects within an instance or database on which you have permissions
to perform an action.
 If you need to allow users to view metadata in a database, you can execute the
following code:
GRANT VIEW DEFINITION TO <user>|<login>
VIEW ANY DATABASE allows a login to see the existence of databases
within the instance.
VIEW SERVER STATE allows a login to see execution statistics such as
sys.dm_exec_requests.

 Ownership Chains
 Each object within a database has an owner associated to it .
 You can also build objects that reference other objects within a database.
* Such as stored procedures that call functions which issue SELECT
statements against views that are based on tables.
 The owner of each object that is referenced in a calling stack forms an
ownership chain as the code transits from one object to the next within the
calling stack.
 SQL Server checks your permissions on an object at the top of the calling stack,
as well as each time the object owner changes within a calling stack.
 By using ownership chains, stored procedures become your most powerful
security mechanism within your database.
 Users are never granted direct access to the underlying tables, the only actions
that can be performed are the actions allowed by the stored procedure.

 Impersonation
 You can impersonate another principal to execute commands in a specific
user context.
 To impersonate another principal, you must have the IMPERSONATE
permission granted to your account on the principal that you want to
impersonate.
 IMPERSONATE permission is assigned on a login OR on a database user.
 You accomplish impersonation by using the EXECUTE AS statement as
follows:
{ EXEC | EXECUTE ] AS <context_specification>
<context_specification> ::=
{ LOGIN | USER } = 'name'
[ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ]
| CALLER
* You have not specified the NO REVERT clause for EXECUTE AS.
 To create a schema owned by another database principal, the user creating the
schema must have IMPERSONATE permission on the principal being
designated as the schema owner.

 Master Keys
 Master keys provide the basis for the encryption hierarchy within SQL Server
and are also required to before you can create a certificate or asymmetric key.
 You have a single service master key for the entire instance along with a
database master key within each database.
 Service Master Key
 Each instance of SQL Server has a service master key that is generated
automatically the first time the instance is started.
 Service master keys are symmetric keys generated from the local machine key
and encrypted using the SQL Server service account by the Windows Data
Protection API.
 The generation and encryption process ensures that the service master key can
be decrypted only by the service account under which it was created or by a
principal with access to the service account credentials.
 By default the service master key is used to encrypt any database master key
that is created within the instance.

 Database Master Key
 A database master key must be generated explicitly using the following
command:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<StrongPasswrd>’
 Each database has a different master key, ensuring that a user with access to
decrypt data in one database cannot also decrypt data in another database
without being granted permission to do so.
 The database master key is used to protect any certificates, symmetric keys, or
asymmetric keys that are stored within a database.
 A copy of the database master key is also encrypted using the service master
key such that automatic decryption can be accomplished within the instance.
 When you make a request to decrypt data, the service master key is used to
decrypt the database master key, that is used to decrypt a certificate, symmetric
key, or asymmetric key, and in turn is used to decrypt the data
 To restore and be able to decrypt data successfully, you must also back up the
database master key and then regenerate the database master key on the
other instance.

 Certificates
 Certificates are keys based on the X.509 standard that are used to authenticate
the credentials of the entity supplying the certificate.
 You can create either public or private certificates :
 Public certificate is essentially a file that is supplied by a certificate
authority that validates the entity using the certificate.
 Private certificates are generated by and used to protect data within an
organization.
 Signatures
 Signatures allow you to elevate a user’s permission but to provide a restriction
such that the elevation occurs only when the user is executing a specific piece
of code.
 You can add a digital signature to a module stored procedures, functions,
triggers, and assemblies by using the ADD SIGNATURE command.
 The process to sign code digitally to manage permissions is as follows:
1. Create a database master key.
2. Create a certificate in the database.
2. Create a user mapped to the certificate.
3. Assign permissions on an object or objects to the user.
4. Execute ADD SIGNATURE on a module by the certificate.
 One of the most useful places to employ a signature is to bridge the gap in a
broken ownership chain.

 Encryption and Decryption mechanism in SQL Server 2008

 Auditing SQL Server Instances (“Trust, But Verify”)
 DDL Triggers
 DDL triggers allow you to trap and respond to login events.
 You can scope DDL triggers at either an instance or a database level.
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] |
EXTERNAL NAME < method specifier > [ ; ] }
Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK
Example:

 Auditing SQL Server Instances (“Trust, but verify.”)
 DDL Triggers
 You specify the DDL event or event group that the trigger fires upon
within the FOR clause.
 DDL triggers fire within the context of the DDL statement being executed.
 DDL triggers allow you to prevent many DDL actions.
 Using ROLLBACK TRANSACTION within the DDL trigger, the DDL
statement that was executed rolls back because almost every DDL
statement is transactional and automatically executes within the context
of a transaction.
 Not all DDL statements execute within the context of a transaction.
 Windows operating system is not transactional, you cannot roll back an
action against the file system.
 The Policy-Based Management Framework creates DDL triggers for all
policies that you configure to prevent an out-of-compliance situation.

 Audit Specifications
 Audit specifications begin with a server-level audit object that defines the
logging location for the audit trail.
 You then create server and database audit specifications tied to the audit
object.
CREATE SERVER AUDIT audit_name
TO { [ FILE (<file_options> [, ...n]) ] |
APPLICATION_LOG | SECURITY_LOG }
[ WITH ( <audit_options> [, ...n] ) ] }[ ; ]
<file_options>::=
{FILEPATH = 'os_file_path'
[, MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]
[, MAX_ROLLOVER_FILES = integer ]
[, RESERVE_DISK_SPACE = { ON | OFF } ] }
<audit_options>::=
{ [ QUEUE_DELAY = integer ]
[, ON_FAILURE = { CONTINUE | SHUTDOWN } ]
[, AUDIT_GUID = uniqueidentifier ]}

 Audit Specifications (continue…)
 If you specify a file to log an audit trail to, you can specify the maximum size of
a single audit file, as well as how many rollover files should be retained on the
operating system.
 You can preallocate disk space for the audit log instead of having the file grow
as audit rows are added.
 Logging messages occurs either synchronously or asynchronously.
 When QUEUE_DELAY = 0, audit records are sent to the audit log
synchronously with the transaction.
 If you specify a delay time (in milliseconds), audit records can be
accumulated, but they still must be written within the specified interval.
 The ON_FAILURE action controls how the instance behaves if audit records
cannot be written.
 The default option is CONTINUE, which allows the instance to continue
running and processing transactions.
 If you specify a value of SHUTDOWN, if the audit record cannot be written
to the log within the specified QUEUE_DELAY interval, the instance is shut
down.

 Audit Specifications (continue…)
 If you want to audit actions that occur at an instance level, you create a
server audit specification with the following general syntax:
CREATE SERVER AUDIT SPECIFICATION audit_specification_name
FOR SERVER AUDIT audit_name
{ { ADD ( { audit_action_group_name } ) } [, ...n]
[ WITH ( STATE = { ON | OFF } ) ]}[ ; ]
 If you want to audit events specific to a database, you create a database
audit specification with the following general syntax:
CREATE DATABASE AUDIT SPECIFICATION audit_specification_name
{ [ FOR SERVER AUDIT audit_name ]
[ { ADD ( { <audit_action_specification> |
audit_action_group_name } )
} [, ...n] ]
[ WITH ( STATE = { ON | OFF } ) ]}[ ; ]
<audit_action_specification>::=
{action [ ,...n ]ON [ class :: ] securable BY principal [
,...n ]}

 C2 Auditing
 C2 auditing is a U.S. Department of Defense audit specification that can be
enabled by executing the following code:
sp_configure 'c2 audit mode', 1
 When C2 auditing is enabled, an audit log file is written to the default data directory
with a rollover size of 200 megabytes (MB).
 SQL Server continues to generate rollover files until you run out of disk space,
thereby causing the instance to shut down.
 With C2 auditing enabled, the audit records are required to be written.
 If the system is too busy, user requests are aborted to free up resources to write
the audit trail.

 Encrypting Data
 Data Encryption
 Data that must remain confidential (credit card numbers) , even from a user
that has SELECT permission on a table, should be encrypted.
 After data encrypted, the data cannot be read and cannot be used as
search arguments or as columns within an index because each action
would defeat the purpose of encrypting the data.
 Columns can be encrypted using :
 Hash
 Passphrase
 Symmetric key
 Asymmetric key
 Certificate
 Symmetric keys are commonly used since a symmetric key provides the best
balance between securing data and performance.
 Asymmetric keys and certificates provide the strongest encryption and decryption
method.

 Encrypting Data
 Hash Algorithms
 Encryption algorithms are either one-way or two-way.
 One-way algorithms only encrypt data, without any ability to
decrypt.
 Two-way algorithms allow you to encrypt and decrypt data.
 A hash algorithm is a one-way algorithm that allows you to encrypt
data but does not allow decryption.
 SQL Server allows you to specify five different hash algorithms SHA,
SHA1, MD2, MD4, and MD5.
 MD5 is the algorithm of choice because it provides stronger
encryption than the other algorithms.
 Salting a Hash
A salt is a string of one or more characters that are added to the value
before hashing.

 Encrypting Data
 Symmetric Keys
 Symmetric keys utilize a single key for both encryption and
decryption.
 Because only a single key is needed to encrypt and decrypt data,
symmetric key encryption is not as strong as asymmetric key or
certificate-based encryption.
 Certificates and Asymmetric Keys
 Certificates and asymmetric keys are based on the X.509 standard
and are essentially equivalent in their application.
 Asymmetric keys are generated by a key server within an
organization and cannot be backed up or moved from one system to
another.
 Certificates can be backed up to and restored from a file, allowing
you to move databases that are encrypted while being able to re-
create the certificate to access your data

 Encrypting Data
 Transparent Data Encryption
 Passphrases and encryption keys can be used by an application to
encrypt data deliberately.
 To use the data, you must apply special routines to decrypt data.
 Although encrypting selective data is possible and manageable,
encrypting the entire contents of a database is generally prohibitive.
 Unless the data is encrypted, an attacker can read data directly from
the database files on disk Because data is stored on disk in a plain
text format that can be viewed within any text editor.
 Transparent Data Encryption (TDE) provides real-time encryption
and decryption services to ensure that data within the files and
backups is encrypted.
 SQL Server transparently encrypts and decrypts the data so that
applications do not have to be recoded to take advantage of the
encryption

 Encrypting Data
 Transparent Data Encryption
 TDE works by using an encryption key stored within the database
boot record
 The TDE key is encrypted by using a certificate within the master
database.
 The contents of the database can’t be accessed without the
certificate stored within the master database.
 The process of implementing TDE on a database is as follows:
1. Create a database master key in the master database.
2. Create a certificate in the master database.
3. Create a database encryption key in the target database using
the certificate in the master database.
4. Alter the database and enable encryption.

 Encrypting Data
 Encryption Key Management
 SQL Server 2008 provides the capability through Extensible Key
Management (EKM) to integrate with enterprise key management
systems.
 Keys can be maintained in a central location within an enterprise and
exported for use within SQL Server.
 By registering a key management provider to SQL Server, an
instance can take advantage of all the advanced features of
hardware and software key management solutions, such as key
rotation

Sql server lesson11

Recommended

More Related Content

What's hot (19)

Similar to Sql server lesson11 (20)

More from Ala Qunaibi (16)

Recently uploaded (20)

Sql server lesson11