linux - 如何使用 unixODBC 和 FreeTDS 连接到 Azure？

Question

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

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

然后我输入我的密码，我就可以登录A-OK了。不幸的是，isql/osql 做同样的事情似乎要困难得多。

我的配置是这样的:

~/.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:

⚘ 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，我需要做什么？

Answer 1

问题是，如果您不提供用户名和密码，那么它将被视为可信连接。我不是 100% 是什么意思，但显然它不起作用。工作配置实际上相当简单。

[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):

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

那会很好用的。如果您使用的是 sqlalchemy，则可以像这样通过 pyodbc 进行连接:

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)

现在你的生活应该是幸福的。