0

I am trying to connect to my SQL Server 2016 Database using pyodbc with django.

In the SQL configuration manager i have all the network configurations as Enabled

  • shared memory
  • Named Pipes
  • TCP/IP

FireWall is turned OFF

I tried using localhost and it worked fine but when I tried to connect to a server on the same network it did not work and displayed the below error:

OperationalError at /connect/

('08001', '[08001] [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53]. (53) (SQLDriverConnect); [08001] [Microsoft][SQL Server Native Client 11.0]Login timeout expired (0); [08001] [Microsoft][SQL Server Native Client 11.0]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. (53)')

I tried to check the ODBC Driver on the server and it displays these drivers:

  • ODBC Driver 13 for SQL Server
  • SQL Server
  • SQL Server Native Client 11.0

and I tried both of them but no success.

views.py

from django.shortcuts import render
import pyodbc
# from .models import Artist
# Create your views here.

def connect(request):
    conn = pyodbc.connect('Driver={ODBC Driver for SQL Server};'
                      'Server=AB-INT-SQL;'
                      'Database=testDB;'
                      'Trusted_Connection=yes;')

    cursor = conn.cursor()
    c = cursor.execute('SELECT * FROM Artist')

    return render (request,'connect.html',{"c":c})

connect.html

{% for row in c %}
    {{ row }}
{% endfor %}
Community
  • 1
  • 1
Dev Dj
  • 169
  • 2
  • 14

1 Answers1

1

If there is only 1 SQL instance installed on the server, just do quick check with telnet HOSTNAME 1433 to confirm SQL Server accepting the communication on default port number.

if telnet doesn't work, add new rule in fire-wall (via Firewall advanced settings) with port numbers 1433 and 1434 even though have turn-off the firewall. Still doesn't work, restart SQL Service and then have a look at SQL error log for a message (following message must appear after restart time)

Server is listening on [ 'any' ipv4 1433].

Aside from this (once telnet test works), i believe, you need use the driver in connection string as "Driver={SQL Server Native Client 11.0};" which you may have already tried.

Edit: SQL Error log screenshot

enter image description here

Shekar Kola
  • 1,287
  • 9
  • 15
  • i followed your answer and it somehow made me on the right track but now it display another error : `('28000', '[28000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (18452) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (18452)')` yet its windows and SQL authentication mode. – Dev Dj Aug 19 '19 at 11:21
  • @DevDj, good to hear that, if you don't have any concern to avoid `windows authentication`, use `SQL authentication` which required you to adjust the connection string accordingly. I believe, by doing this `Trusted_Connection=no;` would allow you to use SQL authentication which lead us to add `UID` and `PWD` manually in connection string. – Shekar Kola Aug 19 '19 at 11:59
  • Yes, SQL server side it has to be `Mixed mode`, but you need change the connection string parameters as mentioned in my last comment. `Trusted_Connection=no` and add user name password – Shekar Kola Aug 20 '19 at 05:44
  • @ Shekar Kola i did as u suggest in your comment and add the UID and PWD BUT it display `login failed for user 'test' `... yet i am able to create a connection on the SSMS – Dev Dj Aug 20 '19 at 09:09
  • Can you just open SQL Error log, and see the reason for `failed login`. added screenshot for your reference: – Shekar Kola Aug 20 '19 at 16:48