On premise SQL to SharePoint online O365

Yes its quite complex to accomplish this over cloud then the so simple task on premise using BCS.

Microsoft should have to think about the security on its SharePoint online.  There are quite no direct documentation available which makes us unhappy every time we are @ the end of such articles. 

Okay, First

Login to your Azure subscription and create new SQL database.

Download "Microsoft SQL Data Sync Agent" from http://www.microsoft.com/en-in/download/details.aspx?id=27693 and install. On Install it asks you username : enter it like domain\username and password. and hit ok.

1. Go to azure and click on 'Add Sync' and select New Sync agent,

Once created, click on Manage ley and hot Generate and copy and paste in to the agent and hit ok.

2. Once the key is submitted, click on +Register  , Enter your on premise SQL server & Database.

Once it is added , now it will be available in to azure for syncing. For this, we need to add the 'New Sync Group', same click on Add sync and now select sync group, in hub enter your SQL username and password you entered earlier on creating new database. Next in reference database, make sure you will be seeing your own premise SQL database you have added in to your agent. If it is not visible, try refresh your page and do the same again, if not then there is some issue in your agent, resolution is to go back to your agent and there is s option to 'Test Connection'. Click on it, if it say Connection Successful, Then it is fine o go ahead else check your firewall and all network related issue.

Also, you can connect azure in to SQL management studio https://www.microsoft.com/en-in/download/details.aspx?id=29062 (download on management studio ~600MB) Also, in many organization there are two WIFI, guest and corp, where corp is more restricted via firewall, so connect to your Guest account so that it can communicate well.

Now you need to add your on premise SQL server machine where you will be creating the BCS also, for that go to azure and click on Manage allowed IP address.

After that just enetr the right pointed arrow, as it will auto populate your IP.

Once it is added, now its time to create BCS yay!!!

Open SP Designer, open your SharePoint site,click on External content type from the left navigation pane. then click on 'external content type from the top'

after click on the below 'external system',

 it will pop for SQL server name and others.

Here you need to add Azure Database server and 'Database' name , which you can find it by going to azure portal SQL database and click on 1. 'Show Connection String' & then check out the Database server name.

Now in order to make the connection you need to have the Secure Store, which you need to go to your SharePoint Admin team or if you may have the access to the admin portal that will help you to do quickly.

For Secure Store:  go to SharePoint admin panel , click on the secure store from the right, and click on New.

Enter application id and display name anything you want and make sure that name is required in the BCS where we left Designer. In Administrator, add your name and in Members add Everyone. Hit save. Now we need to set credentials to secure store to work with azure SQL database.

On your secure store page, select your secure store and click on 'Set' and enter your Azure sql authentication Username and password and hit 'OK'.

Now go back to the designer and enter the Database server name, database name and secure store name we created earlier. 

If it shows the network related error or database not accessible or user does not have permission then just connect your SQL azure to the management studio and hit the below URL's.

exec sp_addrolemember 'db_owner', <username>'; 

Create User <username> from login <username>;

If it says already exist or some error like that then , enter some other name and change it to secure store credentials too. once it is done. go to designer again and try to connect again.

Now we are connected successfully, yippee!!

Now select the table, right click and select create all operations and follow the wizard until finish. Once it is done hit save and click on 'Create Lists & Form' &

a pop up will open , hit 'Yes' and select 'create new external list' radio button and enter the details & hit 'OK'.

But before accessing the list in to SharePoint we need to grant access out External content type to the SharePoint. for this we need to 'Set Metadata store Permissions'. else you will be getting the 

 “Access denied by Business Data Connectivity” error. For this go to bcs to the left and click on 'Manage BDC models and external content types' 

and click on Set metadata store permissions

 After that Grant Everyone as a Execute permission.(Make sure to be more restrictive in your production end) & hit 'OK'.

Now go to your SharePoint site and then to 'Show Contents', and there you can see your list, just click on the name of the list and here you can see your SQL azure list on SharePoint online.

For users it looks like On premise SQL to SharePoint online, But in actual it is connected to Azure SQL which is synced with On premise database. You can also schedule the sync timing by clicking on the SYNC option to the right in your database page in azure.

here we have created BCS with on premise SQL successfully.

Have a nyc day..happy sharepointing...:p