How do I connect excel to a remote sql server

Posted on 2014-02-26
Last Modified: 2014-03-15
I have a server that has sql server installed  - we usually run remote session on it using Remote Desktop.
it has public ip address and we do not use VPN

I need to connect my excel book to get data from a database from the sql server on that remote server.
I have firewall off, the sql server browser is running.
what do I need to do on Excel ? what syntax to use for the conection ?
the server addres is something like ( for RDC) ipaddress:45873
then the SQL server is a named instance say SQLINST ( tcp port for sql instance is something like 12354)
Question by:indikad
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1

Expert Comment

by:Andrei Fomitchev
ID: 39891085
1. RDP to computer that can run SSMS to SQL Server.
2. Start SSMS.
3. Execute query
4. Click on result table
5. Click Ctrl-A (Select all).
6. Click Ctrl-Shift-C (Copy with header)
6. Open Excel on you computer
7. Paste.

1. RDP to computer that can run SSMS to SQL Server.
2. Start SSMS.
3. Execute query
4. Click on result table
5. Right click
6. Export to tab delimited file
7. Send file by e-mail
8. Receive and open with Excel

1. Direct connect from Excel using VBA (Visual Basic for Applications - embedded in Excel)
Note: Excel and SQL Server must be on the same network. You can use VPN to run Excel at home to connect to SQL Server at work. Virtual Private Network  makes your computer to be on the same network as all members of VPN including SQL Server.
LVL 35

Expert Comment

by:David Todd
ID: 39891128

I excel 2013, in the data tab there is a button called connections. What happens if you follow that?


Expert Comment

by:Andrei Fomitchev
ID: 39891191
I doubt that MS SQL Server reside on Windows Server without protection. VPN or RDP takes care about security.

Usual test - if you can start SSMS on your home computer and connect to MSSQL Server then you can go to Excell.

SSMS has many options to connect. Main problem is security. I doubt that your MS SQL Server located in DMZ Zone of security.
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.


Author Comment

ID: 39891581
Hi David Todd,

I need to get the data into Data tab - but how to give connection parameters in this case is what I need help with.

fomand - I am not after copy and paste solution - I need real time data updated to my excel sheet without having to login to server.
LVL 19

Assisted Solution

regmigrant earned 240 total points
ID: 39892476
If you are connecting direct (using the connections tab, from other sources, sql server) then you must use a login/password pair and your sql server must be setup to accept such connections - this is a totally different setup to a remote desktop session where SQL server treats you as though you are logged into the machine its running on - it is also version dependent. (prior to 2012 it was on port 1433 now that is used to assign a dynamic port in response to a udp request). As you can see this requires some thought and setup at the server end.

for 2012:

Alternatively you can publish the results to a web page using IIS and use excel's 'from web' tab to generate the connection string.

In either case you have to have both ends setup to handle the link and once the server is configured the excel part is easy
LVL 35

Accepted Solution

David Todd earned 240 total points
ID: 39893303

If you want real-time, up-to-the-minute info in Excel, then I think you'll have to code a macro in Excel that handles the refresh, and depending on the data volume updates the sheet intelligently.

If you follow the connection button, I'm sure you will find something that can either take your current windows user and connect using that, or you can embed the SQL user/password pair. Of course its not perfectly secure, but if the user has only the appropriate permissions, its likely to be secure enough.

Once you get that in place, and the data then on the spreadsheet, there is likely to be a refresh option which is pretty much automatic once clicked on, something like right-click in the middle of the data, and select refresh. From memory it may refresh on opening the spreadsheet. Its been a few versions of excel ago that I did that.


Author Closing Comment

ID: 39931985
Thank you all for the help.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question