CODEWITHSUNDEEP
pythonsql-serverpyodbcfreetds
HrkBrkkl
HrkBrkkl

Reputation: 693

Not able to connect to SQL Server instance with FreeTDS ODBC

I try to connect to a microsoft sql server

I use pyodbc with FreeTDS.

My script:

import pyodbc
cnxn = pyodbc.connect(
    'DRIVER={FreeTDS};SERVER=myServerAdress;INSTANCE =myInstanceName;PORT=myPortNumber;DATABASE=myDatabasename;UID=myUserName;PWD=myPassword')

cursor = cnxn.cursor()

Server is found but I get an error:

pyodbc.ProgrammingError: ('42000', "[42000] [unixODBC][FreeTDS][SQL Server]Fehler bei der Anmeldung für den Benutzer 'myUserName'. (18456) (SQLDriverConnect)")

I think the translated error message is:

Fehler bei der Anmeldung für den Benutzer 'myUserName' = Login failed for user ‘myUserName’.

So I thought the username or the password were wrong. But connecting with the same credentials over tsql works fine.

tsql -S MyServerDSN -U myUsername -P myPassword

with

[MyServer]
host = myServername
instance = myInstanceName

works fine and I can select myDatabaseName and get results.

Upvotes: 4

Views: 3183

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

Read the FreeTDS ODBC connection attributes documentation carefully. There is no INSTANCE= attribute. It says

To specify a Microsoft SQL Server instance, use the form server\instance.

This works for me:

cnxn_str = (
    r'DRIVER=FreeTDS;'
    r'SERVER=192.168.1.128\SQLEXPRESS;'
    r'DATABASE=myDb;'
    r'UID=sa;PWD=whatever;'
)
cnxn = pyodbc.connect(cnxn_str)

Note also that you should specify an instance name or a port number, not both.

Upvotes: 3

Related Questions