April 4, 2012 at 2:13 pm
I want to run a connection manager inside a SSIS package with a separate windows account. I know the following expression works ,but it will use my account.
Data Source=myservername;User ID=mydomanin\username;Password=12345;Initial Catalog=DB;Provider=SQLOLEDB;Integrated Security=SSPI;
I want to use a different windows account to run this. Which flag should i use? Is this even possible to run a connection manager under different windows account?
i ) I cant make this account as proxy because there is already another account as proxy.
ii) Package runs fine if i give access to this proxy account, but my goal is to run under totally different windows account.
Any help is appreciated.
April 4, 2012 at 11:39 pm
Create another proxy and use that one?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 5, 2012 at 8:39 am
Koen Verbeeck (4/4/2012)
Create another proxy and use that one?
Hmmm....i guess i could. Goal here is to make use of windows account in a connection string. I know you could easy make use of sql account
April 5, 2012 at 8:50 am
As far as I know, that isn't possible, since it would compromise the inherent security of Windows authentication by exposing the password. You could have a look in connectionstrings.com - I'd be interested to be proved wrong! Your only options are to run whatever application is doing the connection in the context of the different user, either using runas (or the GUI equivalent) or the proxy account in SQL Server.
John
April 5, 2012 at 2:07 pm
I think you would need to split the functionality if you want to use Windows Authentication for both since a process can only have one identity. If you can refactor this into two packages you could execute the second package with DTExec using runas through the Execute Process Task of the original package, and provide a separate Windows Cred to runas...but that is certainly not within the realm of what I would call a Best Practice. Is using a SQL Server Login for the connection an option?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 5, 2012 at 2:35 pm
Depending on which type of connection you're setting up, you've got conflicting instructions.
integrated security=sspi
means "use the current windows credentials over Kerberos", so then passing the user ID and password is unnecessary. (unnecessary in this context means SSIS ignores them and uses the logged on user context ).
So in essence - as of now it's already using the service credentials if you run the package from the server. If you're testing this locally, then it will use your credentials.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 5, 2012 at 2:49 pm
Matt Miller (#4) (4/5/2012)
Depending on which type of connection you're setting up, you've got conflicting instructions.
integrated security=sspimeans "use the current windows credentials over Kerberos", so then passing the user ID and password is unnecessary. (unnecessary in this context means SSIS ignores them and uses the logged on user context ).
So in essence - as of now it's already using the service credentials if you run the package from the server. If you're testing this locally, then it will use your credentials.
Thanks. True, i am aware of that. I just want to run under different windows account other than mine. Seems like it is not possible , you can easily use a sql account but won't let me use a different windows account.
April 5, 2012 at 2:53 pm
sqldba_newbie (4/5/2012)
Matt Miller (#4) (4/5/2012)
Depending on which type of connection you're setting up, you've got conflicting instructions.
integrated security=sspimeans "use the current windows credentials over Kerberos", so then passing the user ID and password is unnecessary. (unnecessary in this context means SSIS ignores them and uses the logged on user context ).
So in essence - as of now it's already using the service credentials if you run the package from the server. If you're testing this locally, then it will use your credentials.
Thanks. True, i am aware of that. I just want to run under different windows account other than mine. Seems like it is not possible , you can easily use a sql account but won't let me use a different windows account.
Well this is where OPC.three's comment comes into play.
If you want to test using a different user logon, even locally, then use the DTEXEC function to run the package locally, using the "logon as" function from the OS (this would assume you're on a vista or Win7 machine). Whichever credentials you use would be the ones running the package.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply