Connecting to MS server database

Anvil Q&A
1

Hi all,

Is it possible to connect to a MS SQL server database?

I have been doing that locally through python to connect to my organization’s main db but would like to use Anvil for this if possible (and safe). I’m on a Linux box and my organization is Windows-based and, like I said, I am able to connect but wondering if I can do it from Anvil.

I have tried:

import pymssql

@anvil.server.callable
def get_data():
  
   con = pymssql.connect(server=''the_server_name, user='my_username', password='my_password', database='db_name')

But I get an error that says:

InterfaceError: Connection to the database failed for an unknown reason.
at src/pymssql.pyx, line 645
  called from ServerModule1, line 24
  called from Form1, line 11
2

The most probable cause for this error are the security measures at your site. MS SQL usually listens on port 1433, which is normally blocked by firewalls. Besides opening the “hole” in your firewall, you could try with the uplink modules - this means that a python module which talks to the DB directly is in your local environment (e.g. on your Linux box) and your app on Anvil talks to DB via this module. The connection between Anvil and uplink module traverses firewalls without any problems, in my experiences.

1 Like
3

Firewall makes sense. I knew it wouldn’t be that easy! I was trying to avoid Uplink for this particular app since users need to run a calculation that depends on the external DB connection. But, I suppose I could just run it for them via Uplink and a Cron job for now.

Thanks very much.

4

Hi Alan,

If you can get your administrators to open the firewall to a restricted set of IP addresses, you can whitelist the Anvil servers while still preventing connections from the rest of the internet. Contact support@anvil.works for a list of our current egress IPs (ie the IPs from which connections may emerge), which you can choose to allow through your firewall.

(Of course, those IPs are shared by multiple Anvil apps. If you want your own private egress IP, you’ll need a Dedicated plan.)

2 Likes
5

This is great to know. Thanks very much Meredydd. This will become useful when scheduled server jobs are implemented.