CCA-DIS-8518 System.IO.FileLoadException: Could not load file or assembly 'acm_sql_regex after migrate cca database

Products

• CA Configuration Automation

Issue/Introduction

After customer migrate CCA Database to another SQL Server, when trying to run Management Profile the following errors 
would appears:

CCA-DIS-8607: The "Server Properties" grid job failed on server "example.server.com", error message=An error occurred 
in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, 
or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly 
trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 
'acm_sql_regex, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'

CCA-DIS-8518: Discovery failed on Server "example.server.com", Error message=An error occurred in the 
Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, 
or the assembly may not be trusted.  Run the query again, or check documentation to see how to solve the assembly 
trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly 
'acm_sql_regex, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null'

Environment

Configuration Automation - 12.9

Cause

On cca.log of the CCA server or Grid Node server the following error should appears (in case of Grid Node):

cca.log:
======
...
ERROR [Grid Job 0762bd79-58a0-4f3d-a2a7-70e7103e0ebc] com.ca.acm.database.DBAPI (DBAPI.java:698) 
- DBAPI select error: 'An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. 
The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check 
documentation to see how to solve the assembly trust issues. For more information about this error: 
System.IO.FileLoadException: Could not load file or assembly 'acm_sql_regex, Version=0.0.0.0, Culture=neutral, 
PublicKeyToken=null' or one of its dependencies. An error relating to security occurred. (Exception from 
HRESULT: 0x8013150A)
System.IO.FileLoadException: 
   at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, 
RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, 
Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence 
assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean 
throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& 
stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
   at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& 
stackMark, Boolean forIntrospection)
   at System.Reflection.Assembly.Load(String assemblyString)
' for SQL query: 'select * from acm_bp WITH (NOLOCK) where platform in (select platform_id from acm_bp_platform 
WITH (NOLOCK) where (os_flt is null or dbo.acm_regex_match_ci('Linux',name)=1) and (os_family_flt is null or 
dbo.acm_regex_match_ci('UNIX',os_family_flt)=1) and device_typ=1) and special_bp_typ = 1'
...

Resolution

Request the assistance from the DBA to apply the following adjustments:

1. ALTER ASSEMBLY acm_sql_regex  WITH PERMISSION_SET = EXTERNAL_ACCESS;

2. ALTER DATABASE cca SET TRUSTWORTHY ON;

Additional Information

Error when you run an existing CLR object or create an assembly

ALTER ASSEMBLY (Transact-SQL)

TRUSTWORTHY database property