Connecting to a MS SQL server from django

[Larry Martell]

I am trying to connect to a MS SQL server from django on Linux using
Vernon Cole's django-mssql package
(https://bitbucket.org/vernondcole/django-mssql-ado-merge/src).

When I first tried to connect it failed with:

Exception Type: DatabaseError at /report/CDSEM/EventsTable/
Exception Value: Cannot create connection
to=PYRO:ado.con...@xx.xx.xx.xx:9099

I traced it through
/usr/lib/python2.6/site-packages/adodbapi/remote.py and I see it tries
to access os.environ['PROXY_PORT'], which is not defined and then it
defaults to 9099 - Is that the correct port or should I set
os.environ['PROXY_PORT']? How can I tell what port MS SQL db is
listening on? I googled this and it said the default MS SQL port is
1433. I set os.environ['PROXY_PORT'] to that and now I don't get that
error but it times out:

> /usr/lib/python2.6/site-packages/Pyro4-4.22-py2.6.egg/Pyro4/core.py(160)__call__()
-> return self.__send(self.__name, args, kwargs)
(Pdb) n
TimeoutError: TimeoutE...imeout',)

I tried opening port 1433 with iptables, but still no joy.

I can connect to the MS SQL db locally from the host it's running so I
know it's up and my credentials are correct, and I can ping the
Windows box from the Linux host I am running on, so I know there is
connectivity to it from there.

Anyone here ever get this working? Anyone have any ideas as to what my
problem is or how I can debug this further?

Thanks!
-larry

[Mark Moss]

The default port for MySQL is 3306. Have you tried that one?

------------------------------

- Mark

Try this new Instant Django Hosting

[Alex Mandel]

Check your Windows Firewall settings. You can test from linux using nmap
to port probe.
nmap -PN -p 1433 ip.of.win.machine

It is unlikely to be the linux firewall unless you're filtering all
outgoing data.

Enjoy,
Alex

[Larry Martell]

On Tuesday, January 14, 2014, Mark Moss wrote:

The default port for MySQL is 3306. Have you tried that one?

I'm not trying to connect to MySQL - I'm trying to connect to Microsoft SQL server.  

[Avraham Serour]

I would try using some kind of DB explorer first, to make sure I have the ip, port and auth right, once you are able using some other tool you can just use the same settings in your code

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY5qidVz3PcCxnCfSpJx134hL68cicMaZUCmRKRpLJ6%3DWQ%40mail.gmail.com.

For more options, visit https://groups.google.com/groups/opt_out.

[Larry Martell]

On Wed, Jan 15, 2014 at 12:09 AM, Avraham Serour <tov...@gmail.com> wrote:
> I would try using some kind of DB explorer first, to make sure I have the
> ip, port and auth right, once you are able using some other tool you can
> just use the same settings in your code

Do you know of any such beast for Linux?

> On Wed, Jan 15, 2014 at 6:46 AM, Larry Martell <larry....@gmail.com>
> wrote:
>>
>> On Tuesday, January 14, 2014, Mark Moss wrote:
>>>
>>> The default port for MySQL is 3306. Have you tried that one?
>>
>>
>>
>> I'm not trying to connect to MySQL - I'm trying to connect to Microsoft
>> SQL server.
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "Django users" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to django-users...@googlegroups.com.
>> To post to this group, send email to django...@googlegroups.com.
>> Visit this group at http://groups.google.com/group/django-users.
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/django-users/CACwCsY5qidVz3PcCxnCfSpJx134hL68cicMaZUCmRKRpLJ6%3DWQ%40mail.gmail.com.
>>
>> For more options, visit https://groups.google.com/groups/opt_out.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-users.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/django-users/CAFWa6tKSNGb6PLenqH3h9FHxMpp%3De%2Bv_W686Aq71HnjsGL-%2B%2Bw%40mail.gmail.com.

[Adnan Sadzak]

Maybe Your  MSSQL server is listening only on loopback interface. Telnet from linux box to windows server on port 1433 and check if port is open. 

You can see also this few pages, just depends on Your server version.:

http://blogs.msdn.com/b/sqlblog/archive/2009/07/17/how-to-configure-sql-server-to-listen-on-different-ports-on-different-ip-addresses.aspx

http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx

Check also FreeTDS project: http://www.freetds.org/

Cheers,

Adnan

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CACwCsY4dstOYsGYZ8coq9CX2883w%2BW35YZrzduz3URieAN%3DELw%40mail.gmail.com.

[Avraham Serour]

pycharm and jetbrains tools come with a db explorer

with a simple google search I found this [0] and [1] which have some references, you could also try [2] with wine

[0] http://stackoverflow.com/questions/399505/sql-server-gui-for-linux

[1] http://stackoverflow.com/questions/721852/developer-tools-to-directly-access-databases

[2] http://www.microsoft.com/en-us/download/details.aspx?id=8961

good luck

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAG6E59UXfaBWyk9TgJaFSgFcQ_qsxTL94Sd9yQTySeD-amjcKw%40mail.gmail.com.

[Larry Martell]

Thanks to everyone who replied. This project was put on the back
burner (yet again). When I get back to it I will be sure to try
everyone's suggestions and report back here as to what worked.

[Fred Stluka]

Larry,

+1 for Pycharm (JetBrains).

I use their DB tools to explore both MS SQL and MySQL DBs.  If
you like, I can dig up my notes on exactly how I made the
connection from PyCharm to MS SQL Server.

--Fred

---

Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.

---

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAFWa6tLZO6qT0n07PztX7Q3d3Lo-ME11cV3P-bPZQ5Ba4HOz7g%40mail.gmail.com.

[Fred Stluka]

Larry,

We tried django-mssql and gave up.  Much better luck with
django-pyodbc.  We use the avidal version of it, along with pyodbc,
FreeTDS, and unixODBC.  Works perfectly.  If you like, I can dig up
my notes on exactly how we made the connection from Django to

MS SQL Server.

--Fred

---

Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.

---

[Larry Martell]

Fred-

This project has been put on the back burner for a few weeks, but I
will be getting back to it. If you could dig up your notes on this,
that would be really great.

Thanks very much!
-larry

[Fred Stluka]

Larry,

I did it a long complicated way, but we recently had 2 new people
join our team, and refined the process for them, and it seemed to
go smoothly, so here's what they did. 

Some sections differ for Mac vs Linux, and some even for different
Linux distros, but it should all work.  I'm including all of it, even
the part about how to install Python, Django, virtualenv, etc., which
you probably already have, and MySQL, which you are not using so
you can skip. 

The following document is in Markdown format.

HHL is the name of our project.  Change it to your own project name.

Let me know how it works!



# Setting up your development environment.

## Install Python 2.7, if it's not already on your system.

### Mac

The Mac comes preinstalled with a 2.7 version of Python. Double-check the
version as shown:

    $ /usr/bin/python --version
    Python 2.7.5

If you don't get a 2.7 version of Python, or if you get a
"No such file or directory" error, install your own version of Python via
the [Homebrew][] package manager:

    $ brew install python

### Linux

If your Linux system doesn't already have a version of Python, install
one with the package manager.

For Ubuntu and Debian:

    $ sudo apt-get install python

On CentOS (and, presumably, Fedora and RedHat), see
<http://toomuchdata.com/2012/06/25/how-to-install-python-2-7-3-on-centos-6-2/>.

## Install `virtualenv`

Mac:

The version of Python that comes pre-installed on the Mac comes with
`easy_install`, so you can use that to install `virtualenv`:

    $ sudo /usr/bin/easy_install virtualenv

Linux:

You can either use your system's package manager (e.g.,
`sudo apt-get install python-virtualenv` on Ubuntu and Debian), or you can
install `virtualenv` manually, as described at
<https://pypi.python.org/pypi/virtualenv>.

## Create and activate a virtual environment

Where you put the environment is up to you; the following is just an
example:

    $ virtualenv ~/pythons/hhl

Confirm that you did, indeed, create a Python 2.7 virtual environment:

    $ ~/pythons/hhl/bin/python
    Python 2.7.5 (default, Nov 19 2013, 10:30:44)
    [GCC 4.2.1 Compatible Apple LLVM 5.0 (clang-500.2.79)] on darwin
    Type "help", "copyright", "credits" or "license" for more information.
    >>>

Activate that environment:

    $ source ~/pythons/hhl/bin/activate

## Install MySQL

Mac:

Again, it's easiest to use [Homebrew][]:

    $ brew install mysql

After installing MySQL, homebrew will display suggestions on how to get MySQL
set up. Follow those instructions.

Linux:

You'll need both MySQL and the MySQL development files. Without the latter,
you won't be able to build the MySQL Python driver.

For Ubuntu and Debian:

    $ sudo apt-get install mysql-server libmysqlclient-dev

On CentOS (and, presumably, Fedora and RedHat):

    $ sudo yum -y install mysql-devel

## Install the Python MySQL driver

    $ pip install MySQL-python

## Install non-Python ODBC libraries and tools

### Install unixODBC

Mac:

    $ brew install unixodbc

