Sql-server – Agent Job Running as Computer Domain Account Rather than Service account

service-accountssql server 2014sql-server-2016sql-server-agent

I have two SQL Servers as follows

Microsoft SQL Server 2016 (RTM-CU9-GDR) (KB4058559) - 13.0.2218.0 (X64)   Dec 27 2017 17:10:01   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) 

Microsoft SQL Server 2014 (SP3-CU4) (KB4500181) - 12.0.6329.1 (X64)   Jul 20 2019 21:42:29   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Both are running the SQL Server Agent service as NT Service\SQLSERVERAGENT

If I create a test job as follows 

USE [msdb]
GO

/****** Object:  Job [TEST]    Script Date: 13/08/2019 14:47:44 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 13/08/2019 14:47:44 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'TEST', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'No description available.', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [a]    Script Date: 13/08/2019 14:47:45 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'a', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'WAITFOR DELAY ''00:10:00''', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

And run it on the 2014 server, I can see using sp_whoisactive that the job is running as NT SERVICE\SQLSERVERAGENT

If I run it on the 2016 server, I can see using sp_whoisactive that the job is running as DOMAIN\SERVERNAME$(Which incidentally is not a login on the SQL Server)

Why is this behaviour different between the two servers? As the job is owned by sa, I would expect it to run as the service account user (which is what is happening on the 2014 server) but this is not happening on the 2016 server?

What is causing this?

Best Answer

The job is running as the same identity in both cases. This is a difference only in how the information is reported. NT SERVICE\SQLSERVERAGENT is a least-privilege local identity used for running SQL Agent jobs. Like other built-in local Windows identities this account can access network resources using the computer account. So from the local point-of-view Agent is running as NT SERVICE\SQLSERVERAGENT, but from the network point-of-view it's running as the computer account DOMAIN\SERVERNAME$.

I don't know why the difference in reporting between your servers, but it's nothing to worry about.

It looks like sp_whoisactive might get the login name directly from sys.dm_exec_sessions, which returns the domain identity of the login. If you use the security_id to look up the session's server_principal you'll see the local identities differentiated. eg:

select s.session_id, p.name
from sys.dm_exec_sessions s
join sys.server_principals p
  on s.security_id = p.sid
Related Question