Skip to main content

For a CLR procedure it is possible to register an DLL assembly, so both can be deployed with the Software Factory.

However, this will fail when the assembly is not signed. You can bypass that by adding the assembly to the trusted assemblies, using sp_add_trusted_assembly. Is there a formal way to also incorporate this during deployment?

For now, I simply insert a script in the dll-assembly code that calls sp_add_trusted_assembly.
 

if  not exists (  select  1
                  from    sys.trusted_assemblies  ta
                  where   ta.hash = 0x11986168BBC9874F203A9A7F2935E621F4437A4B668E816EC57372C69E92221B4044E996868A5EE4D75A038532EB3B10D1C367EDD87B67FFA608B77F8FDF8A8A  )
  exec sp_add_trusted_assembly 0x11986168BBC9874F203A9A7F2935E621F4437A4B668E816EC57372C69E92221B4044E996868A5EE4D75A038532EB3B10D1C367EDD87B67FFA608B77F8FDF8A8A, N'for_your_eyes_only'

go

/* Create assembly for_your_eyes_only */
create assembly "for_your_eyes_only"
from 'c:\our_assemblies\for_your_eyes_only.dll'
with permission_set = SAFE

go

 

Hi Dave,

There's no screen inside the Software Factory dedicated for this. However, with either an Upgrade script or a dedicated Code file and a Program object-typed Control procedure you can be able to do this. Using the Code file method, you can decide when the Code file is executed in the Creation process, for example right before the clr_assemblies Code file. 

On details how to create this, I advise to look at some of the sql_% control procedures that are distributed with every model using SQL Server as RDBMS. Take the most easiest one, sql_manual. To find this control procedure, open up Functionality in your model and disable the prefilter Hide generated. Then search for sql_manual and take a look at the Control procedure code.

Edited example:

insert into #prog_object 
(
    prog_object_id,
    prog_object_type_id,
    tab_id,
    task_id,
    report_id,
    process_flow_id,
    process_action_id,
    subroutine_id,
    code_file_id,
    order_no,
    prog_object_file_spec,
    prog_object_status
)
select 
    'sign_ddl_assemblies' , 
    'CLR_ASSEMBLIES',
    null,
    null,
    null,
    null,
    null,
    null,      
    'sign_ddl_assemblies',
    100,
    @prog_object_folder_spec + 'sign_ddl_assemblies.sql',
    0 -- Not flagged.

(Requires Code file “sign_ddl_assemblies” to be created by you.

Path: Models > Model content > Code files)

For your own code file, edit the prog_object_id, and the code_file_id. Update them both from manual to equal the name of your created Code file. Also change the prog_object_file_spec to mirror the changes just made. Finally for the best effect, change the prog_object_type_id to CLR_ASSEMBLIES. This will ensure that the Code group CLR_ASSEMBLIES can be used to connect with your code file

And that's it! You can now create a new control procedure with template, and assign this template to the program object after you have re-generated the Code group of course.

Hope this helps out!

Thanks Mark!

 

I’ve already used a static assignment, for code group CLR_ASSEMBLIES, type Program Object Item. Using a dynamic control proc was a bit overkill.

But my question was more about the formal part. Will this be a feature in the future? Or what is Thinkwise’s recommendation regarding unsigned assemblies?
 

What does the assembly do? The answer depends on that. We typically don't recommend using any as it isn’t portable to a Cloud solution for example. Also nowadays with Indicium as part of the Thinkwise platform, any Web service can be easily integrated into your solutions using process/system flows and using HTTP connectors for example to utilize these services.

Clear answer. That satisfies the question, thank you.

Btw. The assemblies are used to execute system tasks from a Windows Gui. Tasks like (like backup, restore, but also check and create of linked servers) . This way it suppresses messages with a severity < 10.

It’s an administrative tool for on premise sql server, and won’t be used with indicium.

Terms & ConditionsCookie settings