Your Ultimate BCS Guide – Notes from a weekend road trip

Well, I admit, there are many BCS articles, how-to guides, videos, To-Do’s, Not-To-Do’s out there. What’s gonna be so different with mine? At this point I don’t know, but I’m hoping as I type through something useful comes up.

I have looked at and followed so many of those ‘step-by-step’ articles, and honestly they are all good and well, but with most of them, I felt like they were rushing to get to the destination, rushing to make it work.. That time, you keep getting different errors and finally another F5 and you finally see the results from your test ‘Employee’ table. Yes, we’ll get there too, however, in the post, I wanna delay that Finally-Successful-F5 pleasure a little bit.

In our BCS journey, or maybe I should just say.. road trip.. We’ll have different passengers in the car riding with us at different times of the road trip.

Starting Point: InsanityVille

Final destination: The Great Township of BCS-Saints

Distance between: Toootally depends on you…

Now that we are all packed and ready to go, we are setting off with two old-time pals: AD Admin and DB Admin. Before we hit the road maybe I should probably mention that your car is a *brand new* SharePoint 2013 VM environment and you are NOT the-one-and-only driver of your fancy car – SP Admin. That’s a loooong trip, of course you’ll get tired and sleepy, so at times you’ll need to share the driver seat with Ms. SP Designer and Ms. BCS User. That being said, you’ll pick them up from some other towns on the way. We’ll meet those lovely ladies when time comes. For now, as I mentioned, we are setting off with our two grumpy old friends, AD Admin and DB Admin.

Chapter-1: Every road trip needs a Mr. AD Admin

Early in the morning, still chilly, yet you managed to get up and get out of the door and there are your friends already waiting for you outside… And yes, they are grumpy as usual. For some reason they always are. But that’s not our point in this post. You think to yourself already: Oh God! That’s gonna be one heck of a looong day… Oh yeah! It will.

You unlock the car, Mr. AD Admin takes the front passenger seat and DB Admin is still sleepy, he wants to sleep a little bit more on the back seat. No problem. Bags in the trunk, all set, go! First, stop, of course, coffee stop. Who goes on a road trip without Timmys. Our old friend Mr. AD’s all-time favorite is, yes, you guessed it, XL triple-triple..

Rule #1: Don’t ever demand anything from Mr. AD before he gets his morning coffee.

Shoot! You need a great favor from him. Whose gonna create all those service accounts, users and groups for you? Of course, he will. Maybe you buy the coffee today. At the end your AD Shopping List is full of stuff. Let’s see what’s on that list:

• SP_SecureStore Impersonation Account: This is the user account which holds the key to your test Northwind or AdventureWorks database. Make no mistake, this is NOT the service account which runs the Secure Store service application. We can call that fella, SP_Services. As far as this road trip is concerned, he is nowhere in the picture. The name of our key-maker is Mr. Contoso\sp_securestore.

  

   

• SP_Designers group: Those are the lovely SP Designer ladies, whose job is to use SharePoint Designer in order to create External Content Types and External Lists, along with other content updates. Do they even need to login to your SharePoint site? Not really… Imagine, all they see and love is SharePoint Designer but nothing else. What are their names? You only remember one: Ms. Contoso\designer1. That’s good enough for now.

  

   

• SP_BCS_Users group: Those are another lovely group of ladies, hmm… Say HR ladies. They will be using your beautifully crafted SharePoint HR portal to access *external* Employee list. All they have is HR Portal SharePoint site, and they have only Contribute permissions to that site. Our beloved business end-users. Oh, but you gotta be careful. If they see an error, they can easily make a BIG fuss about it and next thing you know, as SP Admin, you have 50 emails in your inbox in less than 5 mins. So, you gotta make sure those ladies are happy. I mean “really” happy! Especially, Ms. Contoso\bcs1.

  

Now that, Mr. AD got his morning coffee, you lay out your big “favor”. Yes, you ask TOO MUCH from him:

• 2 user groups: SP_Designers, SP_BCS_Users
• 3 user accounts: SP_SecureStore, BCS1, Designer1
• … also asking to make BCS1 and Designer1 a member of SP_BCS_Users and SP_Designers respectively.

You exactly now what kinda attitude you’ll get from him, but you know in the end it’s worth it. The fact that he got the first sip of his morning coffee, he replies “No, problem! But you owe me big time…”

Phew… That was easy… Didn’t go as bad as you expected. Could have been much worse like last time. Yeah that time just before spinning a new SP farm, you asked all those service accounts to be created. U remember that time? Such a disaster! You were lucky you didn’t get a black eye. But those days are long gone now. Your farm is up and running, ticking like a clock. But no one still forgets that big fight with Mr. AD over asking “Replicate Directory Changes” permission for Mr. Contoso\SP_UPS. Ok, ok.. Back to our road trip… J

It hasn’t been long, but we already reached Mr. AD’s destination. He had his triple-triple, he’s happy. You got your AD groups and accounts you needed, so you are happy… You pull over and let Mr. AD get off the car. Mr. DB Admin has been awake like for the last 5-10 mins, so he takes the passenger seat after Mr. AD’s happy departure. Right on!

Chapter-2: An unexpected hitchhiker: Ms. SP Designer

You guys are kinda best friends so it’s OK to have demands from each other. But you know Mr. DB Admin’s nature, he’s a little conservative when it comes to db permissions. Although you know you’ll get what you ask, better not push it. Say if you ask for db_owner, the most you can get can be a db_writer. But you know that already, so no problem. You guys are still best friends. Forever? Time will tell.

Off you guys go… Not long after, you come across a hitchhiker. You have a short debate with Mr. DB on the topic of To-Stop and Not-To-Stop. In the end, you decide to stop. She hops in.

After couple of mins intro chit-chat and next thing you know she is an SP Designer, whose job is to design and modify company’s HR Portal. She usually gets calls from HR manager on how to change the content but once in a while she gets some specific demands. She says she heard lots about External Content Types and External Lists, never tried herself. She remembers though, some other colleagues of hers, trying and failing, endless times… Like literally, endless times… She heard the infamous rumors about it. But she likes challenge in life. One of her biggest desires in this life now is to create an External Content Type with absolutely zero errors encountered while adding the SQL Server connection to her external content type. Based on her reckoning, this is the part most people lose it and totally go insane. She doesn’t wanna go insane and asks you, the very SP Admin, for help.

Can you do this SP Admin? Do you have what it takes to create an External Content Type with absolutely zero errors encountered? Only time can tell… But guess what, you already tried yourself million times and yes after failing million times, you know how to do it. Knowing is something else, being able to explain it properly is totally something else. He knows that… And imagine, explaining this while driving? But who cares, she’s cute! Mr. SP Admin will try his best, no matter what!

One eye on the mirror, SP Admin starts explaining it to Ms. SP Designer:

So, you know, you connect to your HR Portal site. Then, there is a link there on the left, saying External Content Types, you click there. On the main area, you type the name and description of your ECT, you know, same as usual. “Then, what?” asks SP Designer. Then, is the most exciting part of creating an external content type. Connecting to an external data source, most likely your SQL server.

Ha, ok! But what a minute, won’t I need access to that data source. I mean to SQL Server itself and then to HR database. “Yes, you will” says SP Admin, by turning to his so-called best friend Mr. DB Admin sitting quietly next to him in the passenger seat.

Chapter-3: Every SP Admin needs a best-friend! Better be a Mr. DB Admin

Now it’s Mr. DB’s turn try to impress the cute Ms. SP Designer. He opens his mouth and spills it out at once. Firstly, you’ll need to be able to login to db server. This needs to be granted on Server level security. All you need is public permissions.

But I need to access HR database, says her impatiently. I know… Then we’ll have to give you only read permissions on HR database. But we are sorry, we cannot give you more than Read permissions, since all you have to do is to read the schema details and create your external content type.

Somewhat satisfied and puzzled, she turns to SP Admin. “So, that’s it? That’s all I need?” “For now, yes!” But, don’t you have to do something before this? What about Secure Store Application ID? What is she gonna put there when asked?

Chapter-4: Guess, we need a Secure Store Application now.

So, what good is an SP Admin if he cannot create and configure a proper Secure Store Application for Ms. SP Designer? Oh, that’s easy… All she needs a group type Target Application.

Then put a meaningful prefix to windows user name and password fields, so when time comes, you can remember which credentials to provide. And that time will come very soon.

Then, provide the big boss as your target application admin. Now, slow down right here! Members? Hmm… What could that be? Well, those are the lovely HR ladies we mentioned before. People who will access your test database with the help of impersonated account, Mr. contoso\sp_securestore. All that employee table data, which will be only accessible by HR ladies. Not SP Designers, not nobody!

Who plays the hero here? That’s our one and only Mr. SP_SecureStore account. He’s the one who gets his hands dirty. He’s the one going down into deeps of database table and grabbing that data for you. He’s the one who will need access to test database as well. HR ladies will just pull the strings, and Mr. SP_SecureStore account will get the data for ’em. How? Coming soon…

So, when are we entering SP_SecureStore credentials, so that Secure Store service application keeps it somewhere safe and cozy? Right at this moment. This is the one time SP Admin will set the credentials for that target application and it’ll be secure for good.

Now, SP Admin sees why he had to put the prefix while creating the target application. Wink wink!

