I have an R Shiny app connecting to SQL database with the following code:

ui <- fluidPage(
column(12, align = 'center', textInput("userName", "User Name")),
column(12, align = 'center', actionButton("dbConnect", "Connect to Database")))

server <- function(session, input, output) {
observeEvent(input$dbConnect(odbc::odbc(), 
driver = "ODBC Driver 17 for SQL Server",
Server = '123......',
Database = 'example',
UID = input$userName,
PWD = askpass("Enter Database Password"))
}

The code properly works for entry of username and then prompts password. However, it does not connect to the database and prompts Login failed for user 'username'.

I have ensured that the username and password are correct and connect to the database when entering SQL.

CodePudding user response：

Are you trying to return a connection?

  conn = eventReactive(
    input$dbConnect,
    {
      
      PWD = askpass::askpass("Enter Database Password")
      
      conn = DBI::dbConnect(
        odbc::odbc(), 
        driver = "ODBC Driver 17 for SQL Server",
        Server = '123.......',
        Database = 'example',
        port=1433,
        UID = input$userName,
        PWD = PWD
        )
      
      return(conn)
    }
  )

You can then use conn() in subsequent calls, for example

  output$tables=renderText({
    req(conn())
    DBI::dbListTables(conn())
  })

CodePudding user response：

1. Your sample code is a syntax error,

   observeEvent(input$dbConnect(odbc::odbc(), 
   driver = "ODBC Driver 17 for SQL Server",
   Server = '123......',
   Database = 'example',
   UID = input$userName,
   PWD = askpass("Enter Database Password"))
   

   should probably be

   observeEvent(input$userName, {
     dbConnect(odbc::odbc(), 
       driver = "ODBC Driver 17 for SQL Server",
       Server = '123......',
       Database = 'example',
       UID = input$userName,
       PWD = askpass("Enter Database Password"))
   })
   

   but even that won't work.

2. You never store that anywhere, so no query can use it. The DBI methods such as DBI::dbGetQuery all require the object as their first argument, so you need to put it in a real object. @langtang's suggestion to use eventReactive is the right start.

3. You cannot use askpass in a shiny app that you intend to deploy. You'll need to use environment variables, config.yml (via the config R package), or some other ways to pass secrets to the app. (I'm going to ignore this part for the sample code below, but keep it in mind.)

My suggestion

Store the credentials in a list (as opposed to a database connection object) and include a function that connects, runs the query, then disconnects. While there is a very little overhead added for each query, it is very small.

But the bigger reason is this: if you ever plan on providing even simple async operations in your shiny app by including promise future, the connection object cannot be transferred to future processes, so any query in a promise will fail. However, a list of parameters can transfer.

So you might try:

myGetQuery <- function(..., cred) {
  tmpcon <- do.call(DBI::dbConnect, c(list(odbc::odbc()), cred))
  on.exit(DBI::dbDisconnect(tmpcon), add = TRUE)
  DBI::dbGetQuery(tmpcon, ...)
}
# similar funcs needed if you need things like: dbExecute,
# dbListTables, dbListFields, etc, elsewhere in your server component
cred <- list(
  driver = "ODBC Driver 17 for SQL Server",
  Server = '123......',
  Database = 'example'
)

And somewhere in your shiny app, something like:

cred_with_userpass <- reactive({
  c(cred, UID = input$userName,
    PWD = askpass("Enter Database Password"))
})
somedat <- reactive({
  myGetQuery("select 1 as a", cred = cred_with_userpass())
})