RStudio Database Connections using Windows Authentication


#1

In general, I've loved the connections tab that was implemented within RStudio. However, I'm now trying to connect to my employer's Microsoft SQL Database and is normally accessed through Windows Authentication.

I've Googled and looked around but I'm still having trouble understanding exactly how to implement this.

Any guidance would be greatly appreciated.


#2

I would recommend that you read the excellent documentation for the odbc package.

If you want to use a connection string it will look something like this below (it can also be specified with params):

library(DBI)

con <- dbConnect(odbc::odbc(), 
                 .connection_string = 'driver={SQL Server};server=[SERVER_NAME];database=[DATABASE_NAME];trusted_connection=true')

Alternatively, what I think is an easy solution is to create a system DSN.

In Windows:

  • search for ODBC Data Source (64-bit)
  • use the SQL server driver
  • Name, describe and write the server name.


When you've created your system DSN then you can connect like below and start querying the database:

library(DBI)

con <- dbConnect(odbc::odbc(), "VIPDATA")

I hope it helps. Remember to close your connection when you are done.


#3

This is great stuff. Now, how can I do it if I'm trying to connect to a MS SQL Server from a Mac, and my MS SQL Server is Windows Authenticated?

HELP HELP HELP


#4

When I'm trying to connect from Mac with ODBC Driver 13, I get "Login timeout expired" error.
For ODBC driver 17, it never stops attempting to connect, and I have to kill RStudio for it to stop


#5

You need to setup a user that isn’t using windows auth. One were you can acces the DB with a regular username and password.

I really had to wrestle my IT department with that request so good luck :wink:

You might have some luck if you argue for a user with limitid access (think read/write/tables/databases/static IP).


#6

Hi Taras, here are some resources in our DB/R site that may help:


#7

Bless your heart.

<3


#8

Hahaha, thanks JD.
For real though.

I'm on a Mac. My data is in SQL Server. Windows VDI is super slow. I want to run queries against the SQL server out of RStudio. I feel like it may be more efficient than going into the virtual Windows, firing up SSMS, running a query, saving the results on a shared drive, and then picking the data up from Mac.

Alternatively, of course, I can run RStudio on the virtual Windows desktop. But then again, it is slow AF. And annoying.

Help


#9

Thanks @edgararuiz! I was about to ping you yesterday, but then I found those links on my own somehow...

I think I figured everything out, I believe my last stumbling block is the fact that I use Windows authentication to get into my SQL server. Which is obviously is hard to do from a Mac :slight_smile:


#10

That's my current workflowfor accessing anything on SQL Server. I can access Redshift (postgres driver) from Mac, but I have not figured out how to get the combination of ODBC windows auth working right in Mac at all.


#11

So how you then shift back into your native Mac environment and the RStudio instance there? Or are you stuck in the Windows RStudio for the entire analysis?


#12

If i have to connect to SQL server I tend to put a top comment that says, ## run this junk in Windows

For my use case that's fine as I have two work arounds: We have an internal system where I can turn any query into an API that I pass parameters to. Because it's just a GET request that works from any platform. So sometimes after I work out a query I just turn it into an API. Clearly that's not helpful for stuff like dbplyr backends. For interactive dplyr stuff I just say in Win. The other workaround is to cache data locally that I fetched from Windows.

not pretty... but works... until it doesn't


#13

@taraas - How do you access other MS assets with your Mac? Such as SharePoint or other?


#14

At the end of the day, you'll need to use either "open text" credentials, or somehow tack into a Kerberos ticket to take advantage to the Integrated Security from MS. Internally, we are able to test Mac with MS databases by using a service account.


#15

Oftentimes, I get a pop-up window asking for my Windows credentials.
I think I need to have a talk with our IT and see if I can get an "open text" credentials account.
Thanks all.

P.S. See you at rstudio::conf2019? Maybe? When does registration open?


#16

There is software out there that allows for Windows Auth within a Mac environment.

  • Tableau is one example. If you open up Tableau on your Mac, and one of the data sources is a SQL Server with Windows Auth, Tableau will simply prompt you to enter your Windows credentials.
  • https://www.sqlprostudio.com/ is another example.

I was hoping something like that would be possible in RStudio.


#17

RStudio and Shiny do that too:


#18

Right. But that's still "open text" credentials, isn't it?

Would that authenticate me based on Windows authentication while I'm on Mac? I don't think so.
I was bringing up examples of software that is able to log you into the MS SQL Server using Windows Authentication, while on Mac.


#19

It seems like it just isn't possible to do with RStudio at this time. Sad.


#20

On sec, when do you authenticate in Tableau? are you referring to Tableau Server?