Managed Service Accounts and DatabaseMail

Question

Managed Service Accounts and DatabaseMail

planetmatt

SSCrazy

Points: 2207
More actions
May 23, 2019 at 10:04 am

#3646449
I have a SQL Job that Emails some data from a user database.
The Job owner is SA.
The Job has one step that calls a SPROC in a User Database. The SPROC gets some data puts it into a Table in the User Database.
Next, the SPROC calls "sp_send_dbmail" to email the contents of that table as an attachment.
```
EXEC msdb.dbo.sp_send_dbmail @profile_name=@MailProfile, 
@recipients=@MailRecpients,
@Subject=@StrSubject,
@body_format = 'HTML',
@body=@strHTML,
@query = 'SET NOCOUNT ON SELECT CSVData FROM MyTable ORDER BY ExportOrder',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'MyData.csv',
@query_result_header = 0,
@query_result_separator = '',
@query_result_no_padding = 1
```
The SPROC runs fine from Management Studio manually, but when scheduled, the job fails with the following error:
"Failed to initialize sqlcmd library with error number -2147024809."
If I change the SPROC to INSERT and SELECT data from a Global Temp Table instead of a table in the User Database, the job successfully run, and I receive the email with attached CSV file, and all is good.
Now, I understand that the first part of the SPROC, the selecting the data and inserting into the table, runs under SA as the job owner. However, when Database Mail runs the SELECT query to get the data from the table to attach in the email, this query runs under the security context of the SQL Server Agent Account.
Both my Database Engine, and Agent Services runs with the same Domain Managed Service Account.
The Service Account is a SQL Login on the Instance, but is a member of only the Public Server Role. It has no explicit access to any User or System Database.
To my mind, the job should fail, regardless of if I use a real or a temp table until I explicit grant the Service Account Login access to either the User Database to use a real table, or the MSDB database to use a Global Temp Table.
So questions:
1. Why do I have inconsistent behaviour between emailing from a real and a global temp table?
2. What is the best practise for granting permissions of Managed Service Account to access data that Agent Jobs may need to email?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

The service account for SQL Server and SQL Agent aren't just members of the public service role and when you use SQL Server configuration manager to set the service accounts there isn't a need to grant extra permissions. You need to also look at the service SID account - NT SERVICE\MSSQLSERVER or NT SERVICE\MSSQL$InstanceName for named instances. Those are members of the sysadmin role.

Try fully qualifying the table name - SELECT CSVData FROM MyDatabase.MySchema.MyTable

Sue

planetmatt SSCrazy Points: 2207 More actions · Answer 2

Hi Sue,

Thank you for your reply.

I have tried both the fully qualified name as you suggest, and I've also tried setting the "@execute_query_database" parameter, with and without the fully qualified table name.

Both give the same error.

So you're saying that both a user table,and a temp table should work fine with no changes to any user permissions?

Here's my Service Config:

Service

and here's that Login on the instance from which I'm running this process.

Are you saying that this account is automatically a Sysadmin even though it doesn't show this on the Login details?

Service2

Thanks

This reply was modified 5 years, 5 months ago by planetmatt.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

I don't see anything for services in your post but if you don't manually change the service accounts permissions and you set it up using SQL Server Configuration Manager (or at install) then yes those accounts are in the sysadmin role. Otherwise the services wouldn't run. It is the service sid that shows in the sysadmins. You can check the members of sysadmins using something like:

SELECT   
[name],
[type_desc],
is_disabled
FROM master.sys.server_principals 
WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1

You normally won't see the explicitly set service account name in there. You would see the service SID which is mapped to the service account. From your description, the service account has been manually change quite a bit. Or the description is maybe not quite accurate. I can't tell which.

Sue

frederico_fonseca SSCoach Points: 16153 More actions · Answer 4

have a look at https://www.sqlservercentral.com/articles/mail-fails-with-sqlcmd-error

planetmatt SSCrazy Points: 2207 More actions · Answer 5

Hi Sue,

I ran your query but none of the Logins listed as belonging to the SysAdmin group are the ones listed in Configuration Manager as the accounts running any of my SQL Services.

Do I have a more serious problem as you state that the services will not run without Sysadmin privileges, but clearly they are.

services

sysadmins

Sue_H SSC Guru Points: 90860 More actions · Answer 6

That would be correct. It is the service SIDs that I have mentioned a couple of times now. And those are listed as being in the sysadmins as they should. NT Service\MSSQL$HF and NT Service\SQLAgent$HF. And those are Windows logins. A majority of the permissions are assigned to the per-service SID for each of its services just as those are listed. And jobs owned by a sysadmin execute under the security context of the Agent service account.

Sue

planetmatt SSCrazy Points: 2207 More actions · Answer 7

So why does Database Mail which we've established runs under a service account mapped to a SysAdmin SID fail to retrieve data from a User Table but not a Temp table?

Sue_H SSC Guru Points: 90860 More actions · Answer 8

It won't happen due to permissions but it can happen when it can't find the object due to whatever database context - what database I am in when I run the query. If I am a sysadmin and am in the master database and execute a query against the tables in msdb, something like: select * from dbo.sysjobs

I would get an error that it's an invalid object. It's not because of permissions but because the object does not exist in master. So the object can't be found. When I am in master, it would work if I execute the query for the table in msdb using: select * from msdb.dbo.sysjobs

Sue

dpimental@districtphoto.com Newbie Points: 1 More actions · Answer 9

I am having a similar issue on Azure Managed Instance of Sql Server.

I have a stored procedure to email the results of a query, which throws an error if I use our user database, but not if my query is from the master db, as in 'SELECT * FROM [master].[dbo].[spt_monitor];'

I get this error: Failed to initialize sqlcmd library with error number -2147467259.

my user sadmin is a sysadmin. Any recommendations?

David

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 10

Why not use a third party application for emailing purposes. SQL server is a critical resource. Just a comment.

I wonder what Brent has to say about this.

DBASupport

cyrusbaratt SSC-Addicted Points: 417 More actions · Answer 11

The browser was loading the answer again..

DBASupport