locked
Reporting Services and ApplicationIntent connection string property

    질문

  • 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

    2011년 8월 16일 화요일 오후 1:07

답변

  • 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).

     

    • 답변으로 표시됨 Rich_McCloud 2011년 8월 17일 수요일 오전 1:43
    2011년 8월 17일 수요일 오전 12:01
  • 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


    2012년 3월 16일 금요일 오후 5:38

모든 응답

  • 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).

     

    • 답변으로 표시됨 Rich_McCloud 2011년 8월 17일 수요일 오전 1:43
    2011년 8월 17일 수요일 오전 12:01
  • 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

    2011년 8월 17일 수요일 오전 1:43
  • "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

    2011년 8월 17일 수요일 오후 4:47
  • 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!

    2011년 8월 17일 수요일 오후 5:12
  • 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.

    2011년 8월 18일 목요일 오전 1:05
  • 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


    2012년 3월 16일 금요일 오후 5:38
  • 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

    2012년 3월 16일 금요일 오후 5:41