How to use Secure Store Service in an External Content Type in SharePoint Foundation 2013

When SharePoint Foundation 2013 was released and you saw Secure Store Service is included you almost jumped in joy as you know this will give you a lot of possibilities. You then started to tinker by creating a new page and a new external content type that can connect to your database.

As you are trying to create the service application

and set it up you are given this error message:

“Sorry, something went wrong The trial period for this product has expired or Secure Store Shared Service is not supported for this SKU.”.

Your initial reaction was WTF? even the error logs show the same message and does not give any clue on why it is giving an error. So you did a lot of Googling and here you are having the same problem as I am.

Well there is a good news and there is a bad news. The bad news is that this feature is not really meant to be included in SharePoint Foundation 2013, not official yet but here is what I found out when I did my own search.

A reply from BusyPart and al00fy at an MSDN forum states that the Secure Store Service is only available for the Enterprise Version.

I also had seen this blog post by exybzy where he did more than contacting Microsoft, he decompiled the assembly using ILSpy to see inside the method Microsoft.Office.SecureStoreService.Server.SecureStoreServiceApplicationProxy.Execute() and found out that method references an assembly called OfficeServerSettings.dll which isn’t bundled with SharePoint Foundation. For more info look at what he did here.

So it’s really sad, its like were kids that were promised with a sack of candy but actually we were only given a bag. Anyways that the bad news, it looks like it is not supported in Foundation version for now. My good news is that there are alternative ways to do this and that’s what we are going to do today, using that alternative method.

I am not sure if this will work for your scenario but for me it works perfect, so in this article I am under the assumption that you want to create a CRUD form in SharePoint connected to an existing SQL Server Database or any other RDBMS.

Lets start!

Lets say you have a database of Employees that contains a table called Employees with fields First Name, Last Name and Position.

Creating a page will be our first step so go to your team site and create a page.

Then in that page write edit the contents and write something, this will make your life easy later.

Now fire up Sharepoint Designer 2013, if you don’t have it you can download it here. Open you team site.
Now instead of creating an External Content Type you will need to create a Data Source. Go to Data Sources and click Database Connection.

Click Configure Database Connection

Set the Server Name and Authentication

You will receive this warning, it just says the password is sent as a clear text, this means any owner of the site can see this details.

Then choose your database and its tables, in our case it called Employees.

Choose what fields you want to expose, filter and sort.

Give it a name

Now go to the page you created earlier and Edit it in Advanced mode.

Now look for the text you entered and highlight it. Without that it will be harder to find where you want to start.

Now while the text is highlighted go to the Insert Tab then choose Data View -> Database Connections then the Employee Connection you just created.

The one highlighted will be overwritten with an auto generated code of the Data Source and Data Grid for the items on your database Employees. Retain you text cursor to the section it does not matter where as long as it’s within the Data Source or the Data Fields. Now you will see the Data View Tools, under the Options Tab we now add the commands you need so tick Edit and Insert. This creates the Edit and Insert command for your Data Source.

Now lets remove unnecessary columns like the ID which is automatically generated in the database, still in Options go to Add/Remove Columns and remove the ID columns.

Now lets amend the paging, by default is shows 10 and we will change it to show 30 per page. Still in Options go to Paging and choose Display Sets of 30 items.

Now let’s go to Design and tick the Header so it shows it, you can also tick Sort and Filter so each column heading will have that option.

Now save it, you will be presented with this warning. Click yes, it just tells you that you lost the site definition because you customized it.

Then you will be presented with another error only if you are using Wiki Pages. The message says that contents which are unsafe will be removed and since we never did any manual coding and its the Designer who did all the work then it will all be fine, so click yes.

Now browse to your page, you will then see a warning message saying you had customized it. You have the option to revert to template or not but I chose to revert because if not every action I did on the page it would show another instance of it. Anyways it did not harm whatever changes I made after I reverted.

You will notice at this point if you do not have data on the database the insert command will not show, you can adjust the codes to show the insert command even there is no data. If you have data at least of at least one record then the insert command will be presented.

There you go a CRUD from a SQL Database Connection.

And here is the snapshot of the data in the SQL Database