R local database connection: Posit Cloud R Studio vs Local Laptop R Studio

Question

I'm trying to use Posit Cloud to connect to a SQL Server database installed locally on my laptop.

The following, using only RStudio on my laptop, runs/connects without issue:

# RStudio version: 2023.06.1 Build 524
# R version:  4.1.2

library(DBI)
library(odbc)

con <- dbConnect(odbc::odbc(), 
         Driver = "SQL Server",
         Server = "DESKTOP-4PC1LOK\\SQLEXPRESS", 
         Port = "1433", 
         UID = "publicaccess", 
         PWD = "password", 
         Database = "crime_camera_west_arlington", 
         timeout = 10)

However, when using RStudio in posit:Cloud to run the same code as above I get:

Error: nanodbc/nanodbc.cpp:1135: 00000: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found 

What makes running this code in posit:Connect different?

Thanks.

Answer 1

Apparently, as mentioned by some of the responders, "SQL Server" is the incorrect driver. As suggested by ChatGPT, I ran odbc::odbcListDrivers(), in RStudio in Posit Cloud, to find the list of drivers available in Posit Cloud. "SQLServer" was listed for SQL Server. Substituting the correct driver removed the error message. Thanks for all responses.