r - 如何从 R 连接到 Azure SQL Server 数据库？

Question

我正在尝试从 R 连接到 Azure SQL Server 数据库，以便我可以创建表并将数据上传到数据库。

This is how I connect to the server in SQL Server Management Studio

我尝试过以下方法:

library(odbc)

con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "server_name, 1433",
                 Database = "database_name",
                 uid = "me@domain.com",
                 pwd = "password")

这会导致错误:

Error: nanodbc/nanodbc.cpp:1021: HY000: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open server "domain.com" requested by the login. The login failed.

library(RODBC)

con.text <- paste("DRIVER={"SQL Server"}",
                  ";Database=database_name",
                  ";Server=server_name",
                  ",1433",
                  ";PROTOCOL=TCPIP",
                  ";trusted_connection=true",
                  sep="")

con <- odbcDriverConnect(con.text)

这会导致警告消息:

1: In odbcDriverConnect(con.text) :[RODBC] ERROR: state HY000, code 40607, message [Microsoft][ODBC SQL Server Driver][SQL Server]Windows logins are not supported in this version of SQL Server.

2: In odbcDriverConnect(con.text) :[RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute

3: In odbcDriverConnect(con.text) : ODBC connection failed

如何从 R 成功连接到此服务器？

Answer 1

Error: nanodbc/nanodbc.cpp:1021: HY000: [Microsoft][ODBC SQL ServerDriver][SQL Server]Cannot open server "domain.com" requested by thelogin. The login failed.

上述错误主要是由于驱动程序问题造成的。使用此命令检查驱动程序是否正确安装。

 library(odbc) 
 sort(unique(odbcListDrivers()[[1]])) 

从R连接Azure SQL数据库，遵循以下语法:

library(odbc)
library(DBI)
library(RODBC)

con <- DBI::dbConnect(odbc::odbc(), 
                            Driver = "[your driver's name]", 
                            Server = "[your server's path]", 
                            Database = "[your database's name]", 
                            UID = "Database user", 
                            PWD = "Database password",
                            Port = 1433)

请遵循以下引用，其中包含有关将 Azure SQL 连接到 R 的详细信息:

Connecting to Azure SQL via R

https://db.rstudio.com/getting-started/connect-to-database

https://www.youtube.com/watch?v=ZkcBwYjHYOc