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.
I can think of many times when I needed a more granular event injection handle than is currently provided by SSDT. Expanding the default workflow would allow for advanced build behaviors that would make the product more useful through easier automation. The specific suggestions Solomon made are exactly what I typically need.
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: