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 :
All of these goals are quite easy to achieve with Excel Database Tasks (EDT) software.
Configuring Secure Remote data interaction with SQL Server hosted on a Web Server
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.
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.
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.
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 :
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.
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 :)
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
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..