Unable to establish connection through to SQL Server from Databricks to read tables from and create-CodePudding

I am using the code below in Databricks to read a table from SQL Server, but it returns the error "('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server Native Client 11.0' : file not found (0) (SQLDriverConnect)")" :

'''
cnxn_str1 = ("Driver={SQL Server Native Client 11.0};"
        "Server=SRV3;"
        "Database=DBN3;"
        'Trusted_Connection=yes;')
cnxn1 = pyodbc.connect(cnxn_str1)

'''

This code is however working perfectly in the local machine Jupyter Notebook. What should I go about doing this? Ideally I would want to create a connection to SQL Server to be able to read tables from there and also create tables. Any help would be appreciated.

CodePudding user response：

The SQL Driver required to connect to a SQL Server is not installed by default. As a quick fix, you could use the following snippet to install it on your cluster. Paste this in a cell and run it :

%sh
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17

If you are looking for a more production-ready solution, I would put this script in an init script so it is installed as the cluster starts.

CodePudding user response：

[Not an answer - Unable to comment yet.]

The below link from Microsoft lists the steps to install the ODBC driver in Ubuntu on which Databricks is usually based on. It is pretty much similar to the comment above. Additionally it has additional information on unixodbc and other tools. Not sure if it is necessary, but you can maybe try after further research.

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16#ubuntu18

# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

Also as per the comments in the link - there seems to be some options that can be set. (Getting Error "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server Native Client 11.0' : file not found (0) (SQLDriverConnect))

Have you installed SQL Server Native Client 11.0 and configured it in /etc/odbcinst.ini? SNC dates from SQL Server 2005-2012 era, though. Consider installing ODBC 17 on Linux for newer development. – AlwaysLearning Feb 12, 2021 at 3:05

As always please do prior research before installing/implementing any changes in your system.