The scenario is probably pretty common. There is a lot of new DTS package development work by a number of people at our location,
and others. The target for installation is a production server located elsewhere. Everyone here has access to the production server,
to look at data, perhaps make schema changes, push DTS packages, etc. This access is allowed to help expidite the development
process.

The "rule" is that you are ONLY supposed to have SQL connection strings in DTS connection objects - AND - there is a standard of
placing this string in Global Variables, for use in script. But it CAN happen, of course, that in the heat of the moment, someone
can hard code a connection string into an ActiveX script. The test package could then run awry, and destroy important information on
the production server.

I am wondering if there might be some easy way to turn access to the production server off and on. It would need to be a quick easy
thing to do. The production server is in a different domain, and we use NT authentication. From the development domain, where the
change needs to be initiated, we do not have the adminstrative rights to change security settings on the production server. We are
hoping there may be a solution to change access in our domain (perhaps without resorting to separate logons for the function).

If the packages are coming over from DEV then the Hard Coded strings should
be pointing to the DEV system no?
I presume that you set the Global Variable at Runtime and replace with the
values necessary for each environment.
You should really check each package as it comes through to you on the
production server, allowing DEV to push their packages through to LIVE with
no checking would not be my preferred way of doing things.

Turn access to the production server to whom or what?

You could remove guest from MSDB.

How are the packages being called?


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lee Gillie" <ANTISPAMIFICATION_lee (AT) odp (DOT) com> wrote

Allan - This is definitely a shot-in-the-dark question. But I appreciate your considering it.

"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Not necessarily. A package may have been written at, or placed into service at the production server. Then enhancement may be
needed, so it is shipped down (or back) to us. We then "localized" it, but miss an inadvertent hard-code.

The "rule" is that you are ONLY supposed to have SQL connection strings in DTS connection objects - AND - there is a standard of
placing this string in Global Variables, for use in script. But we are trying to protect against cockpit errors, where either
someone fails or forgets, or the package (incorrectly) contains hard coded connect strings in ActiveX script.

Agreed, and I hope I presented this is the goal. But we are looking for a mechanism, which may or may not exist, that can help us
provide a level of protection for errors - hence the question.

If we could temporarily make it so the developer can not connect to the production database, and only his sandbox database, from his
workstation, that would pretty much accomplish it. Two functions like "dissallow this workstation to connect to this SQL Server",
and antoher "allow connection again", which we could toggle from our side.

From the development domain, where the change needs to be initiated, we do not have the adminstrative rights to change security
settings on the production server.

Currently developers are running from their workstations, in EM, and are SUPPOSED to be running against a sandbox development server
we have locally. So they may be running a package, or a single task, etc.

Allan - I really doubt there is a simple solution. But I thought I would ask in case someone had found a really nice way to deal
with this.

1. The only real way of knowing is to QA the package.


We implemented something sort of similar but in your case it may not work.

We checked the name of the Server on which the package was executing. We
then checked the name of the servers to which the packages wanted to
connect. There were combinations that were allowed and the rest not. So a
package executing on a production server would not be allowed to run against
a Dev box and vice versa.

From what I can see only you are only going to be able to change the
packages and nothing more.

I would look to add a flag to the package specifying "DEV", "PROD" and then
check that against your connections.

Do the Dev team execute the packages in LIVE and in DEV?

If you cannot change anything on the SQL Server then you have to implement
the workaround in the package.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lee Gillie" <ANTISPAMIFICATION_lee (AT) odp (DOT) com> wrote