November 26, 2013 at 4:10 am
Hello All
We have a 2008 R2 SQL cluster, previously we haven’t had much need for any SSIS jobs but we now have a couple that have been written and we now need to deploy them and get them scheduled etc.
Rather than just deploy them and have done with it I would like to follow best practise where I can as I can see the need for many more SSIS packages in the future.
The current package we are deploying is quite straight forward – it takes some data from one DB and writes it to a table in another DB.
So far to follow best practise I have:
1.Made sure that we use an ‘indirect configuration’ – so the package uses a Env Variable to get the location of the XML config files. This way we can have different DB locations for the local DEV workstation (points to DEV DBs) and for the Server (Points to Live DBs).
2.Specified one XML config file per connection manager.
3.Only configured the connectionstring property for each connection.
We have deployed to the MSDB DB on our cluster and scheduled a job, then changed the XML file connection string to make sure we can change the DBs if reqd and confirmed that it all works.
My concern now though is on security – at the moment the connectionstring in the xml file contains the user and password for each connection. Which I don’t like for obvious reasons. I am thinking that best practise is probably to create a windows authenticated login and give it access to only what it needs on the source and target DBs. Then use a proxy account and run the job as this user. (And also obviously take the user and password out of the connection string)
Could anyone confirm this? Also are there any other best practise recommendations which I should be considering at this point?
Many thanks In advance for any guidance
David
November 26, 2013 at 4:17 am
I would indeed try to use Windows Authentication as much as possible, with dedicated domain accounts who have just enough permissions to run the package. In SQL Server Agent, use proxies coupled to those domain accounts to schedule the jobs.
Regarding your best practise #3: why is it a best practise to only configure the connectionstring? I configure the servername and initial catalog and let SSIS create the connection string.
If you ever upgrade all of your packages to a newer edition of SQL Server, your own configurations - with the native OLE DB provider embedded in them - might break the packages.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 26, 2013 at 5:04 am
Thank you for the reply.
I got that from in here:
http://msdn.microsoft.com/en-us/library/cc671628.aspx
David
November 26, 2013 at 5:26 am
DJH-445911 (11/26/2013)
Thank you for the reply.I got that from in here:
http://msdn.microsoft.com/en-us/library/cc671628.aspx
David
I guess Jamie didn't have to upgrade a lot of packages and changing the native provider in each of his connectionstrings when he wrote that article 🙂
This isn't a right or wrong debate, I just differ in opinion from Jamie and he has some valid points as well.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply