Defining Report Datasets for Other Data Sources

  • Article

Reporting Services supports accessing data sources through any Microsoft .NET Framework managed data provider including OLE DB or ODBC. To set connection string properties specific to a data provider, click the Edit button on the Data Source dialog box, General tab, to open the Connection Properties dialog box. For the OLE DB data source type, you can select the data provider for the data source and set extended properties through the Data Link Properties dialog box. For the ODBC data source type, you can set the connection string.

The query designer for each data provider is registered in the RSReportDesigner.config file. When you create a dataset based on a data source, Report Designer opens the dataset in the default view for the data provider. The generic query designer is available by default. Some Reporting Services features depend on implementations provided by the data provider. For example, some data providers do not support parameterized queries or do not support named parameters.

To view instructions about creating a dataset, see How to: Create a Dataset (Report Designer) and Report Data How-to Topics.

For instructions on creating your own data provider to support custom data sources, see Implementing a Data Processing Extension.

Special Characters in a Password

If you configure your ODBC or OLE DB data source to prompt for a password or to include the password in the connection string, and a user enters the password with special characters like punctuation marks, some underlying data source drivers cannot validate the special characters. When you process the report, the message "Not a valid password" may indicate this problem. If changing the password is impractical, you can work with your database administrator to store the appropriate credentials on the report server as part of a system ODBC data source name (DSN). For more information, see "OdbcConnection.ConnectionString" in the .NET Framework SDK documentation.

Note

It is recommended that you do not add login information such as passwords to the connection string. Report Designer provides a separate tab on the Data Source dialog box that you can use to enter credentials. These credentials are stored securely on the client computer running Report Designer.

See Also

Concepts

Defining Report Datasets
Connecting to a Data Source
RSReportDesigner Configuration File

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

14 April 2006
Changed content:
  • Moved content about specific data providers, such as SAP and Oracle, into separate topics.
New content:
  • Added section about special characters in a password.