December 14, 2011 at 4:33 am
We have planned to run the ssis packages using windows authentication.
Could you please suggest below steps is ok.
1. Source (SQL Server 1)
2. Stage & DW (SQL Server 2)
SSIS job execution Windows Authentication
1. Create a Service account and run the SQL Server services using this ID in Server 2
2. This Service account should have the rights to access the Server 1 (Source) DB
3. Map this Service account with database role to server 2. (staging & DW)
4. Create the Job and add this service account in credential and map the same to proxy
5. In the Job Step, set the Run as = to the Proxy Account
6. In the SQL Server Agent -> Proxies - > the proxy should either be in Operating System (CmdExec) if package is executed using dtexec or in SSIS Package Execution if we directly execute the SSIS package from the job using this proxy.
The Service account should be given in "This account" under Log on tab of sqlserver services..and restart the services. Is it right?
December 14, 2011 at 5:36 am
gugan_ta (12/14/2011)
We have planned to run the ssis packages using windows authentication.Could you please suggest below steps is ok.
1. Source (SQL Server 1)
2. Stage & DW (SQL Server 2)
SSIS job execution Windows Authentication
1. Create a Service account and run the SQL Server services using this ID in Server 2
2. This Service account should have the rights to access the Server 1 (Source) DB
3. Map this Service account with database role to server 2. (staging & DW)
4. Create the Job and add this service account in credential and map the same to proxy
5. In the Job Step, set the Run as = to the Proxy Account
6. In the SQL Server Agent -> Proxies - > the proxy should either be in Operating System (CmdExec) if package is executed using dtexec or in SSIS Package Execution if we directly execute the SSIS package from the job using this proxy.
The Service account should be given in "This account" under Log on tab of sqlserver services..and restart the services. Is it right?
Almost correct 🙂
If you assign the service account to the SQL Agent service - your last sentence - then you don't need a proxy.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 14, 2011 at 8:06 pm
Thanks
So, if my understanding is correct. This service account will act as windows Authentication while running the ssis job.
am i right?
December 15, 2011 at 12:14 am
gugan_ta (12/14/2011)
ThanksSo, if my understanding is correct. This service account will act as windows Authentication while running the ssis job.
am i right?
Yes. So you need to make sure the service account has it's login mapped in the database engine.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply