Optimization Techniques and Security Trimming of BCS

1. Optimization Techniques and Security Trimming of BCS Patrick Curran, MCT May 12, 2012

2. Patrick Curran • MCT, MCITP, MCTS, MCP • Director of Operations, Federal for Planet Technologies. • SharePoint Architect. • Working with SharePoint since 2003. • Administrator / Developer • Architect / Implementer • Troubleshooting / Branding PCfromDC.blogspot.com @PCfromDC

3. Describe scenario and objective. • Prerequisite. • Review farm. • Talk… • Verify Our Solutions. • Conclusion.

4. Scenario and Objectives Scenario: CIO says that employees must validate their HR data on an annual basis. The CIO wants the users to be able to view their own information (including PII) at their discretion. HR already has a database with staff information. Objectives: • Give the end-user the ability to securely see and update only their information. Let’s get started!

5. Prerequisite and Farm Information Prerequisite: External LOB system is accessible. Farm Information: Let’s get started!

6. Create SQL Account

7. Quick Review of SQL… The GET stored procedure:

8. Let’s Get Started… • Create the Secure Store Target Applications • We are now going to create the accounts that will be used to access our data on the BI database. We are going to create a Read/Write application ID. Start off by going to Central Admin  Application Management  Manage Service Applications  Secure Store Service Application. If you get this screen, please click the Generate New Key button in the ribbon and follow the directions…

9. Create the Secure Store Target Applications

10. Create the Secure Store Target Applications

11. Create the Secure Store Target Applications

12. Create the Secure Store Target Applications Add your Application Administrator and click OK to create the SQL Read/Write Application. We now need to add the Login Name and Password to the Application:

13. Create the Secure Store Target Applications

14. Create External Content Types Warning: If you are going to create your External Content Types using Windows Authentication, you will want to run SharePoint Designer from within SharePoint’s Domain. We now are going to open up SharePoint Designer 2010 and open up our BI Site. In the Navigation Pane, you will see a Site Object call External Content Types, and select it. If everything in the Ribbon is greyed out, click inside of the External Content Type work area (somewhere near the middle of the screen) to activate the buttons. Click New External Content Type…

15. Create External Content Types

16. Create External Content Types

17. Create External Content Types

18. Create External Content Types

19. Create External Content Types

20. Create External Content Types

21. Create External Content Types

22. Create External Content Types

23. Create External Content Types

24. Create External Content Types

25. Create External Content Types

26. Create External Content Types

27. Create External Content Types

28. Create External Content Types

29. Create External Content Types

30. Set BDC Obj Permissions Central Administration  Application Management  Manage service applications  Business Data Connectivity Service

31. Set BDC Obj Permissions

32. Set BDC Obj Permissions

33. Create External List

34. Create External List

35. Create External List

36. Update Data So now, let’s update Fred’s information by going to our list, and entering some Data…

37. Conclusion Having the ability for a user to modify their own information is crucial to keep data updated and reliable. By following the steps above, we can keep everyone up to date with their personal information and keep the CIO happy. And most importantly… We can now do it without the risk of getting fired for exposing everyone’s PII to the world by accident.

38. Thank You!!! Email: pcurran@go-planet.com Blog: PCfromDC.blogspot.com Twitter: @PCfromDC