r - 使用 DBI :dbConnect in R 连接到 SQL Server 时出现问题

Question

我尝试的第一种方法有效。我首先在我的窗口上定义了一个 ODBC 连接 my_connection_name 和 ODBC data source administrator。然后我使用以下代码:

library(DBI)
library(odbc)
con <- DBI::dbConnect(odbc(),
                      dsn='my_connection_name',
                      UID = "firstname.lastname@company.com",
                      PWD = "mypassword")

这很好用!

但是，当我尝试在 dbConnect 函数中定义驱动程序、服务器和数据库名称时。它失败!

con <- DBI::dbConnect(odbc(),
                      Driver = "ODBC Driver 13 for SQL Server",
                      Server = 'my_server_name',
                      Database = "my_database_name",
                      UID = "firstname.lastname@company.com",
                      PWD = "mypassword",
                      Trusted_Connection = "yes")

我得到错误:

Error: nanodbc/nanodbc.cpp:950: 08S01: [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.

有趣的是，当我删除 Trusted_Connection = "yes" 时。

con <- DBI::dbConnect(odbc(),
                      Driver = "ODBC Driver 13 for SQL Server",
                      Server = 'my_server_name',
                      Database = "my_database_name",
                      UID = "firstname.lastname@company.com",
                      PWD = "mypassword")

我收到这样的错误信息:

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

我在之前定义 ODBC 连接时使用了相同的驱动程序、服务器和数据库名称。

我的身份验证选项是 ActiveDirectoryPassword。

有人知道这是怎么回事吗？

Answer 1

如果使用 Trusted Connection = Yes，您不需要提供您的用户名和密码。您的服务帐户将用于连接到您的 sql server。

con <- DBI::dbConnect(odbc(),
                      Driver = "ODBC Driver 13 for SQL Server",
                      Server = 'my_server_name',
                      Database = "my_database_name",                    
                      Trusted_Connection = "yes")