R - Manual RODBC Windows Authentication

Question

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.

Manasseh

@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. – Manasseh

i think i get your point. let me try other alternatives. – Clomp

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. – Smithy

if you have admin rights, you can set up ODBC system data source connections. then you can connect using odbcConnect(servername) – Maxey

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. – Blithe

Otherwise the only way is running R with the correct user... maybe using runas command or something similar... – Blithe

Can you try on your own machine to add Windows Credentials towards this specific database user? Cf. mssqltips.com/sqlservertip/3250/… If this works, I am quite sure IT can find a way to deploy credentials for a list of users – Exactly