MB

My feedback

  1. 7 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    MB commented  · 

    I disagree strongly with this post and think that trusted assemblies is a very useful feature, especially for migrating existing databases containing CLR assemblies. Most users who’ve created their own CLR procedures, for example to access C# regex or cryptographic functions, will likely not have signed these and would not want to go through the effort of doing so, especially during a database migration which is quite a busy time in itself. Trusted assemblies make this very straightforward.

    To answer the numbered points above:

    1+2: I’m no expert in cryptography, but my understanding is that, as SHA2-512 is a cryptographic hash function, it is essentially impossible to design a binary that hashes to the same value using currently available computing power. Furthermore, assemblies are stored in SQL Server, not on disk, so it’s not as straightforward as tampering directly with a dll.

    3: I don’t think this is a major issue but in any case it would be fairly straightforward to write a script to remove any unused hashes from the sys.trusted_assemblies catalogue. This would just entail enumerating the hashes of all user assemblies in each database, then removing any entries from sys.trused_assemblies that were not in this collection.

    4: Only remove the trusted assembly once it’s no longer used by any database.

    5: This is unlikely to be an issue for most people. There is nothing preventing anyone from using certificates if so!

    6: Using the script below it’s very easy to trust the assemblies on an attached/restored database without disabling any security features.

    7: Certificates may be more appropriate for future development, but it’s straightforward to obtain the hash of an assembly using T-SQL.

    8: I don’t think this will concern many people.

    If we are creating our own assemblies I can’t envisage any significant additional risk from this functionality. Unless certificates are signed by a recognised authority, which would be preferable for obtaining third party dlls, I can’t see any great advantage that they confer. But, as I say, I’m not an expert in cryptography. Trusted assemblies do perhaps add an additional attack vector but, as always, it’s necessary to balance security against pragmatism.

    In case it helps anyone, I have created a stored procedure that marks all existing assemblies in a database as trustworthy. To use it, add the proc below to the master database, not forgetting to run sp_MS_marksystemobject, which allows the procedure to run in the context of the current database. Then change the context to your user database and run the procedure.

    USE master
    GO

    CREATE PROCEDURE dbo.sp_TrustExistingAssemblies
    AS
    DECLARE @hash binary(64)
    DECLARE @clr_name nvarchar(4000)

    DECLARE c CURSOR FOR
    SELECT hash = HASHBYTES('SHA2_512', f.content), a.clr_name
    FROM sys.assemblies a
    JOIN sys.assembly_files f ON a.assembly_id = f.assembly_id
    WHERE a.is_user_defined = 1
    OPEN c

    FETCH NEXT FROM c INTO @hash, @clr_name
    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF NOT EXISTS(
    SELECT 1
    FROM sys.trusted_assemblies a
    WHERE a.hash = @hash
    )
    BEGIN
    EXEC sp_add_trusted_assembly @hash, @clr_name
    END
    FETCH NEXT FROM c INTO @hash, @clr_name
    END

    CLOSE c
    DEALLOCATE c

    GO

    EXEC sp_MS_marksystemobject sp_TrustExistingAssemblies
    GO

    To get the hash of a dll file in you can use:

    DECLARE @blob varbinary(MAX) =(SELECT BulkColumn FROM OPENROWSET(BULK N'path_of_dll.dll', SINGLE_BLOB) AS x)
    SELECT HASHBYTES('SHA2_512',@blob)

    And for the hash of an assembly from T-SQL script, just grab the binary from

    CREATE ASSEMBLY [SomeAssembly] AUTHORIZATION [dbo] FROM 0x(SomeBinary)

    and use it in the HASHBYTES function:

    SELECT HASHBYTES('SHA2_512',0x(SomeBinary))

    In either case you must run sp_add_trused_assembly before actually creating the assembly.

Feedback and Knowledge Base