The `odbcinst.ini` file ends up in `/usr/local/etc/`.

Linux:

    $ sudo apt-get install unixodbc-dev  # Ubuntu and Debian
    $ sudo yum -y install unixODBC-devel # CentOS, RedHat, and Fedora

The `odbcinst.ini` file ends up in `/etc/`.

### Install FreeTDS

Mac:

    $ brew install freetds --with-unixodbc

Note that the `freetds.conf` file ends up in `/usr/local/etc/freetds.conf`.
Installing this Homebrew package also provides the `tsql` command, which
can be useful for testing.

Linux:

This package is typically available via the Linux package manager.

For Ubuntu and Debian:

    $ sudo apt-get install freetds-dev tdsodbc

The `freetds.conf` file ends up in `/etc/freetds/`. If you need the
`tsql` program (useful for testing), you also need the `freetds-bin` package:

    $ sudo apt-get install freetds-bin

On CentOS (and, presumably, Fedora and RedHat):

    $ sudo yum -y install freetds-devel

The `freetds.conf` file ends up in `/etc/`.

### Configure unixODBC and FreeTDS

This blog post provides a good overview of the process:
<http://lbolla.info/blog/2013/08/28/python-and-odbc>

(NOTE: Those instructions talk about setting up a `$HOME/.odbcinst.ini`.
That file does not appear to be necessary.)

Find your `odbcinst.ini` file (see the section, above, on installing
`unixODBC`), and make its contents look like this.

    [FreeTDS]
    Description             = FreeTDS MSSQL
    # If you're on Linux, uncomment the following settings.
    #Driver                  = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    #Driver64                = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
    #Setup                   = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    #Setup64                 = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
    # Mac settings
    Driver                  = /usr/local/lib/libtdsodbc.so
    Driver64                = /usr/local/lib/libtdsodbc.so
    UsageCount              = 2
    CPTimeout               =
    CPTimeToLive            =
    DisableGetFunctions     =
    DontDLCLose             =
    ExFetchMapping          =
    Threading               =
    FakeUnicode             =
    IconvEncoding           =
    Trace                   =
    TraceFile               =
    TraceLibrary            =

Next, add this section to your `freetds.conf` file (see the section,
above, on installing FreeTDS, for the location on your operation system):

    :::ini
    [_your_odbc_dsn_name_]
        host = _your_db_host_name_or_ip_
        port = _your_db_port_number_
        tds version = 7.0

## Create the requirements.txt file, containing:

    Django==1.4.2
    MySQL-python==1.2.4
    pyodbc==3.0.7
    -e git+https://github.com/avidal/django-pyodbc.git@a329ba8e7ea9dfcffce18139863c977b1218c09b#egg=sql_server.pyodbc-dev

## Install all the packages

    $ pip install -r requirements.txt

### Mac OS X note

The `pyodbc` package that _pip_ installs uses iODBC by default. We want it
to use `unixODBC`, instead, for a couple reasons.

