如何使用unixODBC和FreeTDS连接到Azure?

问题描述：

我在CentOS上，可以使用以下命令登录到我的数据库:

I'm on CentOS and I can log into my database using the following:

TDSVER=7.2 tsql -H example.database.windows.net -U myname -D MyDataBase -p 1433

然后我输入密码，然后可以登录A-OK.不幸的是，isql/osql似乎在做同一件事时要困难得多.

Then I put in my password and I can log in A-OK. Unfortunately isql/osql seem to have much more difficulty doing the same thing.

我的配置如下:

〜/.odbc.ini

[AwesomeDatabase]
Description = Azure Awesome Database
Trace = off
Server = example.database.windows.net
Database = AwesomeDatabase
UID = wayne@example
PWD = mypassword
Port = 1433
TDS Version = 7.2
ForceTrace      = off
Encrypt         = yes
#Driver         = FreeTDS
Driver          = /usr/lib64/libtdsodbc.so
Ansi            = True
client charset  = utf-8

使用isql:

⚘ isql -v CDH
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[ISQL]ERROR: Could not SQLConnect

好吧，那很不幸.尝试osql:

Well, that's unfortunate. Trying osql:

⚘ osql -S AwesomeDatabase -U wayne@example -P mypassword  # I've tried wayne instead of wayne@example, neither works
checking shared odbc libraries linked to isql for default directories...
strings: '': No such file
        trying /tmp/sql ... no
        trying /tmp/sql ... no
        trying /etc ... OK
checking odbc.ini files
        reading /home/me/.odbc.ini
[AwesomeDatabase] found in /home/me/.odbc.ini
found this section:
        [AwesomeDatabase]
        Description = Azure Awesome Database
        Trace = off
        Server = example.database.windows.net
        Database = AwesomeDatabase
        UID = wayne@example
        PWD = mypassword
        Port = 1433
        TDS Version = 7.2
        ForceTrace      = off
        Encrypt         = yes
        #Driver         = FreeTDS
        Driver          = /usr/lib64/libtdsodbc.so
        Ansi            = True
        client charset  = utf-8
looking for driver for DSN [AwesomeDatabase] in /home/me/.odbc.ini
  found driver line: "  Driver          = /usr/lib64/libtdsodbc.so"
  driver "/usr/lib64/libtdsodbc.so" found for [AwesomeDatabase] in .odbc.ini
found driver named "/usr/lib64/libtdsodbc.so"
/usr/lib64/libtdsodbc.so is an executable file
"Server" found, not using freetds.conf
Server is "example.database.windows.net"

Configuration looks OK.  Connection details:

                   DSN: AwesomeDatabase
              odbc.ini: /home/me/.odbc.ini
                Driver: /usr/lib64/libtdsodbc.so
       Server hostname: example.database.windows.net
               Address: 191.235.192.43

Attempting connection as wayne ...
+ isql CDH wayne mypassword -v
[S1000][unixODBC][FreeTDS][SQL Server]Unable to connect to data source
[01000][unixODBC][FreeTDS][SQL Server]Unexpected EOF from the server
[01000][unixODBC][FreeTDS][SQL Server]Adaptive Server connection failed
[ISQL]ERROR: Could not SQLConnect
net.c:202:FAILED Connecting to 191.235.192.43 port 1433 (TDS version 4.2)

在Linux上使用unixODBC连接到Azure需要做什么?

What do I need to do to connect to Azure using unixODBC on Linux?

答

问题是，如果您不提供用户名和密码，则会将其视为可信连接.我不是100％这意味着什么，但是显然这是行不通的.有效的配置实际上非常简单.

The problem is that if you don't provide a username and password then it's treated as a Trusted Connection. I'm not 100% what that means, but obviously it doesn't work. A working config is actually fairly straightforward.

[AwesomeDatabase]
Description = Azure Awesome Database
Server = example.database.windows.net
Database = AwesomeDatabase
Port = 1433
# Note the underscore
TDS_Version = 7.2
# Or `FreeTDS if you want to put some settings in your
# odbcinst.ini file
Driver          = /usr/lib64/libtdsodbc.so

现在，您必须在isql的命令行上提供密码(不确定是否可以通过交互式方式获取它.始终为xargs):

Now you must provide your password on the command line for isql (not sure if there's a way to get it interactively. There's always xargs):

$ isql AwesomeDatabase wayne@example mypassword  # "wayne" without @example seems to work, too.

那将正常工作.如果您使用的是sqlalchemy，则可以像这样通过pyodbc连接:

And that will work just fine. If you're using sqlalchemy you can connect via pyodbc like so:

import sqlalchemy
from urllib.parse import quote

engine = sa.create_engine(
    'mssql+pyodbc://{user}:{password}@AwesomeDatabase'.format(
        user=quote('wayne@example'),
        password=quote('mypassword'),
    ),
    legacy_schema_aliasing=False,
)

for row in engine.execute(sa.text('select current_timestamp')):
    print(*row)

或者，直接使用pyodbc:

import pyodbc

# Without the Uid/Pwd it thinks it's a Trusted Connection
# again
conn = pyodbc.connect('DSN=AwesomeDatabase;Uid=wayne@example;Pwd=mypassword;')
cursor = conn.cursor()
cursor.execute('select current_timestamp');
for row in cursor.fetchall():
    print(*row)

现在生活应该为你幸福.

And life should now be happy for you.

如何使用unixODBC和FreeTDS连接到Azure?

相关推荐