Azure RemoteApp and Azure SQL Database

This blog will show you how to use Azure SQL Database (Azure SQL DB) as a backend for an application installed in Azure RemoteApp (ARA) collection.

For demo purposes and to prove that Azure SQL DB works together with ARA, I have selected a sample database in Azure, called AdventureWorksLT. I will also be using Excel as a front end (client) installed on ARA collection.

The connectivity to Azure SQL Database (backend), from an Azure RemoteApp collection (front end), will vary depending on how your application is interacting with the database, but the concept is exactly the same.

You might need to put your application (front end) on an Azure VM and configure it. Please see this link How to create a RemoteApp template in Azure . Or you can use the readymade templates for office or Server 2012. All depending on your project requirements.

By default, your 30 days of free trial, will get you three templates, Office 365 ProPlus, Office Professional Plus 2013 and Server 2012 R2 templates. All of them already configured with the necessary licenses to start your testing. Check Free 30 day trial of Azure RemoteApp

Requirements

  • Azure Account
  • Azure SQL Database
  • Azure RemoteApp Collection

Steps

I am using the new Azure portal by the way.

Click New \ Data + Storage \SQL Database

New SQl DB

Select the DB name, server name, Source database and pricing. For server name, I have opted to configure a new server.

It is very important that you keep records of the server name and security credentials (user name and password). Whatever location you use for the server you do the same for Azure RemoteApp (both resources should be in the same region). One other crucial configuration is allowing Azure services to access the SQL DB server.

Server Configuration

Click on OK and on Create. In regards to the server name if you have named your database (for example) sqldb1, the server name would be sqldb1.database.windows.net. Make sure that you keep a record of the user name and password you have used for the Server admin login.

If you are creating an Azure VM to use for your application (front end) and connect to an Azure SQL Database, you will have to create an endpoint and open SMB port 445 for file sharing. Please see link for more information.

At some point you will need to establish connection to your database. To do so, you need to know the connections strings and to review them and take copy of each of them (when needed), do so after the Database is created. Select the database, click on the “Show database connection strings” and you can see the connection strings to use with whatever you want to connect your database with.

Connection Strings

I have selected the Office Professional Plus 2013 template (my account still in trial hence why Office 2013 still available). This template will not be available after the 30 days’ trial expires.

  • Publish the apps and assign users to the collection

How to publish applications in a RemoteApp Collection

How to add a user to a RemoteApp Collection

I will only need Excel for this demo. You will also need Azure RemoteApp client, so users can access the published app. You will find the link to the client in the RemoteApp collection dashboard. RemoteApp Client

Publish

As you can see below, after launching the client and providing my user name and password, I am presented with all the published apps, which assigned for my account.

Client

  • Make Connection

Launch Excel, then select from the Menu bar, Data \ From Other Sources, then from the drop down menu select “From SQL Server”.

Excel

Data Connection Wizard window will appear. Enter the server name and then select “Use the following User Name and Password”. The user name and password, are the credentials you have entered while creating the server of the SQL Database.

Data Connection

Hit Next and from the drop down menu select the database that contains the data you want (the name of the DB earlier should be in the list). If no errors, you should be able to connect to the database and a list of tables will be available for you.

Capture6

By default, the “connect to a specific table” is selected. You also have the option to either connect to all the tables and views or connect to a specific table or to multiple tables. In this scenario I have checked both.

Select the table that you want to work on and then select Finish.

Capture7

  • Import Data to the Excel Sheet

The “Import Data”window appears. In this window below you have the options to select how you want to view this data in your workbook. For the purpose of this demo, see my selections below.

Import Data

Hit the ok button. Wait few seconds or minutes (depending on number of tables and size of the database) and you should see new worksheets.

Excell Sheets

Useful Links

 

 

 

 

 

 

 

 

 

 

 

Leave a Reply