Add MSBuild predefined Targets for "BeforeSqlBuild" and "BeforePublish" to SSDT SQL Server Database Projects
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.
Solomon Rutzky commented
I originally submitted this suggestion via: