Bringing SQL table data into SharePoint 2010 Foundation

I am trying to bring data over from our SQL database so we can have lists and columns populated from data in our database.  We rebuilt our SharePoint site and the Report Server but the original database is on a separate machine so it wasn't affected when our server array went down.  I tried adding an External Content Type through the browser but when I opened the ECT Picker dialog window it stated 'External Content Types are not available.  Contact your system administrator.'  My supervisor set up SharePoint 2010 but is not an expert so I am trying to figure out if there was some setting or something he has to do to enable External Content Types and help direct on what he needs to do.  

In SharePoint each person is logged in with the credentials they used to log into their computer/laptop.  For the report server I log in using admin credentials.  Trying to set up external content types before I had authentication issues.  I need to make sure that the credentials are protected and trying to follow one guide I came to a point where it said that the username and password would be shown as plain text in the connection string.

Can anyone outline the way to go about accessing SQL data in SharePoint?  A lot of articles start at a point where everything is already set up to work correctly and they can just go ahead and do the task they are explaining.  First there might be settings that need to be set on SharePoint that got missed in the re-construction?  Then authentication through Secure Store Application?  Do we need to set something up for every SharePoint user or can we just set up one for SharePoint to grab the data?  Then move to creating an ECT through SharePoint Designer or the browser?  

Any direction would be greatly appreciated!

Data is on a Microsoft SQL Server 2005 database.  We use Microsoft SQL Server 2008 R2 for our reports and SharePoint 2010 Foundation for our site.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi HSI_guelph,

      Oh boy, you are literally asking something that I finished JUST last night.  My objective was the same, pull information from my SQL database (not the SharePoint content db or list db) but rather Microsoft Dynamics GP.  

I am currently writing an article to describe it, but until that's done let me give you the outline and then point you to the best resources I found for accomplishing this.

       Central Administration>Application Management>Manage Services on this Server> Click on Secure Store Service and start it.

Step 2:  Provision the NEW Secure Store Service Application:
 Central Administration > Manage Service Application > New (drop-down from the ribbon) > Secure Store Service. Provide a name for this service application, choose a database and choose an application pool or create a new one.
(note this may already be created for you... if you see the application listed in the services list below, just click on it and click MANAGE instead)

(if you've never done this before on this server do step 3, otherwise SKIP)
Step 3: Generate a new Key
After either creating the application from Step 2, or realizing it existed and clicking on MANAGE you will see a warning that tells you that you must generate a new key).  Click GENERATE NEW KEY.  It will prompt you for a passphrase that will govern the SSA you just created... Important: you will be asked to create a passphrase that SharePoint will never again show you, write this passphrase down and store it somewhere!!  Otherwise you will never see it again!!

Step 4: Create a NEW application
Now that you configured your Secure Store Application you need to create one that will authenticate to your SQL database in a later step.  CLICK NEW on the top left to create a new application.  

Name it whatever you want, let the display name be whatever you want.. I would just suggest calling it the site that you use for my example I used "Rental" for name and display, I used my email address for the owner and MOST IMPORTANTLY select "GROUP" and "none" click "OK"

-- Leave the defaults in the next field for Windows User name and password unless you have a good reason to change them.  

Step 5: Set administrators for the application you created.  I used myself and a dummy account - the farm admin (which I am also) automatically can access this.

Important: under MEMBERS I used the base permissions for the site I planned to use this for.. Example... I have a site where I want users to view rental agreements, the only way users access the site is if they are in the Active Directory Group "rental" so I added "rental" to the members of this application.  

Now the application is created

Step 6:  You should be looking at your application in the screen where you originally created it... now click on the dropdown of the app and click SET CREDENTIALS... this is where you need to provide the user name and password to log into the server that your SQL Server is installed on... this is masked and no users can see it... I used my user account.

Click OK and you have the first part done.

Step 7: go to the TOP level site that you want to have access to this particular application (I used a subsite RENTAL below my main site.. but you can use whatever you want).. From that top level site, click on SITE ACTIONS>EDIT IN SHAREPOINT DESIGNER

Step 8: Using SharePoint Designer CREATE AN EXTERNAL CONTENT TYPE
    On the navigation pane on the left click EXTERNAL CONTENT TYPE.. if you haven't made one yet.. there wont' be any items, on the type left click the icon for NEW EXTERNAL CONTENT TYPE.  You should see a window called External Content Type Information with the following:
