July 15, 2011 at 6:11 am
.....And here is my request for help on this long description......
I have to write a SSIS pkg, with each Task/Component OR each Connection configured with own Username & passwords. Can this be done..
Ok, here is the background.... 2 datasources lie in 2 different domains which needs 2 different logins Domain1\Login1 & Domain2\Login2 to access them. Now my work, is to automate the task of comparing the data in the tables of these 2 Data Sources (I have a plan to use LookUp for this.... but still unsure if it works)...
I have to configure UserName & Password seperately.. because either of these Logins cannot be added in either of the Servers-- Adding Domain1\Login1 on DataSource2 isn't allowed 🙁
So here is a blueprint ( includes my assumptions too) of the plan...
1). I open BIDS with my credentials - UserDomain\UserLogin; Create 1 connection to point DataSource 1 using Login1 and 2nd connection with Login2 to DataSource 2.
2). When I execute it... each task acquires the credentials from the configured Connections/Tasks/Components.
Does this plan work? ... I tried to manipulate connection strings setting Integrated Security=False and supply logins but server is considering them as SQL Logins and no success there....
--In 'thoughts'...
Lonely Rogue
July 15, 2011 at 9:28 am
Unfortuantely I can't see how this process could work, a program will only ever run under 1 security context at a time. And while you can sometimes change contexts through impersonation and such you are still only using one context at any one time. This is complicated by SSIS which further restricts what you can do.
As I read that 2 sep domains, no chance of any trusts. Even if you use a Script Task for the source and use impersonation I think you are gonna have trouble. With the included tools available, I don't think you can do this.
You said you were doing compares? Have you considered Red Gate Data Compare, I think they offer an API for automation? Just a thought..
CEWII
July 16, 2011 at 11:34 am
Well, thanks for your reply... however, I would like to know few more things....
Can each Connection be individually configured, irrespective of who opens the BIDS... the tasks uses the credentials specified in the Properties of Connections..I see the Password field in the properties of a Connection... what is it.
--In 'thoughts'...
Lonely Rogue
July 18, 2011 at 3:36 pm
For trusted connectivity, the user context running BIDS is the context that is used. You can do a Run As with BIDS if you have the right script installed. However, username and password have no use when you have said the connection is trusted. For SQL authentication they are used. You can configure each connection to use a different login, BUT only for non-trusted connections. the most basic explanation of trusted (or windows conectivity) is that the user logged in IS the user context passed. Without a lot more hoops that I'm not even 100% sure can even be done in SSIS using a script task/component you cannot change the security context. Which is essentially what you want to do.
Coincidently on retrospect I think that you will still have problems with the red-gate tools unless a snapshot was used.
If you could get one of those connections switched over to a SQL login you could do this easily, but two windows accounts, extremely difficult.
I have worked for companies that prefer to use windows logins for database access, however, all of them provided for cases where that wasn't possible in ALL cases. These were little companies and big ones you have heard of. While I prefer windows logins/groups myself I can't categorically rule out SQL Logins.
CEWII
July 26, 2011 at 11:14 am
Thanks Elliott. It seems all the ways has been blocked out....
still, I will see if any way is available..... else, I am all left with configuration changes...
--In 'thoughts'...
Lonely Rogue
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply