Exam DP-300 All Questions

View all questions & answers for the DP-300 exam

Exam DP-300 topic 1 question 2 discussion

Actual exam question from Microsoft's DP-300

Question #: 2
Topic #: 1

DRAG DROP -
You have SQL Server 2019 on an Azure virtual machine that contains an SSISDB database.
A recent failure causes the master database to be lost.
You discover that all Microsoft SQL Server integration Services (SSIS) packages fail to run on the virtual machine.
Which four actions should you perform in sequence to resolve the issue? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct.
Select and Place:

Show Suggested Answer

Suggested Answer:

Step 1: Attach the SSISDB database
Step 2: Turn on the TRUSTWORTHY property and the CLR property
If you are restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr)
Step 3: Open the master key for the SSISDB database
Restore the master key by this method if you have the original password that was used to create SSISDB. open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'
Alter Master Key Add encryption by Service Master Key
Step 4: Encrypt a copy of the master key by using the service master key
Reference:
https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog

by yuck at Feb. 9, 2021, 3:21 p.m.

Comments

Submit Cancel

bakamon

Highly Voted 2 years, 1 month ago

1. Attach the SSISDB database (E) 2. Open the master key for the SSISDB database (F) 3. Encrypt a copy of the master key by using the service master key © 4. Turn on the TRUSTWORTHY property and the CLR property (D) turning on the TRUSTWORTHY property and the CLR property (D) should be done after opening the master key for the SSISDB database (F) and encrypting a copy of the master key by using the service master key ©. This is because the TRUSTWORTHY property and CLR property are related to security and encryption, so it’s important to ensure that the master key is properly encrypted before enabling these properties.

upvoted 6 times

learnazureportal

7 months, 3 weeks ago

Answer is correct.

upvoted 2 times

voodoo_sh

7 months, 2 weeks ago

1. Attach the SSISDB database (E) 2. Turn on the TRUSTWORTHY property and the CLR property (D) 3. Open the master key for the SSISDB database (F) 4. Encrypt a copy of the master key by using the service master key (C) First step would be attach the SSISDB database, because you can't turn on TRUSTWORTHY setting before database actually exists. Then, turn on CLR. Last, according to below link, opening up master key and encrypting it with SMK - should be the last steps: https://learn.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver16#to-restore-the-ssis-database

upvoted 1 times

...

nova24

Most Recent 10 months ago

Isn't this the right order? Turn on the TRUSTWORTHY property and the CLR property Attach the SSISDB database Open the master key for the SSISDB database Encrypt a copy of the master key by using the service master key

upvoted 2 times

...

DSilsbury

2 years, 11 months ago

I believe this question should refer to the SSISDB being restored to another SQL Server as part of the answer is "Turn on trustworthy property and CLR property"

upvoted 1 times

...

quermi

3 years, 8 months ago

The correct url iss: https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15#to-restore-the-ssis-database

upvoted 4 times

...

o2091

3 years, 9 months ago

What would be the correct order?

upvoted 1 times

...

Aggie0702

3 years, 10 months ago

It means master database lost with relevant information also lost so recreate a new SSIS catalog?

upvoted 2 times

...

gills

4 years, 1 month ago

Odd question.

upvoted 3 times

...

rtk513

4 years, 4 months ago

This is such a weird question, it doesn't address the problem at hand which is a failure causing the master table being deleted. Why would that even happen? I would want to figure that out

upvoted 4 times

Raffer

4 years, 4 months ago

It doesn't say deleted; it says lost. Could be corruption + lost backups.

upvoted 1 times

...

Corbiz

4 years, 4 months ago

Attaching the database and enabling CLR can be done at any order. In this MS documentation, they first enable CLR, then attach/restore the database: https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15

upvoted 3 times

...

yuck

4 years, 5 months ago

Link is 404, this is a correct one https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog

upvoted 3 times

...