I've updated target databases:
sp_configure 'clr enabled', 1;
Just to be clear, "clr enabled", and sp_configure
in general, are instance-level, not database-level configuration.
I've followed this advice on MSSQLTips article
Yikes. Well, that article on MSSQLTips is more of a problem than the error that you are getting. It would be best if you forgot everything you saw in that article and never looked at it again. It is full of bad advice:
- SQLCLR not required for sending email in SQL Server Express (I have tested this and it does work). HOWEVER, it does not work in SQL Server Express LocalDB :-(
- You should use
SqlString
instead of String
for input parameter types (and for return types)
- Deploying to
msdb
? Maybe it's just me, but I would never deploy code to msdb
(and I would only add code to master
if it is physically required, such as when marking a proc as either a start up proc or as a system proc).
UNSAFE
permission set? Why? For email? The assembly should be EXTERNAL_ACCESS
. Code (assemblies, logins, users, or anything else) shouldn't be given more permissions than necessary.
- Setting
TRUSTWORTHY ON
for msdb
is two problems in one:
- Setting
TRUSTWORTHY ON
is lazy. Sure, it's fine for doing proof-of-concepts / quick tests, but it is not a good long-term / production-level option. It's a huge security risk. Instead, Module Signing should be used. Here is a guide I wrote for how to accomplish this, and do so within Visual Studio:
SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" - Solution 1
TRUSTWORTHY
is already ON
for msdb
. At least it is ON
by default.
- The
@body
input parameter should be NVARCHAR(MAX)
, not NVARCHAR(4000)
. This is, after all, HTML email (i.e. myMessage.IsBodyHtml = True
)
- No, absolutely do not store your plain-text email password in the .NET code. DLLs / Assemblies are not encrypted. ALL strings are stored as-is at the end of the EXE / DLL / Assembly. It takes almost no effort to display the contents of an assembly, and if you follow this advice, your SMTP server, login, and password will be stored for anyone to see. Either pass those in from a variable, or do a simple query to select from a table. You might even be able to get them from the Windows Registry.
What database are you actually publishing to? Assuming that you are publishing to another database that is not msdb
, then you, at least simplistically, in this moment to move forward, need to set TRUSTWORTHY ON
for the DB that you are publishing to. But again, enabling TRUSTWORTHY
is just for testing (please see: PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining).
Now, if you are executing the ALTER DATABASE...
manually and then still get the error upon publishing, then most likely you have the "deploy database properties" option selected (in Visual Studio: go to "Project Properties" | "Debug" tab | "Deployment Options" area towards the bottom). TRUSTWORTHY
is one of those database properties. So, when "deploy database properties" is checked, then every deployment will set the options to match what is defined in the project. And by default, Trustworthy
is not enabled.
Your options are:
If you are deploying database properties:
To enable Trustworthy
in Visual Studio (for deployments): go to "Project Properties" | "Project Settings" tab | "Database Settings..." button | "Miscellaneous" tab. Check the box for "Trustworthy" and then click the "OK" button.
If you are not deploying database properties:
Enable trustworthy in the database where you are deploying the assembly (enabling TRUSTWORTHY
in msdb
won't help)
Once you have this working correctly in development, handle security properly before pushing to production:
SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" - Solution 1
For learning more about SQLCLR in general, please visit: SQLCLR Info. Be sure to check out the "Stairway to SQLCLR" link, which is a series I am writing on this topic on SQL Server Central.