Issue connecting to external DB from shinyapps

[kanden]

Hello,
I am making a small project and want to host it on shinyapps. In it I am connecting to an Azure database I have set up. When connecting from my local session everything is working fine however when deploying it to shinyapps i am getting this error:

Error in local({ : 
! ODBC failed with error 00000 from [RStudio][SqlServer].
✖  Failed to locate Server/Instance Specified.
ℹ From 'nanodbc/nanodbc.cpp:1150'.
Calls: local ... tryCatch -> tryCatchList -> tryCatchOne -> <Anonymous>

I have followed the shinyapps guide on deploying an app with a connection to an external database carefully but have not been able to fix the issue or find a similar issue online.

This is my connection R code :

con_string <- "Driver={__DRIVER__};
Server=<server>;
Database=set_daily;
Uid=__USER__;
Pwd=__PASSWD__;
Encrypt=yes;
TrustServerCertificate=yes;
Connection Timeout=1000;"

con <- DBI::dbConnect(
    odbc::odbc(),
    .connection_string = con_string %>% 
      stringi::stri_replace_all(fixed = "__USER__", config::get("azure_user")) %>% 
      stringi::stri_replace_all(fixed = "__PASSWD__", config::get("azure_pwd")) %>% 
      stringi::stri_replace_all(fixed = "__DRIVER__", config::get("driver"))
  )

My config.yml is like so:

default:
  azure_pwd: '<pwd>'
  azure_user: '<user>'
  driver: 'ODBC Driver 18 for SQL Server'
    
shinyapps:
  azure_pwd: '<pwd>'
  azure_user: '<user>'
  driver: 'SQLServer'

Thanks in advance for any help

[pieterjanvc]

Hi,

Welcome to the RStudio community!

The error is suggesting the database cannot be found, and based on the code you shared, it seems you never replace Server=<server>; in the connection string. This means the remote app will have no idea where to look. However this would likely also not work on your local instance unless there are some defaults set system-wide.

If that's not the cause, try and look what info an Azure database needs as each database can have different requirements. For example, some require explicit port numbers to be defined or wont connect.

Hope this helps,
PJ

[kanden]

Thanks for the reply. The <server> in the actual code is the correct server name, I just changed it for the purposes of this post (probably should have made it clear). The port number is in the connection string as well.

I have also whitelisted the IP addresses listed in the shinyapps guide (Shinyapps Documentation - Firewall).

I'm kind of running out of ideas what could be wrong at this point.