Skip to main content

Push & Pull data to SQL via SharePoint Online and BCS

PURPOSE
Ever wondered what sort of SQL data connections are possible in SharePoint Online?
Ever wondered of the limitations when trying this type of setup?

If so, read on...  This tutorial will go over all the steps to setup everything from scratch.  Every step is important, you miss one, it won't work.

WHAT YOU NEED
  • SharePoint Online Plan 2 (you cannot achieve this with the basic version of SharePoint online as you require access to the Admin panel to setup Secure Store access permissions and BCS permissions.
  • A windows azure account (which you can get a free 30 day trial as long as you're willing to provide your credit card.
  • SharePoint Designer 2013 installed locally (required to create the final connection to the azure DB.

Please note, you'll make this a hell of a lot easier if all these accounts are setup using the same office logon.  Otherwise you'll be logging in and out like a madman.

IMPLEMENTATION

Step 1 - Create SQL Database: 
Log into Azures Management portal (https://manage.windowsazure.com/)

From here, Quick Create an SQL Database.  Azure will ask if you want to create a new SQL server, say yes.  In a minute or two your server and DB should be created.  Note down the server name for use later (The Fully Qualified Domain Name:  servername.database.windows.net) as well as the DB name.

Once the Database has been created click 'Design your SQL Database', log in, and create a table and all the columns you want

Now your DB and table have been created, it's time to connect them up to SharePoint.


Step 2 - Add BCS Permissions:
log into your SharePoint Online admin panel (https://[companyname]-admin.sharepoint.com/).  Click on the BCS Tab.

Click 'Manage BDC Models and External Content Types'

In here, 'click Set Metadata Store Permissions', add the office365 account you want to have access to manage the data connection to SQL.  I just used the account I was logged in as, and ticked all the boxes for access.


Step 3 - Create Secure Store:  
While in the SharePoint Admin Portal, click on the 'Secure Store' tab.  Here's where we create the access token to connect to the database.  Click 'New'. 

Add a unique name for the Target Application ID, this can be anything, but note it down as it will be required later.  Click OK.  You have just created the secure store token, but you still need to add the username and password to it.

Check the box and click Set Permissions.  In these text boxes, enter the username and password you created earlier to connect to the SQL Database.  Click Save.


Step 4 - Create External Content Type: 
Ppen up SharePoint Designer and connect to your SP Online site (https://companyid.sharepoint.com).  Don't forget the S in https..

Click 'External Content Types'.  Click 'New External Content Type' in the ribbon.

Click connect to external data sources.  Choose SQL server.  Type in the server name and database you recorded in step 1.  Choose the last radio button and enter your secure store application id from earlier.  and then enter your SP Online username and password.  You should now see the table and columns from the database.  Select the table and click Save.


Step 5 - Create External List: 
Open up your SharePoint Online site and click settings > add an app > external list.  Choose the external content type you just created.


Step 6 - Done!: 
You should now be able to add and remove items in the external list, which should automatically push those changes to your SQL Database.  Cool as ice right!!??


REFERENCES AND THANKS:
  • 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#_Toc350352764 
  • http://michaelwasham.com/2013/03/16/office-365-access-denied-by-business-data-connectivity-connecting-to-sql-server-azure/ 

Comments

  1. Replies
    1. Cheers Christine, great to get some positive feedback!

      Delete
  2. I am trying this with Office 365 sharepoint with an Enterprise e3 account. It has all the components that you listed but I still get a BCS connection error. I am not quite sure if the SQL server address should end ,1433 or not. Does the port matter? And the username for Azure. Should it end @{theserver} or not?
    Thanks in advance
    Alex@stanier.co.uk

    ReplyDelete
    Replies
    1. Hi Alex,

      from memory if you are connecting to an Azure SQL Database, then you won't need to enter a port number. If you are connecting to a SQL Server VM it might be a different story.

      When creating the Secure Store, just enter the Login Name & Password you created when you first built the Azure SQL Database. No need to add the @server.

      In step 4 after you've added the secure store app id, I believe when you click OK you'll need to input your Office 365 username and password.

      Is there a specific error message? Could attempt to log into the Azure SQL DB via SQL Management Studio on your PC with the same login details to see if you can connect directly first?

      Delete
  3. Can you help me out. I was in step 4, I already filled out servername,database name and the Application ID in SPD but when I press ok I was ask for a username password so I entered my O365 credentials but I got an error
    "Cannot connect to the LOBSystem (External System) Reason: 'Cannot open server "server.com" requested by the login. The login failed"

    Note I am using SharePoint online and connecting to Azure SQL Paas

    ReplyDelete

Post a Comment

Popular posts from this blog

Export Group Membership From Active Directory Using Power Query

If you need to export a list of staff from an Active Directory Group, follow these steps.  Allows you to gather all the names & usernames of staff in a particular group and save the information into Excel.

Step By Step How To:
Open up Excel > Power Query TabClick From Other Sources > From Active Directory:(In Excel 2016 this is found under: Data Tab > New Query > From Other Sources > From Active Directory) screenshots from Excel 2016 on left, Excel 2010 w/ Power Query Add-on right A popup will ask you to enter your domain name.  It should already be populated with the correct information but if not, input your companies domain name & Click OK:
In the Navigator that opens up, expand your domain tree and double click group:

You should now see a list of all groups in Active Directory in the Power Query Editor:
In order to find & filter for a specific Group, Click the Down-Arrow for the 'distinguishedName' column > Text Filters > Contains...
Type the name…

HP Laserjet 1022 Printer - Can't Print on Windows 10

If you are having trouble getting your laserjet working on Windows 10, here's how I resolved the problem:

Go to Devices & PrintersFind your printer & go to Printer Properties > Advanced TabSet Driver to: HP LaserJet 1022 Class Driver & ApplyIf HP LaserJet 1022 Class Driver is not available.  download from this site: http://support.hp.com/us-en/drivers/selfservice/HP-LaserJet-1000-Printer-series/439424/model/439431Print a test page.


SharePoint Online - Branding with CSS

-----------------------------------------------------------------------------------------
This post is related to a larger group of posts called Migrate SharePoint to Office 365 - Planning & Steps
-----------------------------------------------------------------------------------------

I've gone with a Publishing Site Collection so that I can use the 'Alternate CSS URL' in Site Settings > Master Page.

First off, thanks to the following websites which helped get me started:
http://blog.sharepointexperience.com/2015/02/sptechcon-austin-february-2015/#more-2766http://blog.sharepointexperience.com/2015/01/to-brand-or-not-to-brand/ Here's what the end result will look like (if you have the same fonts):


Now for the CSS...

/* Design By : Brett Randall*/
/*     Design Colours: Greens/Greys/Blues */
/*     Last Modified : 28/02/2017*/
/*     Description: CSS to rebrand SharePoint Online Publishing Site Collection that is using seattle.master */

/* TABLE OF CONTENTS
-- -…