What are the best way to move data securely over the network by SSIS?

Question

What are the best way to move data securely over the network by SSIS?

harsimranjeetsinghwasson

Ten Centuries

Points: 1231
More actions
June 25, 2014 at 7:06 am

#292679

Need to design ssis package to copy data from local database server to another database server which is public and hosted.
What are the best way to move data securely over the network?
Please suggest.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You can setup SQL Server to use SSL for connections. I would think that this would be the best way. Otherwise I think you'd have to do some kind of encryption of the data at the source, load the encrypted data into the destination, and then have a process at the destination that runs locally to decrypt the data and insert into the final destination database.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 2

Will TDE(Transparent Data Encryption) in sqlserver Help this ?

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 3

Another thing i would like to ask is ,how to connect from ssis to sqlserver instance, after ssl is implemented.

Will Simple Connection establishment in package is sufficient or need to use script task for connecting sql sever instance.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

harsimranjeetsinghwasson (6/25/2014)
Will TDE(Transparent Data Encryption) in sqlserver Help this ?

No, TDE only protects that data at rest (on disk). Once the data is read into the buffer pool it is no longer encrypted.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Jack Corbett SSC Guru Points: 184394 More actions · Answer 5

harsimranjeetsinghwasson (6/26/2014)
Another thing i would like to ask is ,how to connect from ssis to sqlserver instance, after ssl is implemented.
Will Simple Connection establishment in package is sufficient or need to use script task for connecting sql sever instance.

Here's the article about it, http://technet.microsoft.com/en-us/library/ms189067(v=SQL.105).aspx

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 6

Hi Jack,

Does it support Express Edition ?

And does a single certificate ,which is being used for web server earlier i.e. https,

can be used sql server even?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

harsimranjeetsinghwasson (6/30/2014)
Hi Jack,
Does it support Express Edition ?
And does a single certificate ,which is being used for web server earlier i.e. https,
can be used sql server even?

Yes it is supported in Express Edition.

I don't know if you can re-use a certificate.

Did you read the article I provided the link for? If you read that and step through it you should be able to set it up.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

harsimranjeetsinghwasson Ten Centuries Points: 1231 More actions · Answer 8

Hi jack this time, we have two data sources, both exchange data, so i need to implement SSL on both database server , if both are on separate domain.