2

I executed the following command to my database and it give me the message Commands completed successfully.

USE XYZ
GO
DECLARE @clrName nvarchar(4000) = 'XYZCLRDatabase, ...';
DECLARE @asmBin varbinary(max) = <bindary>;
DECLARE @hash varbinary(64);

SELECT @hash = HASHBYTES('SHA2_512', @asmBin);

EXEC sys.sp_add_trusted_assembly @hash = @hash,
                                @description = @clrName;
GO

It also shows the same record into the sys.trusted_assemblies table.

But it does not listed into the Assemblies folder...

XYZ database > Programmability > Assemblies

When I am trying to create the Stored Procedures with the following code I get the error.

USE XYZ
GO
CREATE PROCEDURE SPName @sqlXml XML, @flag1 bit, @flag2 bit, @id int null, @flag3 bit
AS
EXTERNAL NAME XYZCLRDatabase.StoredProcedures.MYClrSP

I get the following error message:

Msg 6528, Level 16, State 1, Procedure SPName, Line 1 [Batch Start Line 23]
Assembly 'XYZCLRDatabase' could not be found in the SQL catalog for database 'XYZ'.

what goes wrong....

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Kalpesh Rajai
  • 2,040
  • 27
  • 39
  • 1
    `sp_add_trusted_assembly` only marks an assembly as trusted for when it ever gets loaded. It doesn't actually add the assembly to the database; for that you need `CREATE ASSEMBLY`. – Jeroen Mostert Nov 29 '19 at 12:34
  • 1
    You can have trusted assemblies, but have disabled the assembly execution at database level or at server level and that would tell you the assemby was not found. A bit missleading, I know – Cleptus Nov 29 '19 at 12:54
  • 1
    Have you tried `EXEC sp_configure 'clr enabled', 1;` and after that `ALTER DATABASE databaseName SET TRUSTWORTHY ON`?? – Cleptus Nov 29 '19 at 12:57
  • @bradbury9 Thank you... Yes I also executed the clr enabled one command... – Kalpesh Rajai Nov 29 '19 at 12:59
  • @JeroenMostert Thank you so much... I just forget to execute the CREATE ASSEMBLY command.. As I think the sp_add_trusted_assembly alsi register the assembly – Kalpesh Rajai Nov 29 '19 at 13:00
  • @JeroenMostert I really welcome you to please submit your answer, So I can accept that answer and it will be helpful to the other developers... – Kalpesh Rajai Nov 29 '19 at 13:03
  • @KalpeshRajai In case Jeroen forgets or has not time to post an answer you could post and answer to your own question. It is not only allowed but encouraged. If my steps helped in any way, add them too to your answer. – Cleptus Nov 29 '19 at 13:28

2 Answers2

2

While bradbury9 is correct that the CREATE ASSEMBLY statement is missing, PLEASE DO NOT enable TRUSTWORTHY in your database!!! Not only is it an unnecessary security risk, it's also absolutely useless when either using "trusted assemblies" or the much preferred signing the assemblies. Both of those cases ("trusted assemblies" and signing the assemblies) exist so that you don't need to set the database to TRUSTWORTHY ON (meaning: either set TRUSTWORTHY ON XOR sign the assemblies / add them as "trusted assemblies"; doing either one of the latter options means that you do not set TRUSTWORTHY ON). For more info on why you shouldn't enable TRUSTWORTHY and the preferred alternative (i.e. Module Signing), please see:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining

Additionally, "trusted assemblies", while not nearly as bad as TRUSTWORTHY ON, isn't ideal either as there are several problems with that feature. Signing the assemblies, either outside of SQL Server (preferred) or even inside SQL Server, is far better. Please see my answer to the following question, also here on S.O., for your best options (see the bottom half, below the line):

CLR Strict Security on SQL Server 2017

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
0

You are missing the CREATE ASSEMBLY instruction. In summary, the steps to add an assembly in SQL Server are:

  1. Make sure the SQL Server instance allows CLR: EXEC sp_configure 'clr enabled', 1; RECONFIGURE;

    This step (#1) should only be required once.

  2. Call the sp_add_trusted_assembly stored procedure

  3. Call the CREATE ASSEMBLY instruction
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Cleptus
  • 3,446
  • 4
  • 28
  • 34
  • Hi there. I want to upvote this as it's correct re: missing the `CREATE ASSEMBLY`. BUT, I'm down-voting due to Step 2: enable `TRUSTWORTHY`. It's not only a very dangerous security hole to open up, it's also 100% unnecessary in this scenario given that "trusted assemblies" exists solely as a mechanism to _avoid_ setting `TRUSTWORTHY ON`. If you set `TRUSTWORTHY` to `ON`, then there's no need/purpose in adding the assembly to the "trusted assemblies" list. And, you don't even need to do that if you sign the assemblies (preferred). Please remove Step 2 and I will change my downvote to an upvote. – Solomon Rutzky Dec 23 '19 at 08:22
  • Thanks for making that update. I made a minor change to make it consistent given the change. Final request: please remove [your comment](https://stackoverflow.com/questions/59104869/msg-6528-assembly-xyzclrdatabase-could-not-be-found-in-the-sql-catalog-for-d#comment104442252_59104869) on the question that recommends enabling `TRUSTWORTHY`, then I will upvote. Also, just FYI, regarding your first comment on the question: CLR integration is an instance-level only option; it cannot be handled at the DB level (Contained DBs might not support it, but you wouldn't enable that just to disable CLR). – Solomon Rutzky Dec 23 '19 at 20:01