search

microsoft / DacFx

DacFx, SqlPackage, and other SQL development libraries enable declarative database development and database portability across SQL versions and environments. Share feedback here on dacpacs, bacpacs, and SQL projects.
https://aka.ms/sqlpackage-ref
MIT License
356 stars 20 forks source link

[SQLCLR supported only on .NET Framework] - CREATE ASSEMPLY script leads to "SQL70557: This assembly is corrupt or not valid" on dotnet build #523

Closed lausitzer closed 1 week ago

lausitzer commented 1 week ago

Steps to Reproduce:

  1. Create new SQL project for SQL-Server 2019: dotnet new sqlproj -tp Sql150
  2. Write SQL-Script for CREATE ASSEMBLY containing your assembly as binary literal, e.g.: CREATE ASSEMBLY [TestCLR] AUTHORIZATION [dbo] FROM 0x4D5A90000300000...
  3. Run: dotnet build

Build fails with "Build error SQL70557: This assembly is corrupt or not valid" pointing to your CREATE ASSEMBLY script. For a cross check, executed CREATE ASSEMBLY script with SSME. Assembly gets imported flawless if appropriate conditions are met (CLR-support enabled, login for assemblies signing key created).

Also tried copying dll to $(IntermediateOutputPath) and $(OutputPath) and using assemblies name instead off binary literal. Leads to SQL70502: The assembly source is not valid. Only binary literals are allowed .

Did not test on prior versions of SDK.

Find sample-project attached. MySqlProj.zip

(DacFx/SqlPackage/SSMS/Azure Data Studio)

lausitzer commented 1 week ago

Maybe related: https://github.com/microsoft/azuredatastudio/issues/21370

dzsquared commented 1 week ago

Unfortunately, SQLCLR is based only on .NET Framework, we can't support it on SQL projects with .NET 8 unless significant changes are done to the SQLCLR. To build a SQL project (including SDK-style SQL projects) with SQLCLR assemblies you would need to build it with msbuild and/or Visual Studio.

Image

Image

This same project fails to build with dotnet build (.NET 8 SDK). (noting that SDK 0.2.4-preview has a bug with SSDT, so I downgraded to 0.2.3-preview for testing with Visual Sutdio)

In a pipeline, you'd use an msbuild task like - https://learn.microsoft.com/en-us/azure/devops/pipelines/tasks/reference/msbuild-v1?view=azure-pipelines - to build a SQL project.

... unfortunately, this does mean you are restricted to Windows build agents only.

I'd encourage you to share feedback on SQLCLR to the SQL engine team via https://aka.ms/sqlfeedback

ps - thank you thank you for the sample project attachment! love it when digging into an issue is made simpler with the repro setup.

lausitzer commented 1 week ago

@dzsquared Many thanks for investigation. To be clear, its not intended to build the CLR-assembly using dotnet build. I just want to integrate an external build assembly into my dacpac. From my perspective there is no need to parse this assembly with .NET 8. However, I don't know what's going on inside that causes this behavior. Wouldn't it be possible to accept the assembly as is?

tn-5 commented 1 day ago

@lausitzer it would be really nice if it were possible to include a pre-build assembly in the "dotnet build" pipeline. Currently we work around in a rather ugly way by defining normal SQL functions for those defined in the CLR assembly and then in the post deploy we drop those, create the assembly and recreate all the functions. At least it works but I agree that it would be much better if one could just integrate a pre-build CLR dll into the dotnet build process.