Module 10: Auditing Data Access and Encrypting Data
1 of 44
Module
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Data
Contents:
Lesson 1: Lesson 2: Lesson 3: Lab:
Module When configuring that you meet
to ensure protection.
Organizations
which mandate
auditing of all
range of
options for implementing encryption of database files
the access to the by providing
transparent data This module
use and
manage the SQL
Objectives After completing
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
2 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• Describe the • Implement • Manage SQL • Implement
Lesson SQL Server provides one tool provides
general, no features often needs
to be used. In this lesson,
Lesson After completing • Describe the • Describe the • Use triggers • Uses SQL
Discussion:
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
3 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
During this discussion, • Why is auditing • What methods • What are • Which standards
with?
Common
Common Criteria
nations in
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
4 of 44
1999 and has
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
is now
maintained by Organization (
Note: however, Criteria
mode; the Common
Enabling Common SQL Server provides
can be set by
using the sp_configure
Enterprise
edition for production
editions for
non-production
enabled
option, you must
Server to
comply with
can download
this script from When the option
Server
operates: • Residual Information
known bit
pattern before • Ability to • Column GRANT
behavior of the
permission
Note: performance
impact the
SQL Trace
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
5 of 44
Many users attempt commands to Profiler can have
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
tracing Server interactively on
production systems. An alternative Server Profiler lightweight method then have a role
that SQL much more Trace can the server, you
can use it to SQL Trace uses
lost, as long
as there is space
fills or write
errors occur,
enabled. The
possibility of
SQL Trace for
auditing purposes.
DML Triggers
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
6 of 44
Triggers can including data and logon triggers
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
types, modifies data logons, based on
business or administrative You can create the trigger should
specifying whether trigger
should take. The following
a row in the
dbo.Employee
table. You
can access the
deleted tables
in the trigger. Creating a DML
CREATE TRIGGER ON dbo.Employee FOR UPDATE AS BEGIN SET NOCOUNT
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
7 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
IF UPDATE( INSERT NewSalary, SELECT getdate() FROM INNER ON i. END; END; GO
Triggers do have • System performance
alongside the usual
load on the • Users with
significant issue
for auditing • You cannot • Triggers have • Only limited
that it captures
all the changes
last trigger
that fires,
procedure.
Demonstration: In this demonstration,
Demonstration Create a DML
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
8 of 44
1.
Ensure and log
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
are running, the
password 2.
In the D:
3.
Start SQL
database engine
instance 4.
Open the
5.
Select the
This creates a
table named AdventureWorks 6.
Select the trigger
This creates a Rate column
is updated, 7.
Select the
updates a
rate in 8.
Select the
This
retrieves 9.
Keep SQL
SQL Server
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
9 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SQL Server Audit
to track
server-level and
events to
audit files or
and
Enterprise edition, database-level
Object Server Audit Server Audit Specification
One per audit.
Database Audit
per database
Actions
Action Groups
example,
For example, restore commands.
Target
Security event
Extended Events SQL Server
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
10 of 44
A wide variety user executes permissions
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
example, when a memory or check Extended Events
feature that enables
events occur.
When SQL Server
an action
that should be sends details
event and memory-based ring
buffers, or Windows® Extended Events
impact on
the database
purposes
where you may Extended Events
Extended Events
infrastructure.
particular
types of events—the
of event.
Configurations unit of deployment part of a particular
Packages are the objects that are package
within Extended Extended Events
described in the
following table:
Object Events Targets Actions
plans to
Types Predicates
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
11 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Object Maps
Lesson Preparing SQL you can create
objects before will learn how
to configure
Lesson After completing • Configure • Detail the • Define audit • Create audits. • Create server • Create database
SQL Server
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
12 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SQL Server Audit • Audits. An target (such written, and
defines a must be space.
• Audit Specifications in an audit.
be included database audit
specifications • Actions and
specification are
based on pre-defined
Server provides a
comprehensive
groups, and you
can audit user-defined
audit
specification groups that
audit-level action when an
administrator
Creating
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
13 of 44
You can create Management
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SQL Server key ones being
listed in the following
Option Name Queue delay
On failure
flushing them to the
fail auditable
Audit destination Maximum file Reserve disk space
Note: security avoids in a significant
between quickly and but can result
Audit Targets
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
14 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Audits can be • A file. File • Windows Application administrators logs. Do not
configure. network of the event can view the
log. • Windows Security to add the
but you need before using
it.
You should review
on a periodic
basis. The following
file as the
target: Creating a Server
CREATE SERVER TO FILE ( WITH ( GO ALTER SERVER GO
Note: server and stores
creating a automatically
Creating
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
15 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
After you create
specification by
using Transact-SQL
specification, it is
automatically
for it to start.
A server audit
grouped into
pre-defined action • BACKUP_RESTORE_GROUP: • DATABASE_CHANGE_GROUP:
database.
• FAILED_LOGIN_GROUP: • SUCCESSFUL_LOGIN_GROUP: • SERVER_OPERATION_GROUP:
Note: Action
The following
Audit
and successful
login attempts: Creating a Server
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
16 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
CREATE SERVER FOR SERVER ADD (FAILED_LOGIN_GROUP), ADD (SUCCESSFUL_LOGIN_GROUP) WITH (STATE
Creating
Creating a database
specification. The
database audit
database level.
You can audit • SELECT: • INSERT: • DELETE: • UPDATE: • EXECUTE:
Additionally, 26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
17 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
• APPLICATION_ROLE_CHANGE_PASSWORD_GROUP:
application
role password • DATABASE_OBJECT_ACCESS_GROUP:
objects.
• DATABASE_PRINCIPAL_CHANGE_GROUP:
database-level
principal is • SCHEMA_OBJECT_ACCESS_GROUP:
specified
schema. • SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP:
changes to object
permissions.
Note: Server
“SQL
The following
all database
principal changes
schema by
members of Creating a Database
USE AdventureWorks; CREATE DATABASE
AdventureWorks_DBSecurity
FOR SERVER ADD (DATABASE_PRINCIPAL_CHANGE_GROUP), ADD (SELECT WITH (STATE
User-Defined
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
18 of 44
The server-level enable you to audit application-specific
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
provides they do not than a
specific threshold To audit custom action group
USER_DEFINED_AUDIT_GROUP procedure in your
application logic The sp_audit_write smallint to identify
log a succeeds,
and a string The following
from an insert
trigger. Calling sp_audit_write
CREATE TRIGGER AFTER INSERT AS DECLARE
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
19 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
select @bonus from inserted IF @bonus BEGIN SET
bonus
is over EXEC
@succeeded
= 1, @user_defined_informat ion = @msg; END
Note: been granted the master on sys.
actions have procedure in permission
Reading
After an audit
have been
audited.
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
20 of 44
You can use
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
from audits that
target Windows
the
sys.fn_get_audit_file
file-based logs
in a specified The sys.fn_get_audit_file initial_file_name
of three
formats: • \* which • \LoginsAudit_{GUID}
specified name
and GUID • \LoginsAudit_{GUID}_00_29384.
The structure
audit file.
detailed and you
rarely need to This example Selecting Specific
SELECT event_time, database_name, FROM sys. \*',default,
Additional (Transact-SQL)
The audit records logs, as well
fn_get_audit_file
system event in size by the
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
21 of 44
rules related chunks that may generate multiple
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
000-character event can the order of
multiple row
Managing
After you have
audit
configuration Enabling and You can use
individual audit
specifications.
SERVER
AUDIT SPECIFICATION,
statements to
set the STATE The following Disabling an
Use master; ALTER SERVER
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
22 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
WITH (STATE
Viewing Audit SQL Server provides
system views
that can help
View sys.dm_server_audit_status
current state of the
sys.dm_audit_actions sys.dm_audit_class_type_map sys.server_audits
descriptions. instance.
sys.server_file_audits
a SQL Server
sys.server_audit_specifications
in a SQL
sys.server_audit_specification_details
details
sys.database_audit_specifications
specifications in a SQL
sys.database_audit_specification_details
specifications in a SQL
Considerations There are several • Each audit
server, SQL
Server attempts
on the
server. If no
executing
the CREATE
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
23 of 44
• If databases
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
same level of
audit capability, • Mirrored have a server SERVER
partner must CREATE on the
primary server. • You should
need to
minimize • If disk space entry to it
to force startup
parameter.
Demonstration: In this demonstration, • Create an • Create a server • Create a database • View audited
Demonstration Create an Audit 1.
If you did
20462C-MIA-DC and
20462C-MIA-SQL ADVENTUREWORKS\Student
Setup.cmd in
the D:\Demofiles\Mod10
Management
Studio,
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
24 of 44
2.
In SQL
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
\Demofiles
\Mod10 3.
Select the
This creates an
audit that 4.
In Object
expandable,
refresh 5.
Double-click
click
Cancel
Create a Server 1.
In SQL comment specification
2.
In Object
under the creates an audit attempts. expand it. Then
double-click
Create a Database 1.
In SQL comment audit specification principals
2.
In Object expand
under the creates an individual database. Security, and is not
expandable, 3.
Double-click
Cancel.
View Audited 1.
Open a
as 26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
25 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
ADVENTUREWORKS\ChadCorbitt ADVENTUREWORKS\Personnel
member of the
ADVENTUREWORKS\HumanResources_Users runas
2.
When prompted
3.
In the SQLCMD
sqlcmd
HumanResources. SELECT GO
4.
Close the
5.
In the D:
created.
6.
In SQL
under the
comment
the audit
folder and
events logged for
the Student 7.
Note that ADVENTUREWORKS\ChadCorbitt Server
8.
excluded).
accesses SQL individual login.
Keep SQL
Lesson With the focus often overlooked and backup tapes. compliance policies
engineers, an such as disks security two ways of 26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
26 of 44
encrypting data:
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Management
(EKM). This lesson describes
Lesson After completing • Describe the • Configure • Move an encrypted • Describe how
Transparent
Transparent Data
files
unreadable without
protection to
your database
need to
reconfigure client Server instance transparent to
because the SQL process is they are
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
27 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
encrypted; when
Note: may increase
Server
Transparent TDE uses the • Service Master
installation of the SQL
Server instance
Key for the
master database.
system Data
Protection • Database
generate a
certificate
that you
specify to
can use a
password • Server Certificate
and is used to
encrypt an • Database
encrypt the
entire database.
Configuring
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
28 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
To enable TDE, 1.
Create
2.
Create
3.
Create
4.
Enable
Creating a You create the
statement and
supplying a password. The code example Creating a DMK
USE master; CREATE MASTER
Creating a
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
29 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
After you have CERTIFICATE DMK to generate
Server uses the master
database. The code example Creating a Certificate
Use master; CREATE CERTIFICATE 'DEK_Certificate';
You should back create them.
soon as you encrypted a database
and then lost The following
private key.
Backing up
BACKUP CERTIFICATE
\backups
\security_certificate. WITH PRIVATE (FILE ENCRYPTION
Creating a The DEK is that can perform database that
two-way keys in the ENCRYPTION KEY
Transact-SQL 26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
30 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
The code example
AES_128 algorithm
in the AdventureWorks
certificate created in
the previous Creating a Database
USE AdventureWorks; CREATE DATABASE WITH ALGORITHM ENCRYPTION
Enabling Encryption After you have database by using
encryption for a SET
ENCRYPTION The Transact-SQL
database.
Enabling Encryption
ALTER DATABASE SET ENCRYPTION
To check whether
of 0 in the
is_encrypted
the
is_encrypted The code example
all databases
on the server Querying sys.
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
31 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
USE master; SELECT name,
Moving
The primary contains in the
the data it attach the files
from an encrypted If you need to associated keys
the moving a
TDE-enabled 1.
On the
2.
Copy or
3.
Create
4.
Use a CREATE on the destination
server.
server certificate and its private
key. 5.
Attach
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
32 of 44
Demonstration:
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Encryption
In this demonstration, • Create a database • Create a server • Create a database • Enable database
Demonstration Create a Database 1.
If you did
20462C-MIA-DC and
20462C-MIA-SQL ADVENTUREWORKS\Student
Setup.cmd in
the D:\Demofiles\Mod10
Management
Studio, 2.
In SQL
\Demofiles
\Mod10 3.
Select the
creates a
database
Create a Server 1.
In the TDE.
certificate and
click Execute
and its private
key.
Create a Database 1.
In the TDE.
click
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
33 of 44
Execute
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
database.
Enable Database 1.
In the TDE. Execute database
2.
Review
encryption and click retrieves database. ConfidentialDB is
1. 3.
Close SQL
Extensible
In an enterprise
managing
encryption keys
Many
organizations keys and certificates securely. Extensible register modules
encryption store keys possible to use the
encryption keys To use keys from
perform the 26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
34 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
following tasks: 1.
Enable configuration options
advanced advanced example.
sp_configure GO RECONFIGURE ; GO sp_configure GO RECONFIGURE ; GO
2.
Create
provider.
CREATE FROM
3.
Create CREATE WITH SECRET FOR
4.
Add the ALTER ADD
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
35 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
ALTER DATABASE SET ENCRYPTION
Lab: Auditing Scenario You are a database must implement must protect
reasons, you database; and you database.
Objectives After completing • Implement • Implement
Lab Setup Estimated Time: Virtual machine: User name: ADVENTUREWORKS Password: Pa$$w0rd
Exercise
Scenario The InternetSales to customer data
commitment to customer
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
36 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
data is audited. The main tasks 1.
Prepare
2.
Create
3.
Create
4.
Create
5.
Implement
6.
View Audited
Task 1: Prepare
1.
Ensure then log
running, and password
Pa$$w0rd 2.
Run Setup.
Task 2: Create
1.
Create an
2.
Enable the
Task 3: Create
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
37 of 44
1.
Create a
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
specification
should:
o
Write
o
Audit
o
Be
Task 4: Create
1.
Create a
specification
should:
o
Write
o
Audit
o
Audit
database
role. o
Audit
schema by the
sales_admin o
Be
Task 5: Implement
1.
Use the
table in the
InternetSales
in the
D:\Labfiles\Lab10\Starter CREATE FOR UPDATE AS BEGIN IF BEGIN DECLARE SET i.EmailAddress FROM
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
38 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
JOIN FOR EXEC
1,
@user_defined_information END; END; GO
2.
Grant EXECUTE
to the public
database
Task 6: View
1.
In a command ADVENTUREWORKS\VictoriaGray ADVENTUREWORKS\Sales_Europe
the
ADVENTUREWORKS\InternetSales_Users runas
2.
When prompted
3.
In sqlcmd, SELECT GO
4.
In sqlcmd,
role and update
the Customers. EXEC UPDATE
WHERE
CustomerID GO
5.
In the D:
created.
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
39 of 44
6.
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
SQL Server
the files in the
audit folder
and the service
account -- View SELECT user_defined_information, object_name FROM \*',default, WHERE 'ADVENTUREWORKS\ServiceAcct');
7.
Note that ADVENTUREWORKS\VictoriaGray through audited
Result: After and a database
Exercise
Server This identity is role.
specification,
Encryption
Scenario You want to data it contains
access to the this, you will
configure transparent The main tasks 1.
Create
2.
Enable
3.
Move the
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
40 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
Task 1: Create
1.
Create a
of SQL
Server. 2.
3.
Create a
o
Name
o
Assign
Back up
o
Back
\Labfiles\Lab10
\Starter o
Back
\Labfiles\Lab10
\Starter o
4.
Encrypt
Create a
o
Use
o
Encrypt
Task 2: Enable
1.
Enable encryption
2.
Query the
HumanResources
database.
Task 3: Move
1.
Detach
2.
Attempt
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
41 of 44
o
The
o
Attaching
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
folder. database
encryption
3.
Create a
4.
Create a
instance. MIA-SQL\SQL2
instance 5.
Attach the
that you can
access the
Result: After encrypted HumanResource
moved the
Review
Module Best Practice: practices: • Choose point are • Make and
Best Practice: following • Use database. • Ensure the
following best usually no occur but environments. free disk space
the master
TDE, and store
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
42 of 44
• If you organization,
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
keys.
Review Question(
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
43 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:52
Module 10: Auditing Data Access and Encrypting Data
44 of 44
https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...
26/09/2016 15:52
