Shiny Server, Microsoft ODBC Driver for SQL Server on Linux, and RODBC problem

[Jeffrey Kingsley]

Anyone used the Microsoft ODBC driver to query SQL Server from RODBC and Shiny Server on Ubuntu?

 

We have installed Shiny Server on Ubuntu 12.04 (64 bit vm) with MS ODBC Driver 11 for SQL Server on Linux (http://msdn.microsoft.com/library/hh568451(SQL.110).aspx). Testing the driver installation with sqllcmd works fine. We are able to open a connection to the SQL server, select a database, and execute a query. We are also able to run a simple shiny app that queries the database we are trying to connect to. This simple app is running within R studio on a windows machine. However the same app on the Linux Shiney Server returns the following error:

 

Warning in odbcDriverConnect(paste0(driveStr, serverStr, database, authStr)) :
  [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified

 

 

The simple app was written using a connection string rather than DSN. The various parts of the connection string are idential to the string used on the windows app except the driver. On the Linux machine, we are using [SQL Server Native Client 11.0] as the driver, based on the response to odbcinst -q -d.

 

Thanks for any help.

[Joe Cheng]

I can't speak for the MS ODBC driver but I've tried FreeTDS with RDBC and it worked fine, connection string like so:

"Driver=FreeTDS;Server=myserver;uid=myid;pwd=mypwd;TDS_Version=7.2;Database=dbname"

I needed to install the following Ubuntu packages:

r-cran-rodbc

unixodbc

unixodbc-dev

freetds-bin

freetds-common

freetds-dev

libct4

libsybdb5

tdsodbc

sqsh

Note that I am very far from an expert on the subject, the above simply got me to the point where I could successfully query.

--
You received this message because you are subscribed to the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this group and stop receiving emails from it, send an email to shiny-discus...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Mayank]

Same problem here . 

I have installed Shiny server on OpenSUSE 12.2 but not able to make it connect with MS SQL server  run with either RJDBC of FreeTDS 

Jeffrey : would be great help if you can guide me on whether you were able to connect and if yes HOW 

[Sam]

Hi,

 

I am using SUSE 12.3 and need to conenct to SQL server. Does any one able to connect to SQL server. If so can you share the steps to do this?

 

Thanks,

Sam

[Frans van Dunné]

In response to the OP: you have most likely not set up your odbc.ini properly. See below. I add further details because you may also have a unixodbc version issue (it must be 2.3) - but you did not specify.

The MS ODBC drivers have just been updated to version 11 (quite a jump from 1.0, but there it is). Installing it on Ubuntu works if you take the pointers from Andrew K into account. Between that post and the Microsoft documentation all steps are well described. I will summarize my steps below for Ubuntu 12.04:

Download from msdn and untar : msodbcsql-11.0.2270.0.tar.gz 

Make sure you have all dependencies installed: sudo apt-get install libssl1.0.0 libkrb5-3 glibc-2.13-1 e2fsprogs openssl libkrb5-3

Also check that you have wget (sudo apt-get install wget).

To update you unixodbc version (through the repositories you will have an older version installed) run the command as sudo bash build_dm.sh. I have no clue why it does not seem to work with the ./ prefix, for some reason I need to give the full reference to bash. The installer gives further instructions to make install unixodbc 2.3.

Now install the actual driver: sudo bash install.sh install --accept-license --force

the --force flag is important because otherwise it will complain that there is no rpm installed on the system (which there is not on Ubuntu). This was a tip from Andrew K again.

The installer updated the /etc/odbcinst.ini file to include the driver like so it should now include:

[ODBC Driver 11 for SQL Server]

Description=Microsoft ODBC Driver 11 for SQL Server

Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0

Threading=1

UsageCount=2

Seeing this is a check that installation went through correctly.

Now another tip from onefinepub is required otherwise it will not work:

sudo ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /usr/lib/libcrypto.so.10

sudo ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 /usr/lib/libssl.so.10

Now make sure that you configure you odbc.ini to make a DSN that connects using the new driver. It will contain the following:

[ConnectionName]

Driver       = ODBC Driver 11 for SQL Server

Description  = ODBC connection to MSSQL Database via the Microsoft Driver

Trace        = No

Server       = xxx

Servername   = xxxx

Database     = xxx

Trace           = No

But it will not contain a username and password. You will have to pass that through to the database server when making the connection (see this post).

Having done all this your R will look like:

> library(RODBC)

> sqlconnection <- odbcConnect(dsn="DBNAME", uid="UserName", pwd="Pass")

And your connection should be open.

[Nestor Montaño]

I don't know about MS driver, but with FreeTDS is as follow (on CENTOS 6):

A good tuto is in http://www.webdbtips.com/256373/ 

- install unixODBC

    + su root

    + yum install unixODBC unixODBC-devel libtool-ltdl libtool-ltdl-devel

- install FreeTDS
    + yum install freetds

- Configure /etc/odbcinst.ini

    + nano /etc/odbcinst.ini
    + add in the end (without spaces or tabs):

# Driver for conect to MSSQL
[FreeTDS]
Description = ODBC for Microsoft SQL
Driver      = /usr/lib/libtdsodbc.so.0 # (here, you must search this file first, sometimes the file is "libtdsodbc.so", and in a 64bit sistem you must go to /usr/lib64/)
UsageCount  = 1
Threading   = 2

- Verify that the system "see" the dirve [FreeTDS]

    + odbcinst -q -d

    + The result must contain the line: [FreeTDS]

- Configure /etc/odbc.ini

    + nano /etc/odbc.ini

    + Add to the end (without spaces or tabs):

[FreeTDS-YourDataBase]

Description     = MS SQL connection to 'NDWH' database
Driver          = FreeTDS
Database        = YourDataBase
ServerName    = Name_for_YourServerName
Trace           = No

- Configure /etc/freetds.conf

    + nano /etc/freetds.conf

    + agregar al final:

# Server Name_for_YourServerName, type Microsoft SQL Server
[Name_for_YourServerName]
        host = 192.168.123.123 (The IP of your server)
        port = 1433 (the port, usually is 1433)
        tds version = 8.0 (the FreeTDS protocolo version, you could try 7.0 too)

- Verify that you could connect to the DDBB through isql

    + isql -v FreeTDS-YourDataBase user pass

    + do a query on it !!

    + If you couldn't connetc, inspect odbc.ini and odbcinst.ini 

- Verify that you could connect to the DDBB through tsql

    + tsql -S Name_for_YourServerName -U user -P pass -D dataBase

    + If you have success with tsql but haven't with isql, then there are some problem in the odbc.ini or odbcinst.ini

    + If you don´t have success with tsql so you probably have a problem with the freetds.conf , tds-version , port or some configuration on the MS-SQL server

See http://freetds.schemamania.org/userguide/confirminstall.htm