SharePoint 2010 Secure Store and DB2

By admin BCS BCS Meta Man Business Connectivity Services 1st March 2011

In this walkthrough we will show using SharePoint 2010 Secure Store in Business Connectivity Services for integration with DB2 via ODBC by means of BCS Meta Man. But the same way you can integrate with any data source that supports ODBC connectivity.

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.

Prerequisites:
- Microsoft SharePoint Server 2010
- Microsoft Visual Studio 2010 Professional or higher
- BCS Meta Man 2.7.0.0 or above
- Download and install “IBM Data Server Runtime Client (Windows 32-bit AMD and Intel x86)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where BCS Meta Man runs
- Download and install “IBM Data Server Runtime Client (Windows AMD64 and Intel EM64T)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where SharePoint 2010 runs
- At least one simple local or domain user apart from Administrator, e.g. dmitry.kaloshin
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. “DB2_ODBC_ID”, 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 fill in the names for two new fields, choose “Username” and “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 “HERCULESdmitry.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 DB2 impersonation credentials.
Enter DB2 username and password that will be used for impersonation by created Secure Store Target Application. Click “OK” button
Now we will create .net Assembly connector with BCS Meta Man and configure External Content Type to point to the “DB2_ODBC_ID” Secure Store Target Application ID.
Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e DB2SecureStoreBCSMetaManDemo and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
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
Click the “Add Connection” button to show the “Connection Dialog”
Select “ODBC Server” as Data Source type, enter DB2 ODBC connection string (for example it can be “Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE;Hostname=cygnus;port=50000;Protocol=TCPIP;”), enter double quotes as left and right delimiters and “?” as parameter symbol, choose “Use Secure Store” as Authentication Mode, enter created Secure Store Target Application ID “DB2_ODBC_ID” and click “Connect” button
You will be prompted to enter DB2 username and password being used for impersonation
The Data Source Explorer will now be populated with your DB2 data
We will be working with the “ADMINISTRATOR”.”DEPARTMENT” table, to add it to the Model , just drag it from the “Data Source Explorer” onto the “Diagram”
When you drop the table you will be shown the following dialog, we have to choose “.Net Assembly”, click “OK”
Accept the default entity name by clicking “Next”
Accept the default “Identifiers” by clicking “Next”
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 )
Select menu “Build”->“Deploy Solution” in Visual Studio
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 “ADMINISTRATOR_DEPARTMENTS” within SharePoint 2010 for our local user “HERCULESdmitry.kaloshin”
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”
Click on the icon to show the available External Content Types
Select our “DB2SecureStoreBCSMetaManDemo.Model.ADMINISTRATOR_DEPARTMENT” External Content Type, click “OK”
Click “Create”
Your External System data from DB2 database is now displayed in the SharePoint External List
If you login to the site as another user and browse created external list, you won’t be able to see the data