Show All

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, such as a data access page, the authoring environment stores this information as an unencrypted (plain text) connection string in the HTML page. 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 Design view of a data access page, click Toolbox on the toolbar.
2. In the toolbox, click the Office PivotTable tool .
3. Click inside the PivotTable list to activate it.
4. Click Commands and Options on the toolbar, and then click the Data Source tab.
5. Click Edit.
6. In the Data Link Properties dialog box, double-click Microsoft OLE DB Provider for SQL Server on the Provider tab.
7. On the Connection tab, specify the name of the server.
8. Select Use Windows Integrated security.
9. Select a database and click OK.

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 (.udc) 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.