SQL Server Issue

(11 posts) (2 voices)

Tags:

  1. ajdjackson, Member

    Hi

    I've had 2 occurrences of the error below:

    The mydbr is running on an Azure Windows Server VM and is connect to an Azure SQL Database.

    When I try to re-run the installation I get the following:

    Whilst receiving this error the connection to the Azure SQL DB is still up as I can access it via SSMS from the VM.

    The only way I can recover from this issue is to reboot the VM.

    I have been using this instance of mydbr for several days now and have been able to create several reports.

    Any thoughts as to why this is happening?

    Cheers

    Jake

  2. myDBR Team, Key Master

    What is the version for the sqlsrv extension in php (check the phpinfo)?

    --
    myDBR Team

  3. ajdjackson, Member

    Hi

    This is the version:

    Thanks

    Jake

  4. myDBR Team, Key Master

    The problem comes from the fact that PHP cannot connect to DB using encryption. There are differect reasons that may cause this and would need more detailed info on the configuration/versions.

    See discussion at https://github.com/microsoft/msphpsql/issues/1003 to see if any of the remedied listed in there helps.

    --
    myDBR Team

  5. ajdjackson, Member

    Hi

    Thanks for getting back.

    Reading that link appears to be bad news. IF I understand what it was saying the fault appears to be in the PHP sqlsrv 5.8 driver. Some folk have remedied this by downgrading to 5.6. However for me to do this I would have to downgrade PHP 7.4 to 7.3 which seems a retrograde step.

    Not really sure what to do here.

    If I do go down the downgrading route is just a matter of re-running the installation? The reports and sps I've written would still be in place?

    Thanks

    Jake

  6. myDBR Team, Key Master

    Note that most of the comments are related to older 5.6 driver. Newer Microsoft drivers contain fixes that make them better. Note also that the error message you are getting is rather generic and can be caused by number of reasons.

    There is no inherent problems with the 5.8 driver, they in most cases work just fine. Something in your setup (driver, ODBC, web server, OS) is causing the problem.

    The best way to solve the issue is to make sure you have latest versions of all the components. If the problems still persist, post the detailed descriptions of all of your componets to Microsoft's msphpsql GitHub Issues section and see if you get some troubleshooting advises.

    You said that you were able to use the setup previously and now you can not. What changed in between?
    --
    myDBR Team

  7. ajdjackson, Member

    Hi

    Following up on the above issue.

    I'm still experiencing random connection drops and I resolve it by an IIS Reset.

    On the GitHub page for the driver there is a current open issue that describes what I am seeing:

    [Microsoft][ODBC Driver 17 for SQL Server]Encryption not supported on the client

    This is still an open issue that the developers have been able to replicate.

    A temporary workaround is suggested that may mitigate but not resolve is setting MultipleActiveResultSets=false

    Again I don't know if this will work for me but is there a way to put the above setting in the user defaults file or where should I set it? I'm using an Azure SQL DB so have little or no control over the configuration.

    Thanks

    Jake

  8. myDBR Team, Key Master

    Jake,
    if you update to the latest build, you can add following setting to user/defaults.php:

    $mydbr_defaults['db_connection']['sqlsrv_connect_info']['MultipleActiveResultSets'] = 0;

    Let us know if this helps circumvent the driver bug.

    --
    myDBR Team

  9. ajdjackson, Member

    Hi

    Just a quick update.

    I've had no issues since using the above setting.

    The GitHub team have posted an update on the issue:

    Hi @mbiebl and @ajdjackson, here is an update: we figured out a way to consistently reproduce the issue and found the underlying causes at long last. We are running different tests, including stress testing the server, to make sure the fix in the ODBC driver does not introduce regressions.

    As I said above I'm not sure if the cause of my issue is the same as the original poster, @mbiebl, even although I was getting the same connection error, as he was experiencing his connection issue under load. My application is very lightweight.

    I'll keep you informed.

    Cheers

    Jake

  10. myDBR Team, Key Master

    That is good news. MARS has been turned off by default in latest build as it it not needed.

    --
    myDBR Team

  11. ajdjackson, Member

    The GitHub team have now released a new version - ODBC Driver 17.7.2

    Still happy to report that I’ve experienced no issues since setting MARS to off.

    Jake


Reply

You must log in to post.