SharePoint 2010 Secure Store Service and Oracle

 BCS Meta Man, Business Connectivity Services.

In this walkthrough we will demonstrate using SharePoint 2010 Secure Store in Business Connectivity Services for access to Oracle by means of BCS Meta Man.

The Secure Store Service replaces the single sign-on (SSO) feature in SharePoint 2010. 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:
    • Microsoft Visual Studio 2010 Professional or higher
    • Microsoft SharePoint Server 2010
    • BCS Meta Man 2.6.0.0 Beta or above
    • At least one simple local or domain user apart from Administrator, e.g. dmitry.kaloshin 

      1 4 thumb SharePoint 2010 Secure Store Service and Oracle

  2. 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”

    2 a thumb SharePoint 2010 Secure Store Service and Oracle

  3. Now we are ready to create new Secure Store Target Application

    2 thumb SharePoint 2010 Secure Store Service and Oracle

  4. Click “New” to create new Secure Store Target Application. Type your Target Application ID i.e. “OracleID”, fill in others fields and choose “Group” as your Target Application Type and click “Next”

    3 thumb SharePoint 2010 Secure Store Service and Oracle

  5. In order to specify the credential fields for your Secure Store Target Application fill in the names for two new fields, choose “Username” and “Password” field types and click “Next”

    4 thumb SharePoint 2010 Secure Store Service and Oracle

  6. 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 “HERCULESdmitry.kaloshin” in our case. Click “OK” button

    5 thumb SharePoint 2010 Secure Store Service and Oracle

  7. Click on created Target Application ID and you’ll see a drop down menu. Select “Set Credentials” item to set Oracle impersonation credentials

    6 thumb SharePoint 2010 Secure Store Service and Oracle

  8. Enter Oracle username and password that will be used for impersonation by created Secure Store Target Application. Click “OK” button

    7 thumb SharePoint 2010 Secure Store Service and Oracle

  9. Now we will create Business Data Connectivity Model file with BCS Meta Man and configure External Content Type to point to the “OracleID” Secure Store Target Application ID
  10. Open Visual Studio 2010
  11. Add a New Project
  12. Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
  13. Give your project a name i.e OracleSecureStoreBCSMetaManDemo and click “OK”

    12 thumb SharePoint 2010 Secure Store Service and Oracle

  14. On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button

    13 thumb SharePoint 2010 Secure Store Service and Oracle

  15. If prompted, click “Trial” on the licensing dialog
  16. To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way

    15 thumb SharePoint 2010 Secure Store Service and Oracle

  17. Click the “Add Connection” button to show the “Connection Dialog”
  18. Select “Oracle” as Data Source type, enter your Oracle server, choose “Use Secure Store” as Authentication Mode, enter created Secure Store Target Application ID and click “Connect” button

    17 thumb SharePoint 2010 Secure Store Service and Oracle

  19. You will be prompted to enter Oracle username and password being used for impersonation

    18 thumb SharePoint 2010 Secure Store Service and Oracle

  20. The Data Source Explorer will now be populated with your Oracle data

    19 thumb SharePoint 2010 Secure Store Service and Oracle

  21. We will be working with the “HR”.”DEPARTMENT” table, to add it to the Model , just drag it from the “Data Source Explorer” onto the “Diagram”
  22. When you drop the table you will be shown the following dialog, we should choose “Database , click “OK”

    21 thumb SharePoint 2010 Secure Store Service and Oracle

  23. Accept the default entity name by clicking “Next”

    22 thumb SharePoint 2010 Secure Store Service and Oracle

  24. Accept the default “Identifiers” by clicking “Next”

    23 thumb SharePoint 2010 Secure Store Service and Oracle

  25. Accept the default 3 methods to be created, click “Generate” (The default 3 methods are: “Finder” – Returns all items, “Specific Finder” – Returns a single item by identifier, “IdEnumerator” – Returns just the Identifiers for all of the items )

    24 thumb SharePoint 2010 Secure Store Service and Oracle

  26. Select menu “Build”->“Deploy Solution” in Visual Studio
  27. Now using this instructions BCS : Access Denied. You do not have permission to access this content we will set permissions to created External Content Type “HR_DEPARTMENTS” within SharePoint 2010 for our local user “HERCULESdmitry.kaloshin”

    26 thumb SharePoint 2010 Secure Store Service and Oracle

  28. Now open your SharePoint page and login to the site as “HERCULESdmitry.kaloshin”. Add a new External List by selection on loaded SharePoint page “Site Action”->“More Options”->“External List”

    27 thumb SharePoint 2010 Secure Store Service and Oracle

  29. Click on the icon to show the available External Content Types

    28 thumb SharePoint 2010 Secure Store Service and Oracle

  30. Select our “OracleSecureStoreBCSMetaManDemo.HR_DEPARTMENTS” External Content Type, click “OK”

    29 thumb SharePoint 2010 Secure Store Service and Oracle

  31. Click “Create”
  32. Your External System data from Oracle database is now displayed in the SharePoint External List

    30 thumb SharePoint 2010 Secure Store Service and Oracle

  33. If you 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

  1. Gustavo Reis

    Any advice on how I can retrieve the server name/datasource from the secure store as well?

     
  2. Krishna

    Could you please guide me how to Integrate Hyperion Essbase with Secure store :)

    Thanks Krishna

     
  3. Hi Dear,
    Thanks for such a nice explanations , would you please me that in this approach the records are where stored physically in Share -point or in Oracle-DB

     
  4. Rich

    Does the Secure Store service pass it’s authentication credentials to Oracle (running on *nix) in clear text? My understanding is that if Oracle is not running OAS it cannot share keys making encryption between the webserver and the DB impossible. Does Secure Store support this kind of shared key with OAS enabled Oracle?

    Thank you for this post!

     

Leave a Comment

Your email address will not be published. Required fields are marked *

*

* Copy This Password *

* Type Or Paste Password Here *

*