4

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
HrkBrkkl
  • 613
  • 5
  • 22

1 Answers1

3

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 1
    Thanks a lot for the fast help. Especially your last sentence gave me some more understanding. Was able to connect. – HrkBrkkl Sep 04 '18 at 12:46
  • Also make sure that the database already exist. if not connect with tsql and run 1> CREATE DATABASE myDB; 2> go – Patrick Apr 05 '20 at 12:22