Multiple Connections AND Multiple Impersonations
-
I have a single package that pulls data from one database (A) and deposits it in another (B). I would like to be able to set it up such that, when connecting to database (A), I connect as one windows user, and when I connect to database (B), I connect as another, different windows user. I can easily impersonate a single user (by running the package as that user) but not two. So far, I'm unable to figure out how I can do it. The closest I've come is a script task which basically impersonates each user, then creates the connections:
' Impersonate TestUser1 for Conn1 '
Using System.Security.Principal.WindowsIdentity.Impersonate(GetWindowsIdentity("TestUser1", "TestDomain", "***").Token)
oConnBuilder.ConnectionString = Dts.Connections("Conn1").ConnectionString()
oConnBuilder.Item("Integrated Security") = "SSPI"
Dts.Connections("Conn1").ConnectionString = oConnBuilder.ConnectionString().Trim()
Dts.Connections("Conn1").AcquireConnection(Nothing)
End Using
' Impersonate TestUser2 for Conn2 '
Using System.Security.Principal.WindowsIdentity.Impersonate(GetWindowsIdentity("TestUser2", "TestDomain", "***").Token)
oConnBuilder.ConnectionString = Dts.Connections("Conn2").ConnectionString()
oConnBuilder.Item("Integrated Security") = "SSPI"
Dts.Connections("Conn2").ConnectionString = oConnBuilder.ConnectionString().Trim()
Dts.Connections("Conn2").AcquireConnection(Nothing)
End Using
Each connection in the connection manager has RetainSameConnection = True. This works fine until SSIS needs to spawn additional threads to perform its work. At that time, the connections revert back to the original user running the package (I assume because my impersonation is not making it to the newly spawned thread).
Is this kind of impersonation even possible in SSIS / SQL 2005? Any ideas or hints would greatly be appreciated. Thanks!
Jason
-
Jason,
Why not use database logon and avoid dealing with user impersonations?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Hi... thanks for the response. I've considered that, and in fact, my design takes this in to account such that, when the custom uses SQL credentials for at least one of the two connections, I'm able to accomodate both. Unfortunately, many of my customers only use windows accounts for their service accounts, so I need to accomodate this scenario the best I can.
Jason
-
I don't think you will be able to solve the problem with the impersonation. Check this article.
Having said that there are 2 solutions I could think of:
1. Limit the package to be able to use only 1 execution thread.
2. Make the transfer in 2 steps:
- export the data to raw data file using account A.
- import from raw data file using account B.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Thanks again for the response... I was afraid of that. I do see those other options. Limiting the package with MaxConcurrentExecutables=1 does seem to work, but at a (signficant) cost to performance. Most of my packages take advantage of a lot of parallelism.
The other option of splitting the data moves into 2 steps is something I didn't consider yet. I certainly complicates things significantly for me and my developers, but is a workable solution.
A final option I might consider would be to create my own connection manager, which would accept both Windows and SQL credentials, and perform the impersonation itself. I'll have to spend some time researching this, though... I haven't extended SSIS in that way yet. I'm always up for a challenge...
Jason
-
Jason (5/22/2009)
Thanks again for the response... I was afraid of that. I do see those other options. Limiting the package with MaxConcurrentExecutables=1 does seem to work, but at a (signficant) cost to performance. Most of my packages take advantage of a lot of parallelism.The other option of splitting the data moves into 2 steps is something I didn't consider yet. I certainly complicates things significantly for me and my developers, but is a workable solution.
A final option I might consider would be to create my own connection manager, which would accept both Windows and SQL credentials, and perform the impersonation itself. I'll have to spend some time researching this, though... I haven't extended SSIS in that way yet. I'm always up for a challenge...
Jason
Implementing your own connection manager is certainly an option, but when you consider the fact you will not be able to use in any of the standard data flow components it becomes useless basically. What would be great if the standard connection managers supported this feature themself. Try to submit suggestion to the SQL Server team. But don't hold too many hopes.
I have another suggestion for you. Our company CozyRoc has the so called Data Flow Source / Destination which can be used as replacement for the Raw File Source / Destination, but without the need for intermediate temporary file creation. The components are based on CozyRoc Package Connection Manager and this manager supports execution of other packages other than the current. We currently don't provide impersonation options for the executed package, but if you are interested to investigate this option we may extend it to support your scenario.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Jason,
I'm thinking about the following idea:
1. Setup the database connection manager with RetainSameConnection property set to true.
2. Do the impersonation process and AcquireConnection from the connection manager.
At this point you should have properly impersonated connection, which will stay the same no matter how many components use it or from whatever thread.
Can you give this idea a try and let us know how it goes?
p.s.
Ignore this. I just reviewed your initial posting. I'm thinking about another idea. Setup your job step to use different proxy account for the required database and then execute the package thru the SQL Job agent.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Using the SQL Agent proxy is how I managed to impersonate 1 account, but of course, not the other. I managed to write my own connection manager. I don't think I'll go with this for my production software, but as a proof-of-concept it worked well ... we'll see. I'm trying to avoid having customers run an installation on the SSIS server, if possible.
... now, if only there was a way to deploy a component to a computer, and to the GAC without running on that machine. 😉
Jason
-
Jason (5/26/2009)
Using the SQL Agent proxy is how I managed to impersonate 1 account, but of course, not the other. I managed to write my own connection manager. I don't think I'll go with this for my production software, but as a proof-of-concept it worked well ... we'll see. I'm trying to avoid having customers run an installation on the SSIS server, if possible.... now, if only there was a way to deploy a component to a computer, and to the GAC without running on that machine. 😉
Jason
CozyRoc SSIS+ includes the so called Script Task Plus, which allows reusable scripts. But you have to first install SSIS+ library of course.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply