October 4, 2012 at 6:30 am
Hi,
Last week I got our IT manager to install SQL Server on a newly built utility server. As I wanted SQL Server DB + SSIS + SSRS installed, I suggested to him that he creates 4 new domain accounts, one for each SQL Server service.
This week I've been working on some SSIS packages but when I tried to run one package via a SQL Agent Job, the package ran successfully but my package error logging is telling me that the package wasn't able to establish connections to other SQL Servers (this works when I run the package manually).
I altered the Protection level in the package but that made no difference. So I'm pretty sure the problem is with the SQL Agent domain account.
My question is, what rights are required for my SQL Agent domain account to be able to connect to other SQL Servers in the domain using windows authentication?
Thanks.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
October 4, 2012 at 6:41 am
Basically the SQLAgent account that you're using has no rights on other server(s).
So Adding the SQL agent account to the other server and granting it access rights to the server and databases will enable it to extract data from these servers.
Min rights would be an account at server level then Data_Reader rights on the databases you want to read from.
Edit: forgot the bit about rights.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 4, 2012 at 7:18 am
Thanks Jason.
I'm officially an idiot. The domain account is just fine but I forgot to create the login on each SQL Server I'm connecting to (in the trusted domain).
But I'm not sure which role to assign to the login. I gave it sysadmin role and the package worked just fine but I feel that it's too much to give. Would I have to create a custom role so the SQLAgent account can only read from the databases?
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply