Reference no: EM133289680

Assignment Part 1

Exercise 1: Create Database Users

Task 2: Create a User and Map It to a Login

1. Use SSMS to create a user called ServiceUser in the AdventureWorks database. Map the user to the SalesSupport login.

Take a screenshot before you press OK on the above user creation.

T-SQL Statement:

Exercise 2: Create Database Users

Task 3: Create a User Using Transact-SQL

In SSMS, start a new query and connect it to the AdventureWorks database. Write a Transact-SQL statement to create a database user called ITSupport that is mapped to the ADVENTUREWORKS\IT_Support login and defaults to the dbo schema.

What command did you use to complete the above objective?

Assignment Part 2

Screenshot:

Exercise 3: Assigning Server Roles

Task 4: Assign Role Membership

1. Under the heading for Task 3, write a query to make the ADVENTUREWORKS\Database_Managers login a member of the database_managers role. Note that a login already exists for ADVENTUREWORKS\Database_Managers, so you do not have to create one.

Take a screenshot of your query.

T-SQL Statement (well, this one is actually a Powershell question):

Exercise 4: Verifying Security

Task 2: Test Sales Employee Permissions

1. In a command prompt window, enter the following command to run sqlcmd as ADVENTUREWORKS\DanDrayton. This user is a member of the ADVENTUREWORKS\Sales_NorthAmerica global group, which is in turn a member of the ADVENTUREWORKS\InternetSales_Users domain group:

runas /user:adventureworks\dandrayton /noprofile sqlcmd

If the above command does not work try

runas /user:adventureworks\anthonyfrizzell /netonly /noprofile sqlcmd
If you still get password related issue - logon to DC1, to active directory and reset anthonyfrizzell and dandrayton passwords. You can also check that those accounts are not disabled in active directory.

Instead of using runas, what PowerShell cmdlet could you use?

Assignment Part 4

Screenshot:

Exercise 5: Encrypt a Column with Always Encrypted

Task 1: Encrypt a Column

1. Using the SSMS GUI, configure the phone row of the salesapp1.Sales.Customers table to be encrypted using Always Encrypted.
2. Run a SELECT statement against the salesapp1.Sales.Customers table to view the encrypted data.

Take a screenshot showing the encrypted column.

T-SQL Statement:

Exercise 6: Working with SQL Server Audit
Task 2: Create a Server Audit

1. Using the SQL Server Management Studio (SSMS) GUI, create a server audit with the following
properties:
o Name: activity_audit
o Queue delay: 1000 ms
o On failure: continue
o Target: file
o Target file path: D:\Labfiles\Lab04\Starter\Audit

2. Enable the server audit you have created.

What commands would you use to create the above server audit using TSQL statements?

Assignment Part 6

Screenshot:

Exercise 7: Backing Up Databases

Task 3: Perform a Full Database Backup

1. Back up the AdventureWorks database.
Use a full backup.
Create a new media set with the name AdventureWorks Backup.
Name the backup set AdventureWorks-Full Database Backup.
Compress the backup.

2. Verify that the backup file has been created and note its size.

Take a screenshot of the backup file that you created.

Transact-SQL:

Create a T-SQL statement that creates a full backup of the Research database that you modified for the last chapter and saves it to the c:\database-backups\Research folder. You do not have to actually run the command, though you can if you'd like to test if it works.

Assignment Part 9

Screenshot:

Exercise 8: Configuring a Credential

Task 1: Create a Credential

Use SQL Server Management Studio to add a new credential called ExtractUser that references the ADVENTUREWORKS\Student domain account. The password for the account is Pa55w.rd.

Take a screenshot after the above command runs.

T-SQL Statement:

Exercise 9: Configuring a Proxy Account

Task 1: Create a Proxy Account

3. Grant permission to use ExtractProxy to the owner of the SQL Server Agent job called Generate Sales Log.

Create a T-SQL statement that grants permissions to the Extract Proxy above.

Take a screenshot

Assignment part 11
Screenshot:

Exercise 10: Getting Started with PowerShell

Task 2: Using PowerShell Help

6. Get a list of all cmdlets that include sql in their name.

Take a screenshot of the output from the previous cmdlet.

T-SQL:

Not TSQL, but POWERSHELL.

What cmdlet would you use to run a backup of the AdventureWorks database and save the database to the UNC path \\MIA-DC\Backups?

Assignment part 13
Screenshot:

Exercise 11: Create and configure a Management Data Warehouse

Task 2: Create a Management Data Warehouse

3. Use SQL Server Management Studio to create a new Management Data Warehouse named MDW.

Take a screenshot showing the new data warehouse MDW.

T-SQL:

No T-SQL portion for this assignment.

Assignment Part 14
Screenshot:

Exercise 12: Troubleshoot and Resolve a Service Issue

Task 2: Troubleshoot and Resolve the Issue

Resolve the Issue

1. The network administrator has notified you that the password for ADVENTUREWORKS\ServiceAcct was recently changed to Pa55w.rd. This information might help you to resolve the problem.
2. Test your fix using Internet Explorer.

Take a screenshot of Internet Explorer showing that the above issues has been resolved.

T-SQL:

Exercise 13: Troubleshoot and Resolve a Service Issue

Task 2: Troubleshoot and Resolve the Issue

In the "Gather More Information" section:

2. Use Event Viewer to check the Windows system log for messages relating to the MIA-SQL\SQL2
service.

What T-SQL statement would you use to query the Windows system log for the above events?

If you wanted to filter your query to just events related to MIA-SQL\SQL2, what statement would you use?

Lab Assignment: Backing up Exchange Server 2016
Lab Setup
Virtual machines: 20345-1A-LON-DC1, 20345-1A-LON-EX1, 20345-1A-LON-EX2
Username: Adatum\Administrator
Password: Pa$w0rd

Exercise 1: Backing up Exchange Server 2016
Take screenshot after completing each task.
The main tasks for this exercise are as follows:
1. Populate a mailbox by using Microsoft Outlook on the web.
2. Install Windows Server Backup.
3. Perform a backup of a mailbox database by using Windows Server Backup.
Task 1: Populate a mailbox by using Microsoft Outlook on the web
On LON-EX1, open Internet Explorer, and then browse to https://LON-EX1.Adatum.com/owa.
Sign in as Adatum\michael with the password Pa$w0rd.
Send a new mail message to Mark Bebbington with the subject Message before backup, and then sign out from Outlook on the web.
Sign in again as Adatum\Mark with the password Pa$w0rd, and then check that the message arrived.
Sign out of Outlook on the web, and then close Internet Explorer.
From the Start screen, open the Exchange Management Shell, and then use the following command to note the name and globally unique identifier (GUID) of the mailbox database that is associated with Mark Bebbington:
Get-Mailbox [email protected] |fl name,database,guid

Task 2: Install Windows Server Backup
On LON-EX1, use Server Manager to install the Windows Server Backup feature.
Task 3: Perform a backup of a mailbox database by using Windows Server Backup
On LON-DC1, open File Explorer, and then create a folder named Backup on drive C. Share this folder for Adatum\Administrator with Read/Write permissions. Close File Explorer.
On LON-EX1, start Windows Server Backup, and then perform a full server backup.
As the location of the backup, select the \\LON-DC1\Backup shared folder, and then select Do not inherit under Access control.
For credentials, use the Administrator account with the password Pa$w0rd.
Close Windows Server Backup when the backup finishes successfully. It might take 10-15 minutes to complete.

===================================================================================
Task: B09-01 -Alternative

Creating 3 Mail enabled users.

Screenshot here:

Create 2 mailbox databases. Show the location of the database in Windows explorer. Move a user mailbox from one mailbox database to mailbox database.

Screenshot here:

Create a Shared mailbox. Add 2 users to the shared mailbox. Send a mail to the shared mailbox and show that the added users received the mail.

Screenshot here:

Create a distribution group in exchange server. Add 3 members to the group. Group owners don't have to group members. Members can be added only by the group owner. Send a mail from any user to the distribution group and show that, at least one of the members received the mail. You can select other options as you deem fit.

Screenshot here:

Configure send on behalf permission in Exchange 2019

Screenshot here:

ASSIGNMENT 1 EXERCISES

