January 12, 2009 at 11:53 am
I am setting up many IS packages to load data from flat files to tables. I have connection managers set up for the databases I need to access. I am setting up the connection managers with Windows authentication, so that I won't have to ever worry about changing the password in all of the packages, if necessary. The other consideration is that in some of the packages I need the ability to TRUNCATE tables, so I will need to give the service account db_ddladmin rights for that database. With more and more packages being added I am afraid that the service account will need more permissions. So my dilemma is, should I use different SQL logins with just the permissions needed, but will have to update each package if the ID's password changes or just use Windows authentication and just add needed permissions to the service account? All thoughts are appreciated.
January 12, 2009 at 11:56 am
You might be better off having a "low authority" account, and a "high authority" account. Only use the high authority one where it's needed, use the low authority one everywhere else.
Would that accomplish what you need?
- 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 12, 2009 at 12:43 pm
By using a high and low authority accounts would be using two SQL logins, and would require changing all of the packages if it is decided that the password needs to be changed for these accounts, plus I would have to remember all of the packages that had these IDs. That is why I would like to use Windows authentication. But by using Windows Authentication I would be using the SQL Server Agent account for all of the packages and would have to place any permission needed for any package on the one account. So which is worse having to "touch" all of the packages or having one account that becomes more powerfull with each additional permission? I know that SQL 2005 says to make service accounts a domain account with as little access as possible. I want it to be as simple as possible, but am I causing too much exposure with Winodws authentication? I was wondering how other shops do it.
January 12, 2009 at 12:59 pm
I'm talking about Windows accounts. Have one that's a sys_admin (full rights to everything), and use that where it's needed. Have another that has minimal rights, maybe datareader, datawriter and execute. Then use those.
- 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 13, 2009 at 12:56 pm
GSquared,
Thank you very much for the information. Being so new to SSIS I was stuck in a SQL2000 frame of mind and was thinking that if the job is scheduled it will be running under the default SQL Server Agent ID. I am now reading up on Proxy accounts and trying to mold my method of operation around that. Thanks again.
January 13, 2009 at 3:18 pm
And I'm so used to 2005 these days that it didn't occur to me to mention that was what I was talking about. Glad you figured it out.
- 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 13, 2009 at 3:29 pm
We're using Config files for all our SSIS packages to improve their portability. Then if I need to switch between two accounts for an SSIS package, I can just edit the config file in notepad and I'm done.
January 14, 2009 at 7:25 am
Karin, I am not familiar with using config files with SSIS can you show me an example of what you are talking about or perhaps a website showing this?
January 14, 2009 at 9:00 am
I have a question I have several packages that I run on several different servers both by using SQL Agent for the Standard and Enterprise editions and using a Standard edition to maintain many Express editions.
The permission to execute the package in this case is that of the SQL server agent. You can also take any user on the machine and create a certificate and operator and use that operator. None of this is part of the SSIS package this is how it is configured when it is placed in a job. Is this where your issue is?
In this case you should be able to create a user for each group of jobs that can reach a set of servers to accomplish the task using windows authentication. However, if you need to reach out to a source where you can't use windows authentication, you will then have to set the login information in the config file(s). In our case the as400 admins have created a couple of logins to access the data the windows group needs and since these are the same from every machine we embed them in the package and don't expose them to the config.
I'm not sure I have answered you question but you have a lot of options at this point.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply