Failed to Create an Assembly in Sql

Failed to CREATE AN ASSEMBLY in SQL

I just encountered exactly the same problem.

This is an old page, but first, formost and finally, the DLL must be built with .NET 2.0. SqlServer 2005 is built with .net 2.0, because that is the latest available when it was written. SqlServer 2008 might also have the same issue.

The DLL and the SqlServer must both reference the same .NET framekwork version. Once I figured this out, I created a separate project under 2.0 and shazam! worked perfect first time.

To see what version of .net your sql server uses:

select * from sys.dm_clr_properties

SQL Server error when trying to create assembly from .net core dll

Don't do that, basically. SQL CLR only supports .NET Framework, and AFAIK there are no plans to change that. Honestly, I'd probably just advise "avoid it altogether", and just use your database to ... store and query stuff. But if you really want to (and you understand how much pain it is going to cause you): you'll have to change your dll to target a version of .NET Framework (not .NET Core, or .NET 5 etc) that is available on the server. At a push you can target .NET Standard 2.0 and use that inside later versions of .NET Framework, but honestly: I don't recommend it - most of the .NET Standard support in .NET Framework is duck-tape surrounding lies holding up shims standing on hacks.

SQL Server 2017: Failed To Create Assembly. Check if referenced assemblies are up-to-date and trusted

You are probably pointing to a reference assembly and not the actual assembly with the actual code in it (hence the "Code size is zero." error message).

If you are using SQL Server 2012 or newer, then the following does work (and does not require setting the DB to TRUSTWORTHY ON, so turn TRUSTWORTHY back to OFF):

USE [master];
CREATE CERTIFICATE [MS.NETcer]
FROM EXECUTABLE FILE =
'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll';

CREATE LOGIN [MS.NETcer] FROM CERTIFICATE [MS.NETcer];

GRANT UNSAFE ASSEMBLY TO [MS.NETcer];

USE [SomeUserDB];
CREATE ASSEMBLY [System.Net.Http]  
FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Net.Http.dll'
WITH PERMISSION_SET = UNSAFE;

Regarding Edit:

If I use the .dll from the 4.7.2 framework instead, I get the following error:

Do not try to use a specific .NET Framework version of a .NET Framework library. You need to use what is currently on the server running SQL Server since the version that SQL Server is using must match the version that the OS is using. So, for system assemblies it is best to pull from disk (i.e. C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ ).

Regard Edit 2:

If I follow the advice and pull the .dll from C:\Windows\Microsoft.NET\Framework64\v4.0.30319, I get this problem instead.

Assembly in host store has a different signature than assembly in GAC.

Hmm. I would think the GAC would get its version from that same directory. Is it possible that there was a Windows Update that updated the version on disk and the GAC hasn't been refreshed yet? Or something like that? Can you rebooting the server and then try loading the assembly again?

Regarding Edit 3:

I copy and paste the System.net.http.dll library to the same SQL Server shared drive.

