SharePoint 2013 Secure Store Service and MS SQL

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.

  1. 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 Create two local or domain user accounts
  2. We can make sure that our user “LYRAdmitry.kaloshin” can’t access to MS SQL database NorthWindUser can’t access to MS SQL database
  3. 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”Encrypt the credential database using a new key
  4. Now we are ready to create new Secure Store Target Application.Create new Secure Store Target Application
  5. 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”. Enter Secure Store Target Application settings
  6. 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”Specify the credential fields for your Secure Store Target Application
  7. 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 Add the users and groups that will be mapped to the credentials defined for the Target Application
  8. Click on created Target Application ID and you’ll see a drop down menu. Select “Set Credentials” item to set MS SQL impersonation credentials. Set MS SQL impersonation credentials.
  9. 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 Enter MS SQL username and password for impersonation
  10. 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.
  11. Open Meta Man. To connect to SQL Server click appropriate data source item on the left pane on the initial screen.Click MS SQL server
  12. 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.MS SQL connection settings using Secure Store
  13. The combo box will now be populated with your database server names. List of databases
  14. Select one database server name and press arrow in order to connect to selected data sourceConnect to MS SQL server
  15. 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.Click "Tables" item
  16. Now we can see listed all the tables for this particular database.List of tables
  17. 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”.Drag a table on the design surface
  18. 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.Configure the external content type
  19. You can see created external content type on the design surface with the listed methods and identifier.Created External Content Type
  20. 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.Meta Man settings
  21. In the model deployment URL textbox you should manually type URL of SharePoint 2013 Central Administration . Click “Save” button.Set BDC model deployment URL
  22. 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.Deploy BDC model file
  23. SharePoint 2013 will now go through and validate your BDC model file is correct, once it is done you will get a success dialog.BDC Model was imported

     

     

  24. Browse to the site where you want to add the External List. Click “Site Contents”->”add an app”.Add an app to SharePoint site

     

     

  25. Select “External List”Select “External List”

     

     

  26. Enter a name for External list and click on the icon to show the available External Content Types External list name

     

     

  27. Select our generated Northwind_MetaMan.Territories External Content Type, click “OK” Select External Content Type

     

     

  28. Click “Create”Create External List

     

     

  29. 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.External data from MS SQL database is displayed on SharePoint 2013 page using Secure Store Service

     

     

  30. If you re-login to the site as another user and browse created external list, you won’t be able to see the data

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

<Dmitry Kaloshin/>