Sharing data outside company best practices

Question

Sharing data outside company best practices

phingers

Ten Centuries

Points: 1041
More actions
April 4, 2016 at 7:59 am

#326245

Hi,
I have an SSIS package which emails data in XML file format to an external company on a regular basis.
They have requested that we allow them access to our SQL server remotely so that they can pull the data themselves (or us push it to them) instead of using email.
What are the best practices for implementing a solution like this please? Are there any 'heads up'? I understand that a direct SQL connection for them would potentially be more secure than mail but I am not confident that doing this won't create another level of potential security issues.
Any comments, pointers greatly appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Sounds like a job for Secure FTP. Clearly it's not acceptable for them to receive data by e-mail, and it's not going to be acceptable (or at least it shouldn't be) for them to poke around your database server, so you need an area that you can write to, they can read from, and nobody else can see.

John

phingers Ten Centuries Points: 1041 More actions · Answer 2

Thanks for the reply,

I'd be happier not allowing them access to SQL and I think secure FTP would suit their requirements. 🙂

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

phingers (4/4/2016)
Hi,
I have an SSIS package which emails data in XML file format to an external company on a regular basis.
They have requested that we allow them access to our SQL server remotely so that they can pull the data themselves (or us push it to them) instead of using email.
What are the best practices for implementing a solution like this please? Are there any 'heads up'? I understand that a direct SQL connection for them would potentially be more secure than mail but I am not confident that doing this won't create another level of potential security issues.
Any comments, pointers greatly appreciated.

If they have a SQL Server instance you can create an SSIS package that connects to it and copies data they need over that way. That's another way to keep your environment secure. Push-based replication is another possible solution.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Eric M Russell SSC Guru Points: 125519 More actions · Answer 4

FTP is a logical choice for providing scheduled data extracts to an external client, especially if they can potentially be more than a handful of MB. If what they need is on-demand access for small datasets, then another option is a web service protected by authentication and certificate based encryption.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Eric M Russell (4/4/2016)
FTP is a logical choice for providing scheduled data extracts to an external client, especially if they can potentially be more than a handful of MB. If what they need is on-demand access for small datasets, then another option is a web service protected by authentication and certificate based encryption.

I absolutely concur, especially if it's SFTP when PII or other sensitive information is contained.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)