1

We had several issues during the in-place upgrade from 2014 to 2017, namely the trusted assembly CLR feature that interfered with the successful installation of SSIS at the time. I have since gotten SSIS installed and working, but the feature is still broken.

The error I received at the time, and that I still receive when I query the system table directly is: "Internal table access error: failed to access the Trusted Assemblies internal table". The system view seems to run an OPENROWSET on the "table" TRUSTED_ASSEMBLIES. I don't see a lot of chatter on the internet of people dealing with this problem.

I don't plan on using that feature, but am fearful that it may cause issues in the future with updates or with outside vendors. Another symptom was that in order to fix some of the issues with SSIS package execution was I had to manually assign execute permissions to low-level procedures that are usually done for you (fortunately, the documentation indicated which built-in groups had access to the procs).

If anyone has any insight on the issue that would be appreciated; guessing a tear-down and complete rebuild might be in order.

drexasaurus
  • 60
  • 6
  • Hi there. If possible, can you please update the question with the _exact and complete_ error message, including the "Msg", "Level", and "State" values, or "HRESULT" if that was provided? That would help narrow this down. Thanks! – Solomon Rutzky Mar 16 '21 at 17:41

1 Answers1

0

Have you applied the CUs (Cumulative Updates) for SQL Server 2017? If not, you probably should.

Yes, the "CLR strict security" / "trusted assemblies" "feature" is quite the dumpster fire. Please see my answer to the following question (also here on S.O.) regarding the proper ways to work around the new (as of SQL Server 2017) restrictions (the final paragraph in that answer deals with your situation: pre-existing, unsigned assemblies):

CLR Strict Security on SQL Server 2017

Using module signing you should be able to get everything working without assigning any permissions directly.

As for that particular "Internal table access error" error, that's new to me. I assume you are executing SELECT * FROM sys.trusted_assemblies' as sa or some other login that is a member of the sysadmin fixed server role? If not you would get a permissions error stating:

Msg 300, Level 14, State 1, Line XXXXX
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line XXXXX
The user does not have permission to perform this action.

Since you aren't getting the permissions error, it's possible that some component didn't upgrade correctly / completely (hence making sure you have installed the latest CU might check / fix that).

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • Yes, unfortunately we have updated to the latest version and that did not resolve the issue. Fortunately that seems to be the only remaining problem, and while I don't plan to use the trusted assembly, it did prevent me from easily creating a new SSISDB catalog and was hoping that we could resolve this to avoid future issues. The error is new to me to, and when I google it, all I get is the documentation that the error was added in 2017. If I find a solution, I will have to record for posterity, apparently. – drexasaurus Mar 18 '21 at 18:45
  • @drexasaurus Well, at least we know what doesn't work, right ;-). Yeah, there's not much out there on this error. I saw one reference in Google that looked like the exact same error (i.e. for trusted assemblies), but the page is no longer valid. I found another question on social.msdn for the same error message (i.e. error reading internal table), but it was for something else and I believe the advice was to apply latest CU but I don't recall there being any confirmation of results. I checked `compatibility_level = 120` and `containment = partial` (just in case) and neither caused the error. – Solomon Rutzky Mar 19 '21 at 05:25
  • @drexasaurus I even just tried `EXEC sp_configure N'clr enabled', 0; RECONFIGURE;` and then: `EXEC sp_configure N'lightweight pooling', 1; RECONFIGURE;` (then restart the instance), and still no error. I tried this as "lightweight pooling" disables ALL CLR-related stuff, even the internal functionality (e.g. `FORMAT()`, `COMPRESS()`, `AT TIMEZONE`, etc). – Solomon Rutzky Mar 19 '21 at 05:31