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
To be clear, a "BeforeSqlBuildEvent" target that occurs just prior to the "SqlBuild" target would fit within the current naming convention and allow for signing DLLs prior to them being grabbed as input for generating either the _Create script or the incremental deployment script. The current "PreDeployEvent" that is called prior to the "SqlDeploy" target works for the incremental deployment script only, it is called too late in the process to help with the _Create.sql script.
Also, breaking up the "SqlDeploy" such that an event exists between the creation of the incremental deployment script and the execution of it ("BeforeSqlDeployment" or "BeforeSqlDeployExection") would also help in making last-chance modifications to the incremental deployment script (or anything else, really) before it is executed on SQL Server.
Solomon Rutzky commented
I originally submitted this suggestion via: