July 27, 2018 at 8:08 am
I've been tasked with moving databases related to a website from our internal network to a managed service provider. Giving them a backup to restore is obviously simple. The struggle is that the website relies on getting data from other SQL Servers on our network and we didn't want to expose our SQL Servers to the outside. We set up a SQL Server in a DMZ and I have an SSIS project with several packages that get data from our internal servers and load it into the DMZ SQL box via SQL authentication. Those SSIS packages run inside an agent job on our internal network.
Once the data is on the DMZ box, I need to push it to the managed service provider. I set up a system DSN and a linked server that uses that DSN and the MSDASQL data provider. That linked server has connectivity to the managed service providers SQL Server where our databases are. The Linked Server object is using SQL authentication.
Internally we are using SQL Agent jobs with calls to stored procedures and some free text SQL to move our data around. Because we currently use SQL Agent jobs on this set up internally, I was going to do the same from the DMZ to the MSP. However, a SQL Server Agent job will try to connect using the SQL Server Agent account, which is just the account of the DMZ computer. That won't have any access on the MSP side of things, although maybe I could ask them for that? I don't think I can use a SQL Agent Proxy to execute the job steps, because that appears to be for Windows accounts only.
The only thing I can come up with to solve these roadblocks is to build an SSIS package on the DMZ box that will do what the internal SQL Agent jobs do. The SSIS pakcages would use the same SQL auth that the Linked Server uses for authentication.
Am I making this too complicated? What suggestions or ideas does anyone have for this? I'm using SQL Server 2016 Standard for the DMZ machine.
July 27, 2018 at 3:17 pm
lmarkum - Friday, July 27, 2018 8:08 AMI've been tasked with moving databases related to a website from our internal network to a managed service provider. Giving them a backup to restore is obviously simple. The struggle is that the website relies on getting data from other SQL Servers on our network and we didn't want to expose our SQL Servers to the outside. We set up a SQL Server in a DMZ and I have an SSIS project with several packages that get data from our internal servers and load it into the DMZ SQL box via SQL authentication. Those SSIS packages run inside an agent job on our internal network.Once the data is on the DMZ box, I need to push it to the managed service provider. I set up a system DSN and a linked server that uses that DSN and the MSDASQL data provider. That linked server has connectivity to the managed service providers SQL Server where our databases are. The Linked Server object is using SQL authentication.
Internally we are using SQL Agent jobs with calls to stored procedures and some free text SQL to move our data around. Because we currently use SQL Agent jobs on this set up internally, I was going to do the same from the DMZ to the MSP. However, a SQL Server Agent job will try to connect using the SQL Server Agent account, which is just the account of the DMZ computer. That won't have any access on the MSP side of things, although maybe I could ask them for that? I don't think I can use a SQL Agent Proxy to execute the job steps, because that appears to be for Windows accounts only.
The only thing I can come up with to solve these roadblocks is to build an SSIS package on the DMZ box that will do what the internal SQL Agent jobs do. The SSIS pakcages would use the same SQL auth that the Linked Server uses for authentication.
Am I making this too complicated? What suggestions or ideas does anyone have for this? I'm using SQL Server 2016 Standard for the DMZ machine.
If things outside of the SQL Server world are acceptable, there are a ton of alternatives to FTP since basic FTP has been such a security nightmare. I'd probably consider something along those lines but make sure to research them, especially around security. Things I can think of off the top of my head would be eTransmittal, SmartFile, Egnyte, GoAnywhere MFT, FileCloud, SFTP/FTP-SSL, HostedFTP, BrickFTP. Filezilla..not sure as there has been some reviews about it barely being much better than FTP. But there are options like that.
Sue
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply