Error trying to connect to a remote mssql database

Question

Error trying to connect to a remote mssql database

Tatay_2016

Right there with Babe

Points: 736
More actions
November 25, 2016 at 5:53 pm

#74552

Hi,
First time here. I'm the admin of a small business with a mssql database that is remotely hosted by our website provider. I need to import/export data to/from a table on the database (which is hosted remotely) into a format where I can manipulate the data.
I have all the database table and server info and the usr/pwd, and I tried to use the ms excel wizard (the latest version of office 365 on a windows 7 laptop) and it just keeps failing, can't connect.
So my question: Is there an easier way to connect to a remote sql database from a laptop for data transfer? The workbook wizard seems inadequate, or I'm missing something. I've just started learning this and don't know all that much about other ways to connect to this database to accomplish what I want that don't require a lot of training or programming knowledge.
Thank you.

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

I would download SSMS and start there. That's a fairly easy way to go. Or just get SQL tools and use SQLCMD or bcp to get data. SSMS gets you the ability to browse and see more, which might be easy.

https://msdn.microsoft.com/en-us/library/mt238290.aspx

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

Tatay_2016 (11/25/2016)
Hi,
First time here. I'm the admin of a small business with a mssql database that is remotely hosted by our website provider. I need to import/export data to/from a table on the database (which is hosted remotely) into a format where I can manipulate the data.
I have all the database table and server info and the usr/pwd, and I tried to use the ms excel wizard (the latest version of office 365 on a windows 7 laptop) and it just keeps failing, can't connect.
So my question: Is there an easier way to connect to a remote sql database from a laptop for data transfer? The workbook wizard seems inadequate, or I'm missing something. I've just started learning this and don't know all that much about other ways to connect to this database to accomplish what I want that don't require a lot of training or programming knowledge.
Thank you.

Quick thought, you should contact the provider for connection information, vpn options etc. as normally, a database server would reside behind a firewall.

😎

Tatay_2016 Right there with Babe Points: 736 More actions · Answer 3

Hello Steve,

Thanks for the reply. I have downloaded SSMS, and I'm working on figuring it out.

SSC,

Thank you also for replying. I do have that connection information, but the excel wizard does not have a field to put it in. It starts out asking if you want to import external data, but then the only two fields are server name and password. If I could put it in there, I would, but any way I tried, it failed.

royce.bacon SSC Eights! Points: 990 More actions · Answer 4

First, most web site databases are behind firewalls and you may have to have the hosting company allow SQL Server traffic from your IP address to pass through the firewall. Talk to your hosting support company to discuss this with them.

Second I would suggest first try to connect to the database with SQL Server Management Server. It gives you the most control on the connection.

Once you have connected you could try using the Import/Export wizard in SSMS to try to import the data. If that works you can create a job from it to either run automatically or manually.

Tatay_2016 Right there with Babe Points: 736 More actions · Answer 5

Thank you for the reply. I do plan on using SSMS. I never have before. Is it fairly straight forward, or is there a learning curve?

royce.bacon SSC Eights! Points: 990 More actions · Answer 6

It isn't that hard to use, especially if you've used other Microsoft development products.

There is a tutorial at That one is for 2016 but I think there are ones for older products too.

Tatay_2016 Right there with Babe Points: 736 More actions · Answer 7

Hello Royce,

Thank you for that link. Actually it is the correct one, at the top of the tutorial article it says it applies to SQL server starting with 2008, so that will do.