Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

ODBC Connection Type (SSRS)

To include data from an ODBC data provider, you must have a dataset that is based on a report data source of type ODBC. This built-in data source type is based on the Microsoft SQL Server Reporting Services ODBC data processing extension.

Use the information in this topic to build a data source. For step-by-step instructions, see How to: Add and Verify a Data Connection or Data Source (Report Builder 3.0 and SSRS).

The connection string for the ODBC data processing extension depends on the ODBC driver that you want. A typical connection string contains name/value pairs that are supported by the driver. For example, the following connection string specifies the ODBC driver for SQL Server Native Client and the AdventureWorks database:

Driver={SQL Server Native Client 10.0};Server=server;Database=AdventureWorks;Trusted_Connection=yes;

Arrow icon used with Back to Top linkBack to Top

Credentials are required to run queries, to preview the report locally, and to preview the report from the report server.

After you publish your report, you may need to change the credentials for the data source so that when the report runs on the report server, the permissions to retrieve the data are valid.

If you configure your ODBC 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 such as punctuation marks, some underlying data source drivers cannot validate the special characters. When you process the report, the message "Not a valid password" might 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.

NoteNote

It is recommended that you do not add login information such as passwords to the connection string. Report Builder provides a separate tab on the Data Source dialog box that you can use to enter credentials.

For more information, see Data Connections, Data Sources, and Connection Strings (SSRS) or Specifying Credentials in Report Builder 3.0.

Arrow icon used with Back to Top linkBack to Top

ODBC is an early data access technology that preceded OLEDB. ODBC supports only relational data sources. ODBC data providers are called drivers. ODBC drivers are provided by Microsoft and third party vendors. For example, Microsoft Office installs ODBC drivers that connect to Office file formats.

Before you can build an ODBC connection string, you must have ODBC drivers installed and build a machine or system DSN. To successfully retrieve the data that you want, you must provide query syntax that is supported by the driver. Parameter support varies by driver. For more information, see topics that are specific to the driver that you select, for example, SQL Server 2008 R2 Native Client (ODBC).

Platform and Version Information

For more information about specific ODBC data providers, see Data Sources Supported by Reporting Services (SSRS) in the Reporting Services documentation in SQL Server Books Online.

Arrow icon used with Back to Top linkBack to Top

These sections of the documentation provide in-depth conceptual information about report data, as well as procedural information about how to define, customize, and use parts of a report that are related to data.

Adding Data to a Report (Report Builder 3.0 and SSRS)

Provides an overview of accessing data for your report.

Data Connections, Data Sources, and Connection Strings in Report Builder 3.0

Provides information about data connections and data sources.

Creating and Adding Datasets (Report Builder 3.0 and SSRS)

Provides information about embedded and shared datasets.

Working with Fields in a Report Dataset (Report Builder 3.0 and SSRS)

Provides information about the dataset field collection generated by the query.

Data Sources Supported by Reporting Services (SSRS) in the Reporting Services documentation in SQL Server Books Online.

Provides in-depth information about platform and version support for each data extension.

Arrow icon used with Back to Top linkBack to Top

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.