Share via

Azure SQL Reporting: Troubleshooting

  • Article

 

This article provides information about troubleshooting for SQL Reporting.

In this article:

In the following article, BIDS refers to the report authoring tool Business Intelligence Development Studio of SQL Server .

Troubleshooting Tips

Use the following tips and techniques to help troubleshoot your SQL Reporting subscription, report servers, and reports.

Report Server Access

To verify that you have credentials sufficient to access the report server, in a browser, use the https protocol and the Web Service URL from your SQL Reporting subscription. For example, if your Web Service URL is abcdefgh.database.windows.net, use https://abcdefgh.database.windows.net/reportserver.

Report Data Access from BIDS

To verify that you have the correct version of tools, review the information in Tool Compatibility.

To verify that you have credentials sufficient to access to the database server:

  • In the Data Source Properties dialog box, General page, select data source type SQL Database, and then click Edit to build the connection string for a Windows Azure SQL Database.
  • In the Connection Properties Dialog Box, in Server name, enter the fully-qualified report server URL, for example, abcde01234.database.windows.net.
  • Select SQL Server authentication and enter the database login credentials for user name, password. You must get this information from the Windows Azure SQL Database administrator. For the user name, use only the first part of the login; do not include the server name.
  • In Connect to a database, click the drop-down list arrow to view the list of databases that you have access to.
  • Click Test Connection. You will see the message “Test connection succeeded.” if your credentials are sufficient to connect to the database server.

To verify that you have credentials sufficient to retrieve schema metadata and data from the database:

  • Create a dataset that is based on the SQL Database data source.
  • Open the dataset query designer.
  • Create or paste in your query, and click Run on the toolbar. The result set you should see is representative of results that are returned when the report runs on the report server.
  • If you do not see results, and you are using a view, stored procedure, or function call, verify with the database administrator that you have execute privileges.

For more information about troubleshooting data connections, see Windows Azure SQL Database Connectivity Troubleshooting Guide.

If you get an error message, review issues in Troubleshooting Issues to help identify a resolution.

↑ Back to top

Troubleshooting Issues: BIDS

The following list provides descriptions and resolutions of issues you might see when you are authoring reports for SQL Reporting.

Report server credentials are entered incorrectly on first deployment of project to the SQL Reporting report server.

Resolution: Restart BIDS, redeploy the project, and enter the correct credentials.

Renaming a shared data source in Solution Explorer does not update data source references in the Report Data pane in Design View.

Resolution: In the Report Data pane, open the data source properties, and select the correct name from the drop-down list of shared data sources.

Resolution: Verify that the report server name is the fully qualified version. You get this information from the Windows Azure developer portal when you login to your Windows Azure account with your Windows Live ID. For example: abcde01234.database.windows.net.

Also, verify that you are using the report server administrator credentials to access the report server.

When creating a data source, you get the error message: Cannot open server ‘<servername>’ requested by the login. Client with IP address ‘<ip address>’ is not allowed to access the server….

Resolution: To preview a report in BIDS, your client IP address must have permissions to access the SQL Database report data sources. Contact the database owner for each data source and ask that your IP address be added to the firewall exception list for that database.

Alternatively, you can deploy the report to the SQL Reporting report server and run it in your browser to view it. The report servers are on the same side of the firewall as the SQL Database databases and do not need firewall exceptions.

When creating a data source, you get the error message: A connection was successfully established with the server... TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.

Resolution: Verify that you have entered the correct credentials. If you are using the Connection Properties dialog box, select Use SQL Server Authentication and enter your user name and password. If you are using the Shared Data Source Properties dialog box, click the Credentials page, select Use this user name and password, and enter your credentials. Only SQL Authentication credentials are supported.

On the SQL Reporting report server, you get the error message: …Cannot create a connection to data source… (rsErrorOpeningConnection)

Resolution: To provide credentials to access a SQL Database datasource in a report data source, use only the first part of the user name. Do not include the server name. For example, if the user name is abcdefgh@database.windows.net, use only abcdefgh.

There are two scenarios for entering credentials for a report data source: at design time and at run time.

  1. At design time, in a Business Intelligence Development Studio report server project, specify credentials on the Shared Data Source Properties dialog box, Credentials tab.

  2. If a data source is configured to Prompt at run time, then in Business Intelligence Development Studio when you preview a report or from the report server when you view a report, you must enter the credentials at run time. Enter the user name and password in the report viewer toolbar:

