Use Secure Store with SQL Server Authentication (SharePoint Server 2010)

  • Makale

 

Applies to: SharePoint Server 2010

You can use Secure Store Service to store credentials for data sources that require SQL Server Authentication. You can then use these credentials to access data sources that require SQL Server Authentication through Excel Services in Microsoft SharePoint Server 2010 or Visio Services in Microsoft SharePoint Server 2010.

We recommend storing credentials in Secure Store over storing them in a workbook file or Office Data Connection file. When you store credentials in Secure Store, they are not stored in plain text and you can manage the credentials in a central location, which can be easier to update than credentials embedded in workbook files or ODC files.

Using Secure Store together with Excel Services or Visio Services to access data sources through SQL Server Authentication requires the following:

  • A Secure Store target application containing the SQL Server credentials with access to the data source must be configured.

  • The Unattended Service Account must be configured.

  • Visio Services must connect to the Secure Store target application through an ODC file that was created in Microsoft Excel 2010.

The procedures in this article assume that you have already deployed Secure Store and Excel Services or Visio Services. For more information about how to deploy Secure Store, Excel Services, and Visio Services, see the related links at the end of this article.

Configure a Secure Store target application

This video demonstrates how to create a Secure Store target application for use with SQL Server Authentication.

Video: Create a target application for SQL Server Authentication

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.

To create a target application for SQL Server Authentication

  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:

    1. Type an application ID in the Target Application ID text box.

    2. Type a display name in the Display Name text box.

    3. Type an e-mail address in the Contact E-mail text box.

    4. From the Target Application Type drop-down list, choose Group.

    5. Click Next.

  5. On the Specify Credentials page:

    1. Change the Windows User Name field name to User ID and change the associated Field Type from Windows User Name to User Name.

    2. Change the Windows Password field name to Password and change the associated Field Type from Windows Password to Password.

    3. Click Next.

  6. On the Specify Membership page:

    1. In the Target Application Administrators box, type the name of the user account that you want to administer this target application.

    2. 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.

    3. 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.

To set 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.

Configure a target application for the Unattended Service Account

This video demonstrates how to create a Secure Store target application for use with the Unattended Service Account in Excel Services or Visio Services.

Video: Configure a target application for the Unattended Service Account

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.

To create a target application for the Unattended Service Account

  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:

    1. Type an application ID in the Target Application ID text box.

    2. Type a display name in the Display Name text box.

    3. Type an e-mail address in the Contact E-mail text box.

    4. From the Target Application Type drop-down list, choose Group.

    5. Click Next.

  5. On the Specify Credentials page, click Next.

  6. On the Specify Membership page:

    1. In the Target Application Administrators box, type the name of the user account that you want to administer this target application.

    2. In the Members box, type the name of the Windows account that runs the application pool for Excel Services or Visio Services.

    3. 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.

To set 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.

Configure Excel Services

This video demonstrates how to configure Excel Services for use with SQL Server Authentication.

Video: Configure Excel Services

If you are using Excel Services, use the procedures in this section to complete the necessary configuration steps.

If the Unattended Service Account has not already been configured for Excel Services, follow these steps to configure it.

To configure the Unattended Service Account

  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.

To configure Secure Store settings in 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:

    1. Type the name of the instance of SQL Server that you want to connect to in the Server name text box.

    2. 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.

    3. 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.

Once you have connected to the data source, you can complete your Excel workbook and publish it to a SharePoint site to be rendered with Excel Services. The connection information to the Secure Store target application will remain embedded in the file.

Note

Excel connects to the database directly. It does not use Secure Store. Secure Store is only used by Excel Services when rendering a workbook from a SharePoint site.

Optionally, you can export the Secure Store connection information as an ODC file and then use it to connect additional Excel workbooks to the same data source. This allows for easier management and distribution of data connections. Use the following procedure if you want to export the Secure Store connection as an ODC file.

Important

You must export the ODC file to a trusted data connection library. Trusted data connection libraries are specified in the Excel Services service application settings. For more information, see Trusted data connection libraries.

To export the ODC file

  1. In Excel, on the Data tab, click Connections.

  2. Select the connection that you are using and then click Properties.

  3. In the Connection Properties dialog box, click the Definition tab.

  4. On the Definition tab, click Authentication Settings.

  5. Confirm that you have the SSS option selected and the correct Secure Store target application is specified in the SSS ID text box, and then click OK.

  6. Click Export Connection File.

  7. Navigate to an appropriate Data Connection Library on the SharePoint site, type a file name, and then click Save.

Once you have exported the ODC file, you can choose to connect to it from any Excel workbook where you want to connect to that data source. If the data connection information changes, you only have to update the ODC file itself and all Excel workbooks referencing it will have the new connection information.

Note

You must select the Always use connection file check box in the Excel connection properties to ensure that the Excel workbook will always use the connection file.

Configure Visio Services

This video demonstrates how to configure Visio Services for use with SQL Server Authentication.

Video: Configure Visio Services

If you are using Visio Services, use the procedures in this section to complete the necessary configuration steps.

If the Unattended Service Account has not already been configured for Visio Services, follow these steps to configure it.

To configure the Unattended Service Account

  1. On the SharePoint Central Administration home page, under Application Management, click Manage Service Applications.

  2. Click the Visio Services service application.

  3. Click Global Settings.

  4. In the External Data section, in the Application ID text box, type the name of the target application that you created for the Unattended Service Account.

  5. Click OK.

In order to use Visio Services with Secure Store, you must first create an ODC file that has a connection string and data query for your dataset. This ODC file must be created in Excel and exported to a data connection library where you can then connect to it from Microsoft Visio 2010.

Use the following procedure to create an ODC file in Excel.

To create an ODC file in 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:

    1. Type the name of the instance of SQL Server that you want to connect to in the Server name text box.

    2. 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.

    3. 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 Finish.

  5. 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.

  6. In the Import Data dialog box, choose one of the PivotTable options, and then click OK.

  7. In the SQL Server Login dialog box, type the password for the Login ID, and then click OK.

  8. In Excel, on the Data tab, click Connections.

  9. Select the connection that you are using and then click Properties.

  10. In the Connection Properties dialog box, click the Definition tab.

  11. On the Definition tab, click Authentication Settings.

  12. Confirm that you have the SSS option selected and the correct Secure Store target application is specified in the SSS ID text box, and then click OK.

  13. Click Export Connection File.

  14. Navigate to an appropriate Data Connection Library on the SharePoint site, type a file name, and then click Save.

Once the ODC file has been exported to a data connection library in the SharePoint site, you can then access it from Visio and use it to create a data-connected Visio Web drawing.

Note

Visio connects to the database directly. It does not use Secure Store. Secure Store is only used by Visio Services when rendering a Web drawing from a SharePoint site.

Use the following procedure to connect to the ODC file from Visio.

To connect to the ODC file in Visio

  1. In Visio, on the Data tab, click Link Data to Shapes.

  2. On the Data Selector page, select the Previously created connection option, and then click Next.

  3. On the Select Data Connection page, click Browse.

  4. In the Existing Connections dialog box, click Browse for More.

  5. In the Data Selector dialog box, browse to the Data Connection Library on the SharePoint site, select the ODC file that you want to use, and then click Open.

    Note

    The ODC file must be on the same farm where you plan to publish the Visio Web drawing.

  6. On the Select Data Connection page, click Next.

  7. In the SQL Server Login dialog box, type a Login ID and password that has access to the data source that is specified in the ODC file, or, if your Windows account has the needed data access, select the Use Trusted Connection check box.

  8. Click OK.

  9. On the Connect to data page, select the columns and rows that you want to include and then click Next.

  10. On the Configure Refresh Unique Identifier page, select the appropriate option for your dataset and then click Next.

  11. Click Finish.

Once you have connected to the data source through the ODC file, you can complete your drawing and publish it to a SharePoint site to be rendered by Visio Services.

See Also

Concepts

Plan the Secure Store Service (SharePoint Server 2010)
Configure the Secure Store Service (SharePoint Server 2010)
Excel Services overview (SharePoint Server 2010)
Excel Services administration (SharePoint Server 2010)
Plan for Visio Services (SharePoint Server 2010)
Visio Graphics Service administration (SharePoint Server 2010)