Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database to Database Connection - Unsecure? Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2013 8:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:03 AM
Points: 160, Visits: 368
Hello, I work for a government agency and I'm trying to implement some automated data pull procedures and I was getting some push back regarding "database to database connections are not secure". When I first heard this I thought they were crazy but I wanted to get some feedback from the community as to how "secure" a linked server or SSIS package is...

I did read that linked servers can be encrypted and use SSL and of course they would be using integrated security with only the permissions they need.

So, I wanted to ask the community for those out there that work in government agencies... how do you handle data transfer between agencies? I have already been using linked servers for some of our trading partners but I have 1 last group that we have to manually download data files on a monthly basis and then manually import them into our database. I personally think that in today's age, we should not have to do any sort of manual data movement but I wanted to get some "offical" positions on securing db to db connections and what the "industry standard" was for doing this.

The data that we are moving is not PII and it is not classified at all... its basically just FOUO data.

Thanks in advance!
Post #1424105
Posted Tuesday, February 26, 2013 8:29 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
Is the physical network layer between the 2 agencies private? Is it a VPN, or some tunnel variant?

If so, then the encryption layer/privacy layer is in place and I would not worry about it. If its not, then the simplest method is to get a secure link between the 2. I would never advocate having a database engine provide that type of security. that is a networking issue all day long.
Post #1424109
Posted Tuesday, February 26, 2013 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:03 AM
Points: 160, Visits: 368
No they are not on the same network and there is no VPN. When you say get a "secure link between the 2" what are you referring to?

I think it would work similarly to how I have another connection established. There is another organizations Oracle database that I push/pull from. We have a linked server connection to their Oracle set up in our SQL Server. We just have permissions in place to allow our IP address to access their server. It is using a login that only is used for our connection and the tables we have access to.

I was just confused when he tried to tell me that database to database is not "secure". He didn't elaborate on what his definition of not secure was. Our Government POC is fine with us pursuing the linked server connection but she wants me to verify that it is secure enough for the standards that the agency has in place (that part I have to research myself) but I wanted to get some other community feedback on how they handle moving data around like this. I'm also fine with doing it in SSIS. I had made the suggestion of using secure FTP but they didn't seem to like that idea.

The only other option they gave was to create a secure VPN connection which would cost like 35k for each destination.... I'm like why would I make them do that for 35k when I can do it for free in SQL? :D
Post #1424120
Posted Wednesday, February 27, 2013 12:44 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, Visits: 460
amy26 (2/26/2013)
No they are not on the same network and there is no VPN. When you say get a "secure link between the 2" what are you referring to?

I think it would work similarly to how I have another connection established. There is another organizations Oracle database that I push/pull from. We have a linked server connection to their Oracle set up in our SQL Server. We just have permissions in place to allow our IP address to access their server. It is using a login that only is used for our connection and the tables we have access to.

I was just confused when he tried to tell me that database to database is not "secure". He didn't elaborate on what his definition of not secure was. Our Government POC is fine with us pursuing the linked server connection but she wants me to verify that it is secure enough for the standards that the agency has in place (that part I have to research myself) but I wanted to get some other community feedback on how they handle moving data around like this. I'm also fine with doing it in SSIS. I had made the suggestion of using secure FTP but they didn't seem to like that idea.

The only other option they gave was to create a secure VPN connection which would cost like 35k for each destination.... I'm like why would I make them do that for 35k when I can do it for free in SQL? :D


Secure VPN for 35k? Seriously? I need to get into the hardware business for the government.

Ok, moving on, your right, you can do it in SQL Server... but obviously the transport layer and hardware would be more efficient. Its like putting a Ferrari engine in a baby buggy... I mean you can do it, but why? If the linked server is already deemed sufficient and within norm for both parties, then SSIS unencrypted should also be acceptable, since they are both using ADO, or native drivers depending on endpoints.
Post #1424701
Posted Wednesday, February 27, 2013 12:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:40 PM
Points: 12,897, Visits: 32,105
wow save that 35K for bonuses!

my first link for creating a free VPN between networks:
http://www.wikihow.com/Set-Up-a-Virtual-Private-Network-with-Windows

another possibility might include secure FTP to throw files on endpoints accessible to both servers.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1424706
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse