When using RODBC to connect to Microsoft SQL Server, I know that I can do the following:
con <- odbcDriverConnect(connection="driver={SQL Server};server=servername;database=dbname;trusted_connection=yes;")
However, this limits the use of Windows authentication for my current Windows username. Our processes often require the use of different credentials for different processes. If I need to use a different credential using Windows Authentication, is there a way to do this manually? For example, for me this fails.
con <- odbcDriverConnect(connection="driver={SQL Server};server=servername;database=dbname;uid=domain\\username;pwd=passwd;")
I thought I could use the domain\\username format, but it never works, and I just keep getting this error:
Warning messages: 1: In odbcDriverConnect (connection = "driver = {SQL Server}; server = servername; database = dbname; uid = domain \ username; pwd = passwd"): [RODBC] ERROR: state 28000, code 18456, message [Microsoft] [ODBC SQL Server Driver] [SQL Server] Login error to user domain \ username
My concern is that this is not possible given the answer I found in this question .
This is pretty annoying because I can use an arbitrary user if I use RSQLServer .
con <- dbConnect(RSQLServer::SQLServer(), "servername", database = "dbname", properties = list(user = "username", password = "passwd", useNTLMv2=TRUE, domain = "domain") )
I would rather use RODBC in this situation, although due to the fact that a lot of the previously written code depends on the RODBC specific functions (e.g. sqlQuery ) instead of DBI functions.