Solved

How do I connect excel to a remote sql server

Posted on 2014-02-26
7
4,196 Views
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)
0
Comment
Question by:indikad
[X]
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
7 Comments
 
LVL 8

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.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39891128
Hi,

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

Regards
  David
0
 
LVL 8

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.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:indikad
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.
0
 
LVL 19

Assisted Solution

by:regmigrant
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: http://jeffreypalermo.com/blog/how-to-configure-sql-server-2012-for-remote-network-connections/

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
0
 
LVL 35

Accepted Solution

by:
David Todd earned 240 total points
ID: 39893303
Hi,

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.

HTH
  David
0
 

Author Closing Comment

by:indikad
ID: 39931985
Thank you all for the help.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

717 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