RStudio Database Connections using Windows Authentication

Posit Professional 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