About making connections to external data sources more secure

When you define a connection to an external data source for a PivotTable list on a Web page, the authoring environment stores this information as an unencrypted (plain text) connection string in the HTML source. As a result, a user opening the page in a browser can easily view the HTML source for the page and read the connection string, which may include a user name and password.

To prevent unauthorized access using information from the connection string, if the data source supports this mode of authentication, use Windows Authentication (also referred to as a Trusted Connection and Integrated Security), which uses the current user's Microsoft Windows® account to connect to an external data source. Using Windows Authentication to connect to an external data source has the following advantages:

• The page designer doesn't have to enter a user name or password to connect to the data source, so that information cannot be exposed in the page's HTML source.
• The user of the page doesn't have to enter a user name or password to connect to the data source when opening the page, so that information cannot be compromised while it's being sent to the server.
• Only a Windows user account that is configured in the security system for the data source will be allowed to connect to that data source.

For example to connect to Microsoft SQL Server using Windows Authentication, use the following procedure when defining a connection.

Important  Before you can use Windows Authentication to connect to a Microsoft SQL Server database, a server administrator must configure the server to use this mode of authentication, and must grant your Windows user account (or a group of which your user account is a member) login access. Additionally, the server administrator should provide your account with the minimum level of permissions required for your PivotTable list.

1. In the design window, make sure the PivotTable list is activated. For instructions, see Help for your design program.
2. Click Commands and Options on the toolbar, and then click the Data Source tab.
3. Click Edit.

   Depending on the design program you are using, clicking Edit displays the Select Data Source dialog box or the Data Link Properties dialog box.

   If the Data Link Properties dialog box appears, do the following:

   1. Double-click Microsoft OLE DB Provider for SQL Server on the Provider tab.
   2. On the Connection tab, specify the name of the server.
   3. Select Use Windows NT Integrated security.
   4. Select a database and click OK.

   If the Select Data Source dialog box appears, do the following:

   1. Double-click New SQL Server Connection.
   2. In the Server name text box, specify the name of the server.
   3. Under Log on credentials, click Use Windows Authentication.
   4. Click Next, and then follow the directions in remaining screens of the Data Connection Wizard to specify the data to retrieve.

   Note  Other data sources that support Windows Authentication may present different options in the Data Connection Wizard. For example, to connect to an Oracle database using Windows Authentication, you must enter only a forward slash (/) in the User Name box. For more information, see the documentation for the data source provider you are using.

If Windows Authentication is not available for the data source you want to connect to, you must enter a user name and password to connect to the data source. Because this user name and password might be viewed when the PivotTable list is on a Web page, do not use this mode of authentication to connect to sensitive data from a PivotTable list on a Web page. Even if you are not concerned about unauthorized users viewing your data, you should connect only with a user account that has limited permissions on the data source. For example, when connecting to a SQL Server database using a user name and password, do not use the SA account or any other account that has elevated permissions, because an unauthorized user might be able to use this account and password to access other data on the server.

Note  If you use the Data Retrieval Service for Microsoft SQL Server (or use an Data Retrieval Service Connections (.uxdc) file that is defined to use that data retrieval service) to connect to an external data source, and you do not use Windows Authentication, the user name and password used to connect to the data source are not saved in the Web page. When you use this method to connect to an external data source, users of the Web Page that hosts your PivotTable list will be prompted to enter their login information.