RE: ssis, sql job and windows authentication - how to impersonate
- From: Sean McCown <SeanMcCown@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 1 Feb 2008 11:56:09 -0800
You'll want to setup an agent proxy. The process is pretty well documented.
One thing it doesn't mention though is that you need to give the proxy acct
dtsoperator group rights in msdb.
But, y... setup a proxy acct with whatever acct you like. Then you can set
the job step to run under that proxy acct.
--
Read my book reviews at:
www.ITBookworm.com
Blog Author of:
Database Underground -- http://weblog.infoworld.com/dbunderground/
DBA Rant – http://dbarant.blogspot.com
"l" wrote:
Let's say I have ssis package that collect some data from N sql servers (all.
on the same domain). All connections are windows authentication.
I want to put this ssis on a sql server (in the same domain). I want this
ssis to run under particular domain account to go browse my N sql servers and
some additional non sql data (aka files on
\\myserver\mydatafiles\etcdatastorage ...).
so my ssis call would be looking kind of like this if recorded from the
job's step (names, sql server count, databases are modified)
/SQL "\ForCollector\SysCatRef" /SERVER prosql1
/CONNECTION "prosql1.db1";"\"Data Source=prosql1;Initial
Catalog=db1;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto
Translate=False;\""
/CONNECTION "prosql1\repository.db2";"\"Data
Source=prosql1\repository;Initial Catalog=db2;Provider=SQLNCLI.1;Integrated
Security=SSPI;Auto Translate=False;\""
/CONNECTION "sql.db3";"\"Data Source=sql;Initial
Catalog=db3;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto
Translate=False;\""
/CONNECTION "sql1.db4";"\"Data Source=sql1;Initial
Catalog=db4;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto
Translate=False;\""
/CONNECTION "sql1.db5";"\"Data Source=sql1;Initial
Catalog=db5;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto
Translate=False;\""
/CONNECTION "sql1.db6";"\"Data Source=sql1;Initial
Catalog=db6;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto
Translate=False;\""
/CONNECTION "sql2.db7";"\"Data Source=sql1;Initial
Catalog=db7;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto
Translate=False;\""
/CONNECTION "sql3.db8";"\"Data Source=sql1;Initial
Catalog=db8;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto
Translate=False;\""
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /LOGGER
"{6BB833A1-E4A2-4431-831D-DE695049DC61}";"prosql1\repository.db2" /REPORTING E
when it executes windows authentication is whatever it is sql server agent
is running under on the server where my job is.
Do not really want to give to this particular sql server agent account any
access rights to all the sql servers and data storages, I'd like to have a
different domain account there. Question is how to do it nicely/impersonate
from a sql job.
REM: job owner has nothing to do with authentication in this case.
- Prev by Date: RE: Error while executing the SSIS package
- Next by Date: ssis oracle to sql server
- Previous by thread: RE: Error while executing the SSIS package
- Next by thread: ssis oracle to sql server
- Index(es):
Relevant Pages
|
Loading