Job done, thinks SP Admin. All good and well. Let’s back to our cute Ms. SP Designer. SP Admin explains all that to her while driving, but still keeping his eyes on the road strictly. He thinks she gets it. Maybe all of that is a little TMI for her as well, who knows? Actually who cares? All she cares is if it’s gonna work or not.

Chapter-5: Back to SharePoint Designer

Now she knows what to put in that last textbox, Secure Store Application ID. That’s the ID of the target application Mr. SP Admin just fictitiously created while driving and passed on her, delivered to back seat with a sharp look from the mirror. So, it must be “NW”, then. Let’s try.

Hmm, getting challenged again. SP Designer enters her credentials and look what happens.

Oooppsss.. What’s that now? Who is to blame for ruining Ms. SP Designers dreams of creating External Content Type with zero errors encountered.

<Here comes a looooong honking!> SP Admin gets back to himself. He thinks he slightly fell asleep while driving. Wow! What a nightmare he saw in that couple of seconds. He thinks again and remembers. Of course BCS! How is Ms. SP Designer will be able to access BCS Service Application is she has no such permissions granted by me. How silly of me?

Off he goes to BCS in Central Admin, and provides the BCS Metadata Store permissions for her. But which ones? Well, since she’s creating external content type and external list, she just needs to edit stuff, so let’s give her only edit permissions.

Now, that must be it! With a confident voice, SP Admin tells her to try now. See what happens? There she tries it. And there it is… She got through for the first time, without any errors.

Rest is like a walk in the park. Select the table, list operations you want and follow the wizards and there you have your first External Content Type created.

Then she creates an External List by having recently created external content type selected. Since HR Manager wanted Employee list, so be it.

She thinks her job must be done by now. They also just arrived to the town she wanted to go, so perfect time. All farewell to Ms. Cute SP Designer, off she goes to her next journey. Did SP Admin forget to get her number? Of course, he didn’t. After all that help and making her dreams come true, SP Admin way deserved that phone number, don’t ya think? J

As soon as she left the car, our conservative best friend Mr. DBA had to mention. So, what’s gonna happen to her credentials on the server and database? I’m sorry old friend, but I have to revoke them. So be it, says Mr. SP Admin with the satisfaction of getting cute SP Designer’s phone number. Do it! She no need ’em any longer.

There Mr. DBA revokes all her permissions from SQL server and test database, like she never existed before. Better be safe than sorry, says he, afterwards, with a smirk on his unshaved face.

“Now what?” says SP Admin to DBA.

“You think after all that configuration SP Designer did, can HR Manager (contoso\bcs1) access Employee table data through Employee External List successfully?” But before answering that, they gotta have a hearty lunch.

Chapter-6: Didn’t we forget something?

I think you did. Mr. DBA already removed her db permissions both, server and database level, however you didn’t. Yes, that Metadata Store permissions you granted on BCS. Assuming she’ll never come back again, do you think she still needs that Edit permissions. Don’t think she does. So, let’s revoke that too and keep it clean.

Now, back to HR Manager, do u think she has everything she needs to see the external list data properly? I think the group created for her business users, SP_BCS_Users, need to be added her, just like we did for SP Designers too. Otherwise how can they communicate to external list data through BCS Service Application? So, let’s add that group here too and give HR Manager a shout to see what happens.

But hang on a minute! They don’t need to Edit the external content type nor the external list. They don’t work with SP Designer. Then they don’t need the same Edit permission we granted to SP Designers. They just need to Execute it.

Now, I’ve done my part, but Mr. DBA, don’t u forget something too? What about that one and only account which will have access to databases, contoso\sp_sercurestore account? Hmm… You might be right. So, let’s first add that one to SQL Server as simply public.

Then grant the READ and/or WRITE permissions on test database, Northwind.

 

But why Read and Write, asks Mr. DBA as you would expect from him. It’s easy to answer this one for SP Admin. If HR Manager/Users want to make modifications on Employee data, they will also need Write permissions on test db, along with Read permissions.

As long as you give a logical answer to Mr. DBA’s questions, he’ll be ok and you’ll get whichever permissions you need from him.

Chapter-7: Moment of Truth!

So, that’s it, then! This time we are really done. What’s gonna happen now when HR users try to access Employee external list? Shall we see, or is it still too early, are we still missing this and that? Moment of truth… Fingers crossed.

[In the meantime, somewhere out there some HR user tries to access the external list in HR Portal and …]

Tataaaa… It works! Wow, that was hell of a road trip. But hang on, we are still on the road… It’s just after lunch. I think we are just getting warmed up!

Stay tuned for Mr. SP Admin’s next adventures…

Coming up – A controlled experiment: All the possible errors you can get while configuring BCS.

Zieglers