Trusted data connection libraries
Trusted data connection libraries are SharePoint Server 2010 data
connection libraries that contain data connection files that Excel Services
will trust to use to connect to databases. These files contain everything that
Excel Services and Excel client have to have to connect to an external data
source. Data connection libraries enable broad reuse and sharing of data connections.
Excel Services does not use data connection files that are not
stored in a trusted data connection library. However, data connection
information can be embedded directly in a workbook that is trying to make a
connection.
You can create different trusted data connection libraries for
different purposes or projects, and you can customize the settings and
permissions to the libraries accordingly.
Data connections connect workbooks to data providers. For
workbooks that use the same data connection file, changing the data connection
file is all that is required to change connection information; changing the
individual workbooks is not necessary.
Initially, there are no Excel Services trusted data connection
libraries (except for Report Center sites, which create their own trusted data
connection file). To store data connection files, you must create at least one
trusted data connection library.
1.
In Microsoft SharePoint
2010 Central Administration, in the Application Management section, click Manage service applications.
2.
On the Manage Service Applications page, click the Excel Services service application that you want
to configure.
3.
On the Manage Excel
Services page, click Trusted
Data Connection Libraries.
1.
On the Excel Services
Application Trusted Data Connection Libraries page, click Add Trusted Data Connection Library.
2.
On the Excel Services
Application Add Trusted Data Connection Library page, in the Location section,
type the address of the trusted data connection library in the Address box.
3.
In the Description box, you
can also type a description of the purpose for this trusted data connection
library.
4.
Click OK.
1.
On the Excel Services
Application Trusted Data Connection Libraries page, either click the data
connection library that you want to configure or point to the data connection
library, click the arrow that appears, and then click Edit.
1.
On the Excel Services
Application Trusted Data Connection Libraries page, point to the data
connection library that you want to delete, click the arrow that appears, and
then click Delete.
2.
Click OK in the
message box that asks whether you want to continue with the deletion.
Trusted data providers
Trusted data providers are data providers from which Excel
Services accesses data. A data provider is a database type combined with a
protocol for accessing data (for example, SQL Server combined with ODBC).
- Excel Services does not access
data that does not come from a trusted data provider.
- Excel Services contains entries
for common data providers. Add additional data providers as needed.
1.
In the Central
Administration page, in the Application Management section, click Manage service applications.
2.
On the Manage Service Applications page, click the Excel Services service application that you want
to configure.
3.
On the Manage Excel
Services page, click Trusted
Data Providers.
1.
On the Excel Services
Application Trusted Data Providers page, click Add Trusted Data Provider.
2.
On the Excel Services
Application Add Trusted Data Provider page, in the Provider section,
type the provider ID of the trusted data provider in the Provider ID box (for
example, type SQL Server). Look in a valid connection string to find the
provider ID.
3.
Under Provider Type, select
one of the following:
·
OLE DB Select this option to access data by using Object
Linking and Embedding (OLE).
·
ODBC Select this option to access data by using Open
Database Connectivity (ODBC).
·
ODBC DSN Select this option to access data by using Open
Database Connectivity with Data Source Name (ODBC DSN).
4.
In the Description box, you
can also type a description of the purpose for this trusted data provider.
5.
Click OK.
1.
On the Excel Services
Application Trusted Data Providers page, click Edit on the
menu of the data provider that you want to configure.
1.
On the Excel Services
Application Trusted Data Providers page, click Delete on the
menu of the data provider that you want to delete.
2.
Click OK in the
message box that asks whether you want to continue with the deletion.
Manage Excel Services trusted file locations
Excel Services administrators can add new trusted file locations
as needed. Trusted file locations are either SharePoint sites, UNC paths, or
HTTP Web sites from which a server that is running Excel Services is permitted
to access workbooks.
1.
On the SharePoint Central
Administration Web site home page, in the Application Management section,
click Manage
service applications.
2.
On the Manage service
applications page, click the Excel Services service application that you want
to configure.
3.
On the Manage Excel
Services Application page, click Trusted File Locations.
4.
On the Excel Services
Application Trusted File Locations page, click Add Trusted File Location.
Use
Secure Store with SQL Server Authentication (SharePoint Server 2010)
To use Secure Store for SQL Server authentication, you must create
a target application which contains the SQL Server login with data access
(usually db_datareader
permissions). Use the following procedure to create the target application.
1.
On the SharePoint Central
Administration home page, under Application Management, click Manage Service Applications.
2.
Click the Secure Store
service application.
3.
On the Edit tab,
click New.
4.
On the Target Application
Settings page:
a.
Type an application ID in
the Target
Application ID text box.
b.
Type a display name in the Display Name text
box.
c.
Type an e-mail address in
the Contact
E-mail text box.
d.
From the Target Application Type drop-down list, choose Group.
e.
Click Next.
5.
On the Specify Credentials
page:
a.
Change the Windows User Name field name to User ID and change the associated Field Type from Windows User Name to User Name.
b.
Change the Windows Password field name to Password and change the associated Field Type from Windows Password to Password.
c.
Click Next.
6.
On the Specify Membership
page:
a.
In the Target Application Administrators box, type the name of the user account that you want to
administer this target application.
b.
In the Members box,
type the names of or browse for the users or Active Directory groups to which
you want to give data access. To give access to all users, type All Authenticated Users.
c.
Click OK.
Once you have created the target application, you must set the
credentials for the target application. These are the SQL Server credentials
that have access to your data source. Use the following procedure to set the
credentials for the target application.
1.
On the SharePoint Central
Administration home page, under Application Management, click Manage Service Applications.
2.
Click the Secure Store
service application.
3.
On the Secure Store page,
point to the target application that you created for SQL Server Authentication,
click the arrow that appears, and then click Set Credentials.
4.
In the User ID text
box, type the SQL Server account that has data access.
5.
In the Password and Confirm Password text boxes, type the password for the SQL Server account.
6.
Click OK.
Using Secure Store for SQL Server Authentication with Excel
Services or Visio Services requires that the Unattended Service Account be
configured. The Unattended Service Account requires no specific permissions for
this scenario; it only has to exist in the system. If you currently have an
Unattended Service Account configured, you can skip the procedures in this
section.
If you have not configured the Unattended Service Account for
Excel Services or Visio Services, you must first create a target application in
Secure Store that can be used as the Unattended Service Account. Use the
following procedure to create the target application.
1.
On the SharePoint Central
Administration home page, under Application Management, click Manage Service Applications.
2.
Click the Secure Store
service application.
3.
On the Edit tab,
click New.
4.
On the Target Application
Settings page:
a.
Type an application ID in the
Target Application ID text box.
b.
Type a display name in the Display Name text
box.
c.
Type an e-mail address in
the Contact
E-mail text box.
d.
From the Target Application Type drop-down list, choose Group.
e.
Click Next.
5.
On the Specify Credentials
page, click Next.
6.
On the Specify Membership
page:
a.
In the Target Application Administrators box, type the name of the user account that you want to
administer this target application.
b.
In the Members box,
type the name of the Windows account that runs the application pool for Excel
Services or Visio Services.
c.
Click OK.
Once the target application has been created, you must associate a
set of Windows credentials with it. This must be a Windows domain account, but
it requires no specific permissions for this scenario. Use the following
procedure to set the credentials for the target application.
1.
On the SharePoint Central
Administration home page, under Application Management, click Manage Service Applications.
2.
Click the Secure Store
service application.
3.
On the Secure Store page,
point to the target application that you created for the Unattended Service
Account, click the arrow that appears, and then click Set Credentials.
4.
In the Windows User Name text box, type the user name of a Windows account.
5.
In the Windows Password and Confirm
Windows Password text boxes, type the
password for the Windows account.
6.
Click OK.
Once the credentials have been set for the target application,
follow the Unattended Service Account configuration steps for Excel Services or
Visio Services in the following sections.
1.
On the SharePoint Central
Administration home page, under Application Management, click Manage Service Applications.
2.
Click the Excel Services service
application.
3.
Click Global Settings.
4.
In the External Data section,
type the name of the target application that you created for the Unattended
Service Account in the Application
ID text box.
5.
Click OK.
In order to use Excel Services with Secure Store, you must specify
a Secure Store target application in Excel before publishing the Excel workbook
to a SharePoint site to be rendered with Excel Services. In this case, you must
specify the Secure Store target application that you created that contains the
SQL Server login credentials.
Use the following procedure to specify a Secure Store target
application from Excel.
1.
In Excel, on the Data tab,
click From
Other Sources, and then click From SQL Server.
2.
On the Connect to Database
Server page:
a.
Type the name of the
instance of SQL Server that you want to connect to in the Server name text
box.
b.
Choose the Use the following User Name and Password option, and then type the user name and password of a SQL Server
account that has access to your data source.
c.
Click Next.
3.
On the Select Database and
Table page, select the database and table that you want to connect to, and then
click Next.
4.
On the Save Data Connection
File and Finish page, click Authentication
Settings.
5.
In the Excel Services Authentication Settings dialog box, select the SSS option, type the name of
the Secure Store target application that you created to use for SQL Server
Authentication, and then click OK.
6.
On the Save Data Connection
File and Finish page, type a name for the data connection file (or keep the
default) in the File Name text box, and then click Finish.
7.
In the Import Data dialog
box, choose one of the PivotTable options, and then click OK.
8.
In the SQL Server Login dialog box, type the password for the Login ID, and
then click OK.
No comments:
Post a Comment