November 28, 2018 at 3:46 pm
We have on-prem SQL Servers as source data that is being moved to a SQL instance in a DMZ via SSIS and then from there to a SQL Server at a Managed Service Provider who is hosting the code for a public website. Some of the data that is written to this database at the MSP now needs to come back on prem for custom apps that use the data from a form on the website. I had suggested that since we were already using SSIS to get data to the DB at the MSP that we use SSIS to move the needed data back to an on prem table so that the custom apps can use it. What risk is there from a security perspective for doing something like this? Should there be any concern about the data being sniffed and read as it travels from the MSP back on prem or about SQL injection, etc.? How would you suggest we approach this scenario?
November 29, 2018 at 6:40 am
lmarkum - Wednesday, November 28, 2018 3:46 PMWe have on-prem SQL Servers as source data that is being moved to a SQL instance in a DMZ via SSIS and then from there to a SQL Server at a Managed Service Provider who is hosting the code for a public website. Some of the data that is written to this database at the MSP now needs to come back on prem for custom apps that use the data from a form on the website. I had suggested that since we were already using SSIS to get data to the DB at the MSP that we use SSIS to move the needed data back to an on prem table so that the custom apps can use it. What risk is there from a security perspective for doing something like this? Should there be any concern about the data being sniffed and read as it travels from the MSP back on prem or about SQL injection, etc.? How would you suggest we approach this scenario?
Can you get a database backup and restore it locally to remove the concern (and improve speed)?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 29, 2018 at 8:07 am
Phil Parkin - Thursday, November 29, 2018 6:40 AMlmarkum - Wednesday, November 28, 2018 3:46 PMWe have on-prem SQL Servers as source data that is being moved to a SQL instance in a DMZ via SSIS and then from there to a SQL Server at a Managed Service Provider who is hosting the code for a public website. Some of the data that is written to this database at the MSP now needs to come back on prem for custom apps that use the data from a form on the website. I had suggested that since we were already using SSIS to get data to the DB at the MSP that we use SSIS to move the needed data back to an on prem table so that the custom apps can use it. What risk is there from a security perspective for doing something like this? Should there be any concern about the data being sniffed and read as it travels from the MSP back on prem or about SQL injection, etc.? How would you suggest we approach this scenario?Can you get a database backup and restore it locally to remove the concern (and improve speed)?
Phil, I hadn't thought about taking an entire backup. I only need a tiny bit of data from one table every 5-10 minutes. That seemed like the perfect scenario for SSIS. Just never done this and the SysAdmin had asked about security of the data in transit from the MSP back to our on prem SQL Servers.
November 29, 2018 at 8:13 am
Well if you already have a job pushing data from to the onsite server why wold pulling data back be any riskier? Is the data you're pulling back more sensitive than what you're pushing?
November 29, 2018 at 9:06 am
lmarkum - Thursday, November 29, 2018 8:07 AMPhil Parkin - Thursday, November 29, 2018 6:40 AMCan you get a database backup and restore it locally to remove the concern (and improve speed)?Phil, I hadn't thought about taking an entire backup. I only need a tiny bit of data from one table every 5-10 minutes. That seemed like the perfect scenario for SSIS. Just never done this and the SysAdmin had asked about security of the data in transit from the MSP back to our on prem SQL Servers.
Fair enough. How are you connecting from SSIS to the external server?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 29, 2018 at 2:26 pm
I would try Replication for small amounts of data
November 30, 2018 at 6:36 am
ZZartin - Thursday, November 29, 2018 8:13 AMWell if you already have a job pushing data from to the onsite server why wold pulling data back be any riskier? Is the data you're pulling back more sensitive than what you're pushing?
I'm not certain if or how it would be. I was hoping to learn if I was overlooking any risk associated with this. I think maybe the SysAdmin was just concerned about the security of us connecting from an internal server to a SQL Server we're not managing that is 3rd party hosted and bringing data back into our side.
November 30, 2018 at 6:38 am
Phil Parkin - Thursday, November 29, 2018 9:06 AMlmarkum - Thursday, November 29, 2018 8:07 AMPhil Parkin - Thursday, November 29, 2018 6:40 AMCan you get a database backup and restore it locally to remove the concern (and improve speed)?Phil, I hadn't thought about taking an entire backup. I only need a tiny bit of data from one table every 5-10 minutes. That seemed like the perfect scenario for SSIS. Just never done this and the SysAdmin had asked about security of the data in transit from the MSP back to our on prem SQL Servers.
Fair enough. How are you connecting from SSIS to the external server?
Phil, I just have a SQL Server login and password making an OLE DB connection. Is that what you're asking? I don't have a ton of experience with SSIS, so I'm not sure if I'm telling you what you want to know.
November 30, 2018 at 6:44 am
Joe Torre - Thursday, November 29, 2018 2:26 PMI would try Replication for small amounts of data
Joe, that is a great suggestion that I thought about. The deterrent there is that the offsite SQL Server isn't our machine. Therefore I wasn't sure about whether I would be allowed to set up a pull subscription. Never hurts to ask though.
There is a 3rd party support company that is engaged for the platform that feeds into the SQL backend that is hosted. They had suggested using a Web Service to move the data back onto our systems but said they would need a VPN connection to do it. Another person on my team shot that down, but I'm thinking we should have just done it that way. Would have gotten this issue of my plate. 🙂
November 30, 2018 at 6:48 am
lmarkum - Friday, November 30, 2018 6:38 AMPhil Parkin - Thursday, November 29, 2018 9:06 AMlmarkum - Thursday, November 29, 2018 8:07 AMPhil Parkin - Thursday, November 29, 2018 6:40 AMCan you get a database backup and restore it locally to remove the concern (and improve speed)?Phil, I hadn't thought about taking an entire backup. I only need a tiny bit of data from one table every 5-10 minutes. That seemed like the perfect scenario for SSIS. Just never done this and the SysAdmin had asked about security of the data in transit from the MSP back to our on prem SQL Servers.
Fair enough. How are you connecting from SSIS to the external server?
Phil, I just have a SQL Server login and password making an OLE DB connection. Is that what you're asking? I don't have a ton of experience with SSIS, so I'm not sure if I'm telling you what you want to know.
While this is not something I have tried myself, it appears that you can force encryption of the data which is transferred via an OLEDB connection simply by modifying the connection string to include "Encrypt=yes":
Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
November 30, 2018 at 3:26 pm
Phil Parkin - Friday, November 30, 2018 6:48 AMlmarkum - Friday, November 30, 2018 6:38 AMPhil Parkin - Thursday, November 29, 2018 9:06 AMlmarkum - Thursday, November 29, 2018 8:07 AMPhil Parkin - Thursday, November 29, 2018 6:40 AMCan you get a database backup and restore it locally to remove the concern (and improve speed)?Phil, I hadn't thought about taking an entire backup. I only need a tiny bit of data from one table every 5-10 minutes. That seemed like the perfect scenario for SSIS. Just never done this and the SysAdmin had asked about security of the data in transit from the MSP back to our on prem SQL Servers.
Fair enough. How are you connecting from SSIS to the external server?
Phil, I just have a SQL Server login and password making an OLE DB connection. Is that what you're asking? I don't have a ton of experience with SSIS, so I'm not sure if I'm telling you what you want to know.
While this is not something I have tried myself, it appears that you can force encryption of the data which is transferred via an OLEDB connection simply by modifying the connection string to include "Encrypt=yes":
Provider=SQLNCLI11;Server=myServerAddress;Database=myDataBase; Trusted_Connection=yes;Encrypt=yes;
Thanks Phil. I'll give encryption a try.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply