1

Today, we got a very weird situation. We added a SQL login to our database server. After adding this login, SQL unloaded a critical CLR routine "due to security data definition language" (error 10310). As a result several user processed crashed. The CLRs unloaded in all the registered databases, but the new login had nothing to do with these databases. The databases using the CLR have the TRUSTWORTHY setting to on.

Any idea why creating a regular SQL login is causing this issue? It's certainly NOT a memory issue, plenty of GB available for the OS. We fixed this issue by executing TRUSTWORTHY and registering the CLR, but we're a little afraid if this happens again when adding another login.

Configuration: SQL server 2019 CU20 Enterprise, 512GB memory, 384GB for SQL

Improve this question
11
  • 1
    Hello: 1) How many custom assemblies do you have? 2) You say TRUSTWORTHY is ON in the DBs containing the assemblies, but then say the issue was fixed by "executing TRUSTWORTHY". What does that mean? 3) Did you actually re-CREATE the assemblies? 4) Did you try executing the CLR functionality again, after it got unloaded? If so, did it error? If so, what error? 4) What Login(s) own the DBs containing the assemblies? 5) Did the new Login belong to any server Roles? 6) Did assemblies get unloaded immediately upon creating the Login? 7) Have you reproduced this in non-Production environments?
    – Solomon Rutzky
    Commented Jun 28, 2023 at 15:27
  • "4) What Login(s) own the DBs containing the assemblies?"...and is it possible the Owner of those databases changed somehow?
    – J.D.
    Commented Jun 28, 2023 at 17:25
  • 1) 2 assemblies: 1 for compressing JSON and 1 for decompressing JSON 2) This server is part of an AG. At every failover we de-register the CLR , enable TRUSTWORTHY and register the CLR again in order to keep it working (figured it out this was the only was to have it running) Since the CLR's were gone after this issue, I ran this step to fix the problem 3) yes, after the crash, see 2 4) Don't know how to determin that, they are recreated in a jobstep after a failover (service account) 5) no 6) Yes, after creating the login 7) we tried, but with no success
    – Wilfred van Dijk
    Commented Jun 29, 2023 at 6:56
  • Willfred, just a heads up that you need to tag someone for them to get notified that you replied. E.g. like this @SolomonRutzky. Also, please put updated information like answers to questions in your original Post so it's easier to read and doesn't get missed in the comments by anyone else who comes to see your question. Then you can just add a reply comment tagging who you're replying to let them know you've updated your Post.
    – J.D.
    Commented Jun 29, 2023 at 12:45
  • Also, you can check the database owner in SSMS by right clicking on the Database, going to Properties, and then under the General section it should say the Owner.
    – J.D.
    Commented Jun 29, 2023 at 12:47

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.