?
Solved

How do I connect excel to a remote sql server

Posted on 2014-02-26
7
Medium Priority
?
4,372 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
Independent Software Vendors: 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!

 

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 960 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 960 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

764 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