Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

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.

4 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    2 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Solomon Rutzky commented  ·   ·  Flag as inappropriate

        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.

        Thanks.

      Feedback and Knowledge Base