Managed Service Accounts and DatabaseMail
-
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 = 1The 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:
- Why do I have inconsistent behaviour between emailing from a real and a global temp table?
- What is the best practise for granting permissions of Managed Service Account to access data that Agent Jobs may need to email?
Thanks
-
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
-
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:
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?
Thanks
-
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) = 1You 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
-
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.
-
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
-
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?
-
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply