About making connections to external data sources more secure
When you define a connection to an external data source for a spreadsheet component 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 HTML source of the page.
- 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.
Important To use Windows Authentication, the users you want to connect to an external data source must have user accounts on a Windows domain. Users that are members of a Windows Workgroup cannot use Windows Authentication.
As an 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 to the tables or stored procedures required for your spreadsheet.
- In the design window, make sure the spreadsheet is activated. For instructions, see Help for your design program.
- Click Commands and Options on the toolbar, and then click the Data Source tab.
- Click Edit, and then double-click New SQL Server Connection.
- In Server name, specify the name of the server.
- Under Log on credentials, click Use Windows Authentication.
- 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 by 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 spreadsheet is on a Web page, do not use this mode of authentication to connect to sensitive data from a spreadsheet 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 a 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 spreadsheet will be prompted to enter their logon information.