How do I connect excel to a remote sql server

indikad
indikad used Ask the Experts™
on
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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
David ToddSenior Database Administrator

Commented:
Hi,

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

Regards
  David
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.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

Commented:
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.
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
Senior Database Administrator
Commented:
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

Author

Commented:
Thank you all for the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial