SharePoint 2013 Secure Store Service

In this walkthrough we will show using SharePoint 2013 Secure Store Service in Business Connectivity Services for integration with MS SQL using Windows Authentication by means of Meta Man (the same way you may use Secure Store and SQL Server Authentication).

The Secure Store Service is authorization service that contains a secure database for storing credentials (e.g. user name and password) that are associated with application IDs. These application IDs can be used to authorize access to external database. Target Application IDs map users to the credentials that will be passed to the external data source. Mappings are available for groups (e.g. domain group) or individuals (e.g. local or domain users). When connecting to the external data source, the Business Connectivity Services runtime passes the mapped credentials from the Secure Store Service to the data source.

Prerequisites:
- SharePoint Server 2013
- Meta Man (it doesn’t necessarily need to be installed on the SharePoint server but it requires .net Framework 4.5 installed on the machine where you are going to run Meta Man from)
- MS SQL server with any sample database (for this walk through we are going to use the NorthWind sample database )
- At least two simple local or domain accounts e.g. Administrator and dmitry.kaloshin
We can make sure that our user “LYRAdmitry.kaloshin” can’t access to MS SQL database NorthWind
Now we should configure Secure Store Service Application. Open Application Management in Central Administration and then Manage Service Applications. Click Secure Store Service Application. If you first time configure Secure Store Service Application, you will be prompted to generate a key because credential database is encrypted by using a key. In the future you will be able to re-encrypt the database using a new key. Click “Generate New Key” button on the ribbon. On the Generate New Key dialog, enter a pass phrase string in the Pass Phrase edit box, and type the same string in the Confirm Pass Phrase edit box. Click “OK”
Now we are ready to create new Secure Store Target Application.
Click “New” to create new Secure Store Target Application. Type your Target Application ID i.e. “MSSQLWinAuth”, fill in others fields and choose “Group” as your Target Application Type and click “Next”.
In order to specify the credential fields for your Secure Store Target Application leave the names for two new fields as default, and leave “Windows User Name” and “Windows Password” field types and click “Next”
Add Target Application administrator and then add the users and groups that will be mapped to the credentials defined for this Target Application. There is only one local user “LYRAdmitry.kaloshin” in our case. Click “OK” button
Click on created Target Application ID and you’ll see a drop down menu. Select “Set Credentials” item to set MS SQL impersonation credentials.
Enter MS SQL username and password (used for Windows Authentication) that will be used for impersonation by created Secure Store Target Application. Click “OK” button
Now we will create Business Data Connectivity Model file with Meta Man and configure External Content Type to point to the “MSSQLWinAuth” Secure Store Target Application ID.
Open Meta Man. To connect to SQL Server click appropriate data source item on the left pane on the initial screen.
Enter the name of your MS SQL server and select the authentication mode in the appeared connection settings area on the right side . Tick “use secure store” checkbox, select “windows credentials” as secure store target, enter created Secure Store Target Application ID. Press arrow near “database name” combo box in order to retrieve list of available databases.
The combo box will now be populated with your database server names.
Select one database server name and press arrow in order to connect to selected data source
Upon successfully connecting to SQL Server you’ll see another window. In order to get a list of tables click “Tables” item in the list on the left pane.
Now we can see listed all the tables for this particular database.
We create an external content type by dragging and dropping a table onto our design surface which is the large area on the right side. We will be working with the “Territories” table, to add it to the model , just drag it onto the “Diagram”.
When you drop the table you will be shown the following dialog, which allows to configure the external content type before creation. You may modify the external content type name, change identifier and add the methods to the external content type. In this example we’ll accept all default values and click “Create” button.
You can see created external content type on the design surface with the listed methods and identifier.
Now all we need to do is generate our application definition file. First we need to set the path to save the file. We set this location by going “Settings” from the title bar.
In the model deployment URL textbox you should manually type URL of SharePoint 2013 Central Administration . Click “Save” button.
Once you have saved the URL to deploy your BDC model file to simply click “Deploy” button and your BDC model file will get deployed.
SharePoint 2013 will now go through and validate your BDC model file is correct, once it is done you will get a success dialog.
Browse to the site where you want to add the External List. Click “Site Contents”->”add an app”.
Select “External List”
Enter a name for External list and click on the icon to show the available External Content Types
Select our generated Northwind_MetaMan.Territories External Content Type, click “OK”
Click “Create”
Now re-login to the site as another user. I will use “LYRAdmitry.kaloshin”. Click on the created External List and the external data from MS SQL database will be displayed. We can see the data using an account that doesn’t have an access to the MS SQL database.
If you re-login to the site as another user and browse created external list, you won’t be able to see the data