r-Shiny Azure WebApp作为Azure SQL Server的用户进行身份验证

Question

我正在构建一个以SQL DB作为后端的WebApp。我正在Azure上部署这两个部分，作为Azure Webapp和SQL Server。

SQL服务器已使用Azure AD(AAD(进行了加密。因此，只有组中的用户才能访问数据库。

因此，我试图设置一个工作流程，让Web应用程序登录用户并收集他的访问令牌。然后使用该令牌查询SQL服务器。

我已经在AAD中注册了该应用程序，在那里它被授权读取用户ID并冒充用户。

我有以下代码，它在本地工作。但我无法让它在Docker镜像中本地部署。

# app file
library(shiny)
library(tidyverse)
library(shinyjs)
library(AzureAuth)
db <- "Azure"
config <- config::get(config = db)
redirect <- 'http://localhost:1410/app/'
ui_func <- function(req) {
useShinyjs()
opts <- parseQueryString(req$QUERY_STRING)
if( is.null(opts$code) ) {
auth_uri <- build_authorization_uri(
resource = config$resource, 
tenant = config$tenant, 
app = config$app, 
redirect_uri = redirect, 
# version = 2, 
prompt = 'login')
redir_js <- sprintf('location.replace("%s");', auth_uri)
tags$script(HTML(redir_js))
} else {
fluidPage(
verbatimTextOutput('token1'), 
verbatimTextOutput('token2'),
verbatimTextOutput('token_list'),
verbatimTextOutput('path'),
tableOutput('files'),
tableOutput("db_tables")
)
}
}
server <- function(input, output, session) {

opts <- parseQueryString(isolate(session$clientData$url_search))
if(is.null(opts$code))
return()
#get_azure_token(
#            config$resource, 
#            config$tenant, 
#            config$app,
#            #pssw,
#            # version = 2,
#            auth_type = 'authorization_code', 
#            use_cache = FALSE, 
#            auth_code = opts$code) -> 
tok1 <- NULL
get_azure_token(
config$resource2, 
config$tenant, 
config$app, 
password = config$secret, 
auth_type = 'authorization_code', 
use_cache = FALSE, 
# auth_type='on_behalf_of', 
# on_behalf_of = tok1,
auth_code = opts$code
) -> 
tok2
output$token <- renderPrint(tok1)
output$token2 <- renderPrint(tok2)
renderText({
rappdirs::user_data_dir() %>% 
fs::dir_ls() }) -> 
output$path
renderText({
list_azure_tokens() 
}) ->
output$token_list
renderTable({
rappdirs::user_data_dir() %>% 
fs::path(., 'AzureR') %>%
fs::dir_ls(., all = TRUE) %>% 
tibble::as_tibble()
}) ->
output$files

pool <- NULL
if(!is.null(tok2)) {
if(db == "Azure") {
pool::dbPool(
drv = odbc::odbc(), 
Driver = config$driver, 
Server = config$server, 
Database = config$database, 
Port = config$port,
Encrypt = config$encrypt,
TrustServerCertificate = config$trustservercertificate,
ConnectionTimeout = config$connectiontimeout,
Authentication = config$authentication
) -> 
pool
} 
if(db == "PCI") {
pool::dbPool(
odbc::odbc(),
drv = odbc::odbc(), 
Driver = config$driver, 
Server = config$server, 
Database = config$database, 
TrustedConnection = config$trustedconnection
) -> 
pool
}
}
onStop(function() {
pool::poolClose(pool)})
reactive({
dat <- NULL
if(!is.null(pool)) {
pool %>% 
DBI::dbListTables(.) %>% 
dplyr::as_tibble() -> 
dat}
return(dat)
}) -> 
db_tables
output$db_tables <- renderTable({
db_tables()})
}
shinyApp(ui_func, server)

Dockerfile

# Dockerfile to test azure
ARG SHINY_PORT=1410
ARG SHINY_HOST="127.0.0.1"
ARG DOCKER_DEPLOY="local"
FROM rocker/shiny-verse:4.0.5
ARG SHINY_PORT
ARG SHINY_HOST
ARG DOCKER_DEPLOY 
RUN apt-get update -y 
&& apt-get install -y 
curl 
gnupg
# See about installing ODBC drivers here: https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017
# Note that the driver version installed needs to match the version used in the code
# In this case for Ubuntu 18.04: ODBC SQL driver 17
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
RUN apt-get update -y
RUN ACCEPT_EULA=Y apt-get install -y msodbcsql17
RUN ACCEPT_EULA=Y apt-get install -y mssql-tools
RUN apt-get install -y unixodbc-dev

RUN echo "noptions(shiny.port=${SHINY_PORT}, shiny.host='${SHINY_HOST}')n" >> /usr/local/lib/R/etc/Rprofile.site
## add any R 
RUN R -e 'install.packages(c("fs", "pkgload", "remotes", "tibble", "lubridate","shiny", "shinyjs", "shinythemes", "hms", "tidyverse", "magrittr", "AzureAuth"), dependencies = TRUE)'
RUN R -e 'install.packages(c("DT", "odbc", "rmarkdown","janitor", "reactable", "pool", "config","RODBC","DBI", "httpuv"), dependencies = TRUE )'
RUN mkdir /srv/shiny-server/app 
&& chown -R shiny:shiny /srv/shiny-server/app
COPY . /srv/shiny-server/app/
# setup shiny server. Create nessesarry dirs for server
COPY ./shiny-server.sh /usr/bin/shiny-server.sh
RUN chmod +x /usr/bin/shiny-server.sh
COPY ./shiny-server.conf /etc/shiny-server/shiny-server.conf
EXPOSE 1410 
# start server. Use sh from rocker project
CMD ["/usr/bin/shiny-server.sh"]

# config file for app
default:
resource: 'https://graph.microsoft.com/'
# resource: 'https://management.azure.com' 
# resource: 
#   - 'https://graph.microsoft.com/.default'
#   - 'openid'
#   - 'profile'
#   - 'email'
#   - 'offline_access'
#   # - 'User.Read'
resource2: 'https://database.windows.net/'
tenant: 'xxxxxxx'
app: 'xxxxxxx'
secret: 'xxxxxxxxx'
driver: 'ODBC Driver 17 for SQL Server'
PCI:
inherits: default
server: 'xxxxxx'
# port: 'xxxxx'
database: 'xxxxx'
trustedconnection: 'xxxx'
Azure:
inherits: default
server: 'xxxxx'
port: 'xxxxxx'
database: 'xxxxxx'
encrypt: 'yes'
trustservercertificate: 'no'
connectiontimeout: '30'
# authentication: 'ActiveDirectoryPassword'
authentication: 'ActiveDirectoryIntegrated'

Answer 1

使用OAuth令牌连接到SQL Server需要使用预连接属性(基本上是指向令牌字符串的指针(。odbc Github repo中有一个开放特性请求。我鼓励你们投票支持它，希望如果它足够受欢迎，它会得到实施。