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.