SharePoint 2013 How to add a new database
In this post, we will add the Adventure Works attached database.
Downloaded adventure works DB at http://blog.metrostarsystems.com/2014/01/03/sharepoint-2013-business-connectivity-services-search-and-profile-page-demo/
SET UP SQL DATABASES
For this example, we will be utilizing the AdventureWorks SQL 2012 database. It is a familiar database for a store that sells bikes that Microsoft likes to use. It has a relatively simple layout and some preconfigured SQL views that we can use for our external content types. We will perform the following steps:
- Download the Database
- Attach the Database
- Configure Security
Download Adventure Works Database
http://msftdbprodsamples.codeplex.com/releases/view/93587
http://msftdbprodsamples.codeplex.com/downloads/get/478214
Extract the MDF and LDF files to your SQL server.
Attach the Database
Open SQL Management Studio and connect to the Instance you want to store the AdventureWorks Database.
Put the database in the c:\bin directory.
Select Databases, and attach database,
This is how attaching a database should look. Select OK.
Once the database is attached you will need to grant permissions to the user you will be using to access the BDC Connection.
Added users CLD\SPDB and CLD\Jeffrey.Rikala as DB owner to the database.
Configure Security, by selecting Security, Logins, right mouse click, New Login
Turning on farm features
You now need to make sure the needed farm features are turned on.
Run Central Administration as a local administrator. Go to Central Administration, and select System Settings,
Manage farm Features
http://spent:22932/systemsettings.aspx
Turn on the following services by selecting active in the right column:
- Access Services
- Access Services 2010
- Business Data Connectivity Service
- Excel Services Application
- Machine Translation Service
- Managed Metadata Service
- Performance Point Service Application
- Search Service Application
- Secure Store Service
- SQL Server Powerpivot service application
- SQL Server Reporting Services Service Application
- User Profile Service Application
- Visio Graphics Service
- Word Automation Services
- Work Management Service Application
SET UP THE SERVICE APPLICATIONS IN SHAREPOINT
We are going to need 3 service applications to be configured to be able to display the AdventureWorks Data in our Search Results.
We will perform the following Steps:
- Create the necessary Service Applications
- Business Data Connectivity Service
- Secure Store service
- Search Service
- Configure the Search Service Application
Create the necessary Service Applications in SharePoint
Click on New then Select Secure Store Service and make any changes to the default you need.
I used the following settings to define the new Secure Store Service Application:
Click OK to create the service.
Now ensure the Secure Store Service is started.
Now ensure the Secure Store Service is started, go to Central Administration, Application Management, Manage services on server.
in this case, the secure store service was not started, so I selected Start next to it. After starting the service you will have to do an IIS reset. Go to Start, control panel, Administrative Tools, Internet Information Services Manager. Select the top instance, and the + sign to get more details. Select Sites, SharePoint – 8080, Right click on the site and select Manage web site, restart.
Configuration of the Secure Store Services
Run Central administration as administrator, go to Application Management, Manage service applications, click on Secure Store,
Click on Generate New Key to get Started.
Create a new passphrase for the key. I used ALEINyBSTv1QBs95BNTv
Click OK to create the passphrase.
You will now see:
Click on New to create a target application ID:
Enter the following information to create a target application ID:
Click Next to continue.
Click Next to Continue
Add any administrators to the administrator’s box and “Everyone” to the member’s box. This is the only location where you can set the Members of the Target Application and to change this you will need to edit the application and click through next until you get back to this screen.
Set the credentials for the target application.
Click on AdventureWorks and click on the Set button.
Enter your credentials with the domain name and password. Don’t worry that everyone is shown as the “Credential Owners”. What this means is that the credentials for any logged in user will be mapped to this accounts permission to access the data.
This SecureStore target will use the CLD\SPDB account, the password is Password123
Create the External Content Type
External Content Types are similar to regular content types in that they contain several fields that make up that particular content type. The main difference is that these fields are mapped to columns in the SQL database in this case. We are only going to be performing read operations for the purposes of search, but you could easily add write operations if you wanted to treat this as a regular SharePoint list. There are a lot of limitations to using External Content Type such as no workflows, version history, folders etc.
We will be performing the following actions:
- Create a new External Content Type
- Create a new data connection
- Create Read Item Operations
- Create Read List Operations
Create a New External Content Type
In order to map the data, we want to SharePoint we now have to open up SharePoint Designer 2013.
Select your site. If you have previously logged in, for example, the site will already be listed. For example, my site is:
Select External Content Types
Select External Content Type at the left-hand side top of the page.
Create a name for the external content type. Click on the name to give your connection a name. Click on Display Name to give your data connection a display name as well.
Select the External system link.
In the right-hand corner select the Add Connection button.
In the External Data Source Type Selection, select SQL Server.
Click OK.
Enter the name of your SQL server and the name of the database. Since we created a Windows account for our target application we will want to select that here. Enter the name of the Application we created in the Secure Store Service.
Select OK.
In the credentials for the application use the account you have configured for the Secure Store service.
Select OK.
you will see a database layout below:
However, on browsing Tables in the database I have received an error message:
This error indicates that I do not have a certain feature pack installed on the sql server. I
am using SQL Server 2012 on Server 2008R2.
In particular about SQL Server 2012:
I went to: http://www.microsoft.com/en-us/download/details.aspx?id=29065
Downloaded the x64 version of Microsoft® System CLR Types for Microsoft® SQL Server® 2012 and applied to my SQL server.
Selection of views shows the following defined views:
Select vProductAndDescription,
Right-click to bring up a menu, and select New Read Item Operation.
We will only be creating this operation since we only gave read access to the database and we want to return an individual, not a list.
Select Next.
Here we will select a unique value. ProductID is unique to each term. So select Product ID, On the right-hand column select Map to Identifier, and change the display name to AdventureWorksProductID.
Renaming fields makes it easier to find in SharePoint’s Search Schema.
Select Name, and on the right-hand column change Display Name to: AdventureWorksName.
Select ProductModel, and on the right-hand column change Display Name to: AdventureWorksProductModel.
Select CultureID, and on the right-hand column change Display Name to: AdventureWorksCultureID.
Select Description, and on the right-hand column change Display Name to: AdventureWorksDescription.
Select Next:
Set the Display Name to include AdventureWorks in each of the field names.
Select Finish.
Select Save at the top of the screen in order to make sure all the changes you have entered will be saved.
Create a Read List Operation
For this step, we are just going to go through the same steps as before to create the read list operations. The read list operation is necessary for the crawler to be able to crawl each particular item in the view. The main difference is that SharePoint recommends a filter to limit the number of items returned.
Select Operations Design View
Select the AdventureWorks database, Views, and vProductModelCatalogDescription
Right click on vProductModelCatalogDescription, and select New Read List Operation
Select Next.
We don’t have any items to filter, select Next.
Select Map to Identifier for the ProductModelID and select Finish.
CONFIGURE THE BUSINESS DATA CONNECTIVITY SERVICE
Here we will be creating the profile page for the items in the database as well as managing the permissions for both users and the search crawler. This is an important step often missed, leading to several authentication errors in the search crawl.
The steps for this action are:
- Create the Profile Page
- Configure Object and Metadata Store Permissions
Create the Profile Page
Run Central Administration as an administrator and go to Application Management. From Application Management go to Service Applications and select Manage service applications.
Select Business Data Service.
Select the AdventureWorks checkbox and then select Create/Upgrade Profile Page.
You will get an error
Click on Configure
Search
Used:
http://spent:8080/sites/WindowsHosting/
Went to: Spent:8080
Creating Object and Metadata Store Permissions
Run Central Administration as an administrator and go to Application Management. From Application Management go to Service Applications and select Manage service applications.
Select Business Data Service.
Select Configure
You will configure the profile pages to a site that doesn’t currently exist yet?
http://spent:8080/sites/WindowsHosting/BDCSearch
Set Everyone in metadata store permissions, gave everyone, execute access. Everyone changes into “All Authenticated Users”
Set Object Permissions, Selected everyone, add, and then assigned the Execute right.