How to add an ODBC driver to streamlit cloud?

Community Cloud
1

Hi,

I have an app that works locally fine, but requires ‘ODBC Driver 17 for SQL Server’ to be installed to retrieve data using pyodbc when deployed. I have tried using a requires.txt file with combinations of the below (as per other posts) but it comes up with a could not be located message (I guess because Linux based).

What does everyone else do to get around this? As there must be a way to do this, any help massively appreciated.

unixodbc-dev
msodbcsql17
mssql-tools

Screenshot 2022-01-07 101337

[This is the link to the microsoft documentation.]

Thanks

1 Like
2

Replying because you and I are in the exact same situation right now and hoping my reply somehow bumps this up to someone who knows the answer.

3

I looked at using FreeTDS as an alternative to the MS drivers which does install on packages.txt using;
freetds-bin
freetds-dev
However I then came to a different error
pyodbc.Error: (‘01000’, “[01000] [unixODBC][Driver Manager]Can’t open lib ‘FreeTDS’ : file not found (0) (SQLDriverConnect)”)

Does steamlit cloud need an odbc.ini and odbcinst.ini file to locate the driver? Does anyone have a working SQL server connection on Streamlit Cloud using either FreeTDS or MSSQL drivers?

4

Hi @Andy_J -

My apologies, I meant to get back to you about SQL Server specifically and then the long holiday weekend happened.

SQL Server is an edge case we need to solve for, since the driver isn’t in a core Debian package list AND you need to accept a EULA. Our engineering team will be making a release shortly to add SQL Server to the base Streamlit Cloud image until we can figure out a more permanent solution. It’s still early on the West Coast of the U.S., but I can find out later today when the fix will go in.

Edit: It looks like this feature is in our QA, pending proof that a connection can be made using the installed driver. I’m told it will happen this week, and I’ll update this thread when it happens (cc: @Mark_Von_Oven)

Best,
Randy

2 Likes
5

Thanks for the reply Randy.

That’s great news, I look forward to testing it out next week sometime.

6

I think the driver should be present on the image now, though the connection guide won’t make it to the documentation for a little bit yet. Essentially, you would pass all the parameters to the connection string (instead of referring to a DSN), and you can use st.secrets within Cloud to keep the credentials from being in your code.

Hopefully you can figure it out, if not, I’ll see if I can get a code snippet from someone in advance of this making it in the docs.

Best,
Randy

1 Like
7

Hi @randyzwitch

Unfortunately I still have the same error (unable to locate package), could you confirm which driver has been added?

Once I get this to work, I’m happy to post an example connection string while awaiting the official guide release.

Thanks
Andy

8

Same here @randyzwitch . I assumed we could just include all three packages that @Andy_J references above in the packages.txt file and reboot…but still getting the exact same errors as before (which are different than Andy sees. Here’s a snippet of the log:

log
log1902×786 273 KB

9

I think you have to delete your app and re-deploy, so that the new version of the container gets used. Could you try that @Mark_Von_Oven and @Andy_J?

The driver should be the official MS SQL driver.

10

Thanks @randyzwitch that worked.

@Mark_Von_Oven you now only need msodbcsql17 in your packages.txt file.
Adding unixodbc causes a clash now I believe but it is no longer needed.

1 Like
11

Thanks for confirming @Andy_J! @snehankekre is working on the connection guide, so if you have any other tips, he’d be a great person to contact to make sure they get into our docs :slight_smile:

12

Guys - I’ve been so busy building cool new stuff over the last week that I forgot to say thank you. Appreciate the quick response here and everything is working great (with the exception of the fact that i need to get better at closing connections before they timeout on me and hose everything up) :slight_smile:

Thanks again!

1 Like
13

That’s fantastic news, thanks @Mark_Von_Oven!

14

Hey @Andy_J , @Mark_Von_Oven, @randyzwitch, thanks so much for sharing this info - it’s allowed me to get one step closer to connecting to an Azure database.

I wonder if one of you, Andy or Mark, would mind sharing your connection string (with sensitive details removed)/anything else you’ve had to do in order to connect? I’m getting a login timeout error - I’ve tried specifying the port number and allowlisting the IP addresses found here and am short on ideas on what else to try.

Or would be interested in any info @snehankekre is able to provide. (Happy to do some user testing of the connection guide you’re producing if that’s helpful!)

Thanks

15

@philipnye No problem…here is the exact connection string I’m currently using in my code. All dynamic variables are set within the secrets file. Some tips on those variables:
server: I use a string that is a combo platter of ‘servername, port’
(example - ‘my-sqlserver.database.windows.net, 1433’)
auth = ‘ActiveDirectoryPassword’
driver = ‘{ODBC Driver 17 for SQL Server}’

con_str = f'Driver={driver};Server={server};Database={database};Uid={username};Pwd={password};Encrypt=Yes;TrustServerCertificate=No;Authentication={auth};'
_cnxn = pyodbc.connect(con_str)

Hope it helps!

1 Like
16

Thanks for your patience everyone! @Mark_Von_Oven is spot on with the connection string.

We’re close to releasing an official tutorial with this PR. It needs to go through review. In the interim, here’s a preview of the tutorial with a demo app, and repo.

Note: On Streamlit Cloud, we have built-in support for SQL Server. On popular demand, we directly added SQL Server tools including the ODBC drivers and the executables sqlcmd and bcp to the container image for Cloud apps, so you don’t need to install them.

All you need to do is add the pyodbc Python package to your requirements.txt file, and you’re ready to go! :balloon:

Please let me know if you have any follow-up questions and feedback.

Happy Streamlit-ing!
Snehan

2 Likes
17

Thanks Mark, that’s helped me spot where I was going wrong!

2 Likes
18

Thanks Snehan. That’s great that Streamlit Cloud now has built-in support for SQL Server - that simplifies things. And I’ve had a quick read through the tutorial preview (thanks for sharing!) - all looks good to me.

One question - when connecting to an Azure SQL database there are a number of different ways you can authenticate. The tutorial authenticates with a username and password, but I wonder whether other types of authentication such as ActiveDirectoryIntegrated could be supported? It would be useful if users could connect to a d/b with their own login, as in many cases they’ll have different permissions, and I think a different authentication type might be needed to allow this.

Love the work the team have been doing on this though - I don’t want to detract from that!

1 Like
19

Thanks for the feedback, @philipnye! I’ll relay it to our team. :smile:

From the Microsoft documentation:

pyODBC uses the Microsoft ODBC driver for SQL Server. If your version of the ODBC driver is 17.1 or later, you can use the Azure Active Directory interactive mode of the ODBC driver through pyODBC. This interactive option works if Python and pyODBC permit the ODBC driver to display the dialog. The option is only available on Windows operating systems.

Almost all the authentication methods on the link you’ve shared are unfortunately only applicable to Windows systems. The host one is connecting to Azure SQL database from (i.e Streamlit Cloud in this case) has to be running Windows.

Apps deployed to Streamlit Cloud currently run in Debian Linux containers and are thus incompatible with the other authentication methods. Nonetheless, I will pass this use-case on to our eng teams.

Additionally, I’ll add a callout in the tutorial describing this limitation of SQL Server.

21

1m

Thanks! The table here does state the following:

ActiveDirectoryIntegrated Windows, and Linux/Mac 17.6+, driver only . Authenticate with an Azure Active Directory identity using integrated authentication.

So I do think there’s at least one additional authentication method ( ActiveDirectoryIntegrated ) that should be possible in Linux. Though there might be other technical reasons why this might not be workable in Streamlit.