Name:   click on the link to the right and name your E.C.T.
Display Name: click on the link to the right and give it a display name.
Office Item Type;
Office Sync for external List:
External System:

Now click to the right of External System where it says "click here to discover external data sources and define operations"

If you don't have any connections click ADD CONNECTION and choose SQL SERVER.

A) Enter the machine name that SQL Server is installed on
B) Enter the Database name you want to pull from


Then enter the name you created earlier (mine was RENTAL).  

Click OK

Step 9: Once your connection validates you should see the database name in your window.  You can access VIEWS, TABLES and ROUTINES...  If I were you, I pause here -- and go create a view that you want to use that includes all your columns from whatever tables you want to view in this particular instance...

Step 10: With your view created (or if you use one already existing or use a table) click on the database to expand it, click on either Tables or Views to expand those lists... NOTE: this may take a while... I have 30,000 tables so it took about 5 minutes to load.  Go to the bathroom or something :)

Step 11: Once you have your view selected you have to do 2 more operations to save it: Create a READ LIST and create a READ ITEM.  -- this is important as what you select will become what is available to you in SharePoint.   Scroll down to the VIEW you want... and right click it and select "NEW READ ITEM OPERATION" follow the very brief wizard here, select the columns you want from the list, and name them the way you want them to appear... now right click on the same view again and select "NEW READ LIST OPERATION" same deal.. very short and simple wizard where you select the columns you want...

Step 12:  With the 2 items done from 11 you can SAVE your content type... CLICK SAVE at the top left and remember the name of your content type...

Step 13: Back to SharePoint-- go back to your top level sharepoint site... you can now create a document library or list with a column that has a lookup to an external content type.. for demo purposes I'm assuming you already have a list... Just click ADD COLUMN in the LIST SETTINGS and use the option external content type in the E.C.T area of the create column click the ICON that says "SELECT EXTERNAL CONTENT TYPE" looks like a database w/ a page... your type should be there... pick the column field you want to use as a lookup and enjoy.

Sorry if this is a little confusing.. here's one article that I used to get me halfway through this... it skips the last half.. but with mine you should be able to get there:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HSI_guelphAuthor Commented:
I'm very excited and trying this out right now!

In the Application Pool I have 5 options: SearchQuery, SearchService, SecurityTokenServiceApplicationPool, SharePoint Web Services Default and SharePoint Web Service System.  I'm going to try SP Web Services default but can you let me know which one you think I should use?
Hi HSI_guelph,

use SecurityTokenServiceApplicationPool
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

As a follow up you could also create a new one - mine uses one I created a while back and it uses one of the four managed accounts I created for security called spcontent -- I believe that Microsoft wants you to create a completely separate managed account and application pool -- I didn't go that far but here's an article I found on it if you need something to make you fall asleep:
HSI_guelphAuthor Commented:
My supervisor wants to add my credentials to the SQL database so when I add an external connection it will take my authentication from my log in.  But I'm concerned about how it works.  Does it check the authentication every time it syncs the list with the database?  Does it mean I'll have to access the list to get it to sync up or will it do it automatically whenever the list is called?  Will there be any problems for other people who have different log in credentials than me when they go to see the external list?  

Thank you for your reply!  Please post a link to the finished article when you are done with it.

        What you are essentially doing, is creating a secure connection to your SQL database and then you are allowing the external content type to retrieve data on behalf of your users by impersonating your user account.  Once you have completed the setup, there's no further action required - anyone with permissions to access the document library or the list using it, will be able to retrieve the information - they do not get to see your credentials or even know how it is being returned.   You won't have to be connected or logged in either.  Now, if your password changes, you would have to change the credentials in your setup as well otherwise it would cease to work.  If you weren't given proper permissions to access the view or table you are retrieving (or your permissions are revoked) it would cease to function as well.  

One other note, let's say you have a view that has 4 columns with datatypes

, VARCHAR(128)

and you've connected up your ECT to that view, and then later you go in and modify the view so that the DATETIME is a DATE or the INT is a VARCHAR -- your users will see a warning when they try to use the lookups saying that it is configured incorrectly.     So if you make changes to the underlying view's datatypes, you will need to go into SharePoint Designer and modify the columns to reflect the new types.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.