When testing a data source, you get the error message: A connection was successfully established with the server … The certificate's CN name does not match the passed value.

Resolution: In the connection string, try adding "TrustServerCertificate=True".  

When creating a data source, you get the error message: Login failed for user '’<username>"

Resolution: In Solution Explorer, open the Shared Data Source Properties. Click Edit (next to Connection string text box). In the Connection Properties dialog box, do the following:

    • For Server name, type the fully qualified report server name. 
    • Select Use SQL Server Authentication
    • In User name and Password, type the database Login user name and password. You must get this information from the owner of the SQL Database datasource.
    • In Select or enter a database name, click the down arrow. The list of databases that you have permissions to appears in the drop-down list.

If you do not see a list of databases in the drop-down list, then you do not have the correct Login credentials.

Click Test Connection to get more information about the specific error condition.

For more information about troubleshooting data connections, see Windows Azure SQL Database Connectivity Troubleshooting Guide.

When deploying the project or an item in the project, you get the error message: The specified report server URL ... could not be found...

Resolution: The most common cause of this error is that the last part of the report server URL in TargetServerURL is "reports". In the project property page, verify that TargetServerURL contains a valid, fully-qualified report server URL, and that the last part is "reportserver", not "reports". For example: https://abcde01234.database.windows.net/reportserver.

When deploying the project or an item in the project, you get the error message: Could not connect to the report server .... Verify that the TargetServerURL is valid...

Resolution: The most common cause of this error is that in the report server URL in TargetServerURL,  you must use the https protocol, not just http. For example: https://abcde01234.database.windows.net/reportserver.

Troubleshooting Issues: Report Server

The following list provides descriptions and resolutions of issues you might see when you are viewing reports on SQL Reporting report servers.

On the SQL Reporting report server, you get the error message: For more information about this error navigate to the report server on the local server machine, or enable remote errors.

Resolution: Contact Support. Include all information in the error message, including the tracing ID.

Troubleshooting Issues: Reports

The following provides descriptions and resolutions for report rendering errors you might encounter when you are viewing reports on SQL Reporting report servers.

 The query that specifies the data to retrieve for a report dataset references database objects such as tables using "three-part naming" (e.g. 'ExpOlap.dbo.HIST_CA_REGION'), which is not supported in queries against SQL Database. You get the error message: Query execution failed for dataset <dataset name>.

Resolution: Update database references and republish the report to the SQL Reporting report server.

 

The query that specifies the data to retrieve for a report dataset did not complete before the built-in timeout of SQL Database elapsed. You get the message: Cannot create a connection to data source <data source name>.

Resolution: Increase the timeout in report datasets and then republish the report to the SQL Reporting report server. You can also review the the queries that datasets use and if possible optimize the queries to run more quickly and this way decrease the probability of encountering timeouts.

Report parameter properties are not compatible with the use of the parameter. For example, there is a data type mismatch between the value of the parameter and the database field the parameter maps to. You get the error message: Error during processing of <parameter name> report parameter.

Resolution: Update the parameter properties and then republish the report to the SQL Reporting report server.

 

You are unable to access the SQL Reporting report server. You get the message: Cannot create a connection to data source <data source name>.

Resolution: On the Server Home page select the Allow other Windows Azure services to access this server checkbox.

To open the Server Home page:

  1. 1. Go to https://windows.azure.com
  2. 2. Navigate to the SQL Database server
  3. 3. Click Firewall rules
  4. 4. Select the Allow other Windows Azure services to access this server checkbox

The query for a report dataset maps columns to data types with which the column data is incompatible. For example, a column with string data is mapped to a numeric data type. You get the message: Cannot read the next data row for the dataset <dataset name>.

Resolution: Update the report query and then republish to the SQL Reporting report server.

 The report does not render. You get the message: Login validation failed for user <user name> at <server name>.

Resolution: Update data source credentials as described in the Troubleshooting Issues: BIDS section above.

 

↑ Back to top

See Also

  • [[Windows Azure SQL Reporting Overview]]
  • [[Windows Azure SQL Database Resources]]