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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi HSI_guelph,
use SecurityTokenServiceApplic
use SecurityTokenServiceApplic
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:
http://technet.microsoft.com/en-us/library/ee806866(v=office.14).aspx
http://technet.microsoft.com/en-us/library/ee806866(v=office.14).aspx
ASKER
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.
Thank you for your reply! Please post a link to the finished article when you are done with it.
Hi HSI,
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
INT
, VARCHAR(24)
, VARCHAR(128)
, DATETIME
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.
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
INT
, VARCHAR(24)
, VARCHAR(128)
, DATETIME
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.
ASKER
In the Application Pool I have 5 options: SearchQuery, SearchService, SecurityTokenServiceApplic