RStudio Database Connections using Windows Authentication

Posit Professional & Hosted Products
41

Here is a segment of the installation guide I published for my team. This part addresses the process of setting up SQL Server drivers on a Mac and being able to connect to it from RStudio for servers that are Windows authenticated.

ODBC connection to Microsoft SQL Server on a Mac (chapter in development)

References and resources

Here is the list of resources I used to attempt setting up the connection:

This part of setup is the hardest one, as there are two simultaneous tasks at hand:

  • connect to a MS SQL Server from RStudio
  • more broadly, being able to connect to a MS SQL Server from MacOS environment. Windows Auth on our servers is an extra layer of complexity

If you already have Simba SQL Server drivers installed, and RStudio recognizes them (unlikely), then no further action is required in this step. You can check it by going to the Connections pane, and trying to set up a new connection. If you don't see the needed connection listed - you need to perform the rest of the steps.

connection
connection731×521 30.3 KB

Required steps

1. Install homebrew

In the Terminal (Terminal window in RStudio, or Terminal App on your Mac), run this line: /usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"

2. Install the unixODBC library

Again, in the terminal, run: brew install unixodbc

3. Install Simba SQL Server drivers

If you have Tableau 10.2 or later installed on your computer, you might already have the Simba SQL Server driver installed. It is likely that the SQL Server Driver is located at /Library/simba/sqlserverodbc/lib/libsqlserverodbc_sbu.dylib. If the Simba SQL Server driver is installed, then no further action is required.

To install the driver manually, download it manually at Driver Download (direct link to the .dmg file seems to be https://downloads.tableau.com/drivers/mac/TableauDrivers.dmg)

4. Initiate .odbcinst.ini and .odbc.ini files

This part is really just hacking things, and is not the best approach. We'll need to improve it later

According to Solutions - Setting up ODBC Drivers, RStudio can read local .odbcinst.ini and .odbc.ini files if placed at the root folder. This setup is not ideal, as it would be better to set everything up globally, but this approach seems to do the trick, so we'll use it.

4.1 Initiate .odbcinst.ini

Create a new text document, and paste the following information there:

[Simba SQL Server ODBC Driver]
Driver = /Library/simba/sqlserverodbc/lib/libsqlserverodbc_sbu.dylib

(your driver's location may be different)

Save it as .odbcinst.ini in your root folder (~/ or your "home" folder with your name next to it). Notice the . in front.

If you succeeded in this step, you should now see "Simba SQL Server ODBC Driver" as an option when setting up a new connection in RStudio

4.2 Initiate .odbc.ini

This step will generate 2 DNSs for your databases.
Create a new text document, and paste the following information there:

[ODBC Data Sources]
server1=Simba SQL Server ODBC Driver
server2=Simba SQL Server ODBC Driver

[server1]
Driver = /Library/simba/sqlserverodbc/lib/libsqlserverodbc_sbu.dylib
Server=some_server
Database=some_database
UID=your_uid
PWD=your_password
Trusted_Connection=NTLM

[server2]
Driver = /Library/simba/sqlserverodbc/lib/libsqlserverodbc_sbu.dylib
Server=some_server
Database=some_database
UID=your_uid
PWD=your_password
Trusted_Connection=NTLM

Save it as .odbc.ini in your root folder (~/ or your "home" folder with your name next to it). Notice the . in front.

If you succeeded in this step, you should now see your servers/databases in the connection pane of RStudio

8 Likes
42

Great work. I'll definitely try this when I get back from holiday.

1 Like
43

We recently migrated to AWS using a SQL Server Client. Before migration, I was able to use odbc to access the datawarehouse (using kerberos/window auth on Mac), however broke during the cloud migration.

I went through this workflow to try to connect using Simba (our Tableau server connections did not break after cloud migration) and still requires a UID/PWD combo based on the error I received. Upon entering my credentials + trusted connection parameters it still is prompting for UID/PWD being absent.

2 Questions:

  1. Why need UID/PWD when it's a trusted connection using Windows Auth?
  2. When placing UID/PWD into my config file after the fact, why is the R session not seeing the updated credentials?
44

well I finally decided to yak shave until I figured this out... here's what I discovered:

It turns out I had 3 (yes three!) different installs of unixODBC on my Mac. I had versions from MacPorts, Anaconda, and Homebrew. I literally didn't know Homebrew had ever been installed on my machine. I blame drunk computing.

I figured this out by searching my box for all instances of odbcinst.ini via the shell

sudo find / -name "odbcinst.ini"

which uncovered /usr/local/Cellar/unixodbc/2.3.0/etc/odbcinst.ini ... in addition to all the ones I was expecting (anaconda and macports). I edited this file adding the Simba SQL drivers as outlined elsewhere, and it worked.

Prior to this I had been editing the odbcinst.ini installed by Macports, obviously to no avail. The reason I was so sure I was using the macports install was that if I ran which isql or which odbcinst it would so files in the opt/local/bin path which is my macports path. Turns out that's not the unixODBC install that R Studio was seeing, however.

3 Likes
45

just to prevent confusion for other folks, the UID should have domain\\username:

con <- dbConnect(odbc::odbc(), 
                     .connection_string = "Driver={Simba SQL Server ODBC Driver}; 
                                           Server={yourserver}; 
                                           Database=yourdb; 
                                           UID=domain\\username; 
                                           PWD=password; 
                                           Integrated Security=NTLM")
2 Likes
46
  1. Oops, yes, domain
  2. Forgot to document the "escaping the \ with another \" part. Curse of knowledge?

There is an expiration date on post editing I guess, and I can no longer edit it, but I hope folks will figure it out.
I also propose a better way in a later guide: writing an .odbc.ini file instead

1 Like
47

One addendum to jdlong's last post: after .connection_string add one more argument, encoding.
So for me,

con <- dbConnect(odbc::odbc(), 
                     .connection_string = "Driver={Simba SQL Server ODBC Driver}; 
                                           Server={yourserver}; 
                                           Database=yourdb; 
                                           UID=domain\\username; 
                                           PWD=password; 
                                           Integrated Security=NTLM", encoding="UTF-32")

Depending on your instance, the encoding may be different. Till that addition, all database responses returned single letter results.

Also, hunting around for the correct place to add DriverManagerEncoding proved fruitless. The encoding argument did the trick.

2 Likes
48

A post was split to a new topic: After Machine Reset - Failure. nanodbc/nanodbc.cpp:950 HY000: [Simba] [ODBC] (11560) Unable to locate SQLGetPrivateProfileString function.