We have a legacy AS400 host system that runs our warehouse. We want it to securely trigger jobs on the SQL server that runs the rest of the business to enable automatic and timely data exchange between the two systems.
Currently we have the IBM iSeries Access for Windows client software installed on an old Windows SQL 2005 server. This allows the AS400 to run remote commands on a Windows box. The AS400 uses this client software to run batch files that execute dtexec on the SQL 2005 server box which then runs SSIS packages that exchanges data between the AS400 and the production SQL servers. The SQL 2005 box is just acting as a go between the AS400 and the production SQL servers. The two problems with this setup are:
The AS400 client software runs as a system process and not a network account so the SQL login info has to be included in the plain text in the SSIS files.
When dtexec is run it adds two entries into the Windows event log. We have several process that poll for new data every few minutes or in some cases seconds so the event log is consistently purging older entries which leaves about 4 hours worth of events. To say this makes it hard to troubleshoot errors on this box would be an understatement.
We are decommissioning the old SQL 2005 server box so I have to replicate this functionality on a newer server. I want to address these two issues before doing so.
I have tried using sqlcmd but since the AS400 Client software is running under a local system process it can't connect to the production SQL servers which are setup with Windows Authentication Mode. I tried using psexec which works if I leave a RDP session for the user open to but would still need the user name and password to be in plain text. I have been testing out having the batch file write a blank file to a directory and having the SQL job triggered by an WMI event. In testing this works but is unreliable when the job needs to be quickly triggered multiple times in a row, like most of our jobs need to be.
I have also looked into using the C# and .NET to store the password using the Windows Security classes and/or data protection API (DPAPI) but I am at best a intermediate .NET programmer so this is a worse case solution for me.
This has to be a common issue with integrating older host systems with SQL Server so I assuming there has to be a solution that allows for the Host system to call the SQL server in a secure method. If not a Job directly, run a stored procedure that can trigger the Job. Any alternative that does not require a the password/user name saved in plain text somewhere and not using dtexec would be appreciated.
LocalSystem Account "has extensive privileges on the local computer, and acts as the computer on the network."
This means you can grant it access to your remote SQL Servers by creating a login for the "computer account". If you server is named MyServer and the domain is MyDomain then:
use msdb
create login [MyDomain\MyServer$] from windows
create user [MyDomain\MyServer$] for login [MyDomain\MyServer$]
alter role SQLAgentOperatorRole add member [MyDomain\MyServer$]
And a process running as LocalSystem on the other server will be able to connect via Windows Integrated Auth and run SQL Agent jobs.