Making Connections to External Data Sources More Secure

When you define a connection to an external data source for an Office Web component on a Web page, this information is stored 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, depending on how you defined the connection.

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 programmer or 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.

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.

Defining a Connection in an Office Web Component's User Interface

For example, to connect to Microsoft SQL Server using Windows Authentication from the user interface of the Spreadsheet component, use the following procedure when defining a connection.

Important  Before you can use Windows Authentication to connect to a Microsoft SQL Server database, the server administrator must configure the server to use this mode of authentication, and must grant login access to your Windows user account (or a group of which your user account is a member) as well as the accounts of any users you want to access your solution. Additionally, the server administrator should give users' accounts the minimum level of permissions to the tables or stored procedures required for your solution.

1. In the design window, make sure the spreadsheet 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, and then double-click New SQL Server Connection.
4. In Server name, specify the name of the server.
5. Under Log on credentials, click Use Windows Authentication.
6. Click Next, and then follow the directions in remaining screens of the Data Connection Wizard to specify the data to retrieve.

Notes

• Some design programs do not support the Spreadsheet's design-time user interface by default. If you are using such a program, the Data Source tab will not be displayed in step 2. To activate the design-time user interface, you must set the DisplayDesignTimeUI property to True.
• 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 using Windows Authentication to connect to a data source, see the documentation for the data source provider you are using.
• 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 login information. To use a data retrieval service, you must have access to a Windows SharePoint Services server on which that data retrieval service is installed. By default, Windows SharePoint Services installs a data retrieval service for connecting to data in SharePoint lists. A SharePoint site administrator can install the Office 2003 Web Parts and Components to add data retrieval services for Microsoft SQL Server and Microsoft Business Solutions. The Office 2003 Web Parts and Components is available from Downloads on Microsoft Office Online. To start defining a connection using a data retrieval service, in step 2 in the procedure above, click Edit, click New Source, and then click either Microsoft Business Solutions (to connect to Microsoft Business Solutions data) or click Data retrieval services (to connect to Windows SharePoint Services lists or Microsoft SQL Server data).

Defining a Connection to External Data Programmatically

When working with the ConnectionString property to define the connection to an external data source (or defining the connection string for an ADO Connection object to pass to the Connection property of the PivotTable object) , you should use Windows Authentication if possible. To use Windows Authentication with SQL Server, your connection string should use this format, which includes Integrated Security=SSPI:

    Provider=SQLOLEDB;Data Source=NameOfServer;Initial Catalog=NameOfDatabase;
   Integrated Security=SSPI
  

Other data sources may require a different format for their connection strings, for example to connect to an Oracle database using Windows Authentication requires you to pass a forward slash (/) for the User ID value:

    Provider=MSDAORA.1;User ID=/;Data Source=NameOfServer;
   Initial Catalog=NameOfDatabase
  

Working with Data Sources that Do Not Support Windows Authentication

If Windows Authentication is not available for the data source you want to work with, you must pass a user name and password to connect to the data source when you define a connection either programmatically or in the user interface of an Office Web component. Because this user name and password might be viewed when the Spreadsheet, Chart, or PivotTable component is on a Web page, avoid using this mode of authentication to connect to sensitive data from a component 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.