How do I connect excel to a remote sql server

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)
indikadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andrei FomitchevCommented:
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
David ToddSenior DBACommented:
Hi,

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

Regards
  David
0
Andrei FomitchevCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

indikadAuthor 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.
0
regmigrantCommented:
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
David ToddSenior DBACommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
indikadAuthor Commented:
Thank you all for the help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.