Connection broken when connecting app on shinyapps.io with Azure SQL database

ublock · June 23, 2020, 8:07am

I am trying to connect an app on shinyapps.io with an Azure SQL database for persistent storage.

I have followed the advices given on, e.g.,

On my local Windows PC, the app is working fine, it connects to the database, and retrieves data as expected.

However, when published on shinyapps.io the app starts up but freezes when trying to connect to the Azure SQL database. According to the log file, the error message is

Error : nanodbc/nanodbc.cpp:983: 00000: [unixODBC][RStudio]Connection broken

I am using a config.yml file:

default:
  XXX_test:
    driver: 'ODBC Driver 17 for SQL Server' 
    server: 'tcp:YYY.database.windows.net'
    uid: 'my_uid'
    pwd: 'my_pwd'
    port: 1433
    database: 'XXX_test'
    encrypt: yes

shinyapps:
  XXX_test:
    driver: 'SQLServer'
    server: 'tcp:YYY.database.windows.net'
    uid: 'my_uid'
    pwd: 'my_pwd'
    port: 1433
    database: 'XXX_test'
    encrypt: yes

which is processed using the code below:

    dc <- config::get("XXX_test")
    cdb <- dbConnect(
        drv = odbc::odbc(),
        Driver = dc$driver,
        Server = dc$server,
        UID    = dc$uid,
        PWD    = dc$pwd,
        Port   = dc$port,
        Database = dc$database
    )

On Azure, if have allowlisted the shiyapps.io ip addresses:

I believe there must be a minor detail which I have not configured correctly or a typo, perhaps.
Any ideas how to track the issue further down, please?