Can't connect django to exist MS SQL Server using mssql-django

Question

I'm getting an error when trying to execute manage.py runserver. I've installed mssql-django and the odbc driver 17 for sql server. I am very new to django and python in general but I want to use exist ms sql server instead of sqlite. this my code in setting.py:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "eHospital_DKAG_NSTL",
        "USER": "sa",
        "PASSWORD": "passwod",
        "HOST": "172.16.2.20\sqlservertest",
        "PORT": "1433",
        "OPTIONS": {
            "driver": "ODBC Driver 17 for SQL Server", 
        },
    },
}

HERE IS ERROR MESSAGE:

Exception in thread django-main-thread:
Traceback (most recent call last):
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 244, in ensure_connection
    self.connect()
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 225, in connect
    self.connection = self.get_new_connection(conn_params)
  File "F:\Python\Django\fptdb\lib\site-packages\mssql\base.py", line 353, in get_new_connection
    conn = Database.connect(connstr, **args)
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: The wait operation timed out.\r\n (258) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (258)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\threading.py", line 1009, in _bootstrap_inner
    self.run()
  File "C:\Users\Admin\AppData\Local\Programs\Python\Python310\lib\threading.py", line 946, in run
    self._target(*self._args, **self._kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\autoreload.py", line 64, in wrapper
    fn(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\core\management\commands\runserver.py", line 137, in inner_run
    self.check_migrations()
  File "F:\Python\Django\fptdb\lib\site-packages\django\core\management\base.py", line 576, in check_migrations
    executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS])
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\executor.py", line 18, in __init__
    self.loader = MigrationLoader(self.connection)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\loader.py", line 58, in __init__
    self.build_graph()
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\loader.py", line 235, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\recorder.py", line 81, in applied_migrations
    if self.has_table():
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\migrations\recorder.py", line 57, in has_table
    with self.connection.cursor() as cursor:
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 284, in cursor
    return self._cursor()
  File "F:\Python\Django\fptdb\lib\site-packages\mssql\base.py", line 246, in _cursor
    conn = super()._cursor()
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 260, in _cursor
    self.ensure_connection()
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 243, in ensure_connection
    with self.wrap_database_errors:
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 244, in ensure_connection
    self.connect()
  File "F:\Python\Django\fptdb\lib\site-packages\django\utils\asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "F:\Python\Django\fptdb\lib\site-packages\django\db\backends\base\base.py", line 225, in connect
    self.connection = self.get_new_connection(conn_params)
  File "F:\Python\Django\fptdb\lib\site-packages\mssql\base.py", line 353, in get_new_connection
    conn = Database.connect(connstr, **args)
django.db.utils.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: The wait operation timed out.\r\n (258) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (258)')

Any help is appreciated. Thank you.

Well, `PORT` isn't a pyodbc connection keyword. If you want to connect to a named instance use `"HOST": "172.16.2.20\sqlservertest"`, but if you want to connect to an instance on a specific port use `"HOST": "172.16.2.20,1433"` instead. Additionally, if you want to connect to a named instance then in SQL Server Configuration Manager the Server Protocols for the instance must have the TCP/IP protocol enabled, the SQL Browser service needs to be running (and restarted if you just changed the protocols), and Windows Firewall needs to allow both udp/1434 as well as the configured TCP/IP port(s). — AlwaysLearning, Jul 21 '22 at 07:38
Aside... consider using doubled-backslashes in instance names due to Python using the backslash as an escape character. `"172.16.2.20\sqlservertest"` wouldn't be a problem, but `"172.16.2.20\name"` would be because Python would convert the `\n` to a linefeed character so pyodbc would be trying to lookup a host named `172.16.2.20ame`. — AlwaysLearning, Jul 21 '22 at 07:41
Thanks a lot, the problem solved. Checking PORT in SQL Server Configuration Manage is 64728 not 1433. Your sharing is very helpful to me!!! — TienVu, Jul 22 '22 at 01:10

score 1 · Answer 1 · answered Sep 02 '22 at 09:23

Keep "PORT" blank or remove "PORT" otherwise there will be error if setting any port number e.g. "2244", "9877" or even "1433" which is the default port number of MSSQL:

DATABASES = {
    "default": {
        "ENGINE": "mssql",
        "NAME": "eHospital_DKAG_NSTL",
        "USER": "sa",
        "PASSWORD": "passwod",
        "HOST": "172.16.2.20\sqlservertest",
        "PORT": "",       # Keep it blank
        # "PORT": "1433", # Or remove it
        "OPTIONS": {
            "driver": "ODBC Driver 17 for SQL Server", 
        },
    },
}

You can see that in Example of mssql-django, "PORT" is kept blank as shown below:

DATABASES = {
    'default': {
        'ENGINE': 'mssql',
        'NAME': 'mydb',
        'USER': 'user@myserver',
        'PASSWORD': 'password',
        'HOST': 'myserver.database.windows.net',
        'PORT': '', # Kept blank

        'OPTIONS': {
            'driver': 'ODBC Driver 17 for SQL Server',
        },
    },
}

# set this to False if you want to turn off pyodbc's connection pooling
DATABASE_CONNECTION_POOLING = False

Can't connect django to exist MS SQL Server using mssql-django

1 Answers1