SQL 2005 Security Policy Enforcement and SSIS

[Ovatvvon]

With each incoming SQL 2005 box we implement, we use the security policy enforcement, so that the policy takes on that of our network (i.e. Password changed every 90 days, etc.)

We have implemented an intranet site to let users change the password on their SQL ID's easily enough; however, we are trying to figure out a good way to update passwords for connections in SSIS packages. We have debated putting them into INI files and pulling them dynamically so the packages do not have to be redone every 90 days (which would be More than a headache with hundreds of packages for each client), but then the password is placed in clear-text. We've also thought about putting it into a database, and encrypting the database, but then you have all the overhead of the decryption from there with all the packages. Additionally, I thought about just using a password hash (encryption) for the password itself, and have the SSIS package decryption the hash for the password, which would hopefully not have as much overhead as an encrypted database, but still provide adequate security for the passwords.

These are the only options we can think of, and aren't 100% happy with any of them. Are there any other tricks anyone out there uses to implement the password policy while using SSIS packages? Is there anything better anyone can think of to do?


-Ovatvvon :-Q

 

[mrdenny]

Um, NT Authentication.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

What are you doing with the SSIS packages that they can't use a common account to run under?

We have a domain user account mapped to our SQL Server that owns all the jobs running the packages and the packages use Windows NT Authentication (as Denny mentioned). Works perfectly and doesn't matter who runs the job because the job is always run as the owner.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"