Using Database Mail to Send Attachments Local to SQL Server

Question

Hi All,

Running SQL Server Express 2017.

We have a requirement to send emails with file attachments using database mail. The files have been placed in a directory located on the same server as the SQL server. The SQL service has read-only access to the directory, and the SQL service account has been added to SQL as a login with sysadmin rights. The SQL login has not been assigned any special permissions (other than sysadmin role), nor does it own or has been added as a user to any of the server databases.

When attempting to execute the sp_send_dbmail stored procedure with the file_attachments parameter (using the local path to the file) the command executes successfully but no mail is queued and the database mail service does not start. If I execute the same command (omitting the file_attachments parameter) the email is sent successfully.

For a number of reasons this particular application uses mixed mode authentication. We are testing this using the sa account but it does not work. I am aware of the documentation saying:

"Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on."

But the above seems to indicate a limitation regarding network shares, where the files I want to attach are local to the SQL server.

Surely there must be a way to accomplish this, whether through permissions, impersonation, etc?

Any help is greatly appreciated!