Connecting to an MS SQL server ?

[TkNeo]

Hi,

This is my first encounter with sqlalchemy. I am trying to connect to
an MS SQL server 2000 that is not on local host. I want to connect
using Integrated Security and not use a specific username and
password. Can anyone tell me the format of the connection string ?

Thanks
TK

[Yannick Gingras]

TkNeo <taru...@gmail.com> writes:

> Hi,

Hello Tarun,

I don't know about Integrated Security but we use alchemy to connect
to a MSSQL from a GNU/Linux box and it works really well. We use Unix
ODBC with TDS with the DSN registered with the local ODBC.

Take a look at

http://www.lucasmanual.com/mywiki/TurboGears#head-4a47fe38beac67d9d03e49c4975cfc3dd165fa31

My obdb.ini looks like

[JDED]
Driver = TDS
Trace = No
Server = 192.168.33.53
Port = 1433

and my alchemy connection string is

mssql://user:pass@/?dsn=JDED&scope_identity=1

--
Yannick Gingras

[Rick Morrison]

The DSN method should work with Integrated Security as well. Here's a short writeup of the DSN configuration:

   http://support.microsoft.com/kb/176378

[TkNeo]

I don't want to use the DSN method. The DSN would not be configured at
some client machines etc etc. ..

TK

[Rick Morrison]

You really should reconsider. DSN is a much easier setup method than trying to specify a myriad of ODBC options in a connection string. There's a GUI user interface for setting up DSN's etc. It's the simpler and better supported method.

If you really are dead-set against it, you'll need to use the 'odbc_options' keyword in the dburi or as a keyword argument to create_engine() and specify the ODBC connection options as a string.
For the details of the ODBC connection string contents, you'll need to consult the ODBC documentation.

[Olivier Thiery]

The following works for me :

mssql://:@<host>/<catalog>

It seems to use automatically SSPI auth if you don't specify any user and password.

Olivier

2008/5/15, TkNeo <taru...@gmail.com>:

[Lukasz Szybalski]

Not sure what platform you are using but on linux I use:
e = sqlalchemy.create_engine("mssql://user:pass@hostname:1433/database?driver=TDS&odbc_options='TDS_Version=8.0'")
but you need sa 0.4.6.

on windows you can use:
e = sqlalchemy.create_engine('mssql://user:pass@hostname:1433/database')

Lucas

[TkNeo]

I get the following error. After this i tried installing pymssql which
requires a minimum of python 2.4 and all i have is python 2.3

Any way out for us python 2.3 users ?


db = create_engine('mssql://wscmsql/ws market datasql.db')
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\__init__.py", line 160, in create_engine
return strategy.create(*args, **kwargs)
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\strategies.py", line 62, in create
dbapi = dialect_cls.dbapi(**dbapi_args)
File "c:\python25\lib\site-packages\SQLAlchemy-0.4.5-py2.5.egg
\sqlalchemy\databases\mssql.py", line 452, in dbapi
raise ImportError('No DBAPI module detected for MSSQL - please
install pyodbc, pymssql, or adodbapi')
ImportError: No DBAPI module detected for MSSQL - please install
pyodbc, pymssql, or adodbapi

On May 15, 9:34 pm, "Lukasz Szybalski" <szybal...@gmail.com> wrote:
> On Thu, May 15, 2008 at 12:51 PM, Yannick Gingras <yging...@ygingras.net> wrote:

>
> >TkNeo<tarun....@gmail.com> writes:
>
> >> Hi,
>
> > Hello Tarun,
>
> >> This is my first encounter with sqlalchemy. I am trying to connect to
> >> an MS SQL server 2000 that is not on local host. I want to connect
> >> using Integrated Security and not use a specific username and
> >> password. Can anyone tell me the format of the connection string ?
>
> > I don't know about Integrated Security but we use alchemy to connect
> > to a MSSQL from a GNU/Linux box and it works really well. We use Unix
> > ODBC with TDS with the DSN registered with the local ODBC.
>
> > Take a look at
>

> >http://www.lucasmanual.com/mywiki/TurboGears#head-4a47fe38beac67d9d03...

[Michael Bayer]

On May 22, 2008, at 11:25 AM, TkNeo wrote:

>
> I get the following error. After this i tried installing pymssql which
> requires a minimum of python 2.4 and all i have is python 2.3
>
> Any way out for us python 2.3 users ?

why cant you get onto py2.5 ?

[TkNeo]

manager. you know how it is...

i waste a lot of time , as you can see, because of being stuck to 2.3
and half the libraries out there are for 2.4 onwards. I think one day
they will realize this and let me upgrade.


so there is no way out for us 2.3 users...

[Kipb]

TkNeo wrote:
> I am trying to connect to an MS SQL server 2000 ...

> using Integrated Security and not use a specific username and
> password. Can anyone tell me the format of the connection string ?

...
> I tried installing pymssql which requires a minimum of python 2.4
> and all I have is python 2.3. Any way out for us python 2.3 users ?

Even if you could get PyMssql to work, you'd still be stuck since it
doesn't support Integrated Security/Trusted Connection.
Instead, you could try PyOdbc. The current version requires
Python 2.4 in order to support Decimal types, however.
The oldest version listed on http://pyodbc.sourceforge.net/ (from
2006)
is still for Python 2.4.

http://adodbapi.sourceforge.net/ says it works on Python 2.3.
I don't know if it will support Integrated Security.

[Lukasz Szybalski]

On Thu, May 22, 2008 at 1:20 PM, TkNeo <taru...@gmail.com> wrote:
>
> manager. you know how it is...
>
> i waste a lot of time , as you can see, because of being stuck to 2.3
> and half the libraries out there are for 2.4 onwards. I think one day
> they will realize this and let me upgrade.
>
>
> so there is no way out for us 2.3 users...
>

are u on linux?
use virtual environment in your username?
http://www.lucasmanual.com/mywiki/TurboGears#head-36fb4094da01b8c28e8bdca803c0f05774eb13b8

Lucas

>
>
>
>
> On May 22, 10:38 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> On May 22, 2008, at 11:25 AM,TkNeowrote:
>>
>>
>>
>> > I get the following error. After this i tried installing pymssql which
>> > requires a minimum of python 2.4 and all i have is python 2.3
>>
>> > Any way out for us python 2.3 users ?
>>
>> why cant you get onto py2.5 ?
> >
>

--
Automotive Recall Database. Cars, Trucks, etc.
http://www.lucasmanual.com/recall/
TurboGears Manual-Howto
http://lucasmanual.com/pdf/TurboGears-Manual-Howto.pdf