0

I am trying to connect to a SQL Server database using SQLAlchemy on a Databricks cluster.

I have some simple code that looks like this,

db_conn_str = "mssql+pyodbc://<user-name>:<password>@<server-name>.database.windows.net:1433/<database-name>?driver=SQL Server"

engine = sa.create_engine(db_conn_str)

metadata = sa.MetaData(bind=None)
table = sa.Table(
    table_name,
    metadata,
    autoload=True,
    autoload_with=engine,
    schema=TABLE_SCHEMA
)

Note: This code is actually written within a custom Python Package that I am calling from Databricks.

However, I keep getting the following error,

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

What exactly is going on here and how can I resolve this?

Another fact that I think should be mentioned is that usually when connecting to my SQL Server database, I need to whitelist my IP. However, I am not sure how this applies when connecting using Databricks.

Minura Punchihewa
  • 1,498
  • 1
  • 12
  • 35
  • If you run `odbcinst -q -d -n "SQL Server"` does it display a `[SQL Server]` section with (at least) a `Driver=…` entry? – Gord Thompson Jul 04 '22 at 11:55
  • I cannot seem to run this command. It fails. – Minura Punchihewa Jul 04 '22 at 12:54
  • Fails with "odbcinst: SQLGetPrivateProfileString failed with ." ? – Gord Thompson Jul 04 '22 at 13:37
  • Yes, that is correct. I tried the answer given here as well, but it does not seem to work for me, https://stackoverflow.com/questions/61022848/do-you-know-how-to-install-the-odbc-driver-17-for-sql-server-on-a-databricks-c – Minura Punchihewa Jul 04 '22 at 15:08
  • Do you get the same error with `odbcinst -q -d` ? – Gord Thompson Jul 04 '22 at 15:26
  • Nope. I get this response `[ODBC Driver 17 for SQL Server]`. – Minura Punchihewa Jul 04 '22 at 15:28
  • Then use `db_conn_str = "mssql+pyodbc:// … ?driver=ODBC+Driver+17+for+SQL+Server` – Gord Thompson Jul 04 '22 at 16:05
  • This worked for me, Gord. The only issue is, this code is contained inside of a custom Python Package that I am calling from Databricks and as a result, if I hard code the driver this way, I become unable to run it locally on my machine. – Minura Punchihewa Jul 05 '22 at 06:11
  • Can't you install ODBC Driver 17 for SQL Server locally? – Gord Thompson Jul 05 '22 at 12:06
  • Yes, I can do that, but there are quite a few others contributing to my project. I feel that might be a bit of an issue, since it will break the code when they're running it on their machines. One other option I was thinking of is using `pyodbc.drivers()[0]` to get the available driver on a given machine and append it to the connection string. – Minura Punchihewa Jul 05 '22 at 13:58
  • 1
    Yes, that's a [good idea](https://stackoverflow.com/a/49736571/2144390). However, on other people's machines you can can't count on the appropriate driver being the first one in the list. – Gord Thompson Jul 05 '22 at 14:31
  • That is true. I will use one of the options we have looked at here. Thank you very much! Maybe you can add an answer outlining the steps? – Minura Punchihewa Jul 06 '22 at 03:16
  • @GordThompson I would really appreciate your feedback on this question as well, https://stackoverflow.com/questions/72877663/running-bash-script-in-databricks-fails – Minura Punchihewa Jul 06 '22 at 03:31

0 Answers0