Executing a job with Triggers across linked server - best way

Hello. I've got a SQL Server 2012 instance called SS12 on server SS12, and a SQL Server 2008R2 instance SS08 on server SS08. SS12 runs Windows Server 2012 R2 Datacenter. SS08 runs under Windows 7 SP1. The SS12 tables are updated from external user applications and from batch jobs. My mission is to keep tables on SS08 in sync with tables on SS12.

The approach I've taken so far is to:

  1. Create a linked server from SS12 to SS08, with self-mapping logins (i.e., @useself=True)
  2. Created triggers on the SS12 tables that copy inserts and updates over to the SS08 tables.
  3. Created a stored procedure that performs batch updates to the SS12 tables and fires the triggers.
  4. Set up a Job on SS12 that executes the stored procedure. I set the job owner as 'DOMAINNAME\Administrator'.

After several attempts, I was able to get the first 3 steps working in harmony and keeping SS08 in sync with SS12. Where I ran into trouble was when I implemented Step 4, the SQL Agent job, which kept throwing this error:
"Executed as user: NT SERVICE\SQLSERVERAGENT. Login failed for user 'DOMAINNAME\SS12$'. [SQLSTATE 28000] [Error 18456]. The step failed."

I tried setting "Run as user" on the job step to my userid 'DOMAINNAME\dhb' (I'm a sysadmin on both servers and dbowner on the referenced databases) and got this error:
"Executed as user: DOMAINNAME\dhb. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] [Error 18456]."

After running down numerous blind alleys, I finally figured out that I had to create a Login for DOMAINNAME\SS12$ on SS08, and add it to the db_datareader/writer roles for each database. And I had to leave "Run as user" blank on the job step. It now works, but it leaves me with some lingering questions, and wondering if this is the best approach from a security standpoint.

  1. Why does the SQL agent run as NT SERVICE\SQLSERVERAGENT but passes the login DOMAINNAME\SS12$?
  2. Why is my current security context not trusted, and how do I make it trustworthy?
  3. In the SQL Server Agent (MSSQLSERVER) properties, the account name is NT Service\SQLSERVERAGENT. I have no idea what the password is. Should this be changed to another account?
  4. I read a blog about running SQL Server agent that referenced a Group called SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER under Computer Management/Local Users and Groups. The article says that "this group is pre-configured with all the required permissions to run the service". I don't see this group on either of my servers, SS12 or SS08. How do I get to it?
  5. One other scenario that worked was specifying a remote user in sp_addlinkedsrvlogin:
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SCMSQL', @useself=N'False', @locallogin= NULL, @rmtuser=N'DHB', @rmtpassword='####' . Is this a more or less secure approach than self-mapping?

Thanks in advance for any

September 14th, 2015 8:47pm