How to use linked server to 3rd party db?

Question

   

There is a small 3^rd party company that have a small SQL Server DB. I need to import records into our instances from this DB. I thought the best way to do this would be to set up a linked server. I’ve done this internally (on the same domain), but never to an external database.

I was wondering if this was possible and what I would need to consider about permissions to their domain?  I guess the linked server would use a SQL login?

Thanks,

Zoe

Answer 1

Hi,

I would approach in a these ways:

• option 1: ask the 3rd party to export the data and ask them to upload it to your ftp dropbox and use SSIS to import it to your DB.
• option 2: you may go with linked server but consider these security settings: put the remote SQL server behind firewall and allow only specific IP addresses to connect with SSL, or use VPN. You are right, if your domain has no trust relationship only auth mode is sql authentication.

I hope it helps.

Janos

---

There are 10 type of people. Those who understand binary and those who do not.
My Blog

Answer 2

Hi,

This may help:

http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm

---

Thanks,

Andrew Bainbridge
SQL Server DBA

Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

Answer 3

Cheers Janos,

I think the SSIS way would be less of a headache really.

Cheers for your help!

Zoe