6

When using RODBC to connect to Microsoft SQL Server I know I can do the following:

con <- odbcDriverConnect(connection="driver={SQL Server};server=servername;database=dbname;trusted_connection=yes;")

However, that limits me to using the windows authentication for my current windows username. Our processes often require using different log in credentials for different processes. If I need to use a different log in credential using windows authentication is there a way to do that manually? For example, the following fails for me.

con <- odbcDriverConnect(connection="driver={SQL Server};server=servername;database=dbname;uid=domain\\username;pwd=passwd;")

I thought I could use the format domain\\username but it never works and I just keep getting an error like this:

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 failed for user 'domain\username'

My concern is that this is impossible given the answer I found in this question.

This is rather frustrating 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 prefer to use RODBC in this situation though because a lot of previously written code depends upon the RODBC specific functions (e.g. sqlQuery) instead of the DBI functions.

Community
  • 1
  • 1
cdeterman
  • 19,630
  • 7
  • 76
  • 100
  • @ChirayuChamoli thanks for the comment but that is doesn't work because with `trusted_connection` it ignores `uid` and `pwd` and uses the current windows user as the login. I need to use a different login. – cdeterman Feb 17 '17 at 13:59
  • i think i get your point. let me try other alternatives. – Chirayu Chamoli Feb 17 '17 at 17:34
  • You could try using FreeTDS as an ODBC driver instead of using Microsoft's ODBC drivers. If I recall correctly, FreeTDS still supports an older authentication protocol that permits the sort of login you describe. – Gord Thompson Feb 20 '17 at 16:23
  • 1
    if you have admin rights, you can set up ODBC system data source connections. then you can connect using `odbcConnect(servername)` – chinsoon12 Feb 21 '17 at 05:04
  • 1
    Yes, if you have admin rights on the machine where R runs, you can create a User or System DNS (look for ODBC Data Sources in windows menu) storing the connection informations and then odbcConnect to that source. – digEmAll Feb 21 '17 at 13:49
  • Otherwise the only way is running R with the correct user... maybe using runas command or something similar... – digEmAll Feb 21 '17 at 13:51
  • Can you try on your own machine to add Windows Credentials towards this specific database user? Cf. https://www.mssqltips.com/sqlservertip/3250/connect-to-sql-servers-in-another-domain-using-windows-authentication/ If this works, I am quite sure IT can find a way to deploy credentials for a list of users – Eric Lecoutre Feb 23 '17 at 13:42

0 Answers0