MSSQL - Running scheduled jobs with alternate domain credentials

[josh.koch]

Greetings folks, I'm in a sticky situation where the corporate red tape has put me into the following position:

I have 2 MS SQL databases, and I need to copy select tables from database #2 back to database #1 (call them DBProd and DBTest, respectively) . The problems arise in that windows authentication is required, and neither machine has a common account with domain permissions. Nor can any changes be made to either the database or windows environment on DBProd.

It boils down to the fact that we'll need to run a single scheduled job under alternate credentials then the scheduler service itself, which is normally run under a limited-rights domain account that is disallowed on DBProd.


Any assistance would be greatly appreciated - given the near-legendary inflexibility of many corporate security folk, I have to believe that this problem has been addressed before. Probably with some suitably embarrassingly simple solution that I should have seen in 5 minuets, 3 weeks ago . . . : )

(This whole mess is caused by the security differences between a testing environment and production environment in our corporate network - oh, joy.)

Thanks

Josh

[skhanal]

Do you use windows authetication only or mixed mode?

Are the machines in domain or are they stand alone (meaning not part of domain)?

You should be able to create a DTS package with two different login credentials, one for source one for target, and copy table. The login could be either windows domain account or sql login.

[rmiao]

It's not unsure. If servers are in different domains and domains are not trusted each other, nothing you can do to let them talk with windows authentication. You can copy prod db's backup file to dev server and restore it.

[josh.koch]

Both the servers are in the same domain, so that's not an issue, and it's the creation of a single DTS package with different logon credentials that I've found to be so difficult. If you've accomplished this, I'd very much appreciate some pointers as to how to go about it.

Thanks

Josh

[rmiao]

Different logon credentials should be ok as long as they have proper permission in both servers.

[josh.koch]

We have experimented with using different credentials by specifying global variables in the DTS packages, but those attempts failed. What method would / have you used successfully?

[rmiao]

For table copy, you can specify login id for source and target servers. Don't know how you built the package.