how to use odbcDriverConnect when using shinyapps.io to show the tables retrieving from SQL by means of RODBC

Question

I just asked a question about SQL not being linked when using shinyapps. “first argument“ error when using shinyapps.io, rodbc to show sql query result in web. This is about the error information "first argument is not an open rodbc channel" when I try to use shinyapps.io to share my SQL query results that I got from SQL database using RODBC.

Thanks a lot for Lars Br.'s help, I think I also need to try odbcDriverConnect instead of odbcConnect. I saw some suggestions from https://support.rstudio.com/hc/en-us/articles/225408367-Connecting-to-databases-using-RODBC-on-shinyapps-io, which could deal with my problem.

However, my problem is: as I am very new to all this stuff, I am confused about all the parameters in odbcDriverConnect. I tried it like:

ch <- odbcDriverConnect('driver=SQL Server;server=HANARB1P;database=testing;uid=****;pwd=****;TrustServerCertificate=true')

I have got the following information:

Warning messages:
1: In odbcDriverConnect("driver=SQL Server;server=HANARB1P;database=testing;uid=****;pwd=****;TrustServerCertificate=true") :
  [RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
2: In odbcDriverConnect("driver=SQL Server;server=HANARB1P;database=testing;uid=****;pwd=****;TrustServerCertificate=true") :
  [RODBC] ERROR: state 01000, code 53, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
3: In odbcDriverConnect("driver=SQL Server;server=HANARB1P;database=testing;uid=****;pwd=****;TrustServerCertificate=true") :
  [RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
4: In odbcDriverConnect("driver=SQL Server;server=HANARB1P;database=testing;uid=****;pwd=****;TrustServerCertificate=true") :
  ODBC connection failed

So far, I only have DSN: HANARB1P. I do not know what driver, sever and database in odbcDriverConnect are. I cannot find the definitions online. Could someone explain to me?

I also find some information probably useful from http://docs.rstudio.com/shinyapps.io/applications.html. It saids: "If your shiny application needs to access a database that you have running behind your organization’s firewall, your IT department will need to know which servers will be connecting through the firewall. Below are the shinyapps.io IP addresses you must whitelist:". I do not know if this is related to my problem. And if so, how I can set up those things?

Alex Dometrius Alex Dometrius · Accepted Answer · 2017-11-22T15:16:17

I think you need to try changing the driver types as mentioned in the below article: https://support.rstudio.com/hc/en-us/articles/225408367-Connecting-to-databases-using-RODBC-on-shinyapps-io . I am inferring that you are trying to access this database remotely, correct? If so, you need to be using the public IP address and port number for that database server- not the server's name.

con <- odbcDriverConnect('Driver=FreeTDS;TDS_Version=7.0;server="publicIPAddress";port"portNumber";database=testing;uid=****;pwd=****;')

Try with all 3 versions of TDS.

If this is your company's database, then you are most likely going to also have to allow access to it over the internet. Be careful though, doing this could have consequences and allow intrusions into your systems. Do you have an IT team that can handle this for you? It seems like you are unfamiliar with the impacts this could have to your company if done incorrectly, so I would leverage any skilled IT resources you have to do this correctly.

You will need to configure your router to allow port forwarding to your database (typically port 1433). This is the port number you will enter into your odbcDriverConnect call above. You will then also need to check your server's firewall settings- my understanding is that the standard rules are to block all incoming traffic to your SQL Server. This means you need to add an inbound rule that will allow your IP address to make a connection. You included a link to shinyapps.io in your post... are you hosting this application on shinyapps.io or just on your own computer? If you are deploying to shinyapps.io then you will also need to include those IPs listed in that article, in the allowed inbound connections I mentioned above.

My suggestion would be to work with your IT department on creating an whitelisted inbound rule on your firewall that only allows connections for those IPs needed to run your shiny app.

how to use odbcDriverConnect when using shinyapps.io to show the tables retrieving from SQL by means of RODBC

1 Answers