Connect to MS SQL database using Windows Authentication from Linux

Posit Professional & Hosted Products
1

I am developing a Shiny application for a client and need to connect to a database which uses Windows Authentication to connect. While I am able to connect to the database from a Windows machine, I can't do it from the Linux machine running the Shiny Server (open source version). However I am able to connect to another SQL database from the Shiny Server, which does not use Windows Authentication, instead uses SQL Server Authentication. This is how I am trying to connect:

con2 <- dbConnect(drv=odbc::odbc(),
              server="MCCSVVSQL01\\POOLPOINT",
              dsn="POOL_PORTAL_TEST",
              uid = "my windows id",
              pwd = "my windows password")

This gives me an error: Error: nanodbc/nanodbc.cpp:950: IM002: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

The same connection works from a Windows machine. I did request for SQL authentication, but that is against their policies. Is there any way that I can connect to the database using Windows Authentication?

3 Likes
2

Hi, is this a MySQL or MS SQL database?

3

Hi @edgararuiz this is a MS SQL database.

4

I edited the topic title to reflect that :slightly_smiling_face:

5

So the specific error you are seeing says that the DSN you are using in dsn="POOL_PORTAL_TEST" is not defined on your system.

On UNIX systems, these DSNs are defined at /etc/odbc.ini and use drivers defined in /etc/odbcinst.ini. There are a few things you will have to explore with respect to connecting to this database:

  • It might be worth looking at the DSNs defined on other systems.
  • Do you want to use Windows authentication? When connecting to a database, this usually means Kerberos. As a result, your linux session will need to be running as the appropriate user you are connecting as, and you will need to ensure that a Kerberos ticket is available.
  • Typically, whatever driver you are using to connect to the database has a parameter that allows you to specify which authentication mechanism you are using. You can also usually increase the verbosity of the driver you are using / enable logging so that you get more specific error messages
  • When using ODBC and DSNs, it can be good to test connections with isql -v and to test Kerberos tickets with kinit or klist, to reduce the number of layers of abstraction between you and the database connection.
6

@cole thank you for your reply. Contents of /etc/odbc.ini are:

[POOL_PORTAL_TEST]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Trace=No
Server=MCCSVVSQL01.testscorpio.priv\POOLPOINT
TDS_Version=8.0
Database=POOL_PRE_PROD

And this is what the odbcinst.ini file looks like:

[FreeTDS]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.1.so.0.1
UsageCount=1

I have absolutely no idea on Kerberos. Could you point me to some resource that I could use to set this up please?

7

Very interesting. Strange that you do have that DSN defined... I would test with:

isql -v POOL_PORTAL_TEST once you have things set up the way that you want. You can also look up the documentation on the driver you are using to see how to add driver logging, etc.

I have to say that Kerberos can be an ugly mess... is it required that all connections to this database use Trusted Auth? I.e. if you can enter in user / password, that is definitely the easiest. Otherwise, I would offload the Kerberos work to your IT team, if possible. In order to make Trusted Auth work with Kerberos, you have to get your PAM login to the UNIX server to check authentication against your Kerberos Server and issue a Kerberos ticket. This is a lot of system level stuff that the IT team normally sets up.

Also, the docs are pretty old... so that's hard too! This is the main page, though: http://web.mit.edu/kerberos/