In this article, I will demonstrate how to build a SQLCLR stored procedure that extends the functionality of the procedure sp_send_dbmail that is provided in SQL Server. I will be including the code, which is on Github here. The main purpose of this article is to illustrate that SQLCLR is a very good way of accessing .NET functionality for special purposes, but I’m also hoping that the example might be useful to you.
There are a few reasons for wanting to have a more comprehensive way of sending email from SQL Server than sp_send_dbmail .
- You may not have the appropriate rights on the instance level
- You need to have more control of the process of sending e-mails
- You need to implement custom logging of emails for security.
- You have to use your e-mail profiles, rather than those provided by DBA team
- 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.
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’s dedicated e-mail address to transmit a report, graph or warning message.
Then you have to negotiate with local DBA team or with the other software vendors. In SQL Server Agent’s alert system, it is only possible to have one active e-mail profile, and you can’t change it without restarting SQL Server Agent.
- There are situation when local DBA team, just replace public ¸e-mail profile in SQL Server mailing system and you suddenly start receiving phone calls that something goes wrong.
- 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 sp_send_dbmail. Alternatively, you see a message like this:
You will sometimes hear that a reason to replace ‘sp_send_dbmail’ with a custom SQLCLR stored procedure is that you is that e-mailing isn’t a feature of SQL Server EXPRESS edition. Actually, this isn’t 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.
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.
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’d need for an e-mail profile.
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 Appendix B for information how to do that) but this is the only setting at the instance level that should be changed.
This article talks about how to build such procedure using .NET on the server. This requires using SQLCLR.
Here is how this article is organized:
- In the first section, I will make a brief overview about e-mailing on SQL Server.
- Then I will introduce CLR stored procedure EMAIL.CLRSendMail
- In the third section, I will present a set of new tables needed to implement custom e-mailing.
- Then we are talking about sensitive information and how to protect them
- Fifth section talks about testing and monitoring SQLCLR solution
- Sixth section talks about debugging SQLCLR solution
- Finally, I will discuss how supported material is organized.
A Brief Overview of SQL Server E-Mailing
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 ‘sp_send_dbmail’ returns immediately.
According Microsoft documentation: ’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’.
Therefore, by default, Service Broker has enabled in MSDB database and there is no mailing without Service Broker.
Probably some of you did not know that in background Service Broker uses external program. The Database Mail external executable is DatabaseMail.exe, located in the MSSQL\Binn directory of the SQL Server installation
In addition to the procedure (sp_send_dbmail) parameters, there are configuration parameters that ‘sysmail_configure_sp’ 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.
|
Parameter name
|
Description
|
Default Value
|
|
Account Retry Attempts
|
The number of times that the external mail process attempts to send the e-mail message using each account in the specified profile
|
1
|
|
Account Retry Delay (seconds)
|
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.
|
5000
|
|
Database Mail Executable Minimum Lifetime (seconds)
|
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.
|
600
|
|
DefaultAttachmentEncoding
|
The default encoding for e-mail attachments
|
MIME
|
|
MaxFileSize
|
The maximum size of an attachment, in bytes.
|
1000000
|
|
Prohibited Extensions
|
A comma-separated list of extensions, which cannot be sent as an attachment to an e-mail message.
|
exe,dll,vbs,js
|
|
LoggingLevel
|
Specify which messages are recorded in the Database Mail log. One of the following numeric values:
1 – This is normal mode. Logs only errors.
2 – This is extended mode. Logs errors, warnings, and informational messages.
3 – This is verbose mode. Logs errors, warnings, informational messages, success messages, and additional internal messages. Use this mode for troubleshooting.
|
2
|
|
Save Emails
|
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.
|
False
|
|
Send Mail Asynchronously
|
This is default, but you can choose to send syncronously
|
True
|
|
No Piping
|
There is no messages in message box, after success sending
|
True
|
|
Save Attachments
|
Do we save attachments
|
True
|
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.
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 Account Retry Delay) and then the process will start sending an e-mail using another account.
Of course it is not easy to reproduce some of the features such as scalability and reliability using SQLCLR.
Introduction to the stored procedure [EMAIL].[CLRSendMail]
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 ‘UNSAFE’ (Microsoft designers divide SQLCLR projects according the permission set to ‘SAFE’,’EXTERNAL_ACCESS’ & ‘UNSAFE’).
SQL Server uses ‘GRANT’ ‘DENY’ ‘REVOKE’ 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.
In ‘SAFE’ mode, the assembly’s methods can do pretty much the same as in T-SQL stored procedure. Caller’s credentials are used to execute the assembly method.
In ‘EXTERN_ACCESS’ mode the assembly method can perform file & network input & output. External code runs with the SQL Server service account’s privileges, so impersonation should be used to ensure that the caller’s privileges are used when controlling access to external resources specified by user input.
In ‘UNSAFE’ 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 ‘UNSAFE’ mode. Of course, it is not. As I explained in earlier Practical usage of SQLCLR: Building QueryToHtml Function calling compiler is not possible, although it might be very cool feature in some cases
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!
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 ‘EXTERNAL ACCESS’ or ‘UNSAFE’. I add another strange notice about message 6522 In Appendix D
Msg 6522, Level 16, State 1, Procedure CLRSendMail, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "CLRSendMail":
System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: ExternalThreading
System.Security.HostProtectionException:
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)
We will create a SQLCLR stored procedure for this project by harnessing the ‘SmtpClient’ class, locating in ‘System.Net.Mail’ namespace.
The end-product of the SQLCLR project will be an assembly that contains information about our SQLCLR stored procedure.
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 ‘SimpleTalk.SQLCLR.SendMail’. The stored procedure name is ‘EMAIL.CLRSendMail’.
In the Deploying 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.
The simplest call is like this:
EXEC [EMAIL].[CLRSendMail]
@profileName = N'SimpleTalk',
@mailTo = N'<semicolon-delimited list of e-mail addresses to send the message to>',
@mailBody = N'test';
…will produce an e-mail like this.
You’ll have noticed that we have to specify only three parameters in order to send an e-mail using SQLCLR. Although mailBody is also optional in sp_send_dbmail, it could not be optional in SQLCLR, because SQLCLR does not allow default values for nvarchar (max) types.
Help is available in any point by executing
SELECT
*
FROM EMAIL.CustomSendMailHelp('EMAIL.ClrSendMail');
The result of help command is a list of parameters similar like table below.
|
Parameter Name
|
Parameter Description
|
Parameter Type
|
Default Value
|
Can be null
|
|
@profileName
|
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.
|
Nvarchar(20)
|
No default value
|
No
|
|
@mailTo
|
Is a semicolon-delimited list of e-mail addresses to send the message to.
|
Nvarchar(max)
|
No default value
|
No
|
|
@mailSubject
|
Is the subject of the e-mail message. The subject is of type nvarchar(255). If no subject is specified, the default is ‘SQLCLR Server Message’
|
Nvarchar(255)
|
No SQLCLR Server Message’ |
Yes
|
|
@mailBody
|
Is the body of the e-mail message. The message body is of type nvarchar(max),
|
Nvarchar(max)
|
No default value |
No. This is limitation of SQLCLR nvarchar(max)
|
|
@fromAddress
|
Is the value of the ‘from address’ 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.
|
Nvarchar(500)
|
NULL |
Yes
|
|
@displayName
|
Display name associate with from email address
|
Nvarchar(400)
|
NULL |
Yes
|
|
@mailCC
|
Is a semicolon-delimited list of e-mail addresses to carbon copy the message to.
|
Nvarchar(4000)
|
NULL |
Yes
|
|
@blindCopyRec
|
Is a semicolon-delimited list of e-mail addresses to blind carbon copy the message to.
|
Nvarchar(4000)
|
NULL |
Yes
|
|
@replyAddress
|
Is the value of the ‘reply to address’ 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.
|
Nvarchar(400)
|
NULL |
Yes
|
|
@fileAttachments
|
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
|
Nvarchar(max)
|
NULL |
Yes
|
|
@requestReadReceipt
|
Request read receipt. Not implemented in sp_send_dbmail
|
Bit
|
0 |
YES
|
|
@deliveryNotification
|
Delivery notification.Not implemented in sp_send_dbmail.
|
Şmallint
|
1 |
Yes
|
|
@sensitivity
|
Is the sensitivity of the message. The parameter may contain one of the following values: Normal,Personal,Private & Confidential
|
Smallint
|
1 |
YES
|
|
@mailPriorty
|
Is the importance of the message.The parameter may contain one of the following values: Low,Normal & High
|
Smallint
|
1 |
Yes
|
|
@bodyHtml
|
Is the format of the message bodyCan be HTML or TEXT
|
Bit
|
1 |
0
|
|
@configName
|
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.
|
Nvarchar(20)
|
NULL |
Yes
|
Here are some examples how to call the stored procedure:
If we need to send attachments in our mail message, we specify
EXEC [EMAIL].[CLRSendMail] @profileName =N'SimpleTalk'
,@mailTo = N'<your recepiens>
,@mailSubject = N'test'
,@fileAttachments = 'D:\a1\AtlantaMDF_031207112.ppt;D:\OneDrive\TKD\608-Horvat.pdf'
,@mailBody = N'test'
GO
When working with attachments, it is important to know that there are some default extensions such as ‘exe’,’js’,’dll’ that are prohibited for security reasons.
The file attachment should exist on file system. If the file does not exist, a warning item is added to the ‘Monitor Log’ table. The E-mail message will be still sent.
The file attachment should be smaller than the size specified by the configuration object.
If you need to specify the display name, you just override the default by specifying it:
EXEC [EMAIL].[CLRSendMail] @profileName = N'SimpleTalk'
,@mailTo = N'<your recepiens>’
,@mailSubject = N'Test with display name'
,@displayName = 'SQLCLR display name'
,@mailBody = N'test';
Which will produce an email like this
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 ‘DefaultDisplayName’, so parameter @displayName, could be omitted too.
If you need to specify a reply address that is different from address you sent an e-mail to, and then specify parameter @replyAddress.
,@replyAddress = '<your reply address>'
If you like to request read recipient, you can call procedure like this:
,@requestReadReceipt = 1
Similar, if you like to setup delivery notification, you can choose one of the value. 0-means none, 1-On Success, 2-On Failure.
,@deliveryNotification = 1 --On Success
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.
,@sensitivity=2
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.
,@mailPriorty = 2
Omnia Mea Mecum Porto
What information we need to in order to implement SQLCRL support for sending e-mails?
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.
For the sake of the readability of these tables, we will set it in a special schema called EMAIL.
As you see on the table below, I added five (5) tables.
|
Table name
|
Table description
|
|
EMAIL.Configurations
|
We will save configuration options such as maximum attachment size or prohibited extension. This table will take on the equivalent role of msdb.dbo.sysmail_configuration
|
|
EMAIL.Profiles
|
This is where we save information such as the login name, password, host name, port number etc.
|
|
EMAIL.MailItems
|
This is similar to msdb.dbo.sysmail_mailitems. If it is specified by configuration options, then e-mails will be saved in this table..
|
|
EMAIL.Attachments
|
Similar to msdb.dbo.sysmail_attachments
|
|
EMAIL.MonitorLog
|
Basic logging table
|
You can see relations between tables on the screenshot bellow.
The T-SQL script for creating the database schema and tables is included in the supporting materials. See the Deployment section for details.
As I wrote at the beginning, we will use the SmtpClient 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 SysConfig and the other SysProfile.
SysConfig do pretty much the same as sysmail_configure_sp. It is a simple class with bunch of properties. It has the implementation like this:
public class SysConfig
{
//Implemented
/// <summary>
/// Maximum file size in bytes. Default 1MB
/// </summary>
public Int32 maxFileSize
{
get; set;
} = 1000000;
//Implemented
/// <summary>
/// File extension that are not allowed
/// </summary>
public string prohibitedExtension
{
get; set;
} = "exe,dll,vbs,js";
public eLoggingLevel loggingLevel
{
get; set;
} = eLoggingLevel.Minimal;
/// <summary>
/// Do we save e-mails?
/// </summary>
public bool saveEmails
{
get; set;
} = true;
/// <summary>
/// What is configuration name
/// </summary>
public string name
{
get; set;
} = null;
/// <summary>
/// How we send an e-mail?
/// </summary>
public bool sendAsync
{
get; set;
} = true;
/// <summary>
/// Do we need piping
/// </summary>
public bool noPiping
{
get; set;
} = true;
/// <summary>
/// Do we save attachments
/// </summary>
public bool saveAttachments
{
get; set;
} = true;
}
Using the object type SysConfig 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. 
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
SELECT * FROM EMAIL.CONFIGURATIONS;
SysProfilehas 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 SysProfile by reading information from the ‘Email.Profiles’ table.
This table has structure similar like this:
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.
private static SysProfile GetClient(string name, ref string error)
{
SysProfile p = null;
EncryptSupport.Simple3Des wrapper = new EncryptSupport.Simple3Des(SECRET_WORD);
//Built in profile called ssl
if (name == "ssl")
{
p = new SysProfile();
p.builInName = name;
p.client = new SmtpClient();
p.client.UseDefaultCredentials = false;
p.client.Port = 587;
p.client.Host = "smtp.gmail.com";
p.client.EnableSsl= true;
p.client.Credentials = new NetworkCredential(wrapper.DecryptData("DE5ZET4hY95fZ7JadaxKqchFuvrR3p12vlY="), wrapper.DecryptData("ovkrtZ/="));
}
//Built in profile called simple
else if (name == "simple")
{
p = new SysProfile();
p.builInName = name;
p.client = new SmtpClient();
p.client.UseDefaultCredentials = false;
p.client.Port = 25;
p.client.Host = "mail.iskon.hr";
p.client.EnableSsl = false;
p.client.Credentials = new NetworkCredential(wrapper.DecryptData("El+=="), wrapper.DecryptData("=="));
}
else
{
//determine profile by querying the database
SqlParameter[] listOfParams = new SqlParameter[1];
SqlParameter pName = new SqlParameter("name", name);
pName.Size = 20;
pName.SqlDbType = System.Data.SqlDbType.Char;
listOfParams[0] = pName;
p = DataAccess.GetProfile(listOfParams, wrapper,ref error);
}
return 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
IF ( SELECT COUNT(*) FROM EMAIL.PROFILES ) = 0
BEGIN
INSERT INTO email.profiles (profilename, EnableSsl, DefaultCred, Port, HostName, UserName, Password,DefaultFrom)
SELECT
'SimpleTalk'
,1
,0
,587
,'smtp.gmail.com'
,N'L5Yr/GLP9vbH82Fah78mSeUHInP/ws8XN0K0ky3W4PvpfSOgo='
,N'KYEVsZBY8c08fKO4vLihIrcwwOxInvas1I='
,'your_address@company.com';
---Use these values if you decide to apply T-SQL encryption by using ENCRYPTBYCERT function
--,ENCRYPTBYCERT(CERT_ID('TestCert'), N'pomocniracun25@gmail.com')
--,ENCRYPTBYCERT(CERT_ID('TestCert'), N'Anja26032011')
--,'darko.martinovic@outlook.com';
END
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 ‘UserName’ and ‘Password’, with your concrete values. . See more on this section ( Sensitive data).
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.
The main table for saving e-mails is Email.MailItems with structure similar to this:
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 ‘true’. The structure of this table looks similar to the screenshot bellow.
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 ‘DataTable’ as a parameter to the command object. This parameter has SqlDbType.Structured type and it has the value of ‘EMAIL.TVP_Emails’.
if (saveAttachments && validAttachments.Trim().Equals(string.Empty) == false)
{
comm.CommandText = @"INSERT INTO [EMail].[MailAttachments] (mailitem_id,fileName,fileSize,attachment)
SELECT @mailItem, fileName, fileSize, attachment FROM @tvpEmails";
comm.Parameters.AddWithValue("@mailItem", id);
comm.Parameters[comm.Parameters.Count - 1].SqlDbType = System.Data.SqlDbType.BigInt;
DataTable dt = CreateTable();
DataRow newRow = null;
foreach (Attachment eml in mm.Attachments)
{
newRow = dt.NewRow();
newRow["FileName"] = eml.Name;
newRow["FileSize"] = eml.ContentStream.Length;
byte[] allBytes = new byte[eml.ContentStream.Length];
int bytesRead = eml.ContentStream.Read(allBytes, 0, (int)eml.ContentStream.Length);
newRow["Attachment"] = allBytes;
eml.ContentStream.Position = 0;
dt.Rows.Add(newRow);
}
comm.Parameters.AddWithValue("@tvpEmails", dt);
comm.Parameters[comm.Parameters.Count - 1].TypeName = "EMAIL.TVP_Emails";
comm.Parameters[comm.Parameters.Count - 1].SqlDbType = SqlDbType.Structured;
comm.ExecuteNonQuery();
dt = null;
newRow = null;
}
comm.Transaction.Commit();
if (conn.State == ConnectionState.Open)
conn.Close();
During the deployment of the SimpleTalk.SQLCLR.SendMail solution, a T-SQL script will detect if such a TVP exists. If not, then the T-SQL script will create it.
IF NOT EXISTS (SELECT
*
FROM sys.types st
JOIN sys.schemas ss
ON st.schema_id = ss.schema_id
WHERE st.name = N'TVP_Emails'
AND ss.name = N'EMail')
CREATE TYPE [EMail].[TVP_Emails] AS TABLE ([FileName] [NVARCHAR](260) NOT NULL
, [FileSize] [BIGINT] NOT NULL
, [Attachment] [VARBINARY](MAX) NOT NULL);
Finally, there is a table for logging called Email.MonitorLog. It has the structure similar like this:
Correspondent class is ‘LogEntry’, with only one static method called ‘LogItem’.
Typically, the monitor log looks similar like this:
It captures information about the file attachment, profiles etc. Records in this table can be queried by record type. The record type can be ‘Information’, ‘Warning’ and ‘Fatal’. 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 ‘MailMessage’. All these extension are located in ‘Extensions’ folder inside the solution. The extension to describe the e-mail is named ‘HeaderInformation’.
public static string HeaderInformation(this MailMessage mm)
{
return @"From : " + "'" + mm.From.Address.Trim() + "'" + "\r\n" +
"To : " + "'" + mm.To[0].Address.ToString().Trim() + "'" + "\r\n" +
"Subject : " + "'" + mm.Subject.ToString().Trim() + "'";
}
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.
What about Sensitive Data?
When you create an account on public servers (see Appendix C 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.
T-SQL encryption can be accomplished by creating master key and certificate, as in this script.
--Create master key. Master key has id 101
IF NOT EXISTS (SELECT *
FROM sys.symmetric_keys
WHERE symmetric_key_id = 101)
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SimpleTalk'
END
GO
--Create our certificate if not exists
IF NOT EXISTS (SELECT
*
FROM sys.certificates
WHERE name = 'TestCert')
BEGIN
CREATE CERTIFICATE TestCert WITH SUBJECT = 'Test certifikate', EXPIRY_DATE = '20251231'
END
GO
--Encrypt string by using certificate
SELECT
ENCRYPTBYCERT
(
CERT_ID('TestCert'), N'UserSensitiveData');
GO
--Decrypt by using certificate
SELECT
CAST(DECRYPTBYCERT(CERT_ID('TestCert'), 0x4189E1E22596180DF3407C831484ED4E6DDD34F97E6798E741542E7C0967AFF9EF93BA1C8D157AA37003670BC33B4AED6BB82D2997C0AF0712774AD902E7CCD6C08F5641DB713A2681FC1963749A315FC8903F9769B34F10316135F77AB546123E81B4A5BB75C10FEADCE22D4CE56C9BF7C0920B0F0D906C389B153636C71492) AS NVARCHAR(100));
--Will return string ‘UserSensitiveData’
Therefore, when inserting record in table EMAIL.PROFILES, we can use script like this
IF ( SELECT
COUNT(*)
FROM EMAIL.PROFILES)
= 0
BEGIN
INSERT INTO EMAIL.PROFILES (ProfileName, EnableSsl, DefaultCred, Port, HostName, UserName, Password, DefaultFrom)
SELECT
'SimpleTalk'--Profile name
,1--EnableSsl
,0--Use default credential
,587--Port number
,'smtp.gmail.com'--Host name
,ENCRYPTBYCERT(CERT_ID('TestCert'), N'<your login name>')
,ENCRYPTBYCERT(CERT_ID('TestCert'), N'<your password>’)
,'<default from address’;
END
In our SQLCLR solution, it is possible to make use of an instance of the X509Certificate2 class, located in the System.Security.Cryptography.X509Certificates 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.
I make a simple utility, which is available on this address. It is WPF program, which has four steps. First, you can choose an action, as in the picture below. You can choose ‘encrypt’ or ‘decrypt’.
Then you can enter or paste the string to encrypt or decrypt as in the screenshot below, when I entered the string “My user information”.
Then you choose ‘secret key’, as below, at which point I entered “Simple Talk”.
At the final step, you can view the result.
This is a simple tool, which uses TripleDESCryptoServiceProvider located in the System.Security.Cryptography namespace, in order to encrypt/decrypt the string. If, at the last step, receive the result of ‘bad data’, it is probably because your secret key is incorrect.
As a result of these operations, we can create T-SQL code to fill our EMAIL.PROFILES table. It means replacing ENCRYPTBYCERT with the ‘Result’ string we’ve 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.
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.
Testing & Monitoring
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.
After that, I need to choose some content for the body of the e-mail. It cannot be just a simple ‘Test’ word.
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.
The declaration part of the T-SQL script to test should look something like this
DECLARE @myFileAttachment as nvarchar(max) = 'your attachments hear'
DECLARE @myBody as nvarchar(max) = N'a lot of text'
DECLARE @counter as int = 0
DECLARE @mysubject as nvarchar(255) = 'Test'
--mail to. You should replace values with concrete e-mail addresses
DECLARE @myMainTo as nvarchar(max) = ''
DECLARE @myMainTo_1 as nvarchar(max) = '<list_one>'
DECLARE @myMainTo_2 as nvarchar(max) = '<list_two>'
DECLARE @myMainTo_3 as nvarchar(max) = '<list_three>'
DECLARE @myMainTo_4 as nvarchar(max) = '<list_fourth>'
DECLARE @myMainTo_5 as nvarchar(max) = '<list_five>'
--end mail to
--Profiles. First you have to insert all these profiles in EMAIL.Profiles table
DECLARE @myProfile as nvarchar(20) = ''
DECLARE @myProfile1 as nvarchar(20) = 'Test1'
DECLARE @myProfile2 as nvarchar(20) = 'Test2'
DECLARE @myProfile3 as nvarchar(20) = 'Test3'
DECLARE @myProfile4 as nvarchar(20) = 'Test4'
DECLARE @myProfile5 as nvarchar(20) = 'Test5'
--End profiles
--How many times we will executing the stored procedure
DECLARE @multiple as int = 1
DECLARE @firstStop as int = 5 * @multiple
DECLARE @secondStop as int = 10 * @multiple
DECLARE @thirdStop as int = 15 * @multiple
DECLARE @fourtstop as int = 20 * @multiple
DECLARE @fifthStop as int = 25 * @multiple
The script body should look like this
WHILE (1=1)
BEGIN
IF @counter < @firstStop
BEGIN
SET @myMainTo = @myMainTo_1
SET @myProfile = @myProfile1
END
ELSE
IF @counter >= @firstStop and @counter < @secondStop
begin
SET @myMainTo = @myMainTo_2
SET @myProfile = @myProfile2
end
ELSE
IF @counter >= @secondStop and @counter < @thirdStop
begin
SET @myMainTo = @myMainTo_3
SET @myProfile = @myProfile3
end
else IF @counter >= @thirdStop and @counter < @fourtStop
begin
SET @myMainTo = @myMainTo_4
SET @myProfile = @myProfile4
end
else IF @counter >= @fourtStop and @counter <= @fifthStop
begin
SET @myMainTo = @myMainTo_5
SET @myProfile = @myProfile5
end
EXEC [EMAIL].[CLRSendMail] @profileName = @myProfile
, @mailTo = @myMainTo
, @mailSubject = @mysubject
, @fileAttachments = @myFileAttachment
, @mailBody = @mybody;
SET @mysubject = CAST(@counter AS NVARCHAR(10))
SET @counter = @counter + 1
IF @counter > @fifthStop
BREAK;
END
We could start not only one, but also several clients in a batch. In my testing environment the SQLCLR solution is faster than sp_send_dbmail.
As a monitoring tool, we could use a winforms application; I developed one of these that I described in my last article, Practical usage of SQLCLR: Building QueryToHtml Function.
This tool has a tab labelled ‘Monitor’ as showed on this screenshot.
Binding will only occur on the ‘Monitor’ tab only if you have first specified a credential on the first tab that has ‘view server state’ 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 Monitor is the combination of three system views. Surrounded with red is the information from sys.assemblies. This returns a row for each assembly that is created in the current database, the database you choose on the first tab. Information from sys.dm_clr_loaded_assemblies 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 sys.dm_clr_appdomains. 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.
According to the Microsoft documentation, the sys.dm_clr_loaded_assemblies view has a many-to-one relationship with sys.dm_clr_appdomains.appdomain_address. In addition, sys.dm_clr_loaded_assemblies view has a one-to-many relationship with sys.assemblies. 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.
When you create an assembly in your database, you will have a corresponding record in the sys.assemblies system view. You know information like principal id, assembly id, permission set etc.
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.
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
Debugging
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 ‘devenv.exe’, and then choose ‘properties’ from the menu. Click on the ‘Shortcut’ tab for a program shortcut, and then click on the ‘Advanced’ button. Check the ‘Run as administrator’ box, and click on ‘OK’.
Then on Visual Studio’s ‘View’ menu, choose ‘SQL Server Object Explorer’.
Expand the ‘SQL Server’ node. Choose your server. Right click, and check both ‘Application Debugging’ and ‘Allow SQL/CLR Debugging’, as showed on the picture below.
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 ‘NOT in build’, as showed on the picture bellow.
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.
Notice the special green triangle.
Click on black ‘dropdown’ triangle near to green one to see more options, as in the screenshot below, or press ALT+F5
Then you have to confirm this in the alert box. One frustrating thing happens when you restart Visual Studio. Maybe I’ve just got it wrong, but I could not find any possibility of how to save checks to ‘Application Debugging’ and ‘SQL/CLR’ debugging.
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.
Of course, the project settings should include debugging information. This you can check by choosing project settings, then tab ‘SQLCLR build’, then scroll down through the end and click on button ‘Advanced’. Noticed Combo Box labeled with ‘Debug info’, as showed on the screenshot bellow
And you are in debugging mode! As I wrote in my previous article on SQLCLR Practical usage of SQLCLR: Building QueryToHtml Function, debugger capabilities are less powerful than in the standard Visual Studio solution. However, even such a debugger as this is indispensable in solving problems.
All that I’ve 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
Support Material & Deploying
One of the weakness of Microsoft’s 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 ‘EMAIL’, as are the other objects created in this solution.
Microsoft designers give us the means to do this by specifying the default schema on the tab ‘Project Settings’, as shown in this screenshot.
However, if you try to compile the solution after making such changes, you will receive an error like this.
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.
In order to prefix the resulting stored procedure with specific schema named ‘EMAIL’, I use another possibility provided by the SQLCLR framework in ‘Visual Studio’.
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.
In the pre-deployment script, I include the following statement to create database schema ‘EMAIL
IF NOT EXISTS (
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name = 'EMAIL' )
BEGIN
EXEC sp_executesql N'CREATE SCHEMA EMAIL'
END
In post deployment script, I include following script in order to transfer the stored procedure from ‘DBO’ to the ‘EMAIL’ schema.
--Transfer procedure
IF EXISTS (SELECT
*
FROM sysobjects
WHERE id = OBJECT_ID(N'[EMAIL].[CLRSendMail]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [EMAIL].[CLRSendMail]
END
ALTER SCHEMA EMAIL TRANSFER dbo.CLRSendMail;
--Transfer function
IF EXISTS (SELECT
*
FROM sysobjects
WHERE id = OBJECT_ID(N'[EMAIL].[CustomSendMailHelp]')
AND type = N'FT')
BEGIN
DROP FUNCTION [EMAIL].[CustomSendMailHelp]
END
ALTER SCHEMA EMAIL TRANSFER dbo.CustomSendMailHelp
Result is as showed on the picture bellow.
Summary
In this article, I have explained, bit-by-bit, how to replace or complement SQL Server’s 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.
Appendix A: Enabling CLR on SQL Server
-- Enable & Check CLR
sp_configure N'clr enabled', 1
GO
RECONFIGURE
GO
SELECT
sc.*
FROM sys.configurations AS sc
WHERE sc.[name] = N'clr enabled'
Appendix B: T-SQL script to setup e-mail on EXPRESS (or any kind) edition
--Enabling Database Mail
sp_configure N'show advanced options',1
RECONFIGURE
GO
sp_configure N'Database Mail XPs',1
RECONFIGURE
GO
--Profile definition
DECLARE @profileName sysname='MyProfile'
DECLARE @profileDescription nvarchar(256)='MyProfileDescription'
--Account definition
DECLARE @accountName as sysname='MyAccount'
DECLARE @accountDescription as nvarchar(256) = 'MyDescription'
--Enter e-mail address you choose e.g. simpleTalk@gmail.com
DECLARE @accountEmail as sysname=''
--Enter reply address e.g. SimpleTalk@SimpleTalk.com
DECLARE @accountReplyTo as sysname=''
--Enter display information. E.g. Test account
DECLARE @accountDisplayName as sysname=''
DECLARE @accountMailServer as sysname='smtp.gmail.com'
DECLARE @accountMailPort as int = 587
-----------------------------------------------------------Most sensitive date
DECLARE @accountUserName as sysname=''
DECLARE @accountPassword as sysname=''
---------------------------------------------------------------------------------------
DECLARE @accountUseDefaultCredential as bit = 0
DECLARE @accountEnableSsl as bit = 1
--Sequence number
DECLARE @accountSequenceNumber as int = 1
--Principal definition
DECLARE @principalName as sysname = 'dbo'
DECLARE @princtipalDefault as bit = 1
-- Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @accountName
,@description = @accountDescription
,@email_address = @accountEmail
,@replyto_address = @accountReplyTo
,@display_name = @accountDisplayName
,@mailserver_name = @accountMailServer
,@port = @accountMailPort
,@username = @accountUserName
,@password = @accountPassword
,@use_default_credentials = @accountUseDefaultCredential
,@enable_ssl = @accountEnableSsl
GO
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @profileName
,@description = @profileDescription;
GO
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @profileName
,@account_name = @accountName
,@sequence_number = @accountSequenceNumber;
GO
-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = @profileName
,@principal_name = @principalName
,@is_default = @princtipalDefault;
GO
Appendix C: Public SMTP servers
In the table below, you can find one of the public servers and create an account on them.
|
SMTP servers
|
|
Google
|
smtp.gmail.com
|
SSL
|
587
|
|
OUTLOOK
|
smtp-mail.outlook.com
|
SSL
|
587
|
|
Yahoo
|
smtp.mail.yahoo.com
|
SSL
|
465
|
|
AOL
|
smtp.aol.com
|
SSL
|
587
|
You can choose an SMTP server from the following site SMTP & POP3 servers as well.
Appendix D 6522 Message
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 ‘UNSAFE’ and you receive the 6522 message, it is better to try step into your code and figure out what problem is in your code.
The post SQLCLR in Practice: Creating a Better Way of Sending Email from SQL Server appeared first on Simple Talk.
Wir haben zwei Monate lang auf Reisen den Internet-Zugang in den deutschen Mobilfunk-Netzen unter die Lupe genommen. Aus dem Telefónica-Netz hatten wir dazu gleich zwei SIM-Karten zur Verfügung.
Die IETF diskutiert einen Vorschlag, um DNS-Informationen nativ über HTTPS zu übertragen. Der Grund ist laut einem der Autoren des Entwurfs ganz einfach: Über Port 443 lässt sich mehr machen, als Bilder zu übertragen, und es ist einfach besser. (
Die iOS-App Gudak macht aus dem iPhone eine Wegwerfkamera, indem es die Funktionen der eingebauten Smartphonekamera deutlich einschränkt, die Bildqualität senkt und die Fotos erst nach drei Tagen ausgibt. (