Share via

SSRS ODBC datasource using DSN Intermittently Fails

Garrett Tongue 1 Reputation point
2021-04-17T21:11:42.333+00:00

Hello,

I've recently run into an issue and I can't find any reason why this failure is happening. I have a report deployed to our SSRS 2016 instance that is pulling data from Mongodb through an ODBC connection to the MongoDB BI Connector. This connection is setup as a System DSN. The report seems to fail at complete random (Looking through logs it will open ~7 ODBC connections and somewhere between 1-3 will fail) with an authentication error on the Mongo side of things. The reason I am leaning towards SSRS is I have tested opening the ODBC connection through powershell and running 200 queries in parallel (all of them passed no problem).

I'm not 100% sure if the issue is on the SSRS side of things but if anybody has any idea around this it would be extremely helpful.

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,855 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 34,836 Reputation points
    2021-04-19T07:54:25.557+00:00

    Hi @Garrett Tongue ,

    Do you mean that when you run the report on web service url, the report will fail?

    The fail means that the report keep loading or it will show the error message on the web?

    Have you check the error log or the execution log for more details?

    The errorlog path:

    In SQL Server Reporting Services 2016 or earlier: C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles

    Regards，

    Zoe

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

  2. Garrett Tongue 1 Reputation point
    2021-04-19T13:35:26.033+00:00

    Thanks for answering Zoehui,

    When I mean it fails I mean that is shows an rsErrorOpeningConnection message within the ui (this only happens ~50% of the time). The error log on the machine as well as the Mongodb Connector & the actual MongoDB database all say the same thing (Authentication failed). I'm not sure how it is possible for only some of the connections to the datasource to fail with this error since I know the password is correct (some of the connections succeed)

    I can't provide all of these logs (can't copy paste) as it is against my companies policies but here are some snippets. Just to be clear this error only occurs sometimes which is the part that is really stumping me. If it was all the time that's a much easier issue to trace down

    --- reportServer log ---

    INFO: RenderingForNewSession('XXXXXXXXX')
    ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'XXXXXX' ---> System.Data.Odbc.OdbcException: ERROR [HY000] [MySQL][ODBC 1.4(w) Driver]Access denied for user 'XXXXXXXx'