WilliamRen / django-pyodbc

Automatically exported from code.google.com/p/django-pyodbc
BSD 3-Clause "New" or "Revised" License
0 stars 0 forks source link

Database Connection Fails Depending on connstr Order #131

Open GoogleCodeExporter opened 8 years ago

GoogleCodeExporter commented 8 years ago
Hello, I submitted a ticket on the github repository but just noticed this is 
the official one.  Here's the link to it (better formatting).  
https://github.com/schmidsi/django-pyodbc/issues/1

Hello, I was recently troubleshooting an issue with one of my applications. I 
had to connect to a MS SQL DB with a specific LDAP user. Until this point, I 
had been connecting without any issues using the normal integrated security 
string.

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'my_database',
        'USER': '',
        'PASSWORD': '',
        'HOST': 'server\instance',
        'PORT': '',
        'OPTIONS': {
            'driver': 'SQL Server',
            'MARS_Connection': True,
            'use_legacy_datetime': True,
        }
    }
}

However, whenever I try connecting as another LDAP user, the connection fails. 
This can be achieved in pyodbc, but the order is important. For example, the 
code below works.

import pyodbc

DRIVER = 'DRIVER={SQL Server};'
UID = 'UID=domain\user;'
PWD = 'PWD=mypassword;'
AUTHENTICATION = 'LDAP;'

conn = pyodbc.connect(DRIVER + SERVER + DATABASE + AUTHENTICATION + UID + PWD)
cursor = conn.cursor()

Changing these settings to the order that connstr puts them, returns an error.

import pyodbc

DRIVER = 'DRIVER={SQL Server};'
UID = 'UID=domain\user;'
PWD = 'PWD=mypassword;'
AUTHENTICATION = 'LDAP;'

conn = pyodbc.connect(DRIVER + SERVER + UID + PWD + DATABASE + AUTHENTICATION)
cursor = conn.cursor()

The config below is the one I'm using to try connecting as a different user.

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'my_database',
        'USER': 'domain\user',
        'PASSWORD': 'mypassword',
        'HOST': 'server\instance',
        'PORT': '',
        'OPTIONS': {
            'driver': 'SQL Server',
            'MARS_Connection': True,
            'use_legacy_datetime': True,
            'extra_params': 'LDAP;',
        }
    }
}

The edit below will allow you to connect to as another LDAP user.

#sql_server/pyodbc/base.py
cstr_parts = []
        if dsn:
            cstr_parts.append('DSN=%s' % dsn)
        else:
            # Only append DRIVER if DATABASE_ODBC_DSN hasn't been set
            cstr_parts.append('DRIVER={%s}' % driver)
            if ms_drivers.match(driver) or driver == 'FreeTDS' and \
                options.get('host_is_server', False):
                if port:
                    host += ';PORT=%s' % port
                cstr_parts.append('SERVER=%s' % host)
            else:
                cstr_parts.append('SERVERNAME=%s' % host)

        cstr_parts.append('DATABASE=%s' % database)  # Moved up

        if options.get('auth', None): # Added the auth option
            cstr_parts.append(options['auth'])

        if user:
            cstr_parts.append('UID=%s;PWD=%s' % (user, password))
        else:
            if ms_drivers.match(driver):
                cstr_parts.append('Trusted_Connection=yes')
            else:
                cstr_parts.append('Integrated Security=SSPI')

        if self.supports_mars:
            cstr_parts.append('MARS_Connection=yes')

        if options.get('extra_params', None):
            cstr_parts.append(options['extra_params'])

Original issue reported on code.google.com by chirinos...@gmail.com on 14 Mar 2014 at 10:54