Data Connections, Data Sources, and Connection Strings (SSRS)

Specifying a Data Source

To create a data source, you must have the following information:

• **Data source type   **The connection type, for example, Microsoft SQL Server. Choose this value from the drop-down list of connection types. 

• Connection information   Connection information includes the name and location of the data source, and connection properties that are specific to each data provider. The connection string is the text representation of connection information. For example, if the data source is a SQL Server database, you can specify the name of the database. For embedded data sources, you can also write expression-based connection strings that are evaluated at run time. For more information, see Expression-based Connection Strings later in this topic.

• Credentials   You provide the credentials that are needed to access the data. The data source owner must have granted you the appropriate permissions to access both the data source and the specific data on the data source. For example, to connect to the AdventureWorks sample database installed on a network server, you must have permission to connect to the server and also read-only permission to access the database.

  Note

  By design, credentials are managed independently from data sources. Credentials that you use to preview your report on a local system may differ from credentials you need to view your published report. After you save a data source to the report server or SharePoint site, you might need to change the credentials to work from that location. For more information, see Credentials for Data Sources.

The Report Data pane displays embedded data sources and references to shared data sources that have been added to the report. In Report Builder, a shared data source reference points to a shared data source on a report server or SharePoint site. In Report Designer, a shared data source reference points to a shared data source in Solution Explorer under the Shared Data Source folder.

Common Connection String Examples

Connection strings are the text representation of connection properties for a data provider. The following table lists examples of connections strings for various data connection types.

If you fail to connect to a report server using localhost, check that the network protocol for TCP/IP protocol is enabled. For more information, see Connecting to the SQL Server Database Engine.

For more information about the configurations needed to connect to these data source types, see the specific data connection topic under Adding Data from External Data Sources (SSRS) or Data Sources Supported by Reporting Services (SSRS).

Credentials for Data Sources

By design, credentials can be saved and managed independently from connection information. Credentials are used to create a data source, to run a dataset query, and to preview a report.

Credentials that you enter for data access from your computer are stored securely in the local project configuration file and are specific to your computer. If you copy the project files to another computer, you must redefine the credentials for the data source.

When you deploy a report to the report server or SharePoint site, its embedded and shared data sources are managed independently. The data source credentials needed to access the data from your computer may differ from the credentials needed for the report server to access the data. A good practice is to verify that the data source connections continue to connect successfully after you publish a report. If you need to change the credentials, you can modify them directly on the report server.

To change the data sources that a report uses, you can modify the report properties by manually navigating to a report server folder and selecting a data source by name. For more information, see How to: Bind a Report or Model to a Shared Data Source.

For more information, see the following topics:

• Specifying Credential and Connection Information for Report Data Sources (SSRS)

• How to: Store Credentials for a Data Source (Report Manager)

• Specifying Connections for Custom Data Processing Extensions

• Specifying Credentials in Report Builder 3.0

• How to: Add and Verify a Data Connection or Data Source (Report Builder 3.0 and SSRS)

Special Characters in a Password

If you configure your ODBC or SQL 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 server as part of a system ODBC data source name (DSN). For more information, see "OdbcConnection.ConnectionString" in the .NET Framework SDK documentation.

Expression-based Connection Strings

Expression-based connection strings are evaluated at run time. For example, you can specify the data source as a parameter, include the parameter reference in the connection string, and allow the user to choose a data source for the report. For example, suppose a multinational firm has data servers in several countries. With an expression-based connection string, a user who is running a sales report can select a data source for a particular country before running the report.

The following example illustrates the use of a data source expression in a SQL Server connection string. The example assumes you have created a report parameter named ServerName:

="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks

Data source expressions are processed at run time or when a report is previewed. The expression must be written in Visual Basic. Use the following guidelines when defining a data source expression:

• Design the report using a static connection string. A static connection string refers to a connection string that is not set through an expression (for example, when you follow the steps for creating a report-specific or shared data source, you are defining a static connection string). Using a static connection string allows you to connect to the data source in Report Designer so that you can get the query results you need to create the report.

• When defining the data source connection, do not use a shared data source. You cannot use a data source expression in a shared data source. You must define an embedded data source for the report.

• Specify credentials separately from the connection string. You can use stored credentials, prompted credentials, or integrated security.

• Add a report parameter to specify a data source. For parameter values, you can either provide a static list of available values (in this case, the available values should be data sources you can use with the report) or define a query that retrieves a list of data sources at run time.

• Be sure that the list of data sources shares the same database schema. All report design begins with schema information. If there is a mismatch between the schema used to define the report and the actual schema used by the report at run time, the report might not run.

• Before publishing the report, replace the static connection string with an expression. Wait until you are finished designing the report before you replace the static connection string with an expression. Once you use an expression, you cannot execute the query in Report Designer. Furthermore, the field list in the Report Data pane and the Parameters list will not update automatically.

See Also

Tasks

Reference

Concepts