Add MSBuild predefined Targets for "BeforeSqlBuild" and "BeforePublish" to SSDT SQL Server Database Projects - by Solomon Rutzky

Status : 

 


3
0
Sign in
to vote
ID 3139701 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 8/21/2017 10:40:03 PM
Access Restriction Public

Description

Sometimes, especially when working with SQLCLR objects,  it is useful to manipulate the DLL / assembly and/or the generated deployment DDL T-SQL prior to the publishing process taking over. MSBuild allows for easily overriding Targets, but for some reason the SSDT workflow (found in C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v14.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets ) doesn't have any predefined Targets that are a natural fit for this.

For example, it's difficult to sign SQLCLR assemblies with a certificate due to the assembly being converted into string format for inclusion in the Create and/or incremental publish script _before_ the PostBuild event executes. This was ok due to signing with a strong name key being enough to get EXTERNAL_ACCESS and UNSAFE assemblies to work without needing to resort to enabling TRUSTWORTHY.

BUT, with SQL Server 2017, even SAFE assemblies need to be signed, AND the signature-based Login needs to exist along with having the UNSAFE ASSEMBLY permission _before_ the assembly can be created. Signing with a strong name key is no longer enough since Asymmetric Keys (in SQL Server) cannot be created from hex bytes (i.e. a VARBINARY literal). But Certificates can be created from a VARBINARY literal, so that's what we need to use. Great, except now we are back to the part where we have no (easy-enough) mechanism for signing the assembly before it gets packaged up for publishing.

I found that the easiest way _currently_ is to override "SqlBuildDependsOn". I have documented this in a blog post ( https://SqlQuantumLeap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/ ). While this works, overriding the default workflow introduces some amount of risk since the definition of "SqlBuildDependsOn" can change in a future SSDT update that causes existing projects to break.
Sign in to post a comment.