2

I am using ubuntu and python3.5 Trying to connect with remote database with credentials(demo)

jdbc_host = '57.56.254.98'
jdbc_port = '21433'
jdbc_user = 'username'
jdbc_pass = "password"
jdbc_dbname = 'database'
jdbc_server=jdbc_host+':'+jdbc_port
jdbc_url = 'jdbc:sqlserver://{0}:{1}'.format(jdbc_host, jdbc_port,jdbc_dbname)
jdbc_driver_path = 'sqljdbc41.jar'
configProperties = {
    'user':jdbc_user,
    'password':jdbc_pass
}

i have tried pyodbc, pymssql, and pypyodbc with the following code 

con = pyodbc.connect("DRIVER={SQL Server};server=jdbc_server;database=jdbc_dbname;uid=jdbc_user,pwd=jdpc_pass")

and similar code example i found from many sites all of them returns some errors like 

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")

i tried configuring FreeTDS server but it returns error

Authentication failed for user 'username'

although i can connect this database with pyspark using this code with same credentials

marksInfo = spark.read.jdbc(url=jdbc_url, table='tablename', properties=configProperties)

I can read and process data from it using pyspark but its kinda slow for this small database in 'Action' operations so looking for alternatives

Hope i am clear Thanks any help will be appreciated

/etc/odbcinst.ini

[SQL Server]
Description = TDS driver (Sybase/MS SQL)
# Some installations may differ in the paths
#Driver = /usr/lib/odbc/libtdsodbc.so
#Setup = /usr/lib/odbc/libtdsS.so
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
CPTimeout =
CPReuse =
FileUsage = 1

result for command : ldd /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so

linux-vdso.so.1 =>  (0x00007ffc36b38000)
    libodbcinst.so.2 => /usr/local/lib/libodbcinst.so.2 (0x00007f28212f3000)
    libgssapi_krb5.so.2 => /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2 (0x00007f28210a9000)
    libgnutls.so.30 => /usr/lib/x86_64-linux-gnu/libgnutls.so.30 (0x00007f2820d78000)
    libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f2820b5b000)
    libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f2820791000)
    libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x00007f2820586000)
    libkrb5.so.3 => /usr/lib/x86_64-linux-gnu/libkrb5.so.3 (0x00007f28202b4000)
    libk5crypto.so.3 => /usr/lib/x86_64-linux-gnu/libk5crypto.so.3 (0x00007f2820085000)
    libcom_err.so.2 => /lib/x86_64-linux-gnu/libcom_err.so.2 (0x00007f281fe80000)
    libkrb5support.so.0 => /usr/lib/x86_64-linux-gnu/libkrb5support.so.0 (0x00007f281fc75000)
    libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f281fa5b000)
    libp11-kit.so.0 => /usr/lib/x86_64-linux-gnu/libp11-kit.so.0 (0x00007f281f7f6000)
    libidn.so.11 => /usr/lib/x86_64-linux-gnu/libidn.so.11 (0x00007f281f5c3000)
    libtasn1.so.6 => /usr/lib/x86_64-linux-gnu/libtasn1.so.6 (0x00007f281f3b0000)
    libnettle.so.6 => /usr/lib/x86_64-linux-gnu/libnettle.so.6 (0x00007f281f179000)
    libhogweed.so.4 => /usr/lib/x86_64-linux-gnu/libhogweed.so.4 (0x00007f281ef46000)
    libgmp.so.10 => /usr/lib/x86_64-linux-gnu/libgmp.so.10 (0x00007f281ecc6000)
    /lib64/ld-linux-x86-64.so.2 (0x0000560d012ae000)
    libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f281eac1000)
    libkeyutils.so.1 => /lib/x86_64-linux-gnu/libkeyutils.so.1 (0x00007f281e8bd000)
    libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f281e6a2000)
    libffi.so.6 => /usr/lib/x86_64-linux-gnu/libffi.so.6 (0x00007f281e499000)

i can connect using tsql with the database

tsql -S server:port -U username -P 'p@sword\\'

But pyodbc throws this error

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'FreeTDS' : file not found (0) (SQLDriverConnect)")
Waqar
  • 817
  • 2
  • 8
  • 18
  • Did you check this ? https://stackoverflow.com/questions/43606832/r-unixodbcdriver-managercant-open-lib-sql-server-file-not-found – developer developer Sep 21 '17 at 15:18
  • Does your /etc/odbcinst.ini file have a `[SQL Server]` section? If so, have you verified that the `Driver=...` entry correctly points to the appropriate .so file? – Gord Thompson Sep 21 '17 at 15:22
  • have edited the question with odbcinst.ini config @GordThompson – Waqar Sep 21 '17 at 15:30
  • and i couldnt able to find right configuration for odbc* files too i followed this tutorial https://tryolabs.com/blog/2012/06/25/connecting-sql-server-database-python-under-ubuntu/ but after configuring everything it was giving me Authentication error for my username altho as i explained i can connect it with spark so credentials are correct – Waqar Sep 21 '17 at 15:31
  • Does `ldd /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so` find the driver and all of its dependencies? – Gord Thompson Sep 21 '17 at 16:11
  • included command result too – Waqar Sep 21 '17 at 16:19
  • Does `tsql -H 57.56.254.98 -p 21433 -U username -P password` work? – Gord Thompson Sep 21 '17 at 19:54
  • yes it does work – Waqar Sep 22 '17 at 07:25
  • sql alchemy now throwing this error ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000] [unixODBC][FreeTDS][SQL Server]Login failed for user 'username'. (18456) (SQLDriverConnect)") – Waqar Sep 22 '17 at 07:39
  • @Waqar did you find any solution for this? I'm stuck in file not found (0) (SQLDriverConnect) error, it runs fine in deploy-mode client, but I get this issue when I run in cluster mode – BigDataGeek Aug 01 '20 at 14:34

0 Answers0