NOTE: Take all the necessary screenshots for all these exercises in a pdf or MS World

EXERCISE 1
Exercise 1: Evaluating requirements and prerequisites for an Exchange Server 2016 installation
You need to verify that the AD DS environment and DNS meet all the prerequisites for installing Exchange Server 2016.
The main tasks for this exercise are as follows:
Evaluate the AD DS requirements.
Evaluate the DNS requirements.

Task 1: Evaluate the AD DS requirements
On LON-DC1-B, evaluate whether the domain controller requirements are met:
Use Active Directory Users and Computers to evaluate whether the domain and forest functional level requirements are met.
Note: They should be at least Windows Server 2008.
Use Adsiedit.msc to evaluate whether the Exchange Server 2016 schema changes are applied.

Task 2: Evaluate the DNS requirements
On LON-EXCH-B, verify that the DNS settings are configured appropriately.
Use the ping command to verify network connectivity with the domain controller LON-DC1.adatum.com.
Start the Nslookup utility from Windows PowerShell.
Type set type=all.
Perform a nslookup search for the _ldap._tcp.dc._msdcs.adatum.com SRV record.
Verify that an SRV record for lon-dc1.adatum.com is returned.
Close Windows PowerShell.

Assignment: Lab 1 Exercise 2
NOTE: Take all the necessary screenshots for all these exercises and submit them in a pdf format for grading.
Exercise 2: Deploying Exchange Server 2016 (2 Marks)
The main tasks for this exercise are as follows:
Prepare AD DS for Exchange Server 2016 deployment.
Install Exchange Server 2016 on a single server.
Verify Exchange Server installation.

Task 1: Prepare AD DS for Exchange Server 2016 deployment
On LON-DC1-B, attach D:\Program Files\Microsoft Learning\20345-1\Drives\Exchange2016.iso to the virtual machine.
On LON-DC1-B, open a Windows PowerShell window, and then switch to drive D.
Execute the following command to prepare AD DS for your Exchange Server installation.
.\Setup.exe /PrepareAD /IAcceptExchangeServerLicenseTerms /OrganizationName:Adatum
Wait until the process completes.
Close Windows PowerShell.
Q. Take a screenshot after the successful completion of the above command.

Task 2: Install Exchange Server 2016 on a single server
On LON-EXCH-B, attach D:\Program Files\Microsoft Learning\20345-1\Drives\Exchange2016.iso to the virtual machine.
Run the Exchange Server 2016 setup wizard.
Choose not to check for updates.
Choose to install the Mailbox server role and the required Windows roles and features.
Do not disable anti-malware functionality.
After setup completes, choose to open the Exchange Admin Center. Note: If you get Request timeout error during first sign in, refresh the page and try again.

Task 3: Verify Exchange Server installation
On LON-EXCH-B, from Server Manager, open the Services console.
Review the status for each Exchange Server service. Ensure that all services that are set for automatic startup are running.
Note: You might see that Microsoft Exchange Notifications Broker service is not running. You can ignore this.
Using File Explorer, browse to C:\Program Files\Microsoft\Exchange Server\v15. This list of folders includes ClientAccess, Mailbox, and TransportRoles. These three roles were installed as part of the typical setup.
Sign into Outlook on the web as Adatum\Administrator with the password Pa$w0rd.
Send a new message to Administrator and verify that the message was delivered to the inbox.
Close Outlook on the web.
Shut down all virtual machines.
Q. What PowerShell cmdlets would you use to complete the above objective? (Note, directory browsing using standard command prompt commands won't be acceptable).

Assignment 4.1: Create a mail-enabled User in EAC and EMS

Create Mail-enabled User in EAC and EMS and provide screenshots.
Please Note: One user created by EAC and a different User created EMS.
Screenshot here

Assignment 3.1: Messaging Communication

Using a client program, you are familiar with, send an email to a recipient and highlight the mandatory elements that must be provided in a messaging communication.

Submit a screenshot of your email to the assignment dropbox.

Assignment 5.1: Create mail-enabled contact

Create Mail-enabled contacts that are objects in the global address list in EAC and EMS and take screenshots. Submit your screenshots to the submission dropbox.

Attachment:- SQL_Assignment.rar