* First, there are numerous sites on the Internet that make claims such as
  "iODBC is a slightly less desirable ODBC manager than unixODBC"
  (http://www.cerebralmastication.com/2013/01/installing-debugging-odbc-on-mac-os-x/).
  More people _seem_ to have better luck with `unixODBC`.
* Second, we're using `unixODBC` on Linux, and the configuration is already
  complicated enough. So, why not just use the same approach on both platforms?

To get `pyodbc` to use `unixODBC` on the mac, you have to build it from source,
and you have to hack the `setup.py`. Here's how to do it.

Create the pyodbc1.patch file containing:

    *** setup.py.orig    2013-12-19 21:49:10.000000000 -0500
    --- setup.py    2013-12-19 21:35:21.000000000 -0500
    ***************
    *** 145,151 ****
     
          elif sys.platform == 'darwin':
    !         # OS/X now ships with iODBC.
    !         settings['libraries'].append('iodbc')
     
              # Apple has decided they won't maintain the iODBC system in OS/X and has added deprecation warnings in 10.8.
              # For now target 10.7 to eliminate the warnings.
    --- 145,151 ----
     
          elif sys.platform == 'darwin':
    !         # OS/X now ships with iODBC. We need unixodbc, however.
    !         settings['libraries'].append('odbc')
     
              # Apple has decided they won't maintain the iODBC system in OS/X and has added deprecation warnings in 10.8.
              # For now target 10.7 to eliminate the warnings.

Do these steps:

    $ . ~/pythons/hhl/bin/activate
    $ cd /tmp
    $ git clone https://code.google.com/p/pyodbc
    $ cd pyodbc
    $ patch < pyodbc1.patch
    $ pip uninstall pyodbc
    $ python setup.py install

## Install the SQL Server ODBC backend

`pip install` does not completely install the SQL Server ODBC backend, so
there's one more manual step. You must manually copy some software within
your Python virtual environment.

    $ cd ~/pythons/hhl/lib/python2.7/site-packages
    $ cp -r ../../../src/sql-server.pyodbc/sql_server django/db/backends

## Set up your Django environment, by adding this to settings.py:

    DATABASES = {
        'cf': {
            'ENGINE'    : 'django.db.backends.sql_server.pyodbc',
            'NAME'      : '_your_db_name_',
            'USER'      : '_your_db_username_',
            'PASSWORD'  : '_your_db_password_',
            # ODBC DSN defined in /etc/freetds.conf
            'HOST'      : '_your_odbc_dsn_name_',
            # Ignored for Windows; Required for Linux
            'OPTIONS'   : {
                # ODBC driver name in /etc/odbcinst.ini
                'driver': 'FreeTDS',
            },
        },
    }

## Verify that Python can get to the SQL Server database

At this point, you should be able to open a connection to the HHL test
SQL Server database as follows:

    $ python
    Python 2.7.5+ (default, Sep 19 2013, 13:48:49)
    [GCC 4.8.1] on linux2
    Type "help", "copyright", "credits" or "license" for more information.
    >>> import pyodbc
    >>> conn = pyodbc.connect('DRIVER=FreeTDS;'
                              'SERVER=_your_db_host_name_or_ip_;'
                              'PORT=_your_db_port_number_;'
                              'DATABASE=_your_db_name_;'
                              'UID=_your_db_username_;'
                              'PWD=_your_db_password_')
    >>> conn
    <pyodbc.Connection object at 0x7f6df62d5f80>


## Initialize your database

    $ python manage.py syncdb

## Fire it up!

    $ python manage.py runserver

[Homebrew]: http://brew.sh/



Good luck!

--Fred

---

Fred Stluka -- mailto:fr...@bristle.com -- http://bristle.com/~fred/
Bristle Software, Inc -- http://bristle.com -- Glad to be of service!
Open Source: Without walls and fences, we need no Windows or Gates.

---

[Larry Martell]

Thank you very much Fred. When I get back to this I will let you know
how it went.

[Larry Martell]

Fred-

I finally got around to trying this. It all worked perfectly until I
tried to access the MSSQL server from django. That fails with:

mod_wsgi (pid=27541): Exception occurred processing WSGI script
'/usr/local/motor/motor/wsgi.py'.
Traceback (most recent call last):
File "/usr/local/lib/python2.7/site-packages/django/core/handlers/wsgi.py",
line 243, in __call__
signals.request_started.send(sender=self.__class__)
File "/usr/local/lib/python2.7/site-packages/django/dispatch/dispatcher.py",
line 170, in send
response = receiver(signal=self, sender=sender, **named)
File "/usr/local/lib/python2.7/site-packages/django/db/__init__.py",
line 58, in reset_queries
for conn in connections.all():
File "/usr/local/lib/python2.7/site-packages/django/db/utils.py",
line 105, in all
return [self[alias] for alias in self]
File "/usr/local/lib/python2.7/site-packages/django/db/utils.py",
line 93, in __getitem__
backend = load_backend(db['ENGINE'])
File "/usr/local/lib/python2.7/site-packages/django/db/utils.py",
line 45, in load_backend
raise ImproperlyConfigured(error_msg)
ImproperlyConfigured: 'django.db.backends.sql_server.pyodbc' isn't an
available database backend.
Try using 'django.db.backends.XXX', where XXX is one of:
u'mysql', u'oracle', u'postgresql_psycopg2', u'sqlite3'
Error was: No module named sql_server.pyodbc.base

Any ideas?

> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-users.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/django-users/52DCB372.3090403%40bristle.com.