No, no, no. Do not copy/paste any .NET Framework DLL. You need to use the version that is on the same server as SQL Server. This is why you need to execute the statements that I provided above (i.e. CREATE ASSEMBLY [System.Net.Http] FROM 'C:\Windows\Microsoft.NET\Framework64\... ). It doesn't matter if you compile your project against .NET 4.5.2 and the server hosting SQL Server has .NET 4.8 installed. Just load the .NET Framework DLL from the server running SQL Server, then load your DLL. The only time a version difference matters between what you compile against and what SQL Server is using (based on the server it's running on) is if you are developing on a machine with a newer version of .NET Framework that has a new feature not found in the version being used by SQL Server. And in that case (which is rare to happen), you simply need to update the version of .NET Framework on the server running SQL Server.

Failed to create assembly 'System.ServiceModel.Internals' in SQL

Have you tried installing the assembly with the UNSAFE permission set option?

I have System.ServiceModel.Internals (v4 from GAC) installed on SQL Server 11.0.5058 as UNSAFE, I don't know if you will be having versioning issues as well but I believe the assembly can only be installed as unsafe as it may access unmanaged resources.

From your error message:

[found unmanaged pointer]
[expected unmanaged pointer]
Unexpected type on the stack.

I understand this as Expected Unmanaged pointer, found unmanaged pointer, unmanaged pointer not allowed.

See https://msdn.microsoft.com/en-us/library/ms189566.aspx for definitions of permission sets.

Deploying SQL CLR Project fails when creating assembly on database

First off, only a short list of .NET Framework libraries are "supported". You can find that list on the MSDN page for Supported .NET Framework Libraries. System.Data.DataSetExtensions is not one of them. That is why you got the first error.

The second thing posted is a warning, not an error. It is telling you that you can run into one or more problems that Microsoft will not care about or fix as you are doing something that is unsupported.

Run the following in the database where you are deploying the assemblies:

SELECT * FROM sys.assemblies sa WHERE sa.is_user_defined = 1;

and you should see both. Although if System.Data.DataSetExtensions has dependent libraries, they won't be automatically loaded because only the folder that the initial CREATE ASSEMBLY is pointing to will get loaded, and that is now where your DLL is being built and not the .NET Framework folder.

You might be better off loading System.Data.DataSetExtensions by itself in its own CREATE ASSEMBLY pointing to the appropriate C:\Windows\Microsoft.NET\Framework (or Framework64) folder. Especially if you notice that the error message shows "Code size is zero", which I have only seen when trying to load the DLL from one of the Reference Assemblies folders.

Now, because this is an unsupported library it probably does stuff that disallow it from being loaded as SAFE. Since we don't have the private key info in order to go the ideal route of creating an Asymmetric Key and then a Login based on that Asymmetric Key, you have little choice but to:

• set the database you are deploying to as TRUSTWORTHY via:

  ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON;
  

• load the assembly WITH PERMISSION_SET = UNSAFE

Essentially:

USE [DatabaseName];

ALTER DATABASE CURRENT SET TRUSTWORTHY ON;

CREATE ASSEMBLY [System.Data.DataSetExtensions]
FROM
   'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Data.DataSetExtensions.dll'
WITH PERMISSION_SET = UNSAFE;

After that you should be able to load your assembly, though it will likely have to be set to UNSAFE as well.

And I would delete the copy of System.Data.DataSetExtensions.dll that you put in your build folder.

---

For more info on SQLCLR in general, please see the series I am writing on SQL Server Central: Stairway to SQLCLR (free registration required).

Unable to create the tSQLtCLR assembly in SQL Server 2017

SQL Server 2017 introduces a new server-level configuration option named "CLR strict security", and it is enabled by default. This option requires that ALL Assemblies, even SAFE ones, be signed with a certificate or strong name key, and that the Certificate or Asymmetric Key used to do that signing is loaded into [master], and has a Login created from it, and that Login has been granted the UNSAFE ASSEMBLY permission.

Due to SAFE Assemblies now needing to have the signature-based Login in place before being loaded via CREATE ASSEMBLY, it is no longer possible to have an empty, signed Assembly that gets loaded into [master] via CREATE ASSEMBLY ... FROM 0x... WITH PERMISSION_SET = SAFE;.

Now, there are only two ways to create objects usable to set up SQLCLR security from a VARBINARY literal or variable (i.e. not from an external file):

1. CREATE ASSEMBLY ... FROM 0x...;
2. CREATE CERTIFICATE ... FROM BINARY = 0x...;

Option #1 is no longer an option, at least not by itself. Option 2 is fine, but was never preferred due Certificates not being fully integrated into the Visual Studio / MSBuild build process.

Fortunately, there are two ways to fix this as discussed in the following two blog posts of mine:

1. SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1 — more steps than Part 3, Solution 2 (below), but a good fit for existing projects as it requires almost no changes to the existing solution or even deployment process (and in fact, this is effectively the route that I went for my SQL# project as all it did was add 3 simple steps to the beginning of the installation script)
2. SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

HOWEVER,

that just answers the question of "why" you are in the situation that you are currently in. To fix that situation, assuming that you likely aren't going to update the tSQLt build process to include a Certificate, then you can do a simple one-time fix of:

ALTER DATABASE [master] SET TRUSTWORTHY ON;
EXEC tSQLt.InstallExternalAccessKey;
EXEC master.sys.sp_executesql N'GRANT UNSAFE ASSEMBLY TO [tSQLtExternalAccessKey];';
ALTER DATABASE [master] SET TRUSTWORTHY OFF;

The GRANT UNSAFE ASSEMBLY is there due to the tSQLt.InstallExternalAccessKey Stored Procedure only granting EXTERNAL ACCESS ASSEMBLY to the Login, which used to be fine, but now is not enough.

Of course, you won't be able to load the tSQLt Assemblies until those 4 steps are done, so if the process is to load everything first and that is failing, then you will need to do:

EXEC sp_configure 'clr strict security', 0; RECONFIGURE;
-- Install tSQLt ...
EXEC tSQLt.InstallExternalAccessKey;
EXEC master.sys.sp_executesql N'GRANT UNSAFE ASSEMBLY TO [tSQLtExternalAccessKey];';
EXEC sp_configure 'clr strict security', 1; RECONFIGURE;

I created an issue in the tSQLt GitHub repository with the steps require to incorporate the ideal fix into the source files: https://github.com/tSQLt-org/tSQLt/issues/25

PLEASE NOTE

that none of these possible solutions includes using the new "Trusted Assemblies" feature. That feature should never, ever be used by anyone for any reason (outside of sheer curiosity and testing). The reasons for avoiding it are detailed in several blog posts (currently 3 and more on the way) starting with:

SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment

---

Related Topics