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? 😀
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.