Change database properties during image or clone creation

hoytm

We want to use SQL test in conjunction with clones and want to make as simple for users to add SQL test to the clones as possible. Where we are having a tough time is setting the TRUSTWORTHY property of a clone by the time it is ready for use, as we are getting dinged for having insufficient permissions to run the statement, presumably due to limitations of the account used to create images and clones. Does anybody have a good workaround for this situation?

Cheers,
Matt

Eddie D

Hi @hoytm, thank you for your forum post.

Sadly, you cannot make use the ability to apply any T-SQL modifications to either the image or clone, as SQL Clone runs these scripts creating a temporary user using db_owner permissions to apply the modification and to set  the TRUSTWORTHY property requires sysadmin privileges.

Also if you use a backup file as the source of the image, the SQL Clone Agent creates a temporary database for the restore operation, SQL Server will set the TRUSTWORTHY property to off, as per this Microsoft article.

Because a database that is attached to an instance of SQL Server can't be immediately trusted, the database isn't allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy. Therefore, if you back up or detach a database that has the TRUSTWORTHY option ON and you attach or restore the database to the same or another SQL Server instance, the TRUSTWORTHY property will be set to OFF when attach or restore is completed.

Based on the above statement I copied from the Microsoft article, I believe the same will also be true when creating an image from a live database and then creating and attaching the clone to the SQL Instance, although I have not tested this scenario.

The only way for sure that the TRUSTWORTHY property can be set to on, is to manually do this once the clone has been created on the SQL Instance using a sysadmin account outside of SQL Clone.  However, as this will cause a write to the differencing disk, it will increase the size of the Clone.

Many Thanks
Eddie

hoytm

Eddie, thanks a lot for your response.

Given what you've said, it's challenging to be able to combine clones with SQL test in such a way a person without highly elevated credentials could add SQL test to a clone they created. Do you have any other suggestions?

Cheers,
Matt

Jon_Kirkwood

Thank you for reaching out to us with your concern. I understand that the limitations you mentioned with combining SQL Clone and SQL Test.

 One solution that has worked for another client was to use tsqlt directly in their solution and add the tSQLtCLR assembly as a trusted assembly using the sys.sp_add_trusted_assembly stored procedure. 

This allowed the user to run the SQL Test on the cloned database without requiring highly elevated credentials.

 If this solution does not work for you, another option is to explore a PowerShell workaround to set the TRUSTWORTHY property on the cloned database. As Eddie mentioned this is a write change to the disk and would increase the size of the clone. This may be an acceptable change within your environment and allow SQL Test to function for the users.