locked
Reporting Services and ApplicationIntent connection string property RRS feed

  • Question

  • Hi,

    I have setup a Denali availability group and am attempting to use Reporting Services to connect to read-only replica which has been configured to allow read-only intent connections using the ApplicationIntent=ReadOnly connection string properly; however, the Reporting Services data source does not support the ApplicationIntent property in the connection string when using Microsoft SQL Server as the data source type.  I see that ApplicationIntent is supported in the system.data.sqlclient namespace.  Will support for ApplicationContent be available for reporting services data sources in a future CTP?

    Thanks

    Tuesday, August 16, 2011 1:07 PM

Answers

  • Are you using Report Builder? try to switch to "ODBC" connection type.

    "Microsoft SQL Server" connection type is probably the ADO.NET driver and "ApplicationIntent " is a new keyword we added for SQL 11 natvie client and .NET 4.5

    we plan to backport it to .NET4.0 and .NET3.5 (still under discussion).

     

    • Marked as answer by Rich_McCloud Wednesday, August 17, 2011 1:43 AM
    Wednesday, August 17, 2011 12:01 AM
  • I think SSRS needs the .Net 3.5 patch to support ApplicationIntent. The patch/hotfix is available only on a case by case basis at present, so you have to open a support ticket to get it I think. Looking into getting it to be more easily downloaded, but that may take a while. This is the one http://support.microsoft.com/kb/2654347

    There is also a .Net 4.0 patch to add ApplicationIntent keyword, but Reporting Services needs the 3.5 one above I think.

    The .Net 4.0 one:

    http://support.microsoft.com/kb/2600211

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=29053

    The above Run-time update will update the ADO.NET driver to support ApplicationIntent keyword.

    Note - The equivalent Design-time and Multi-Targeting updates (not listed here) do NOT update the ADO.NET driver, they are strictly for updating Visual Studio.


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance


    Friday, March 16, 2012 5:38 PM

All replies

  • Are you using Report Builder? try to switch to "ODBC" connection type.

    "Microsoft SQL Server" connection type is probably the ADO.NET driver and "ApplicationIntent " is a new keyword we added for SQL 11 natvie client and .NET 4.5

    we plan to backport it to .NET4.0 and .NET3.5 (still under discussion).

     

    • Marked as answer by Rich_McCloud Wednesday, August 17, 2011 1:43 AM
    Wednesday, August 17, 2011 12:01 AM
  • Actually just trying to change the connection string for the data source of the report in report manager.  I've configured to allow all connections to the AG secondary replica for now.

    Thanks for the information.  Do you anticipate this keyword to be support with Denali RTM?

    Rich

    Wednesday, August 17, 2011 1:43 AM
  • "Do you anticipate this keyword to be support with Denali RTM?" 

    this keyword is already supported in CTP3, you can use native client  or sqlcmd to try this feature.

    C:\Scripts>sqlcmd -?
    usage: Sqlcmd            [-U login id]          [-P password]
      [-S server]            [-H hostname]          [-E trusted connection]
      [-N Encrypt Connection][-C Trust Server Certificate]
    ...
      [-R use client regional setting]
      [-K application intent]
      [-b On error batch abort]
    ...

     

    For example:

     

    sqlcmd -S MyServer -d Db1 -K ReadOnly

    Wednesday, August 17, 2011 4:47 PM
  • Sorry, let me be more precise with my question.

    Do you anticipate that the keyword will be supported by Reporting Services, specifically, the default ADO.Net "Microsoft SQL Server" connection type:

    The connection string needed would be:

    Data Source=< >; initial catalog = <>; ApplicationIntent=ReadOnly

    The point being that the read-only secondary would be expected to be used mostly for report type scenario's and it would seem like a fairly large hole in the SQL Rreporting solution to not have this keyword supported for the default connection method.

    Thanks for your help!

    Wednesday, August 17, 2011 5:12 PM
  • Thanks.

    The issue you saw is mainly about the legacy ADO.NET driver support. this will be back ported and we're doing integration testing with reporting service before we can officially announce this support.

    Thursday, August 18, 2011 1:05 AM
  • I think SSRS needs the .Net 3.5 patch to support ApplicationIntent. The patch/hotfix is available only on a case by case basis at present, so you have to open a support ticket to get it I think. Looking into getting it to be more easily downloaded, but that may take a while. This is the one http://support.microsoft.com/kb/2654347

    There is also a .Net 4.0 patch to add ApplicationIntent keyword, but Reporting Services needs the 3.5 one above I think.

    The .Net 4.0 one:

    http://support.microsoft.com/kb/2600211

    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=29053

    The above Run-time update will update the ADO.NET driver to support ApplicationIntent keyword.

    Note - The equivalent Design-time and Multi-Targeting updates (not listed here) do NOT update the ADO.NET driver, they are strictly for updating Visual Studio.


    Didn't get enough help here? Submit a case with the Microsoft Customer Support team for deeper investigation - http://support.microsoft.com/select/default.aspx?target=assistance


    Friday, March 16, 2012 5:38 PM
  • Hi Jason,

    You are correct, the .NET 3.5 patch does fix the issue with SSRS 2012...I was a bit surprised that RS was still only using .NET 3.5 but it did resolve the problem.

    Thanks

    Rich

    Friday, March 16, 2012 5:41 PM