Connect to MS SQL database using Windows Authentication from Linux

linux

#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?


#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.