Lab Answer Key: Module 10: Auditing Data Access and Encrypting Data https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

1 of 9

Lab Answer Encrypting

and

Lab: Auditing Exercise

Task 1: Prepare

1.

Ensure running,

are both ADVENTUREWORKS\Student

with the 2.

In the D:

click Run as

administrator 3.

Click Yes

file, and then

wait for

Task 2: Create

26/09/2016 16:04

Lab Answer Key: Module 10: Auditing Data Access and Encrypting Data https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

2 of 9

1.

Start SQL

database engine

instance 2.

In SQL

\Labfiles

\Lab10\Solution 3.

Select the

This creates an

audit that 4.

In Object

expandable,

refresh 5.

Double-click

click

Cancel

Task 3: Create

1.

In SQL comment specification

2.

In Object

under the creates an audit attempts. expand it. Then

double-click

Task 4: Create

1.

In SQL comment audit specification InternetSales

under the creates an events in the database

role and 2.

In Object

Security, and

expand 3.

Double-click

Cancel.

26/09/2016 16:04

Lab Answer Key: Module 10: Auditing Data Access and Encrypting Data https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

3 of 9

Task 5: Implement

1.

In the Audit. user-defined

for a custom audit

event when 2.

updated.

Select the

and click

Execute

procedure to

the public

Task 6: View

1.

Open a

as

ADVENTUREWORKS\ ADVENTUREWORKS\

member of the

ADVENTUREWORKS\ runas

2.

When prompted

3.

In the SQLCMD

sqlcmd

Customers. SELECT Customers. GO

4.

In the SQLCMD

sales_admin

application EXEC UPDATE

26/09/2016 16:04

Lab Answer Key: Module 10: Auditing Data Access and Encrypting Data https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

4 of 9

'user1@contoso. GO

5.

Close the

6.

In the D:

been created.

7.

In SQL

under the

comment

the audit

folder and

and the service

account 8.

Note that ADVENTUREWORKS\

accesses SQL

Server

individual login.

This identity

context of an

application 9.

Keep SQL

Result: After and a database

Exercise

specification,

Encryption

Task 1: Create

1.

In SQL

\Labfiles

\Lab10\Solution 2.

Select the

creates a

database 3.

Select the

Execute. This

26/09/2016 16:04

Lab Answer Key: Module 10: Auditing Data Access and Encrypting Data https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

5 of 9

creates 4.

Select the

Execute. This

backs up 5.

Select the

creates a

database

Task 2: Enable

1.

In the TDE.

encryption and click

Execute 2.

3.

Select the

Execute. This

retrieves

master database.

Review

HumanResources is

1.

Task 3: Move

1.

In Object Tasks,

2.

In Object

point to OK. Then

connect 3.

In Object

Databases and

click Attach 4.

In the Attach the M:\Data because

HumanResources.mdf file in is displayed does not exist

on the MIA-SQL\SQL2 5.

Open the

26/09/2016 16:04

Lab Answer Key: Module 10: Auditing Data Access and Encrypting Data https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

6 of 9

6.

Select the This creates

7.

Select the This creates

click Execute. MIA-SQL\SQL2. click Execute. the backup

files you 8.

Select the

This attaches

the HumanResources 9.

Select the

queries the

Employees. 10. Review

without saving

any files.

Result: After encrypted HumanResource

moved the

26/09/2016 16:04

Lab Answer Key: Module 10: Auditing Data Access and Encrypting Data https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

7 of 9

26/09/2016 16:04

Lab Answer Key: Module 10: Auditing Data Access and Encrypting Data https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

8 of 9

26/09/2016 16:04

Lab Answer Key: Module 10: Auditing Data Access and Encrypting Data https://skillpipe.courseware-marketplace.com/reader/fr-FR/Book/Book...

9 of 9

26/09/2016 16:04