Monday, March 8, 2010

Security Architecture of SSIS Packages

One thing that I am thinking about lately is how to make sure that package running in Development or Test environment does not change something in Production? Such problem may occur when:

  1. a DBA forgets to update the configuration file
  2. test or development is done in mixed environment (for example,  test DW servers are loaded from Production OLTP servers)
  3. same connection (and login) is used for both reading and writing…

One way to solve that would be to use different logins in these environments. SQL Server logins (now that they are more robust in (SQL Server 2005) comes to mind as a potential solution.

Another solution is to give developers two sets of Windows accounts: regular login for everyday development work and a special login for firefights in Production  environment. That way a developer will not be able to mix up the privileges and accidentally change something in production.

No comments:

Post a Comment