SharePoint Add a database

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:

  1. Download the Database
  2. Attach the Database
  3. 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 you 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,

Attach DB

This is how the 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

 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:

  1. Create the necessary Service Applications
    1. Business Data Connectivity Service
    2. Secure Store service
    3. Search Service
  2. Configure the Search service Application

Create the necessary Service Applications in SharePoint

Appmgmt serviceapps

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:

create new store1

create new store1

create new store 2

create new store 2

 
Click OK to create the service.

 secure store created

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.

Server Search Services

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.

gen key

Create a new passphrase for the key, i used ALEINyBSTv1QBs95BNTv

Generate new key

Click OK to create the passphrase.

You will now see:

 

Secure store

Secure store

Click on New to create a target application ID:

New target App ID

Enter the following information to create a target application ID:

New secure store target application

Click Next to continue.

New secure store target application permissions

Click Next to Continue

Add any administrators to the administrators box and “Everyone” to the members 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.

create new secure store app

Set the credentials for the target application.

 

Click on AdventureWorks and click on the Set button.

2014-10-06_14-01-21

 

Enter your credentials with the domain name and password. Dont 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

set credientals for secure store target app group

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 like a regular SharePoint list.  There are a lot of limitations to using the External Content Type such as no workflows, version history, folders etc.

We will be performing the following actions:

  1. Create a new External Content Type
  2. Create a new data connection
  3. Create Read Item Operations
  4. 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:

recent sites

Select External Content Types

select external content types

Select External Content Type at the left hand side top of the page.

External Content Type left hand

Create a name for the external content type. Click on name to give your connection a name. Click on Display Name to give your data connection a display name as well.

 external content type info

Select the External system link.

 

In the right hand corner select the Add Connection button.

add connection

In the External Data Source Type Selection, select SQL Server.

external data source type selection

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.

sql server connection

Select OK.

In the credentials for the application use the account you have configured for the Secure Store service.

adventureworks AD setup

Select OK.

 

you will see a database layout below:

 Add connection general

However on browsing Tables in the database I have received an error message:

designer error

 

Which means.. I dont 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:

sql 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:

 adventureworks views

Select vProductAndDescription,

vproduct description

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.

All operations

read item

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.

 read item2

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:

read item3

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

op design view

Select the AdventureWorks database, Views, and vProductModelCatalogDescription

vProductModelCatalogDescription

Right click on vProductModelCatalogDescription, and select New Read List Operation

vProductModelCatalogDescription new read

read list properties

Select Next.

 read list nothing

We don’t have any items to filter, select Next.

 read parameters

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:

  1. Create the Profile Page
  2. 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.

bus

 

Select the AdventureWorks checkbox and then select Create/Upgrade Profile Page.

AdventureWorks

 

You will get an error

sp no errors

 

Click on Configure

configure

Search

external content 

Used:

http://spent:8080/sites/WindowsHosting/

Went to: Spent:8080

<Problems with this section>

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.

 Service Apps

Select Business Data Service.

Select Configure

configure selector

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.

 

 

When create/upgrade is launched, a dialog box appears with nothing in it!

 

<manage engine? >

Fiddler2?

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s