January 2, 2013 at 10:01 am
Greetings!
I am currently managing about 50 SSIS packages, all set to EncryptAllWithPassword, and all are using the default "sa" username/password setup to access production data (bad, I know).
We are upping security and removing all uses of "sa." In addition, all passwords that touch production must be able to change on a schedule. This means each developer touching the packages will need to utilize their own username/password login in all of the connection managers while developing, but when the packages are deployed to the production server and run as SQL Agent Jobs, they need to utilize "Windows Authentication" because the SQL Server Job service account is tied to a windows account. The goal is: we only want to have to change the passwords in one place when they expire.
We COULD *manually* switch each and every production connection manager in all of the packages to our specific usernames/passwords while developing, then remember to switch them back to "Windows Authentication" before we saved the packages to the servers, but I'm trying to come up with a less painful and more elegant (automated) solution.
Has anyone done this before? I'd love to get some advice on this.
Thanks in advance!
January 2, 2013 at 10:55 am
You should be able to make this fairly easy to manage using package configurations to control the connection strings for the different connections.
But it will take a bit of setting-up initially, I'm afraid.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 2, 2013 at 11:49 am
As Phil suggests package configurations are the way to go here.
January 2, 2013 at 12:43 pm
Thanks, Phil and Daniel!
I've already spent a few days researching and setting up the package configuration table (actually using a View which filters the rows on HOST_NAME() per this SQLSC article): http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/66426/
I think I have my configuration table set up properly, and everything I've read tells me my approach should work, but when I try to execute the package, Visual Studio totally crashes out (program has stopped responding). After the first run, every time I try to open the package after that, VS crashes on opening the .dtsx file.
The view I created returns two rows from the [SSIS Configurations] table for the following "PackagePath" properties on my connection manager:
\Package.Connections[ProductionData].Properties[UserName]
\Package.Connections[ProductionData].Properties[Password]
I've seen other articles reference modifying the "ConnectionString" property itself, but that seems like a messy approach. I'd have to mess with values within the middle of that big text string in order to set the username and password values. It seems like it would be much more straightforward to set the username and password values directly in their own properties. Do I HAVE to alter the ConnectionString in order to accomplish what I'm trying to do?
Thanks again!
Ian
January 2, 2013 at 12:52 pm
I usually simplify this kind of thing by setting up an SQL Server account called something like "SSISUser". Limited rights, etc. Then all normal SSIS packages just use that.
Any packages that need higher rights, get their own specialized SQL Server account with the exact rights they need. I think that's happenned twice in the last 7 years.
That approach makes it very easy to manage that kind of thing.
Do the same kind of thing for applications (including websites). Make them all use "AppUser" or some such similar name.
Of course, I'm usually forcing almost all database access to be via procs, so the AppUser account just gets execution rights on procs and no direct table/view access. SSIS can't really work that way, so it needs its own access.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 2, 2013 at 1:14 pm
iglinka (1/2/2013)
Thanks, Phil and Daniel!I've already spent a few days researching and setting up the package configuration table (actually using a View which filters the rows on HOST_NAME() per this SQLSC article): http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/66426/
I think I have my configuration table set up properly, and everything I've read tells me my approach should work, but when I try to execute the package, Visual Studio totally crashes out (program has stopped responding). After the first run, every time I try to open the package after that, VS crashes on opening the .dtsx file.
The view I created returns two rows from the [SSIS Configurations] table for the following "PackagePath" properties on my connection manager:
\Package.Connections[ProductionData].Properties[UserName]
\Package.Connections[ProductionData].Properties[Password]
I've seen other articles reference modifying the "ConnectionString" property itself, but that seems like a messy approach. I'd have to mess with values within the middle of that big text string in order to set the username and password values. It seems like it would be much more straightforward to set the username and password values directly in their own properties. Do I HAVE to alter the ConnectionString in order to accomplish what I'm trying to do?
Thanks again!
Ian
Hi Ian
We use the ConnectionString method and it seems to work well.
Once it's set up, there is no 'messing with values' required, so I'm not sure that your concerns are completely valid.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply