Office 365, SharePoint, SharePoint 2013, SharePoint Online

SharePoint Online – How to display an External Azure Database as a list

In this guide I will be exposing an Azure Database in a SharePoint Online list, there’s a quite a lot of configuration so I’ve tried to capture everything step by step so you can follow along at home.

This blog post is going to operate under the assumption that you have already created your Windows Azure Database

Azure Database

Although I will not take you through how to setup your Azure SQL Database I will step by step show you how to find the information you will need to create the external content type and display it as an external list

There is only a few pieces of information that you will require to connect an azure database to SharePoint Online so please follow these instructions and make notes.

  1. Login to your Azure Account (https://manage.windowsazure.com)
  2. Once logged in on the left hand nav click SQL Databases
  3. A List of your available databases will appear like so, The First piece of information to write down is the SERVER name

  4. Click SERVERS and choose your database Server

  5. Choose your database server where the database you would like to connect to SharePoint Online is stored.

  6. Click DASHBOARD
  7. On the Very right hand side you will notice your Administrator Login details, write them down.

I now have the following Information, these are extremely important without them you will be unsuccessful.
Server Name: icxrr89hp1
Administrator Login: SQLAdmin

Secure Store

We are now going to need to configure the secure store entry we will need to use some of the Azure database information to complete the secure store configuration so make sure you wrote those details down.

  1. Log onto your SharePoint Admin Centre as a global Admin, and click secure store

  2. You will need to create a new secure store entry click New

You will be required to enter in parameters such as Target Application ID, Display Name, and Contact Email & Application Type.

Here is Microsoft’s definition of each.

  • Under Target Application ID, specify a value for a unique ID. This ID maps the External Content type to credentials that are required to authenticate the user. You cannot change the Target Application ID once you create the Target Application.
  • Under Display Name, specify a user-friendly name for referring to the Target Application.
  • Under Contact E-mail, specify the e-mail address that you want people to use when they have a question about the Target Application (external data system).
  • Under Target Application Type, verify that the value is set to Group Restricted. Group Restricted means that the Secure Store contains a mapping that connects a group of SharePoint users to a single, external data account that can act on their behalf. In addition, a Group Restricted application type is restricted to the specified external data system.

    Taken from http://office.microsoft.com/en-au/office365-sharepoint-online-enterprise-help/make-an-external-list-from-a-sql-azure-table-with-business-connectivity-services-and-secure-store-HA102933008.aspx

By default, the Secure Store uses the Windows User Name and Windows password, I recommend to leave as is.

In the members field enter the name of the group that will use the Target Application.

Once you have created your secure store entry you will need to set the Credentials on the Target Application

  1. Select the Target Application and click Set


  2. You will be required to set credential owners, Windows User Name and Windows Password – now this is where I  made a mistake first time around setting this up – the username you need to enter is the Azure SQL Database Administrator followed by @ your Azure SQL Database name.

In my case these details are:
Administrator Login: SQLAdmin
Server Name: icxrr89hp1

So I end up with the username of SQLAdmin@icxrr89hp1
As you can see the Windows Username is my Azure SQL Administrator account ‘@’ My SQL Database Server name

Click OK

Creating the External Content Type

Now that the Secure Store is configured correctly we need to create the external content type, you will need SharePoint Designer 2013 installed to be able to create the external content type.

  1. Open up SharePoint Designer 2013
  2. Click Open Site

  3. Type the url in the site name: field and click Open , this can take a minute or two to load so be patient

  4. Once the Site collection has loaded on the left hand navigation pane click External Content Types
  5. On the SharePoint Ribbon click External
    Content
    Type


This will load up the new external content type wizard,

  1. Give the Content Type a name
  2. select the ‘Click here to discover external data sources and define operations‘ to begin the process

  1. Click Add Connection
  2. Select SQL Server as Data Source Type and click OK
  3. Once you select SQL Server, specify the following
  • Database Server Name
  • Database Name

    IMPORTANT please note the Database Server is to be entered as your databasename.database.windows.net , you must include this for it to work! You cannot use a SQL Server data source that is on-premise with SharePoint Online

  1. Select Connect with Impersonated Custom Identity and type in your secure Store App ID that we had from Secure Store


  1. Click OK
  1. You will prompted to enter in the BCS Secure Store info for your Application ID enter in the Administrator account the Database Server name as the domain and your password , click OK to proceed

    1. Once done you will be able to expand out all your Tables Columns etc.

    2. Right click the table you would like to make a list and click Create All Operations

  1. An All Properties display will appear click Next
  2. Configure your Parameters Configuration accordingly to your needs , click Next

  3. Enter any Filter Parameters you have especially on a large table this will be needed, in my case I only have dummy data so I’m going to leave it empty.
  4. Click Finish
  5. Press ctrl+s to save

Create a list from your External Content Type

Now that we have our external content type created we need to create a list from our new content type

  1. In SharePoint Designer Click External Content Types > Your Content Type
  2. On the SharePoint Ribbon click Create Lists & Form

  3. Give your new list a name and click OK

    Once Created navigate to your site collection where you created the list and you should see your data from your external database available in a SharePoint List

Advertisements
Office 365, SharePoint, SharePoint Online

My Top 5 Free Apps from the SharePoint Store

Here is a list of my recommended apps, a brief description of each app and why I find them useful.

 

Number 5 – Newsfeed notifier by QuePort

An awesome tool that displays how many Newsfeed updates that are related to your user account are available tool is awesome but ceases to work when you turn on the social enterprise features of yammer.

What I use it for: Exactly what it was created for showing users how many updates are available.

 

 

Number 4 – The Shreyan Accordion Display

The Shreyan Accordion Display runs off a central list that allows you to add content in a Accordion fashion, while this is possible to do with Jquery I much prefer the App available to display content in an Accordion,

What I use it for: frequently asked questions

Number 4 – Important Messages by The Habanero consulting Group

A really cool solution that allows you to display Messages by, Critical, Warning and Informational types … extremely handy to use for an outage system on your home page especially in a large organization when this type of information needs to be prevalent on a site and not static.

What I use it for: Outage System

 

Number 3 – Mini Calendar by Artezio LLC

The Mini calendar allows you to roll up multiple calendars and display it in a small compact WebPart extremely useful and have found it was designed extremely well with very little to any flaws.

What I use it for: Great little webpart for the home page of your intranet


Number 2 – AvePoint Meetings for SharePoint Online

A cool feature AvePoint have added to the SharePoint store is the AvePoint Meetings an app that allows you to create meetings, set agendas, do roll calls, set tasks, topics and so much more

Definitely a tool worth adding to your SharePoint Online site collection.

What I use it for: Use it to replace similar functionality to the Meeting Workspace from previous versions of SharePoint

 

Number 1 and my favorite – MetaVis Information Manager for Office 365

The Information Manager helps you manage your content so easy, allows you to bulk edit, tag, categorize items , upload from file shares and much, much more. I couldn’t count how many times this tool has saved my behind so it’s a clear number 1 for me.

What I use it for: I use it to manage my content, bulk upload data and set metadata on a multiple item level