2

How can I send mail with a user within a contained database is SQL Server using Database Mail?

I tried using msdb.dbo.sp_send_dbmail but since any users local to the contained database do not see msdb, therefore msdb.dbo.sp_send_dbmail is unreachable as well. Then I tried creating certificates and signing procedures, first going straight to msdb and then re-routing the whole stuff through the master database (I know, it's not a 'very good idea') but even that does not work (I suppose it would break the containment, hence not allowed).

‌‌Is there a way do that at all?

  • What problem are you having? I just created a contained database (which according the the documentation I'm reading is really only 'partially' contained) and was able to execute the msdb.dbo.sp_send_dbmail sp sucessfully and I received the email. – Scott Hodgin Mar 22 '17 at 15:20
  • Did you use an user in the contained database to connect? – gemisigo Mar 22 '17 at 15:22
  • I haven't used contained databases, but your question intrigued me. I have something working, but it involves creating a certificate, a custom stored procedure (that invokes sp_sendmail under the covers) which has to be signed and a few other things. Before I go to the trouble of posting my solution as an answer, are you even interested in going down that path? – Scott Hodgin Mar 22 '17 at 18:56
  • Hi, Scott. I tried this path but it failed. The interesting thing is that it does not complain about not seeing msdb, it actually says that Msg 229, Level 14, State 5, Proc "sp_send_dbmail", The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'. which is a bit funny, regarding that all the necessary rubbish was created and applied. Is there anything else to be done there besides creating the certificates in both databases + the certified user, signing the wrapper stored procedure and permitting the certificate user to execute the original? – gemisigo Mar 23 '17 at 15:34
  • This approach definitely works from a non-contained database, though. – gemisigo Mar 23 '17 at 15:47
4

The following solution involves the creation of a certificate to sign a newly created stored procedure in a contained database that emulates the functionality of msdb.dbo.sp_send_mail. This has worked successfully in my testing and hopefully, it will work for you.

-----------------------------------------------------------------------------
--This example expects an empty directory called C:\ContainedDatabaseExample 
--Make sure no files exist in the target directory first
-----------------------------------------------------------------------------


USE [master]
GO
--Cleanup from previous runs of this process
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
--===========================================================================
--Create a new 'contained' database for testing
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Contained')
    DROP DATABASE [Contained]
GO
CREATE DATABASE [Contained]
 CONTAINMENT = PARTIAL
 ON  PRIMARY 
(
NAME = N'Contained', 
FILENAME = N'C:\ContainedDatabaseExample\Contained.mdf' , 
SIZE = 1024KB , 
MAXSIZE = UNLIMITED, 
FILEGROWTH = 1024KB
)
 LOG ON 
(
NAME = N'Contained_log', 
FILENAME = N'C:\ContainedDatabaseExample\Contained_log.ldf' , 
SIZE = 1024KB , 
MAXSIZE = 1024GB , 
FILEGROWTH = 1024KB
)
GO
ALTER DATABASE [Contained] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Contained].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Contained] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Contained] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Contained] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Contained] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Contained] SET ARITHABORT OFF 
GO
ALTER DATABASE [Contained] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Contained] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Contained] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Contained] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Contained] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Contained] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Contained] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Contained] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Contained] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Contained] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Contained] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Contained] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Contained] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Contained] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Contained] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Contained] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [Contained] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [Contained] SET RECOVERY FULL 
GO
ALTER DATABASE [Contained] SET  MULTI_USER 
GO
ALTER DATABASE [Contained] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Contained] SET DB_CHAINING OFF 
GO
ALTER DATABASE [Contained] SET DEFAULT_FULLTEXT_LANGUAGE = 1033 
GO
ALTER DATABASE [Contained] SET DEFAULT_LANGUAGE = 1033 
GO
ALTER DATABASE [Contained] SET NESTED_TRIGGERS = ON 
GO
ALTER DATABASE [Contained] SET TRANSFORM_NOISE_WORDS = OFF 
GO
ALTER DATABASE [Contained] SET TWO_DIGIT_YEAR_CUTOFF = 2049 
GO
ALTER DATABASE [Contained] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [Contained] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [Contained] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [Contained] SET QUERY_STORE = OFF
GO
USE [Contained]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
ALTER DATABASE [Contained] SET  READ_WRITE 
GO



--Use the 'contained' database
USE Contained
GO

--Create a new user in the contained database
CREATE USER [ContainedUser] WITH PASSWORD=N'BaconLover1', DEFAULT_SCHEMA=[dbo]
GO

--Create a new role that allows for easy additions of users to 
--execute the 'contained' database new email procedure
CREATE ROLE [SendMailFromContainedDatabase]
GO

--Add the new contained database user to the database role
ALTER ROLE [SendMailFromContainedDatabase] ADD MEMBER [ContainedUser]
GO

--Create a new procedure in the Contained database that 
--emulates all of the functionality of msdb.dbo.sp_send_dbmail
--My example below doesn't take any parameters and hardcodes
--enough information to send a test email.
--The actual procedure would need all of the normal parameters
--that msdb.dbo.sp_send_dbmail uses

CREATE PROCEDURE ContainedSendMail
    (@ValidEmailAddress varchar(100)) 
    WITH EXECUTE AS OWNER
AS
BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,@recipients = @ValidEmailAddress
        ,@subject = 'From Contained Database'
        ,@importance = 'High';
END
GO

--Create a certificate in the contained database that will be
--used to sign the new email stored procedure
CREATE CERTIFICATE [DatabaseMailFromContainedDatabases] 
    ENCRYPTION BY PASSWORD = 'Password#1234'
    WITH SUBJECT = 'DatabaseMailFromContainedDatabases';
GO

--Sign the new email stored procedure with the certification
ADD SIGNATURE TO OBJECT::[ContainedSendMail] 
BY CERTIFICATE [DatabaseMailFromContainedDatabases]
WITH PASSWORD = 'Password#1234';
GO

--Remove the PRIVATE KEY because it's not really needed
ALTER CERTIFICATE [DatabaseMailFromContainedDatabases] 
REMOVE PRIVATE KEY;
GO

--Grant permissions to the database role to execute the 
GRANT EXECUTE ON ContainedSendMail TO [SendMailFromContainedDatabase]
GO
GRANT VIEW DEFINITION ON ContainedSendMail TO [SendMailFromContainedDatabase]
GO

--Backup the newly created certificate from the contained database
BACKUP CERTIFICATE [DatabaseMailFromContainedDatabases]
TO FILE = 'C:\ContainedDatabaseExample\DatabaseMailFromContainedDatabases.CER';
GO

--Now, we need to import the 'contained' certificate into master
USE master
go
IF (select Count(*) from sys.certificates where name = 'DatabaseMailFromContainedDatabases') = 1
    DROP CERTIFICATE DatabaseMailFromContainedDatabases;
create CERTIFICATE [DatabaseMailFromContainedDatabases]
from FILE = 'C:\ContainedDatabaseExample\DatabaseMailFromContainedDatabases.CER';
GO

--We need to associate the new certificate with a server login
--and grant permissions
USE [master]
GO
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
CREATE LOGIN [DatabaseMailFromContainedDatabases]
FROM CERTIFICATE [DatabaseMailFromContainedDatabases];
GRANT AUTHENTICATE SERVER TO [DatabaseMailFromContainedDatabases];
GO

--Add the new login to the MSDB DatabaseMailUserRole 
USE [msdb]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DatabaseMailFromContainedDatabases')
    DROP USER [DatabaseMailFromContainedDatabases]
CREATE USER [DatabaseMailFromContainedDatabases] FOR LOGIN [DatabaseMailFromContainedDatabases]
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [DatabaseMailFromContainedDatabases]
GO

At this point, you 'should' be able to connect as the 'Contained' database user id (that was created in the example script above) and run

exec ContainedSendMail @ValidEmailAddress = 'YourEmailAddress'

making sure you provide a valid email address. After a minute or so, I received the test email.

  • Love that password. – Nelz Mar 24 '17 at 13:08
  • Great! I see where my error lies now. The only significant difference between your solution and mine (besides the former being correct while the latter not) is your indirect approach to msdb, that is, you created a login from the certificate in master first and then assigned a user to that login in msdb. I tried to save that effort and created the user from the certificate straightaway in msdb. Apparently, that did not work as expected. I'm pretty sure adding that extra step will solve the issue here. Thank you very much, Scott, I owe you one. +1 for the savory password. – gemisigo Mar 24 '17 at 13:48
1

I tried for a bit to replicate your scenario and finally I managed to effectively send an email to myself using SMTP, but I had to create an SQL CLR stored procedure as follows:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Net;
using System.Net.Mail;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void CLR_SendEmail(string subject, string body, string to)
    {
    SmtpClient smtpClient = new SmtpClient();
    NetworkCredential basicCredential =
    new NetworkCredential("user", "passkey");
    MailMessage message = new MailMessage();
    MailAddress fromAddress = new MailAddress("email@address.com");

    smtpClient.Host = "smtp.server.com";
    smtpClient.UseDefaultCredentials = false;
    smtpClient.Credentials = basicCredential;

    message.From = fromAddress;
    message.Subject = subject;
    message.Body = body;
    message.To.Add(to);

    try
    {
        smtpClient.Send(message);
    }
    catch (Exception ex)
    {
        SqlContext.Pipe.Send(ex.Message);
    }
        SqlContext.Pipe.Send("Sent" +Environment.NewLine);    
    }
}

I had a lot of grief publishing this project until I realized I needed to declare the containment status of the target db on Visual Studio:

enter image description here

After that its all good to go:

enter image description here

A good guide to SQL CLR on VS 2015

  • Well, thanks, but that seems quite a bit an overkill to me for such a minuscule task as sending an email that should be possible to achieve without workarounds. I hope that I won't have to do that in the end... – gemisigo Mar 23 '17 at 15:42
  • @gemisigo well... It is a minuscule task if your using a mail client, this is a database engine you're trying to use, IMO you shouldn't be using it for emails, period. That is without even accounting for the fact that you cant use the built in msdb.dbo.sp_send_dbmail – Nelz Mar 23 '17 at 17:09
  • We disagree on that. Though I imagine it would be an amazingly amusing waste of time trying to force the database server to use a mail client to send periodic reports&results of certain operations instead of using the solution that was built for that purpose, IMO the database server is exactly the place where this should be done. The issue isn't that I can't use msdb.dbo.sp_send_dmail. I can, albeit indirectly. The solution you propose does not solve that and it introduces additional dependencies on CLR + VS and removes maintenance of mail account data from where it belongs: Database Mail – gemisigo Mar 23 '17 at 23:41
0

We recently had this issue and did below to get it worked..

  1. Created a sql login and gave dbmailuser permission
  2. Created a linked server with sql native provider which uses above created login credentials.. eg: LS_DBMAIL
  3. Call the sp send mail procedure with linked server name inside the contained db..eg: exec LS_DBMAIL.msdb.dbo.sp_send_mail

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.