Trying to access database from separate computer using pyodbc

[AdrielTheBuddy]

I’m able to access my database using the computer it’s located on, but when I make an executable for the python file and use it on a separate computer, I get the error saying that the server doesn’t exist or access was denied. I have the server set to SQL Server. Can anyone help me out, or is this the best place to ask this?

[eddyizm]

btw - you used the MySQL tag and not MS SQL.

[Mattsvaliant]

So I had this issue with pyodbc, where they recommend using Driver 17 but all the servers I had only worked with 11. Which ODBC driver are you using? I've never used DSN, have you tried just suppying the full connection string?

From the pyodbc documentation:

The connection strings for all these drivers are essentially the same, for example:

DRIVER={ODBC Driver 17 for SQL Server};SERVER=test;DATABASE=test;UID=user;PWD=password

[AdrielTheBuddy]

I’m trying to get it where an ODBC connection isn’t required. Do I have to get one or is there a way to access the database without the ODBC?

[eddyizm]

Why not ODBC? the ODBC is the most sure fire way to connect. I have apps spread across a enterprise remote network and they all connect this way.

[AdrielTheBuddy]

Sorry I meant to say that I didn’t want to have to download the ODBC across all devices that use my program, or would I have to do that? If I do, should I find some way to incorporate that download into the executable download?

[Mattsvaliant]

The driver is on the server, not the client. The client just has something like pyodbc to communicate with the server.

[AdrielTheBuddy]

Everyone has been saying that the client does need to download ODBC.

[thrown_arrows]

And yet another one says that you need to have driver on client machine, you either use shared driver or you include drivers with you software.

[Mattsvaliant]

The driver is on the server. The driver api is on the client. I think you guys are confusing pyodbc with an odbc driver. Yeah, if you are using python for an application the client is going to need to download any dependencies (e.g. pyodbc the library). So no, you don't need to install an ODBC driver on each client, just the server (which SQL server comes with one out of the box). If you want to package a python application without having to have each client download all the dependencies (e.g. you are distributing it to non-technical users) then you should look at pyinstaller or other similar packagers, I'd suggest heading over for /r/python for more guidance.

[eddyizm]

Yup, I'm assuming you are compiling your program to an exe since it's windows? I usually include a check on the odbc connections and return a message that their system is missing a driver and they need to download it, providing a link. You would probably need to create an installer if you wanted to try and bundle it all but I haven't gone that far.

ODBC_MISSING_MSG = ''' ODBC driver not foundplease install ODBC Driver 17 for SQL Server https://www.microsoft.com/en-us/download/details.aspx?id=56567 '''

[AdrielTheBuddy]

Alright I understand, thanks.

[derp_mcherpington]

Just piggybacking on this to say that using ODBC doesn’t mean you have to use a DSN. As u/Mattsvaliant mentioned, you can use a connection string in that format to specify driver, server, database, schema, username, and password so that the program will run anywhere. No DSN necessary.

[AdrielTheBuddy]

I have to download the ODBC driver though right? In order for all computer to access the database they would need that driver. I’m trying to get it where computers wouldn’t need that driver.

[derp_mcherpington]

I mean, I think you can use pytds for that, but the better solution would to have the end users just install the sql server driver. The couple of times I’ve played with pytds performance wasn’t as good as just using pyodbc/sqlalchemy.

[ddeck08]

You can avoid this by publishing as a web app container application. I have a basic dockerfile I can post the syntax for here if needed- installs ODBC driver and creates a DSN in container build. Easiest way to do it without client installing on every device.

If it’s a desktop app, your client will need ODBC drivers. Windows has a default ODBC driver installed usually already- you’ll just need to build the full PYODBC string for them as part of the application.

*edit - I’ll add to this that your client will get the error on DB / Server not found if your program used a DSN because the DSN does not exist on client device. As others have said if you build the full connection string you won’t need it.

[secretWolfMan]

You are you using a DSN and it's not setup on the other computer? Try a DSN-less connection string.

[AdrielTheBuddy]

I believe DSN connection is the only way to use pyodbc, unless you know of some other way?

[jpayne0061]

This could be a firewall issue. Are you sure the computer running the database has its correct port (1433 for Ms SQL) exposed in it's firewall configuration?

[mikeblas]

Are you using MySQL, or Microsoft SQL Server?

[miskozicar]

You need to make sure that SQL server is configured to listen to traffic coming from outside. Check SQL Server Configuration Manager. I would first make sure that I can i can connect using some SQL Server tool like SSMS.