Connecting to sql server

Product Help
1

Hi there,
I am trying to connect to a remote SQL server, but getting error about driver not being installed. How would I go about installing the driver on thee notebook environment?

2

Hello,

You can download the database driver for the version of SQL you are running from the vendor’s website(Microsoft, Oracle, etc). You will install the driver on the version of operating system you are running. It doesn’t matter what conda environment you are using.

3

I am using Anaconda Cloud Notebooks, so should the driver be within the cloud environment instead rather than on my local computer?

4

Hello,

No, you need to install the driver on your local computer and use a python database package like ‘pyodbc’ to connect to your database.

5

Hi,
yes i do have the odbc driver installed on my laptop and running it from Notebooks generated this error even thought it is installed.
Error: (‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘ODBC Driver 17for SQL Server’ : file not found (0) (SQLDriverConnect)”)

6

It looks like your ODBC configuration is not correct, though the driver is installed.

I would suggest checking to make sure your driver configuration is correct from the unix prompt using ‘odbcinst’ and the ‘isql’ utilities:

If your configuration is correct ‘odbcinst’ should return the following:

unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/<your_user_name>/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

at /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
UsageCount=1

The other thing you can do to check the connection is to try connecting to the database using ‘isql’:

isql -v -k “DRIVER={ODBC Driver 17 for SQL Server};SERVER=xxx;UID=xxx;PWD=xxx”

If it works you should see something like the following:

Connected!

sql-statement
help[tablename]
quit

7

(datacloud) 02:03 ~ $ odbcinst -j
unixODBC 2.3.10
DRIVERS…: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES…: /etc/ODBCDataSources
USER DATA SOURCES…: /home/dd45f252-72ab-4f71-bf57-8c9fab62de39/.odbc.ini
SQLULEN Size…: 8
SQLLEN Size…: 8
SQLSETPOSIROW Size.: 8
(datacloud) 02:04 ~ $

/etc/odbcinst.ini is empty and uneditable.

isql -v -k “DRIVER={ODBC Driver 17 for SQL Server};server=datacloud.healthcareit.net"
[IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified
[ISQL]ERROR: Could not SQLDriverConnect

so looks like the no default driver specified is the issue, but I don’t know how to correct in the Notebook environment.

locally, I am able to connect to my datasource using SqlServer Management Studio fine.

thank you!

8

I don’t think that your SQL connection string is right. It doesn’t look like you are specifying a login and password for the connection.

Can you paste the contents of your /etc/odbc.ini and /etc/odbcinst.ini files below?

9

both /etc/odbc.ini and /etc/odbcinst.ini in Notebook environment are empty