SQL Server Performance

How to safely over-ride user credentials in data sources

Discussion in 'SQL Server 2005 Integration Services' started by araskas100, Sep 14, 2007.

  1. araskas100 New Member

    A developer working on an off-site location in his own dev environment send a .dtsx file to move it to the production server in your environment and schedule it as a sql job. The package basically moves a set of data from source SQL server to a destination SQL server. I can only make sql authenticated connections to the source and destination and I do not keep dev studio on production servers and all production and dev servers are in different domains.
    To deploy this package I import teh package to the sql server and save it to the msdb database and schedule it as a sql job. In the job, I override the data sources and the user id credentials. The job runs fine.
    When you override the userid and password in the data sources, the password is not displayed, but if you or other user who has access to the job can script out the job, and the password can be seen in plain text. The same will apply even if you use a config file for the package. Another user, even if he has admin access on the server, should not be able to see the password in plain text whether it is embedded in a job, in a config file or in a registry. Just as you can't see another login's password even if you are a sys admin on a sql server you can only reset it.
    My question is how we can over-ride the package data sources, without opening the package in dev studio.
  2. satya Moderator

    Don't you have same data sources defined on the Production, I beleive that is best practice to reduce such admin tasks for re-writing the data sources when the package is moved.
    Moreover if the current login is a SYSADMIN privileged then such login access issues can be avoided.
  3. araskas100 New Member

    Thanks for the response. Developers usually work on a their own copy of the databases and once the packages are moved to production, they cannot have access to production. Even if same login is mapped on dev and prod servers, the passwords will bedifferent. So during the package move, the DBA makes the appropriate credentail changes. This is how it used be when moving DTS packages. Since the designer for DTS was part of SQL Server, it was possible to encrypt the credentials by opening the package in dts designer.

Share This Page