June 10, 2020 at 9:29 pm
Hello,
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!
June 10, 2020 at 10:09 pm
Is the code that is executed in these packages trying to use a linked server? If so - then the only way to fix that is to setup and validate Kerberos.
Note: I see no reason to use a linked server in any code executed from SSIS - it just doesn't make sense to call code on ServerA - that reaches across a linked server to ServerB to return data. Create a connection in SSIS to ServerB and pull that data directly.
Either way - you need to create a proxy account and grant that proxy account the required permissions in all systems being access by your ETL packages. Then - from within SQL Server Agent you run the Integration Services project using that proxy account.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 11, 2020 at 3:16 pm
Thanks Jeff.
We are not using the linked server and both the servers are in the same domain like Dom1\Sql1 and Dom1\SSISSql1.
We are using sql service account is Mcrosoft cirtual account and this account having SysAdmin Role in both the servers.
Actually i have also added another domain account as a service account instead of Microsoft virtual account (Restarted Services) , assign SysAdmin role and also added into other server with SysAdmin role plus assign permissions for specific database but still having error.
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)
June 11, 2020 at 5:55 pm
How are you running the SSIS package *manually*? What are the exact steps you take?
When you run the package from SSDT/VS - do you get this error?
How are the connections setup/configured in the package(s)?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 11, 2020 at 9:12 pm
Thanks Jeff.
We are still in testing mode so we are running through SSIS server ==> Integration Services Catalogs => SSISDB => SalesFolder => Projects => PAckages ==> Sales.dtsx (Execute from here)
Another, we have set up the job and running job through Sql Agent which we have assign the job oqner who have SysAdmin Role.
connection are setup using connection Manager like normally we set up using Server name, Database name ...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply