Creating Excel applications that interact with data held on a Remote Web Server.

 

In this tutorial we will help you to Securely connect Excel Remote SQL database across the internet.

The database being hosted on a remote Web Server.

 

Example end-game task goals might be to :

  1. Retrieve data in the form of a live Report from remote Web Server
  2. Upload data from local source (such as Excel file, CSV file, or local database) to remote Web Server
  3. Edit existing data on the remote Web server
  4. Retrieve and process data from the remote Web server to insert/update a local database
  5. Create multiple Tasks to create a complete Remote data Excel application solution

All of these goals are quite easy to achieve with Excel Database Tasks (EDT) software.


  If you need help with this task, drop us an email to Support@LeanSoftware.net, we will be able to help you.


 
  • Note that setting up the task is a do it once process - EDT saves all your task settings into a task XML file.  The next time you want to repeat the process you simply select the Task from the main EDT interface.
  • Once a task is working correctly you can if needed, distribute the EDT application and your task file(s) by way of a Zip file to the intended users of the system.
  • If the users are on a local network, then you would create a shared network folder - please see Network Installation. 

If you wish to create a data import task you may later fully automate / schedule the task using the EDT exe command line options and even launch the task from a task report email hyperlink.
See this page for command line details

 

Configuring Secure Remote data interaction with SQL Server hosted on a Web Server


    Prerequisites:
  • The Web server is a Virtual or Dedicated Server environment, or at least Administrator rights to the server environment
  • You have access to your Web server via a Remote Desktop connection
  • You have administrative access to the SQL Server database hosted on the Web Server

STEP 1: Remote SQL Server : Allow remote connections

The first thing you want to check is if Remote Connections are enabled on your SQL Server database. In SQL Server you do this by opening SQL Server Management Studio, connect to the server in question, right click the server…

… and open the Server Properties.

Navigate to Connections and ensure that Allow remote connections to this server is checked.


STEP 2: Remote SQL Server : Protocols for MS SQL Server

The next thing is to check the SQL Server Network Configuration. Open the SQL Server Configuration Manager, unfold the node SQL Server Network Configuration and select Protocols for MSSQLServer (or whatever the name of your SQL Server instance is).

Make sure that TCP/IP is enabled.

STEP 3: Remote Server : Initially allow only yourself to have SQL access through the Remote Firewall

Initially, until we have set all necessary security options, we will just open SQL Server access to your own local machine:

 On the remote web server, open Windows Firewall with Advanced Security:

Create a new Inbound rule: 'SQL Server Remote Connection' :

Under Protocols and Ports, Select TCP, ports 1433, 1434 separated with a comma:

 

Under Scope, Remote IP Address, add your local External IP address.

To find out your External IP address, from the local PC browse to http://www.whatsmyip.org/

 

Note : If you only wish to allow access by IP address, then you could add all of the IP addresses of the expected clients.

We will see later how EDT can also pass the External IP address or local IP address  as stored procedure parameter to SQL Server, and so SQL Server too can control access by IP address.

STEP 4: Local machine : Modify Firewall to allow SQL connection

From the Windows Start menu enter 'Windows Firewall' - select the program Windows Firewall with Advanced Security.

 

Right Click Outbound rules, select New


 

Select Rule Type Custom


Allow All Programs :

Specify Allow TCP Protocol, specific local and remote port numbers 1433, 1434  :

Specify the Remote IP Address, this is the static IP Address of your Web Server:

Select Allow the connection :

Select Name the rule :

STEP 5: Create a Remote SQL Server Login

We now need to create an SQL Server login, specifically for this remote connection.

Select New Login:

Specify Login details:

 

User name : as you choose

Password : we recommend a 20 character strong password.

( Here is a handy site for Generating a strong password http://passwordsgenerator.net)

Authentication : choose SQL Server Authentication

Default database : set this to the target database

 

Great - now we need to give the login some permissions.

We will just grant read/write permission to one table in this example.

 

STEP 6 : Grant rights to the sql Login

We only wish to grant minimal rights to the SQL login.

For this example we will grant Read/Write access to one table to test connectivity.

 

[In a later step in this tutorial we will show you how to use stored procedures to access data, this being a more secure method]

 

In the target database tree - select Security - Users - New Users

 

Enter the user name created in Step 5

 

Here we now click the Securables tab, add the target table Customers in this example - check Select and Update permissions.

 

And now we are ready to test remote connectivity :)

STEP 7 : Test secure SSL connection to remote database

You can complete this task using the FREE unrestricted 30 day trial of EDT Version 3.1

Download EDT Free

 

Launch the Excel Database Tasks (EDT )application.

Select Login, then New Task from the EDT Ribbon.

 

For this example task we will select Data Edit for this Task Method.

On the Target Connection tab, paste the following connection string:

Provider=SQLNCLI10;Encrypt=true;TrustServerCertificate=true;

Persist Security Info=True;User ID=RemoteTest;Password=XYZ;

Initial Catalog=TestDB;Data Source=109.228.14.14;Initial File Name="";Server SPN=""

 

Modify the highlighted values to match your Web server setup.

 

 

Notice these particular parts of the connection string:

Encrypt= true; TrustServerCertificate=true;

This enures both the connection information and any data transferred are encrypted.

If you do not have an SSL certifiacte installed with SQL server - then SQL server will generate one to support this connection - HOWEVER - the encryption will not be as strong - so we highly recommend you install an SSL certificate for use with SQL Server.

See this link to learn "How to how to a certificate and enable stronger SSL encryption for an instance of SQL Server using Microsoft Management Console" : http://support.microsoft.com/kb/316898

Click Test - any connection error will displayed - or confirmation of successful connection

STEP 8 : Test a complete data edit EDT Task

Following on from Step 7 -

Select Table on the Target Columns tab

Note : in our example we only granted rights to the Customer table, hence only this table is listed in the dropdown.

Also Note: It is often preferable to use stored procedures as a Destination with remote data, if this is the case we would have used the Task method 'Transfer to procedure', and the procedures would be listed rather than table names.

 

Notice the UPDATE SQL is automatically generated for you (far right)

Provide a Task name (top left) and save the task.

 

Here we now have a task allowing us to edit Customer Information:

 

Modify values - here we just enter TEST into the Address 2 column (Note you can change column names and layout within Task options):

 

Click Validate in the EDT Ribbon : this will check with the remote SQL database that the modification is Valid:

Click Send in the EDT Ribbon: this will commit the changes to the remote database

 

Confirm task Reload, and the modified data records are now displayed:

Voila !

Your remote Excel task is working - you are able securely maintain data on the remote web server.

 

EDT can of course perform many different tasks with remote data, perhaps Reporting, Editing data, Uploading or Downloading or Processing data.

 

If you need help - in preference please log a ticket on the Lean Help Desk, or email the support team directly Support@LeanSoftware.net

 

We hope you have enjoyed this Tutorial, and would greatly value your comments..

 

 

Your Contact Information

Your Feedback