SQLCI: SET TRUSTWORTHY ON and other DB Properties

Automated deployment for web applications and databases

SQLCI: SET TRUSTWORTHY ON and other DB Properties

Postby Totu » Mon Apr 07, 2014 4:48 pm

Hi RG,

Recently I have encountered a problem with the Team City SQL build runner.

When I was developing my SQL CLR assembly the validation of the creation script failed because there is no way to tell the SQLCI if the TRUSTWORTHY property should be ON or OFF and my assembly requires EXTERNAL_ACCESS.

My resolution for now is disabling the validation of the creation script.

I am aware that I could be singing the assembly and load the asymmetric key onto the CI server and that should solve the problem but that is a lot of maintenance I don't want to handle at this point as the project is rather small.
It would be a lot easier to let SQLCI know that it needs to set the TRUSTWORTHY property to ON. I am sure other properties would be nice to have as well but this is a prime example.

Cheers,
Balint
Bálint Bartha, Skyscanner
Totu
 
Posts: 11
Joined: Mon Apr 07, 2014 2:38 pm

Postby david.conlin » Thu Apr 10, 2014 11:02 am

Hi Balint -

Unfortunately, we don't deal particularly well with storing database properties in source control yet.

In the meantime, you can create a migration script to update your database with the required properties. You can read about migration scrips here:

http://documentation.red-gate.com/display/SOC3/Introduction+to+Migrations+V2

Since the migration script must be able to run against any database, it should not use the database name directly, but instead get it from the connection, like this:

EXEC ('ALTER DATABASE [@@DATABASE] SET TRUSTWORTHY ON');

The migration script will be incorporated into the creation script at the start, so it should successfully validate.[/list]
David Conlin
Software Developer
Deployment Manager
david.conlin
 
Posts: 50
Joined: Fri May 04, 2012 3:44 pm


Return to Deployment Manager

Who is online

Users browsing this forum: No registered users and 1 guest