Looking help for login error.
I have configured the brand new server for dedicated ETL process with the service account as Microsoft virtual account NT Service\MSSQLSERVER and Sql AGENT ACCOUNT is NT SERVICE\SQLSERVERAGENT.
We have created SSIS package on ETL server and which is executing and inserting data into DW1 Server.
When i am running the SSIS package manually then i am getting following error:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Could not find a login matching the name provided(in DW server Error log)
Then I have created Agent job and made owner as 'DOM\ETL01' then i am getting following error:
Login failed for user 'DOM\ETL01$'. Reason: Could not find a login matching the name provided (in DW server Error log)
I also changed Job owner as 'SA', still same error
I have also tried to change the Sql service account from 'NT Service\MSSQLSERVER' to 'DOM\ETL01' but error remaining the same. I have restarted the sql services (MS Sql Server, Sql Agent and SSIS) after changing the service account.
In both the servers, all these accounts (DOM\ETL01, NT Service\MSSQLSERVER, NT SERVICE\SQLSERVERAGENT)
having Sysadmin role. I have also added Permission into Database and Schema level.
I have also added SSIS_Admin Role for these accounts into ETL server .
Thanks for your help!