Sql-server – How to Excel communicate with a remote SQL Server that is disconnected from the Internet

sql serversql server 2014

My setup involves three computers:
1. An application sits on an Internet-connected Windows Server in the Cloud.
2. This application is connected to a SQL Server instance on another Windows Server in the Cloud, which is disconnected from the Internet
3. I have an Excel sitting on a third computer.

How can Excel send data to the Windows Server database?

One working option is to use Dropbox to synchronize text files between the Excel and the Internet-connected Windows Server, and then to use sqlcmd on the Internet-connected Windows Server to run these text files on the database.

However this solution is not ideal for a number of reasons!

Any better way? As far as I know I can't open up SQL Server to the Excel – using such approaches as described in https://leansoftware.net/Help/Excel-Database-Tasks/Worked-examples/How-to-connect-Excel-to-Remote-Web-Server-SQL-Data.aspx – because my SQL Server is not connected to the Internet.

Note that the SQL Server is the Express edition (we hope to upgrade to Azure in the not-too-distant future)

Best Answer

We have created an Excel Add-In for updating tables from Excel to SQL Server. The Add-In supports working in offline mode, and I'm pretty sure the off-line mode will handle this case.

You will need to install Excel and the SQL Spreads Add-In on both the non-internet connected server and on the laptop. The process will then be like this:

  1. On your "off-line" server containing your SQL Server databases, create a new Excel file and select the SQL Server table(s) you like to update.
  2. The data from the tables will be loaded into the Excel file. When you save the file, all the SQL Server data will be stored into the Excel file.
  3. Move the Excel file to your laptop and edit the data there. When you save the file, all your changes will be stored into the file.
  4. Move the Excel file back to the server, open the file and click Save in the SQL Spreads tab in Excel to push the updates to the SQL Server tables.

The Add-In can also check for conflicts if the data has been changed in the database while you were editing it in your off-line doc.

If that's of interest, you can read more here and download a free trial.

Disclaimer: I'm the founder of the company.

Related Question