I don't have the rights to do this at work, but you might. Open a SQL Server Management Studio session and try to create a SQL Server Auth following this guide:
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/aa337562(v=sql.105)
I fear you might be heading down a rabbit hole trying to get Windows auth to work.
Once, I started to build a REST API for my desktop with plumber to get around the issue, but I stopped not to get in trouble 
Me neither.
Well, again though, Tableau can authenticate you into your MS SQL Server from a Mac in case of Windows Auth.
Which tells me it the mission is not impossible and is only dependent on the capabilities of the software.
Is this with the free or the paid for version of Tableau?
I've done it on the trial version of Tableau Prep.
So, I haven't paid for anything yet - I just wanted to see if it is possible in theory.
The connection looks like this (I'm on Mac):
That's interesting, if this article is current, and it applies to the Prep product as well, it looks like the database driver provider is Simba, which is the same provider we use for our Pro products:
https://onlinehelp.tableau.com/current/pro/desktop/en-us/help.htm#examples_sqlserver.html%3FTocPath%3DReference|Connector%20Examples|_____43
Our page: http://db.rstudio.com/rstudio/pro-drivers/ & https://www.rstudio.com/products/drivers/
Interesting!
RStudio professional drivers are not available to RStudio Commercial though, are they?
Yes, the professional drivers are included at no charge when purchasing one of our professional products.
So, RStudio Commercial is not considered one of professional products?
Sorry for the confusion, I usually refer to the paid-for products as Pro products, because that's usually part of their name. If you have a licence for any of our commercial (paid-for) products, you are able to download and use the database drivers we provide.
Thanks Edgar @edgararuiz!
I was (and still am) confused by this phrase on the RStudio Professional Drivers page:
If you are using RStudio Server Pro, RStudio Connect, or Shiny Server Pro, then you can download and use RStudio Professional Drivers at no additional charge.
It doesn't list RStudio Commercial (the $995/user/year one), unless I'm confusing the product names.
Right, you're referring to the RStudio Commercial Desktop license (https://www.rstudio.com/products/). That is one of the valid products that gives you access to the drivers, I verified that yesterday. We probably need to update the page.
Since Tableau Desktop install already provides us with Simba SQL Server Driver, I was able to leverage it to connect to MS SQL Server from a Mac.
The key thing was to set this parameter: Integrated Security=NTLM (I think same could have been done for Trusted Connection)
That's great to know. Is there anything we should note in this page? I feel like there may be a documentation gap on that: http://db.rstudio.com/databases/microsoft-sql-server/
I think the main thing was my note above about the NTML option (which isn't widely documented anywhere)
@jdlong Dude, check out the solution above. It works for me. You'll need Simba driver for SQL server (I downloaded mine here: https://downloads.tableau.com/drivers/mac/TableauDrivers.dmg), and from there my connection string looks like this:
library(odbc)
con <- dbConnect(odbc::odbc(),
.connection_string = "Driver={Simba SQL Server ODBC Driver};
Server={yourserver};
Database=yourdb;
UID=server\\username;
PWD=password;
Integrated Security=NTLM")
My ride has been smooth ever since I got this setup to work. Doing my SQL in RStudio now, and haven't accessed VDI in days!
I'm glad you got it going! I'm not having such luck, however:
Error: nanodbc/nanodbc.cpp:950: 01000: [unixODBC][Driver Manager]Can't open lib 'Simba SQL Server ODBC Driver' : file not found
ODBC on Mac is such an infuriating mess, IMO. At this point I seriously have no idea which incarnation of unixODBC my RStudio install is picking up. Is the Anaconda one? The xcode one? One from Mars? And I could probably figure it out eventually... given enough random slaps against the keyboard... but the very thought is demoralizing.
First off, you might want to first try seeing if the odbc package can even see that driver on your machine by using the command from this page, as I would suspect that it most likely is not:
sort(unique(odbcListDrivers()[[1]]))
https://db.rstudio.com/getting-started/connect-to-database
I think I see what is going on here....your error is stating that the unixODBC can't point to (or find) where the "Simba SQL Server ODBC Driver" is on your local machine. I think @taras's solution essentially tried to setup these files for your automatically, which seems like it did not work. The error is definitely a bit cryptic if you have never had to manually modify configuration files to make things connect and work properly. This page goes in more depth on how to configure it:
https://db.rstudio.com/best-practices/drivers/
This is a good example of how to configure the file necessary for your specific issue:
Let me know if this helps or not.
@edgararuiz it might be helpful for newcomers to outline from the ground up on this page:
http://db.rstudio.com/getting-started/connect-to-database/
how to create and configure these files from the start, as I think most people would have a hard time setting up these files to work properly, and getting the odbc package to verify the driver is being picked up.
Amen to that, brother!
I remember I needed to mess with odbc.ini (or whatchamacallit? actually it's the other one with the driver list: odbcinst.ini i think) files to make RStudio "see" my freshly installed ODBC drivers from Microsoft. I had to edit a few files to get it to work. I found several .ini files, some blank, some semi-complete. I guess RStudio saw one but not the other
Simba drivers, however, were recognized instantly.
When I dealt with ODBC on linux, db.rstudio.com was very helpful! Especially this part talking about odbc.ini and odbcinst.ini
Solutions - Setting up ODBC Drivers
What I learnt:
odbcinst -j command line toolODBCSYSINI can be used to change the default location of system files~/.odbc.ini for datasource connexion and ~/.odbcinst.ini for drivers locations - useful for testing quickly without admin rights. (WARNING : there is a dot in local ini file name that is not present in system ini file name.)It is very helpful to set up those files to simplify connection string afterward (you can juste use the DSN name). However, they are not required - you can pass all argument at the connection but:
Hope sharing this experience could help.