{"id":71790,"date":"2017-07-17T16:56:37","date_gmt":"2017-07-17T16:56:37","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=71790"},"modified":"2026-03-12T13:49:19","modified_gmt":"2026-03-12T13:49:19","slug":"sqlclr-practice-creating-better-way-sending-email-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/sqlclr-practice-creating-better-way-sending-email-sql-server\/","title":{"rendered":"SQLCLR Email in SQL Server: Build a Custom sp_send_dbmail Alternative"},"content":{"rendered":"<p>This article builds a SQLCLR stored procedure that replaces sp_send_dbmail for sending email from SQL Server. The SQLCLR approach lets you send email without MSDB database access, without DBA-configured Database Mail profiles, and with full control over SMTP settings, logging, and error handling. The implementation uses .NET\u2019s System.Net.Mail namespace inside a SQLCLR assembly, with email profiles stored entirely within your application database.<\/p>\n<p>Use cases include: multi-tenant environments where you can\u2019t share instance-level email profiles, Express edition without the Database Mail GUI, and scenarios where you need custom logging or alternative SMTP providers. The source code is available on <a href=\"https:\/\/github.com\/Darko-Martinovic\/SQLCLR\/\" target=\"_blank\" rel=\"noopener\">GitHub here.<\/a><\/p>\n<h2>Introduction<\/h2>\n<p>In this article, I will demonstrate how to build a SQLCLR stored procedure that extends the functionality of the procedure <strong>sp_send_dbmail <\/strong>that is provided in SQL Server. I will be including the code, which is <a href=\"https:\/\/github.com\/Darko-Martinovic\/SQLCLR\/\"> on Github here.<\/a>\u00a0The main purpose of this article is to illustrate that SQLCLR is a very good way of \u00a0accessing .NET functionality for special purposes, but I&#8217;m also hoping that the example might be useful to you.<\/p>\n<p>There are a few reasons for wanting to have a more comprehensive way of sending email from SQL Server than\u00a0<strong>sp_send_dbmail<\/strong> .<\/p>\n<ul>\n<li>You may not have the appropriate rights on the instance level<\/li>\n<li>You need to have more control of the process of sending e-mails<\/li>\n<li>You need to implement custom logging of emails for security.<\/li>\n<li>You have to use your e-mail profiles, rather than those provided by DBA team<\/li>\n<li>If you, as a software vendor, are providing a database that is co-hosted with databases from other software vendors, there are some server-based resources that you have to share. Usually, each of your customers has their DBA team, but you, the vendor, are responsible for your database. <br \/>It is all simple until you require a server-based resource: maybe you have to create some SQL Server Agent alerts or jobs. It could be that you need to send an e-mail to yourself or your company\u2019s dedicated e-mail address to transmit a report, graph or warning message. <br \/>Then you have to negotiate with local DBA team or with the other software vendors. In SQL Server Agent\u2019s alert system, it is only possible to have one active e-mail profile, and you can\u2019t change it without restarting SQL Server Agent.<\/li>\n<li>There are situation when local DBA team, just replace public \u00b8e-mail profile in SQL Server mailing system and you suddenly start receiving phone calls that something goes wrong.<\/li>\n<li>When you need to send an e-mail from a process in your database, that process must have appropriate rights to MSDB database in order to execute <strong>sp_send_dbmail<\/strong>. Alternatively, you see a message like this:<br \/><img loading=\"lazy\" decoding=\"async\" width=\"836\" height=\"63\" class=\"wp-image-71791\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-86.png\" \/><\/li>\n<\/ul>\n<p>You will sometimes hear that a reason to replace <strong>\u2018sp_send_dbmail\u2019<\/strong> with a custom SQLCLR stored procedure is that you is that e-mailing isn\u2019t a feature of SQL Server EXPRESS edition. Actually, this isn\u2019t the case because EXPRESS edition merely lacks the GUI wizard to set up e-mailing. If, instead, you use the T-SQL script (see Appendix B) and replace the information in the declaration statement with actual values, you can set up e-mail on EXPRESS or any other edition of SQL Server.<\/p>\n<p>If you ever look at the parameter list of sp_send_dbmail, you will probably be slightly astonished at the number of parameters. Of course, many of them are optional. In the screenshot below, I surround with red those parameters that I consider crucial for sending an e-mail.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"378\" height=\"476\" class=\"wp-image-71792\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-87.png\" \/><\/p>\n<p>I decided that, if I had to create an alternative sp_send_dbmail for a database, it would be more convenient to have an email profile rather than have to repeatedly enter every item of information that you\u2019d need for an e-mail profile.<\/p>\n<p>The main goal for the project was to have everything needed for e-mailing contained within the database. This means that we must not be required to access the instance or system databases in any way. Of course, CLR should be enabled (see <a id=\"post-71790-AppendixB\"><\/a>Appendix B for information how to do that) but this is the only setting at the instance level that should be changed.<\/p>\n<p>This article talks about how to build such procedure using .NET on the server. This requires using SQLCLR.<\/p>\n<p>Here is how this article is organized:<\/p>\n<ul>\n<li>In the first section, I will make a brief overview about e-mailing on SQL Server.<\/li>\n<li>Then I will introduce CLR stored procedure EMAIL.CLRSendMail<\/li>\n<li>In the third section, I will present a set of new tables needed to implement custom e-mailing.<\/li>\n<li>Then we are talking about sensitive information and how to protect them<\/li>\n<li>Fifth section talks about testing and monitoring SQLCLR solution<\/li>\n<li>Sixth section talks about debugging SQLCLR solution<\/li>\n<li>Finally, I will discuss how supported material is organized.<\/li>\n<\/ul>\n<h2>A Brief Overview of SQL Server E-Mailing<\/h2>\n<p>Database mail is extremely huge and excellent designed system. Sending e-mails occurs outside of SQL Server, in a separate process using external executable. When you send a message, Database Mail adds request to Service Broker queue. The stored procedure \u2018sp_send_dbmail\u2019 returns immediately.<\/p>\n<p>According Microsoft documentation: \u2019<em>Database Mail uses Service Broker activation to start the external program when there are e-mail messages to be processed. Database Mail starts one instance of the external program. The external program runs in the security context of the service account for SQL Server\u2019.<\/em><\/p>\n<p>Therefore, by default, Service Broker has enabled in MSDB database and there is no mailing without Service Broker.<\/p>\n<p>Probably some of you did not know that in background Service Broker uses external program. The Database Mail external executable is <strong>DatabaseMail.exe<\/strong>, located in the <strong>MSSQL\\Binn directory<\/strong> of the SQL Server installation<\/p>\n<p>In addition to the procedure (sp_send_dbmail) parameters, there are configuration parameters that \u2018sysmail_configure_sp\u2019 stored procedure regulate. Parameters have listed in table below. These ones colored with green have implemented in SQLCLR solution. In addition, these one colored with blue are new in SQLCLR solution and black ones are omitted.<\/p>\n<table>\n<thead>\n<tr>\n<td>\n<p>Parameter name<\/p>\n<\/td>\n<td>\n<p>Description<\/p>\n<\/td>\n<td>\n<p>Default Value<\/p>\n<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\n<p>Account Retry Attempts<\/p>\n<\/td>\n<td>\n<p>The number of times that the external mail process attempts to send the e-mail message using each account in the specified profile<\/p>\n<\/td>\n<td>\n<p>1<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Account Retry Delay (seconds)<\/p>\n<\/td>\n<td>\n<p>The amount of time, in seconds, for the external mail process to wait after it tries to deliver a message using all accounts in the profile before it attempts all accounts again.<\/p>\n<\/td>\n<td>\n<p>5000<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Database Mail Executable Minimum Lifetime (seconds)<\/p>\n<\/td>\n<td>\n<p>The minimum amount of time, in seconds, that the external mail process remains active. The process remains active as long as there are e-mails in the Database Mail queue. This parameter specifies the time the process remains active if there are no messages to process.<\/p>\n<\/td>\n<td>\n<p>600<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>DefaultAttachmentEncoding<\/p>\n<\/td>\n<td>\n<p>The default encoding for e-mail attachments<\/p>\n<\/td>\n<td>\n<p>MIME<\/p>\n<\/td>\n<\/tr>\n<tr style=\"color: green;\">\n<td>\n<p><strong>MaxFileSize<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>The maximum size of an attachment, in bytes.<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>1000000<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr style=\"color: green;\">\n<td>\n<p><strong>Prohibited Extensions<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>A comma-separated list of extensions, which cannot be sent as an attachment to an e-mail message.<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>exe,dll,vbs,js<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr style=\"color: green;\">\n<td>\n<p>LoggingLevel<\/p>\n<\/td>\n<td>\n<p>Specify which messages are recorded in the Database Mail log. One of the following numeric values:<\/p>\n<p>1 &#8211; This is normal mode. Logs <strong>only errors.<\/strong><\/p>\n<p>2 &#8211; This is extended mode. Logs errors, warnings, and informational messages.<\/p>\n<p>3 &#8211; This is verbose mode. Logs errors, warnings, informational messages, success messages, and additional internal messages. Use this mode for troubleshooting.<\/p>\n<\/td>\n<td>\n<p>2<\/p>\n<\/td>\n<\/tr>\n<tr style=\"color: blue;\">\n<td>\n<p>Save Emails<\/p>\n<\/td>\n<td>\n<p>Saving e-mails in database. E-mails have saved in MSDB database. There are two tables: [dbo]. [sysmail_mailiems] and [dbo]. [sysmail_attachments]. In SQLCLR there are two tables too, located in schema EMAIL. In SQLCLR, solution there is option to save an e-mail to the database.<\/p>\n<\/td>\n<td>\n<p>False<\/p>\n<\/td>\n<\/tr>\n<tr style=\"color: blue;\">\n<td>\n<p>Send Mail Asynchronously<\/p>\n<\/td>\n<td>\n<p>This is default, but you can choose to send syncronously<\/p>\n<\/td>\n<td>\n<p>True<\/p>\n<\/td>\n<\/tr>\n<tr style=\"color: blue;\">\n<td>\n<p>No Piping<\/p>\n<\/td>\n<td>\n<p>There is no messages in message box, after success sending<\/p>\n<\/td>\n<td>\n<p>True<\/p>\n<\/td>\n<\/tr>\n<tr style=\"color: blue;\">\n<td>\n<p>Save Attachments<\/p>\n<\/td>\n<td>\n<p>Do we save attachments<\/p>\n<\/td>\n<td>\n<p>True<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Finally, there are two configuration objects: The database configuration objects provide a way for you to configure the settings that Database mail should use when sending an email from your database application or SQL Server Agent. These are the Database Mail accounts and Database Mail profiles.<\/p>\n<p>The Database Mail profile can contain one or more Database Mail accounts. When you fail to send an e-mail using the first account on the list, the process of sending e-mails will sleep for a while (the duration of this wait is specified by configuration property <strong>Account Retry Delay<\/strong>) and then the process will start sending an e-mail using another account.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"359\" height=\"272\" class=\"wp-image-71793\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-88.png\" \/><\/p>\n<p>Of course it is not easy to reproduce some of the features such as scalability and reliability using SQLCLR.<\/p>\n<p><strong>Read also: <\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/programming-sql-server-sql-server-management-objects-framework\/\">SMO framework for SQL Server automation<\/a><\/p>\n<h2>Introduction to the stored procedure [EMAIL].[CLRSendMail]<\/h2>\n<p>In order to implement any CLR project such as sending e-mails on SQL Server, we should create a SQLCLR project. One of the properties of the project is the permission set (level). In our case it should be \u2018UNSAFE\u2019 (<em>Microsoft designers divide SQLCLR projects according the permission set to \u2018SAFE\u2019,\u2019EXTERNAL_ACCESS\u2019 &amp; \u2018UNSAFE\u2019<\/em>).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"540\" height=\"437\" class=\"wp-image-71794\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-89.png\" \/><\/p>\n<p>SQL Server uses \u2018GRANT\u2019 \u2018DENY\u2019 \u2018REVOKE\u2019 commands in order to provide access to specific database objects. .NET framework on the other hand uses Code Access Security that controls the interaction between modules of higher\/lower privilege. It is very difficult to combine these two security methods, so Microsoft designers decide to simplify the whole thing by dividing it into three parts.<\/p>\n<p>In \u2018SAFE\u2019 mode, the assembly\u2019s methods can do pretty much the same as in T-SQL stored procedure. Caller\u2019s credentials are used to execute the assembly method.<\/p>\n<p>In \u2018EXTERN_ACCESS\u2019 mode the assembly method can perform file &amp; network input &amp; output. External code runs with the SQL Server service account\u2019s privileges, so impersonation should be used to ensure that the caller\u2019s privileges are used when controlling access to external resources specified by user input.<\/p>\n<p>In \u2018UNSAFE\u2019 mode, permission extends the external set of permissions and allows the assembly to call unmanaged (uncontrolled) code. This might suggest that everything is possible in \u2018UNSAFE\u2019 mode. Of course, it is not. As I explained in earlier <a href=\"http:\/\/www.sqlservercentral.com\/articles\/SQLCLR\/156474\/\">Practical usage of SQLCLR: Building QueryToHtml Function<\/a> calling compiler is not possible, although it might be very cool feature in some cases<\/p>\n<p>I wish that there was a wizard in Visual Studio that could determine the correct permission set, because it is a daunting task for anyone inexperienced in SQLCLR to work out the correct permission set. If you make the wrong assumption, you will probably receive an error during the execution of your SQLCLR procedure. Strangely, that solution will compile successfully!<\/p>\n<p>This error will look something like this. A Massage 6522 should suggest to you that you have a problem with security and that you will have to decrease the security level to \u2018EXTERNAL ACCESS\u2019 or \u2018UNSAFE\u2019. I add another strange notice about message 6522 In <a href=\"#post-71790-_Appendix_D_6522\">Appendix D<\/a><\/p>\n<pre>Msg 6522, Level 16, State 1, Procedure CLRSendMail, Line 0\nA .NET Framework error occurred during execution of user-defined routine or aggregate \"CLRSendMail\": \nSystem.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.\n\nThe protected resources (only available with full trust) were: All\nThe demanded resources were: ExternalThreading\n\nSystem.Security.HostProtectionException: \n   at StoredProcedures.CLRSendMail(SqlString profileName, SqlString mailTo, SqlString mailSubject, SqlString mailBody, SqlString fromAddress, SqlString mailCc, SqlString blindCopyRec, SqlString replyAddress, SqlString group, SqlString fileAttachments, SqlBoolean requestReadReceipt, SqlInt16 deliveryNotification, SqlInt16 sensitivity, SqlInt16 mailPriorty, SqlBoolean bodyHtml, SqlString configName)\n<\/pre>\n<p>We will create a SQLCLR stored procedure for this project by harnessing the \u2018<em>SmtpClient\u2019<\/em> class, locating in \u2018System.Net.Mail\u2019 namespace.<\/p>\n<p>The end-product of the SQLCLR project will be an assembly that contains information about our SQLCLR stored procedure.<\/p>\n<p>In order to use this stored procedure, we first have to deploy the appropriate assembly. I named the assembly as showed in the screenshot below as \u2018<strong>SimpleTalk.SQLCLR.SendMail\u2019<\/strong>. The stored procedure name is \u2018<strong>EMAIL.CLRSendMail<\/strong>\u2019.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"300\" height=\"281\" class=\"wp-image-71795\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-90.png\" \/><\/p>\n<p>In the <a href=\"#post-71790-_Support_Material_&amp;\">Deploying<\/a> section, I will explain how to deploy the assembly. Now, let see what the parameters of the stored procedure are and how to call it.<\/p>\n<p>The simplest call is like this:<\/p>\n<pre>EXEC [EMAIL].[CLRSendMail]\n     @profileName = N'SimpleTalk',\n     @mailTo = N'&lt;semicolon-delimited list of e-mail addresses to send the message to&gt;',\n     @mailBody = N'test';\n<\/pre>\n<p>\u2026will produce an e-mail like this.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"315\" height=\"153\" class=\"wp-image-71798\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-91.png\" \/><\/p>\n<p>You\u2019ll have noticed that we have to specify only three parameters in order to send an e-mail using SQLCLR. Although <strong>mailBody<\/strong> is also optional in <strong>sp_send_dbmail<\/strong>, it could not be optional in SQLCLR, because SQLCLR does not allow default values for <strong>nvarchar (max)<\/strong> types.<\/p>\n<p>Help is available in any point by executing<\/p>\n<pre>SELECT\n\t*\nFROM EMAIL.CustomSendMailHelp('EMAIL.ClrSendMail');\n<\/pre>\n<p>The result of help command is a list of parameters similar like table below.<\/p>\n<table>\n<thead>\n<tr>\n<td>\n<p><strong>Parameter Name<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Parameter Description<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Parameter Type<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Default Value<\/strong><\/p>\n<\/td>\n<td>\n<p><strong>Can be null<\/strong><\/p>\n<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\n<p>@profileName<\/p>\n<\/td>\n<td>\n<p>Is the name of the profile to send the message from. Can be built in or user defined (readable from database). @profileName must be specified.<\/p>\n<\/td>\n<td>\n<p>Nvarchar(20)<\/p>\n<\/td>\n<td>\n<p>No default value<\/p>\n<\/td>\n<td>\n<p>No<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@mailTo<\/p>\n<\/td>\n<td>\n<p>Is a semicolon-delimited list of e-mail addresses to send the message to.<\/p>\n<\/td>\n<td>\n<p>Nvarchar(max)<\/p>\n<\/td>\n<td>\n<p>No default value<\/p>\n<\/td>\n<td>\n<p>No<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@mailSubject<\/p>\n<\/td>\n<td>\n<p>Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is &#8216;SQLCLR Server Message&#8217;<\/p>\n<\/td>\n<td>\n<p>Nvarchar(255)<\/p>\n<\/td>\n<td>No SQLCLR Server Message&#8217;<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@mailBody<\/p>\n<\/td>\n<td>\n<p>Is the body of the e-mail message. The message body is of type nvarchar(max),<\/p>\n<\/td>\n<td>\n<p>Nvarchar(max)<\/p>\n<\/td>\n<td>No default value<\/td>\n<td>\n<p>No. This is limitation of SQLCLR nvarchar(max)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@fromAddress<\/p>\n<\/td>\n<td>\n<p>Is the value of the &#8216;from address&#8217; of the email message. This is an optional parameter used to override the settings in the mail profile. SMTP security settings determine if these overrides are accepted. If no parameter is specified, the default is NULL.<\/p>\n<\/td>\n<td>\n<p>Nvarchar(500)<\/p>\n<\/td>\n<td>NULL<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@displayName<\/p>\n<\/td>\n<td>\n<p>Display name associate with from email address<\/p>\n<\/td>\n<td>\n<p>Nvarchar(400)<\/p>\n<\/td>\n<td>NULL<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@mailCC<\/p>\n<\/td>\n<td>\n<p>Is a semicolon-delimited list of e-mail addresses to carbon copy the message to.<\/p>\n<\/td>\n<td>\n<p>Nvarchar(4000)<\/p>\n<\/td>\n<td>NULL<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@blindCopyRec<\/p>\n<\/td>\n<td>\n<p>Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to.<\/p>\n<\/td>\n<td>\n<p>Nvarchar(4000)<\/p>\n<\/td>\n<td>NULL<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@replyAddress<\/p>\n<\/td>\n<td>\n<p>Is the value of the &#8216;reply to address&#8217; of the email message. It accepts only one email address as a valid value. This is an optional parameter used to override the settings in the mail profile.<\/p>\n<\/td>\n<td>\n<p>Nvarchar(400)<\/p>\n<\/td>\n<td>NULL<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@fileAttachments<\/p>\n<\/td>\n<td>\n<p>Is a semicolon-delimited list of file names to attach to the e-mail message. Files in the list must be specified as absolute paths. The attachments list is of type nvarchar(max). By default, SQLCLR Mail limits file attachments to 1 MB per file<\/p>\n<\/td>\n<td>\n<p>Nvarchar(max)<\/p>\n<\/td>\n<td>NULL<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@requestReadReceipt<\/p>\n<\/td>\n<td>\n<p>Request read receipt. Not implemented in sp_send_dbmail<\/p>\n<\/td>\n<td>\n<p>Bit<\/p>\n<\/td>\n<td>0<\/td>\n<td>\n<p>YES<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@deliveryNotification<\/p>\n<\/td>\n<td>\n<p>Delivery notification.Not implemented in sp_send_dbmail.<\/p>\n<\/td>\n<td>\n<p>\u015emallint<\/p>\n<\/td>\n<td>1<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@sensitivity<\/p>\n<\/td>\n<td>\n<p>Is the sensitivity of the message. The parameter may contain one of the following values: Normal,Personal,Private &amp; Confidential<\/p>\n<\/td>\n<td>\n<p>Smallint<\/p>\n<\/td>\n<td>1<\/td>\n<td>\n<p>YES<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@mailPriorty<\/p>\n<\/td>\n<td>\n<p>Is the importance of the message.The parameter may contain one of the following values: Low,Normal &amp; High<\/p>\n<\/td>\n<td>\n<p>Smallint<\/p>\n<\/td>\n<td>1<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@bodyHtml<\/p>\n<\/td>\n<td>\n<p>Is the format of the message bodyCan be HTML or TEXT<\/p>\n<\/td>\n<td>\n<p>Bit<\/p>\n<\/td>\n<td>1<\/td>\n<td>\n<p>0<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>@configName<\/p>\n<\/td>\n<td>\n<p>Is the name of the configuration. Configuration is responsible for protecting system of large file attachements, prohibiting file extension, saving e-mail in database etc.<\/p>\n<\/td>\n<td>\n<p>Nvarchar(20)<\/p>\n<\/td>\n<td>NULL<\/td>\n<td>\n<p>Yes<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Here are some examples how to call the stored procedure:<\/p>\n<p>If we need to send attachments in our mail message, we specify<\/p>\n<pre>EXEC [EMAIL].[CLRSendMail]\t@profileName =N'SimpleTalk'\n  \t,@mailTo = N'&lt;your recepiens&gt;\n  \t,@mailSubject = N'test'\n  \t,@fileAttachments = 'D:\\a1\\AtlantaMDF_031207112.ppt;D:\\OneDrive\\TKD\\608-Horvat.pdf'\n  \t,@mailBody = N'test'\n  GO<\/pre>\n<p>When working with attachments, it is important to know that there are some default extensions such as &#8216;exe&#8217;,&#8217;js&#8217;,&#8217;dll&#8217; that are prohibited for security reasons.<\/p>\n<p>The file attachment should exist on file system. If the file does not exist, a warning item is added to the \u2018Monitor Log&#8217; table. The E-mail message will be still sent.<\/p>\n<p>The file attachment should be smaller than the size specified by the configuration object.<\/p>\n<p>If you need to specify the display name, you just override the default by specifying it:<\/p>\n<pre>EXEC [EMAIL].[CLRSendMail]\t@profileName = N'SimpleTalk'\n  \t,@mailTo = N'&lt;your recepiens&gt;\u2019\n  \t,@mailSubject = N'Test with display name'\n  <strong>\t,@displayName = 'SQLCLR display name'<\/strong>\n  \t,@mailBody = N'test';<\/pre>\n<p>Which will produce an email like this<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"352\" height=\"190\" class=\"wp-image-71799\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-92.png\" \/><\/p>\n<p>This project uses a profile. In the next section I will explain what a profile is, and what properties it has. One property of the profile is \u2018<strong>DefaultDisplayName<\/strong>\u2019, so parameter<strong> @displayName<\/strong>, could be omitted too.<\/p>\n<p>If you need to specify a reply address that is different from address you sent an e-mail to, and then specify parameter @replyAddress.<\/p>\n<pre><strong>\t,@replyAddress = '&lt;your reply address&gt;'<\/strong><\/pre>\n<p>If you like to request read recipient, you can call procedure like this:<\/p>\n<pre><strong>\t,@requestReadReceipt = 1<\/strong><\/pre>\n<p>Similar, if you like to setup delivery notification, you can choose one of the value. 0-means none, 1-On Success, 2-On Failure.<\/p>\n<pre>\t,@deliveryNotification = 1 --On Success<\/pre>\n<p>In order to setup sensitivity, you can choose one of the value for parameter sensitivity. 0-means personal, 1- means private, 2-means Company confidential.<\/p>\n<pre><strong>\t,@sensitivity=2<\/strong><\/pre>\n<p>And finally, if you like to setup e-mail priority, you can call the stored procedure like this. You can use 0-for normal priority, 1-for low priority and 2-for high priority.<\/p>\n<pre>\t,@mailPriorty = 2<\/pre>\n<h2>Omnia Mea Mecum Porto<\/h2>\n<p>What information we need to in order to implement SQLCRL support for sending e-mails?<\/p>\n<p>In order to setup accounts and configuration for e-mailing and save e-mail messages in database, we have to create a set of new tables.<\/p>\n<p>For the sake of the readability of these tables, we will set it in a special schema called <strong>EMAIL<\/strong>.<\/p>\n<p>As you see on the table below, I added five (5) tables.<\/p>\n<table>\n<thead>\n<tr>\n<td>\n<p>Table name<\/p>\n<\/td>\n<td>\n<p>Table description<\/p>\n<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\n<p>EMAIL.Configurations<\/p>\n<\/td>\n<td>\n<p>We will save configuration options such as maximum attachment size or prohibited extension. This table will take on the equivalent role of <strong>msdb.dbo.sysmail_configuration<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>EMAIL.Profiles<\/p>\n<\/td>\n<td>\n<p>This is where we save information such as the login name, password, host name, port number etc.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>EMAIL.MailItems<\/p>\n<\/td>\n<td>\n<p>This is similar to <strong>msdb.dbo.sysmail_mailitems<\/strong>. If it is specified by configuration options, then e-mails will be saved in this table..<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>EMAIL.Attachments<\/p>\n<\/td>\n<td>\n<p>Similar to <strong>msdb.dbo.sysmail_attachments<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>EMAIL.MonitorLog<\/p>\n<\/td>\n<td>\n<p>Basic logging table<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>You can see relations between tables on the screenshot bellow.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"758\" height=\"808\" class=\"wp-image-71800\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-93.png\" \/><\/p>\n<p>The T-SQL script for creating the database schema and tables is included in the supporting materials. See the Deployment section for details.<\/p>\n<p>As I wrote at the beginning, we will use the <strong>SmtpClient<\/strong> class in order to send an e-mail message. In addition to this class, there are two more classes implemented in SQLCLR solution. One is called <strong>SysConfig<\/strong> and the other<strong> SysProfile<\/strong>.<\/p>\n<p><strong>SysConfig<\/strong> do pretty much the same as <strong>sysmail_configure_sp<\/strong>. It is a simple class with bunch of properties. It has the implementation like this:<\/p>\n<pre>    public class SysConfig\n      {\n          \/\/Implemented\n          \/\/\/ &lt;summary&gt;\n          \/\/\/ Maximum file size in bytes. Default 1MB\n          \/\/\/ &lt;\/summary&gt;\n          public Int32 maxFileSize\n          {\n              get; set;\n          } = 1000000;\n          \/\/Implemented\n          \/\/\/ &lt;summary&gt;\n          \/\/\/ File extension that are not allowed\n          \/\/\/ &lt;\/summary&gt;\n          public string prohibitedExtension\n          {\n              get; set;\n          } = \"exe,dll,vbs,js\";\n          public eLoggingLevel loggingLevel\n          {\n              get; set;\n          } = eLoggingLevel.Minimal;\n          \/\/\/ &lt;summary&gt;\n          \/\/\/ Do we save e-mails?\n          \/\/\/ &lt;\/summary&gt;\n          public bool saveEmails\n          {\n              get; set;\n          } = true;\n          \/\/\/ &lt;summary&gt;\n          \/\/\/ What is configuration name\n          \/\/\/ &lt;\/summary&gt;\n          public string name\n          {\n              get; set;\n          } = null;\n          \/\/\/ &lt;summary&gt;\n          \/\/\/ How we send an e-mail?\n          \/\/\/ &lt;\/summary&gt;\n          public bool sendAsync\n          {\n              get; set;\n          } = true;\n          \/\/\/ &lt;summary&gt;\n          \/\/\/ Do we need piping\n          \/\/\/ &lt;\/summary&gt;\n          public bool noPiping\n          {\n              get; set;\n          } = true;\n  \/\/\/ &lt;summary&gt;\n          \/\/\/ Do we save attachments\n          \/\/\/ &lt;\/summary&gt;\n          public bool saveAttachments\n          {\n              get; set;\n          } = true;\n      }<\/pre>\n<p>Using the object type <strong>SysConfig<\/strong> is an option. If we do not specify the configuration parameter then the default one is used. The default behavior is pretty much the same as showed on the code snippet above. The configuration object could be instantiated by specifying the configuration parameter. In that case, the configuration object will be instantiated by reading the record from the EMAIL.Configurations table. Here is the structure of this table. <img loading=\"lazy\" decoding=\"async\" width=\"905\" height=\"611\" class=\"wp-image-71801\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-94.png\" \/><\/p>\n<p>During the assembly deployment, the T-SQL setup script will detect if there is any record in this table. If not, the T-SQL script will insert default one, like showed here<\/p>\n<pre>SELECT * FROM EMAIL.CONFIGURATIONS;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"756\" height=\"184\" class=\"wp-image-71802\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-95.png\" \/><\/p>\n<p><strong>SysProfile<\/strong>has to be instantiated. There are two options to doing this. First in code, there are two ( in this solution, but you have as many as you wish) built-in profiles. In addition to these profiles, you can instantiate <strong>SysProfile<\/strong> by reading information from the \u2018<strong>Email.Profiles\u2019<\/strong> table.<\/p>\n<p>This table has structure similar like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"860\" height=\"701\" class=\"wp-image-71803\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-96.png\" \/><\/p>\n<p>My idea was to have built-in profiles. These profiles should be considered as a fall-back. When the stored procedure is invoked, its first action is to resolve the profile. If the profile name matches one of the built-in profiles, then that built-in profile is used. Otherwise, the stored procedure will determine the profiles by querying the database. Determining the profile in the stored procedure is accomplished as in this snippet of code.<\/p>\n<pre>    private static SysProfile GetClient(string name, ref string error)\n      {\n          SysProfile p = null;\n          EncryptSupport.Simple3Des wrapper = new EncryptSupport.Simple3Des(SECRET_WORD);\n          \/\/Built in profile called ssl\n          if (name == \"ssl\")\n          {\n              p = new SysProfile();\n              p.builInName = name;\n              p.client = new SmtpClient();\n              p.client.UseDefaultCredentials = false;\n              p.client.Port = 587;\n              p.client.Host = \"smtp.gmail.com\";\n              p.client.EnableSsl= true;\n              p.client.Credentials = new NetworkCredential(wrapper.DecryptData(\"DE5ZET4hY95fZ7JadaxKqchFuvrR3p12vlY=\"), wrapper.DecryptData(\"ovkrtZ\/=\"));\n          }\n          \/\/Built in profile called simple\n          else if (name == \"simple\")\n          {\n              p = new SysProfile();\n              p.builInName = name;\n              p.client = new SmtpClient();\n              p.client.UseDefaultCredentials = false;\n              p.client.Port = 25;\n              p.client.Host = \"mail.iskon.hr\";\n              p.client.EnableSsl = false;\n              p.client.Credentials = new NetworkCredential(wrapper.DecryptData(\"El+==\"), wrapper.DecryptData(\"==\"));\n          }\n          else\n          {\n              \/\/determine profile by querying the database\n              SqlParameter[] listOfParams = new SqlParameter[1];\n              SqlParameter pName = new SqlParameter(\"name\", name);\n              pName.Size = 20;\n              pName.SqlDbType = System.Data.SqlDbType.Char;\n              listOfParams[0] = pName;\n              p = DataAccess.GetProfile(listOfParams, wrapper,ref error);\n          }\n          return p;\n      }<\/pre>\n<p>Result of publishing SQLCLR solution is T-SQL script, which is by default putted in SQLCMD mode. We can add during developing pre-deployment and post-deployment action, also in form of T-SQL scripts. The post-deployment script will detect if there is no record in this table. If it does not exist, then the T-SQL script will add a record. So, part of this post-build script looks similar like this<\/p>\n<pre class=\"\">IF ( SELECT COUNT(*) FROM EMAIL.PROFILES ) = 0 \n  BEGIN\n  INSERT INTO email.profiles (profilename, EnableSsl, DefaultCred, Port, HostName, UserName, Password,DefaultFrom)\n  \t\tSELECT\n  \t\t\t'SimpleTalk'\n  \t\t\t,1\n  \t\t\t,0\n  \t\t\t,587\n  \t\t\t,'smtp.gmail.com'\n  \t\t\t,N'L5Yr\/GLP9vbH82Fah78mSeUHInP\/ws8XN0K0ky3W4PvpfSOgo='\n  \t\t\t,N'KYEVsZBY8c08fKO4vLihIrcwwOxInvas1I='\n  \t\t\t,'your_address@company.com';\n  ---Use these values if you decide to apply T-SQL encryption by using ENCRYPTBYCERT function \n  \t\t\t--,ENCRYPTBYCERT(CERT_ID('TestCert'), N'e-mailAddress')\n  \t\t\t--,ENCRYPTBYCERT(CERT_ID('TestCert'), N'secretPassword')\n  \t\t\t--,'your_address@company.com';\n  \t\n  END<\/pre>\n<p>You can, of course, modify this script in order to test this solution, because at the end I will replace sensitive information such as the \u2018UserName\u2019 and \u2018Password\u2019, with your concrete values. . See more on <a href=\"#post-71790-_What_about_Sensitive\">this section<\/a> ( Sensitive data).<\/p>\n<p>During the execution of the stored procedure, the configuration and its profile is known. If we decide to save the details of the e-mail into the database by choosing the appropriate property in the configuration object, then we can see the configuration properties for this particular e-mail and what profile was used.<\/p>\n<p>The main table for saving e-mails is <strong>Email.MailItems <\/strong>with structure similar to this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"839\" height=\"798\" class=\"wp-image-71804\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-97.png\" \/><\/p>\n<p>In addition to this table, there is a table for saving attachments. It is named Email.MailAttachments. There is a property in the configuration object that determines whether we save e-mail attachments as well. The default value for this property is \u2018true\u2019. The structure of this table looks similar to the screenshot bellow.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"814\" height=\"599\" class=\"wp-image-71805\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-98.png\" \/><\/p>\n<p>The process of saving e-mail attachments is resource-consuming. In my opinion, the best way of inserting e-mail attachments in the database, is to use table value parameters (TVPs). This code snippet will save e-mail attachments. Therefore, we pass \u2018DataTable\u2019 as a parameter to the command object. This parameter has <strong>SqlDbType.Structured<\/strong> type and it has the value of \u2018EMAIL.TVP_Emails\u2019.<\/p>\n<pre>if (saveAttachments &amp;&amp; validAttachments.Trim().Equals(string.Empty) == false)\n     {\n     comm.CommandText = @\"INSERT INTO [EMail].[MailAttachments] (mailitem_id,fileName,fileSize,attachment)\n                         SELECT @mailItem, fileName, fileSize, attachment FROM @tvpEmails\";\n     comm.Parameters.AddWithValue(\"@mailItem\", id);\n     comm.Parameters[comm.Parameters.Count - 1].SqlDbType = System.Data.SqlDbType.BigInt;\n      DataTable dt = CreateTable();\n      DataRow newRow = null;\n      foreach (Attachment eml in mm.Attachments)\n      {\n        newRow = dt.NewRow();\n        newRow[\"FileName\"] = eml.Name;\n        newRow[\"FileSize\"] = eml.ContentStream.Length;\n        byte[] allBytes = new byte[eml.ContentStream.Length];\n        int bytesRead = eml.ContentStream.Read(allBytes, 0, (int)eml.ContentStream.Length);\n        newRow[\"Attachment\"] = allBytes;\n        eml.ContentStream.Position = 0;\n        dt.Rows.Add(newRow);\n      }\n      comm.Parameters.AddWithValue(\"@tvpEmails\", dt);\n      comm.Parameters[comm.Parameters.Count - 1].TypeName = \"EMAIL.TVP_Emails\";\n      comm.Parameters[comm.Parameters.Count - 1].SqlDbType = SqlDbType.Structured;\n      comm.ExecuteNonQuery();\n      dt = null;\n      newRow = null;\n      }\n      comm.Transaction.Commit();\n      if (conn.State == ConnectionState.Open)\n          conn.Close();<\/pre>\n<p>During the deployment of the <strong>SimpleTalk.SQLCLR.SendMail <\/strong>solution, a T-SQL script will detect if such a TVP exists. If not, then the T-SQL script will create it.<\/p>\n<pre>IF NOT EXISTS (SELECT\n  \t\t*\n  \tFROM sys.types st\n  \tJOIN sys.schemas ss\n  \t\tON st.schema_id = ss.schema_id\n  \tWHERE st.name = N'TVP_Emails'\n  \tAND ss.name = N'EMail')\n  CREATE TYPE [EMail].[TVP_Emails] AS TABLE ([FileName] [NVARCHAR](260) NOT NULL\n  , [FileSize] [BIGINT] NOT NULL\n  , [Attachment] [VARBINARY](MAX) NOT NULL);<\/pre>\n<p>Finally, there is a table for logging called <strong>Email.MonitorLog<\/strong>. It has the structure similar like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"853\" height=\"672\" class=\"wp-image-71806\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-99.png\" \/><\/p>\n<p>Correspondent class is \u2018<strong>LogEntry\u2019<\/strong>, with only one static method called \u2018<strong>LogItem\u2019<\/strong>.<\/p>\n<p>Typically, the monitor log looks similar like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"755\" height=\"171\" class=\"wp-image-71807\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-100.png\" \/><\/p>\n<p>It captures information about the file attachment, profiles etc. Records in this table can be queried by record type. The record type can be \u2018Information\u2019, \u2018Warning\u2019 and \u2018Fatal\u2019. It is very helpful during logging that you know the basic information about the e-mail message. This include information about form address, subject and send to address. In order to make it more readable and easier to reuse, I make many extensions of the class \u2018<strong>MailMessage<\/strong>\u2019. All these extension are located in \u2018Extensions\u2019 folder inside the solution. The extension to describe the e-mail is named \u2018<strong>HeaderInformation\u2019<\/strong>.<\/p>\n<pre>public static string HeaderInformation(this MailMessage mm)\n  {\n    return @\"From     : \" + \"'\" + mm.From.Address.Trim() + \"'\" + \"\\r\\n\" +\n            \"To       : \" + \"'\" + mm.To[0].Address.ToString().Trim() + \"'\" + \"\\r\\n\" +\n            \"Subject  : \" + \"'\" + mm.Subject.ToString().Trim() + \"'\";\n  }<\/pre>\n<p>The benefit of this type of coding is that Visual Studio Intellisense knows your extension and so coding is easier and more readable. Now is the time to say something about sensitive data. E.g. such as username and password.<\/p>\n<h2><a id=\"post-71790-_What_about_Sensitive\"><\/a>What about Sensitive Data?<\/h2>\n<p>When you create an account on public servers (see <a href=\"#post-71790-_Appendix_C:_Public\">Appendix C<\/a> for choosing public SMPT servers), you usually have to provide information such as your login name, password, and wanted e-mail address, host name, port number etc. Some of this information is very sensitive. It means that you have to save them in an encrypted form in order to not compromise confidential data. Usually, this includes an encrypted login name and password. Encryption could be accomplished using T-SQL and\/or .NET.<\/p>\n<p>T-SQL encryption can be accomplished by creating master key and certificate, as in this script.<\/p>\n<pre>--Create master key. Master key has id 101\nIF NOT EXISTS (SELECT *\n  \tFROM sys.symmetric_keys\n  \tWHERE symmetric_key_id = 101)\n  BEGIN\n  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SimpleTalk'\n  END\n  GO\n  --Create our certificate if not exists\n  IF NOT EXISTS (SELECT\n  \t\t*\n  \tFROM sys.certificates\n  \tWHERE name = 'TestCert')\n  BEGIN\n  CREATE CERTIFICATE TestCert WITH SUBJECT = 'Test certifikate', EXPIRY_DATE = '20251231'\n  END\n  GO\n  --Encrypt string by using certificate\n  SELECT\n  \tENCRYPTBYCERT\n  \t(\n  \tCERT_ID('TestCert'), N'UserSensitiveData');\n  GO\n  --Decrypt by using certificate\n  SELECT\n  \tCAST(DECRYPTBYCERT(CERT_ID('TestCert'), 0x4189E1E22596180DF3407C831484ED4E6DDD34F97E6798E741542E7C0967AFF9EF93BA1C8D157AA37003670BC33B4AED6BB82D2997C0AF0712774AD902E7CCD6C08F5641DB713A2681FC1963749A315FC8903F9769B34F10316135F77AB546123E81B4A5BB75C10FEADCE22D4CE56C9BF7C0920B0F0D906C389B153636C71492) AS NVARCHAR(100));\n  --Will return string \u2018UserSensitiveData\u2019<\/pre>\n<p>Therefore, when inserting record in table EMAIL.PROFILES, we can use script like this<\/p>\n<pre>IF ( SELECT\n  \t\tCOUNT(*)\n  \tFROM EMAIL.PROFILES)\n  = 0\n  BEGIN\n  INSERT INTO EMAIL.PROFILES (ProfileName, EnableSsl, DefaultCred, Port, HostName, UserName, Password, DefaultFrom)\n  \t\tSELECT\n  \t\t\t'SimpleTalk'--Profile name\n  \t\t\t,1--EnableSsl\n  \t\t\t,0--Use default credential\n  \t\t\t,587--Port number\n  \t\t\t,'smtp.gmail.com'--Host name\n  \t\t\t,ENCRYPTBYCERT(CERT_ID('TestCert'), N'&lt;your login name&gt;')\n  \t\t\t,ENCRYPTBYCERT(CERT_ID('TestCert'), N'&lt;your password&gt;\u2019)\n  \t\t\t,'&lt;default from address\u2019;\n  END<\/pre>\n<p>In our SQLCLR solution, it is possible to make use of an instance of the X509Certificate2 class, located in the <strong>System.Security.Cryptography.X509Certificates<\/strong> namespace, To do this, we import the private key into the solution and do decryption without specifying any secret key or password. However, I found this solution difficult to follow. In addition, because this is SQLCLR solution, I decide to implement everything using .NET.<\/p>\n<p>I make a simple utility, which is available on this <a href=\"https:\/\/1drv.ms\/u\/s!Arn-Vk2Le7QvwGnw-bUpGqHj_Si3\">address<\/a>. It is WPF program, which has four steps. First, you can choose an action, as in the picture below. You can choose \u2018<em>encrypt\u2019<\/em> or \u2018<em>decrypt\u2019<\/em>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"511\" height=\"343\" class=\"wp-image-71808\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-101.png\" \/><\/p>\n<p>Then you can enter or paste the string to encrypt or decrypt as in the screenshot below, when I entered the string \u201cMy user information\u201d.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"511\" height=\"343\" class=\"wp-image-71809\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-102.png\" \/><\/p>\n<p>Then you choose \u2018<em>secret key\u2019<\/em>, as below, at which point I entered \u201cSimple Talk\u201d.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"511\" height=\"343\" class=\"wp-image-71810\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-103.png\" \/><\/p>\n<p>At the final step, you can view the result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"511\" height=\"343\" class=\"wp-image-71811\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-104.png\" \/><\/p>\n<p>This is a simple tool, which uses <strong>TripleDESCryptoServiceProvider<\/strong> located in the <strong>System.Security.Cryptography<\/strong> namespace, in order to encrypt\/decrypt the string. If, at the last step, receive the result of \u2018<em>bad data\u2019<\/em>, it is probably because your secret key is incorrect.<\/p>\n<p>As a result of these operations, we can create T-SQL code to fill our EMAIL.PROFILES table. It means replacing ENCRYPTBYCERT with the \u2018<em>Result\u2019<\/em> string we\u2019ve just obtained. In front of the result string, and before the initial single-quote delimiter you have to put N to specify that it is a Unicode string. This script will be with the other T-SQL scripts in last section.<\/p>\n<p>In SQLCLR, we can specify a hard-coded secret key in the solution, although I provide built-in hardcoded user profiles as well. These profiles could be used as some kind of fallback.<\/p>\n<h2>Testing &amp; Monitoring<\/h2>\n<p>It is not easy to test this solution. Firstly, you have to create a couple of new profiles and dedicated e-mail addresses for testing purpose. The reason is quite simple; there is a good chance that some public server will detect you as a spammer. When you are flagged as a spammer, then further sending is blocked.<\/p>\n<p>After that, I need to choose some content for the body of the e-mail. It cannot be just a simple \u2018Test\u2019 word.<\/p>\n<p>I would like to include some information from a blocked process report as a body, which usually contains a bunch of text as well as few attachments.<\/p>\n<p>The declaration part of the T-SQL script to test should look something like this<\/p>\n<pre>DECLARE @myFileAttachment as nvarchar(max) = 'your attachments hear'\n  DECLARE @myBody as nvarchar(max) = N'a lot of text'\n  DECLARE @counter as int = 0\n  DECLARE @mysubject as nvarchar(255) = 'Test'\n  --mail to. You should replace values with concrete e-mail addresses\n  DECLARE @myMainTo  as nvarchar(max) = ''\n  DECLARE @myMainTo_1  as nvarchar(max) = '&lt;list_one&gt;'\n  DECLARE @myMainTo_2  as nvarchar(max) = '&lt;list_two&gt;'\n  DECLARE @myMainTo_3  as nvarchar(max) = '&lt;list_three&gt;'\n  DECLARE @myMainTo_4  as nvarchar(max) = '&lt;list_fourth&gt;'\n  DECLARE @myMainTo_5  as nvarchar(max) = '&lt;list_five&gt;'\n  --end mail to \n  --Profiles. First you have to insert all these profiles in EMAIL.Profiles table\n  DECLARE @myProfile as nvarchar(20) = ''\n  DECLARE @myProfile1 as nvarchar(20) = 'Test1'\n  DECLARE @myProfile2 as nvarchar(20) = 'Test2'\n  DECLARE @myProfile3 as nvarchar(20) = 'Test3'\n  DECLARE @myProfile4 as nvarchar(20) = 'Test4'\n  DECLARE @myProfile5 as nvarchar(20) = 'Test5'\n  --End profiles\n  --How many times we will executing the stored procedure\n  DECLARE @multiple as int = 1\n  DECLARE @firstStop as int = 5 * @multiple\n  DECLARE @secondStop as int = 10 * @multiple\n  DECLARE @thirdStop as int = 15 * @multiple \n  DECLARE @fourtstop as int = 20 * @multiple\n  DECLARE @fifthStop as int = 25 * @multiple<\/pre>\n<p>The script body should look like this<\/p>\n<pre>WHILE (1=1)\n  BEGIN\n  \tIF @counter &lt; @firstStop\n  \tBEGIN\n  \t\tSET @myMainTo = @myMainTo_1\n  \t\tSET @myProfile = @myProfile1\n  \tEND\n  \tELSE\n  \t\tIF @counter &gt;= @firstStop and @counter &lt; @secondStop\n  \t\tbegin\n  \t\t\tSET @myMainTo = @myMainTo_2\n  \t\t\tSET @myProfile = @myProfile2\n  \t\tend\n  \t\tELSE\n  \t\t\tIF @counter &gt;= @secondStop and @counter &lt; @thirdStop\n  \t\t\tbegin\n  \t\t\t\tSET @myMainTo = @myMainTo_3\n  \t\t\t\tSET @myProfile = @myProfile3\n  \t\t\tend\n  \t\t\telse IF @counter &gt;= @thirdStop and @counter &lt; @fourtStop\n  \t\t\t\tbegin\n  \t\t\t\t\tSET @myMainTo = @myMainTo_4\n  \t\t\t\t\tSET @myProfile = @myProfile4\n  \t\t\t\tend\n  \t\t\t\telse IF @counter &gt;= @fourtStop and @counter &lt;= @fifthStop\n  \t\t\t\t\tbegin\n  \t\t\t\t\t\tSET @myMainTo = @myMainTo_5\n  \t\t\t\t\t\tSET @myProfile = @myProfile5\n  \t\t\t\t\tend\n  \tEXEC [EMAIL].[CLRSendMail] @profileName     = @myProfile\n  \t,                          @mailTo          = @myMainTo\n  \t,                          @mailSubject     = @mysubject\n  \t,                          @fileAttachments = @myFileAttachment\n  \t,                          @mailBody        = @mybody;\n  \tSET @mysubject = CAST(@counter AS NVARCHAR(10))\n  \tSET @counter = @counter + 1\n  \tIF @counter &gt; @fifthStop\n  \t  BREAK;\n  END<\/pre>\n<p>We could start not only one, but also several clients in a batch. In my testing environment the SQLCLR solution is faster than <strong>sp_send_dbmail<\/strong>.<\/p>\n<p>As a monitoring tool, we could use a winforms application; I developed one of these that I described in my last article, <a href=\"http:\/\/www.sqlservercentral.com\/articles\/SQLCLR\/156474\/\">Practical usage of SQLCLR: Building QueryToHtml Function<\/a>.<\/p>\n<p>This tool has a tab labelled \u2018<em>Monitor\u2019<\/em> as showed on this screenshot.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"790\" height=\"576\" class=\"wp-image-71812\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-105.png\" \/><\/p>\n<p>Binding will only occur on the <em>\u2018Monitor\u2019 tab<\/em> only if you have first specified a credential on the first tab that has \u2018<em>view server state\u2019<\/em> permission. In that case, you will see a screen similar to the one below. If you have no appropriate permission, you see only blanks. The Information on the tab <strong><em>Monitor<\/em><\/strong> is the combination of three system views. Surrounded with red is the information from <strong>sys.assemblies<\/strong>. This returns a row for each assembly that is created in the current database, the database you choose on the first tab. Information from<strong> sys.dm_clr_loaded_assemblies <\/strong>is surrounded with a green line. This view returns a row for each managed user assembly that is loaded into the server address space. The area of the screen surrounded with blue is information from <strong>sys.dm_clr_appdomains<\/strong><em>.<\/em> This returns a row for each application domain in the server. An application domain is a construct in the Microsoft .NET Framework common language runtime (CLR) that is the unit of isolation for an application.<\/p>\n<p>According to the Microsoft documentation, the <strong>sys.dm_clr_loaded_assemblies<\/strong> view has a many-to-one relationship with <strong>sys.dm_clr_appdomains.appdomain_address<\/strong>. In addition, <strong>sys.dm_clr_loaded_assemblies<\/strong> view has a one-to-many relationship with <strong>sys.assemblies<\/strong>. So, it possible that you have a different user principal. We only have one user principal. In that case, we can assume that the relation between views are one to one.<\/p>\n<p>When you create an assembly in your database, you will have a corresponding record in the <strong><em>sys.assemblies <\/em><\/strong>system view. You know information like principal id, assembly id, permission set etc.<\/p>\n<p>When user asks for the first time for some functionality located in your assembly, an application domain is created. As I wrote above, the appdomain is as a lightweight process used to enforce isolation between running .NET code within the same SQLOS process. SQLCLR uses appdomains to isolate execution of .NET code on a per-database and per-assembly owner basis.<\/p>\n<p>This process of loading (creating appdomains) takes time (this is a performance penalty). Therefore, the first execution of your SQLCRL function is always much slower than next execution. Unlike .NET appdomains, our appdomain, once loaded, remains loaded for further reuse. This behavior increases performance, so that the managed database objects contained in the assembly can be called in the future without having to reload the appdomain. The appdomain remains loaded until SQL Server comes under memory pressure. Main conclusion in monitoring should be to take care of memory and to watch periodically three-system views sys.dm_clr_appdomains, sys.dm_clr_loaded_assemblies and sys.assmblies<\/p>\n<h2>Debugging<\/h2>\n<p>In order to debug the SQLCLR project, you have to start Visual Studio as Administrator. There are couple of ways to do that. In my opinion, the easiest way is to right-click the program shortcut \u2018devenv.exe\u2019, and then choose \u2018<em>properties<\/em>\u2019 from the menu. Click on the \u2018<em>Shortcut<\/em>\u2019 tab for a program shortcut, and then click on the \u2018<em>Advanced<\/em>\u2019 button. Check the <em>&#8216;Run as administrator&#8217;<\/em> box, and click on \u2018<em>OK\u2019<\/em>.<\/p>\n<p>Then on Visual Studio\u2019s \u2018View\u2019 menu, choose \u2018SQL Server Object Explorer\u2019.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"311\" height=\"161\" class=\"wp-image-71813\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-106.png\" \/><\/p>\n<p>Expand the \u2018SQL Server\u2019 node. Choose your server. Right click, and check both \u2018<em>Application Debugging<\/em>\u2019 and \u2018<em>Allow SQL\/CLR Debugging\u2019<\/em>, as showed on the picture below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"368\" height=\"85\" class=\"wp-image-71814\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-107.png\" \/><\/p>\n<p>In the newest version of Visual Studio, you can have multiple starting points for debugging. These could be a T-SQL script, which type is \u2018NOT in build\u2019, as showed on the picture bellow.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"524\" height=\"290\" class=\"wp-image-71815\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-108.png\" \/><\/p>\n<p>You can name such a script as you wish. Once you have added the script, you can start writing your T-SQL code to test as shown on the screenshot below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"950\" height=\"295\" class=\"wp-image-71816\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-109.png\" \/><\/p>\n<p>Notice the special green triangle.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"410\" height=\"94\" class=\"wp-image-71817\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-110.png\" \/><\/p>\n<p>Click on black \u2018dropdown\u2019 triangle near to green one to see more options, as in the screenshot below, or press ALT+F5<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"316\" height=\"128\" class=\"wp-image-71818\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-111.png\" \/><\/p>\n<p>Then you have to confirm this in the alert box. One frustrating thing happens when you restart Visual Studio. Maybe I\u2019ve just got it wrong, but I could not find any possibility of how to save checks to \u2018<em>Application Debugging\u2019<\/em> and \u2018SQL\/CLR\u2019 debugging.<\/p>\n<p>It means that you should always check these settings and if you do not see an alert as in the picture below, you probably should not check these two check boxes.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"461\" height=\"293\" class=\"wp-image-71819\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-112.png\" \/><\/p>\n<p>Of course, the project settings should include debugging information. This you can check by choosing project settings, then tab \u2018SQLCLR build\u2019, then scroll down through the end and click on button \u2018Advanced\u2019. Noticed Combo Box labeled with \u2018Debug info\u2019, as showed on the screenshot bellow<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"471\" height=\"330\" class=\"wp-image-71820\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-113.png\" \/><\/p>\n<p>And you are in debugging mode! As I wrote in my previous article on SQLCLR <a href=\"http:\/\/www.sqlservercentral.com\/articles\/SQLCLR\/156474\/\">Practical usage of SQLCLR: Building QueryToHtml Function<\/a>, debugger capabilities are less powerful than in the standard Visual Studio solution. However, even such a debugger as this is indispensable in solving problems.<\/p>\n<p>All that I\u2019ve written in this section refers to debugging the source code of an assembly that is installed on the local SQL Server instance. Remote debugging is out of the scope of this article<\/p>\n<h2><a id=\"post-71790-_Support_Material_&amp;\"><\/a>Support Material &amp; Deploying<\/h2>\n<p>One of the weakness of Microsoft\u2019s SQLCLR solution shows itself when you try to work with schemas in SQL Server. When I started developing this solution, I decide that the resulting stored procedure would be in appropriate database schema, named \u2018EMAIL\u2019, as are the other objects created in this solution.<\/p>\n<p>Microsoft designers give us the means to do this by specifying the default schema on the tab \u2018<em>Project Settings<\/em>\u2019, as shown in this screenshot.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"643\" height=\"390\" class=\"wp-image-71821\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-114.png\" \/><\/p>\n<p>However, if you try to compile the solution after making such changes, you will receive an error like this.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"929\" height=\"72\" class=\"wp-image-71822\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-115.png\" \/><\/p>\n<p>This is normal, because at the time of compilation, we do not specify the connection information. In order to apply a specific schema, we have to import the database information. It means pulling in information from a specific database.<\/p>\n<p>In order to prefix the resulting stored procedure with specific schema named \u2018EMAIL\u2019, I use another possibility provided by the SQLCLR framework in \u2018Visual Studio\u2019.<\/p>\n<p>This means specifying something in the pre-deployment script and then to add something else in the post-deployment script. Both script are combined during the publishing process.<\/p>\n<p>In the pre-deployment script, I include the following statement to create database schema \u2018EMAIL<\/p>\n<pre>IF NOT EXISTS (\n  \tSELECT schema_name\n  \tFROM information_schema.schemata\n  \tWHERE schema_name = 'EMAIL' )\n  BEGIN\n  \tEXEC sp_executesql N'CREATE SCHEMA EMAIL'\n  END<\/pre>\n<p>In post deployment script, I include following script in order to transfer the stored procedure from \u2018DBO\u2019 to the \u2018EMAIL\u2019 schema.<\/p>\n<pre>--Transfer procedure\n  IF EXISTS (SELECT\n  \t\t*\n  \tFROM sysobjects\n  \tWHERE id = OBJECT_ID(N'[EMAIL].[CLRSendMail]')\n  \tAND OBJECTPROPERTY(id, N'IsProcedure') = 1)\n  BEGIN\n  DROP PROCEDURE [EMAIL].[CLRSendMail]\n  END\n  ALTER SCHEMA EMAIL TRANSFER dbo.CLRSendMail;\n  --Transfer function\n  IF EXISTS (SELECT\n  \t\t*\n  \tFROM sysobjects\n  \tWHERE id = OBJECT_ID(N'[EMAIL].[CustomSendMailHelp]')\n  \tAND type = N'FT')\n  BEGIN\n  DROP FUNCTION [EMAIL].[CustomSendMailHelp]\n  END\n  ALTER SCHEMA EMAIL TRANSFER dbo.CustomSendMailHelp<\/pre>\n<p>Result is as showed on the picture bellow.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"277\" height=\"187\" class=\"wp-image-71823\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2017\/07\/word-image-116.png\" \/><\/p>\n<h2>Summary<\/h2>\n<p>In this article, I have explained, bit-by-bit, how to replace or complement SQL Server\u2019s mailing system with a custom SQLCLR solution. I have used this solution for years in many settings and have found no difficulties with it. The solution performs well and could be extended in many ways. It gives you the feeling that you can manage an e-mail sending process far simpler than through Service Broker. This solution works well on all editions of SQL Server since SQL 2008 onwards.<\/p>\n<p><strong>Read Also: <\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/t-sql-window-functions-and-performance\/\">Window functions for performance analysis<\/a><\/p>\n<h2><a id=\"post-71790-_Appendix_A:_Enabling\"><\/a><strong>Appendix A<\/strong>: Enabling CLR on SQL Server<\/h2>\n<pre>-- Enable &amp; Check CLR\n  sp_configure N'clr enabled', 1\n  GO\n  RECONFIGURE\n  GO\n  SELECT\n  \tsc.*\n  FROM sys.configurations AS sc\n  WHERE sc.[name] = N'clr enabled'<\/pre>\n<h2><a id=\"post-71790-_Appendix_B:_T-SQL\"><\/a><strong>Appendix B<\/strong>: T-SQL script to setup e-mail on EXPRESS (or any kind) edition<\/h2>\n<pre>  --Enabling Database Mail\nsp_configure N'show advanced options',1\nRECONFIGURE\t\t\t\t\t\t\nGO\nsp_configure N'Database Mail XPs',1\nRECONFIGURE\nGO\n--Profile definition\nDECLARE @profileName sysname='MyProfile'\nDECLARE @profileDescription nvarchar(256)='MyProfileDescription'\n--Account definition\nDECLARE @accountName as sysname='MyAccount'\nDECLARE @accountDescription as nvarchar(256) = 'MyDescription'\n--Enter e-mail address you choose e.g. simpleTalk@gmail.com\nDECLARE @accountEmail as sysname=''\n--Enter reply address e.g. SimpleTalk@SimpleTalk.com\nDECLARE @accountReplyTo as sysname=''\n--Enter display information. E.g. Test account\nDECLARE @accountDisplayName as sysname=''\nDECLARE @accountMailServer as sysname='smtp.gmail.com'\nDECLARE @accountMailPort as int = 587\n-----------------------------------------------------------Most sensitive date\nDECLARE @accountUserName as sysname=''\nDECLARE @accountPassword as sysname=''\n---------------------------------------------------------------------------------------\nDECLARE @accountUseDefaultCredential as bit = 0\nDECLARE @accountEnableSsl as bit = 1\n--Sequence number\nDECLARE @accountSequenceNumber as int = 1\n--Principal definition\nDECLARE @principalName as sysname = 'dbo'\nDECLARE @princtipalDefault as bit = 1\n-- Create a Database Mail account  \nEXECUTE msdb.dbo.sysmail_add_account_sp\t@account_name = @accountName\n\t\t\t,@description = @accountDescription\n\t\t\t,@email_address = @accountEmail\n\t\t\t,@replyto_address = @accountReplyTo\n\t\t\t,@display_name = @accountDisplayName\n\t\t\t,@mailserver_name = @accountMailServer\n\t\t\t,@port = @accountMailPort\n\t\t\t,@username = @accountUserName\n\t\t\t,@password = @accountPassword\n\t\t\t,@use_default_credentials = @accountUseDefaultCredential\n\t\t\t,@enable_ssl = @accountEnableSsl\nGO\n-- Create a Database Mail profile  \nEXECUTE msdb.dbo.sysmail_add_profile_sp\t@profile_name = @profileName\n\t\t\t,@description = @profileDescription;\nGO\n-- Add the account to the profile  \n\nEXECUTE msdb.dbo.sysmail_add_profileaccount_sp\t@profile_name = @profileName\n\t\t\t,@account_name = @accountName\n\t\t\t,@sequence_number = @accountSequenceNumber;\nGO\n-- Grant access to the profile to the DBMailUsers role  \n\nEXECUTE msdb.dbo.sysmail_add_principalprofile_sp\t@profile_name = @profileName\n\t\t\t,@principal_name = @principalName\n\t\t\t,@is_default = @princtipalDefault;\nGO\n<\/pre>\n<h2><a id=\"post-71790-_Appendix_C:_Public\"><\/a>Appendix C: Public SMTP servers<\/h2>\n<p>In the table below, you can find one of the public servers and create an account on them.<\/p>\n<table>\n<thead>\n<tr>\n<td colspan=\"4\">\n<p><strong>SMTP servers<\/strong><\/p>\n<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>\n<p>Google<\/p>\n<\/td>\n<td>\n<p>smtp.gmail.com<\/p>\n<\/td>\n<td>\n<p>SSL<\/p>\n<\/td>\n<td>\n<p>587<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>OUTLOOK<\/p>\n<\/td>\n<td>\n<p>smtp-mail.outlook.com<\/p>\n<\/td>\n<td>\n<p>SSL<\/p>\n<\/td>\n<td>\n<p>587<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>Yahoo<\/p>\n<\/td>\n<td>\n<p>smtp.mail.yahoo.com<\/p>\n<\/td>\n<td>\n<p>SSL<\/p>\n<\/td>\n<td>\n<p>465<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>\n<p>AOL<\/p>\n<\/td>\n<td>\n<p>smtp.aol.com<\/p>\n<\/td>\n<td>\n<p>SSL<\/p>\n<\/td>\n<td>\n<p>587<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>You can choose an SMTP server from the following site <a href=\"https:\/\/www.arclab.com\/en\/kb\/email\/list-of-smtp-and-pop3-servers-mailserver-list.html\">SMTP &amp; POP3 servers<\/a> as well.<\/p>\n<h2><a id=\"post-71790-_Appendix_D_6522\"><\/a>Appendix D 6522 Message<\/h2>\n<p>Message 6522 will appear in any situation when where you have an unhandled exception, or if an exception occurs in a catch block. This message will be misleading. If your project has permission set property \u2018UNSAFE\u2019 and you receive the 6522 message, it is better to try step into your code and figure out what problem is in your code.<\/p>\n<div class=\"note\">\n<p>The source code for this article is <a href=\"https:\/\/github.com\/Darko-Martinovic\/SQLCLR\/\">stored on Github here (https:\/\/github.com\/Darko-Martinovic\/SQLCLR\/)<\/a><\/p>\n<\/div>\n\n\n<section id=\"my-first-block-block_e91fdda6fa5bfb2fae6be75f818bb877\" class=\"my-first-block alignwide\">\n    <div class=\"bg-brand-600 text-base-white py-5xl px-4xl rounded-sm bg-gradient-to-r from-brand-600 to-brand-500 red\">\n        <div class=\"gap-4xl items-start md:items-center flex flex-col md:flex-row justify-between\">\n            <div class=\"flex-1 col-span-10 lg:col-span-7\">\n                <h3 class=\"mt-0 font-display mb-2 text-display-sm\">Fast, reliable and consistent SQL Server development&#8230;<\/h3>\n                <div class=\"child:last-of-type:mb-0\">\n                                            &#8230;with SQL Toolbelt Essentials. 10 ingeniously simple tools for accelerating development, reducing risk, and standardizing workflows.                                    <\/div>\n            <\/div>\n                            <a href=\"https:\/\/www.red-gate.com\/products\/sql-toolbelt-essentials\/\" class=\"btn btn--secondary btn--lg\">Learn more &amp; try for free<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>Frequently Asked Questions (FAQs)<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. How do you send email from SQL Server using SQLCLR?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Build a SQLCLR stored procedure that uses .NET\u2019s System.Net.Mail.SmtpClient class. Create a CLR assembly project in Visual Studio, add the email logic, deploy to SQL Server with CREATE ASSEMBLY (requires UNSAFE permission set since SMTP uses external network access), then create a stored procedure wrapper. The procedure accepts standard email parameters (To, From, Subject, Body, Attachments) plus SMTP server details.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. What is the difference between SQLCLR email and sp_send_dbmail?<\/h3>\n            <div class=\"faq-answer\">\n                <p>sp_send_dbmail uses Database Mail (Service Broker-based, asynchronous, configured at the instance level in MSDB). SQLCLR email is synchronous, configured at the database level, and doesn\u2019t require MSDB access. sp_send_dbmail is the standard approach for most scenarios. SQLCLR email is better when you need database-contained profiles, custom SMTP providers, Express edition support, or independence from DBA-managed instance settings.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. What security requirements does SQLCLR email need?<\/h3>\n            <div class=\"faq-answer\">\n                <p>The CLR assembly must be marked UNSAFE because sending email requires external network access (System.Net.Mail opens SMTP connections). This means: CLR must be enabled on the instance (sp_configure \u2019clr enabled\u2019, 1), the assembly needs UNSAFE permission, and on SQL Server 2017+ you either need the TRUSTWORTHY database setting or a certificate-based approach. Evaluate the security trade-offs before deploying UNSAFE assemblies.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Build a SQLCLR stored procedure for sending email from SQL Server without sp_send_dbmail. Covers .NET SMTP integration, custom email profiles, CLR security setup, and database-contained email.&hellip;<\/p>\n","protected":false},"author":314795,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143538,143531],"tags":[95509],"coauthors":[47071],"class_list":["post-71790","post","type-post","status-publish","format-standard","hentry","category-dotnet-development","category-t-sql-programming-sql-server","tag-standardize"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71790","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/314795"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71790"}],"version-history":[{"count":18,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71790\/revisions"}],"predecessor-version":[{"id":109108,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71790\/revisions\/109108"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71790"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71790"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71790"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71790"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}