Raila, Wayne F.
2014-09-16 14:31:17 UTC
I am having trouble getting FreeTDS to pick up my kerberos credentials when connecting to MS SQL Server. Client is a VirtualBox running CentOS. I have joined my virtual machine to the Windows domain, and I have logged in to the virtual machine with a domain account. I am able to connect to the sql server using my windows credentials, provided I specify username and password.
What I am not able to do is have FreeTDS detect my current kerberos credentials and use those automatically. I am also not able to get FreeTDS to use a kerberos context that was loaded from a keytab file.
I am able to connect using tsql as long as I supply the username. Using this command, I am prompted for my password, after which the connection succeeds.
tsql -S ftdsdsn -U 'MYDOMAIN\myusername'
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
I would like to connect using just the dsn name, and have FreeTDS detect the existing kerberos credentials. When I try this I get Error 20002 (severity 9) Adaptive Server connection failed.
tsql -S ftdsdsn
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
I can verify credentials using klist.
klist
Ticket cache: FILE:/tmp/krb5cc_16777216_A8R0lC
Default principal: myusername at MYDOMAIN.ORG
Valid starting Expires Service principal
09/12/14 15:51:59 09/13/14 01:51:58 krbtgt/MYDOMAIN.ORG at MYDOMAIN.ORG
renew until 09/19/14 15:51:59
I have also tried using kinit to load a user context from a keytab file. kinit succeeds, and klist shows the default principal, but tsql fails in same way.
Ultimately I will be using kinit and a keytab file to provide credentials in pyodbc, but I presume I should get tsql working before I delve into pyodbc.
Because I am able to login and execute queries, I believe the configuration of FreeTDS and unixODBC is correct as far as parameters for the SQL Server are concerned. The problem appears to be that tsql is not finding or sending the credentials to sql server.
I've really hammered on this a lot. I could use some help.
Wayne Raila
MGH Lab of Computer Science
Configuration info below.
CentOS release 6.5 (Final) (i686)
unixODBC-2.2.14-12.el6_3.i686
unixODBC-devel-2.2.14-12.el6_3.i686
freetds-0.91-2.el6.i686
krb5-libs-1.10.3-15.el6_5.1.i686
pam_krb5-2.3.11-9.el6.i686
krb5-devel-1.10.3-15.el6_5.1.i686
krb5-workstation-1.10.3-15.el6_5.1.i686
samba-common-3.6.9-169.el6_5.i686
samba-client-3.6.9-169.el6_5.i686
samba-winbind-clients-3.6.9-169.el6_5.i686
samba-3.6.9-169.el6_5.i686
samba-winbind-3.6.9-169.el6_5.i686
tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v0.91
freetds.conf directory: /etc
MS db-lib source compatibility: yes
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: 4.2
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: yes
/etc/odbcinst.ini
[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log
[FreeTDS]
Description = ODBC for SQL Server
Driver = /usr/lib/libtdsodbc.so.0
Setup = /usr/lib/libtdsS.so
FileUsage = 1
TDS Version = 7.1
/etc/odbc.ini
[odbcdsn]
Driver = FreeTDS
Servername = ftdsdsn
TDS_Version = 7.1
Trusted_Connection = Yes
~/.freetds.conf
[global]
tds version = 8.0
dump file = /tmp/freetds.log
text size = 64512
[ftdsdsn]
host = mysqlserver.mydomain.org
port = 1433
tds version = 7.1
a portion of tdsdumpconfig
config.c:301:Success: [ftdsdsn] defined in /home/MYDOMAIN/myusername/.freetds.conf.
config.c:224:Final connection parameters:
config.c:225: server_name = ftdsdsn
config.c:226: server_host_name = mysqlserver.mydomain.org
config.c:227: ip_addr = xxx.xxx.xxx.x
config.c:228: instance_name =
config.c:229: port = 1433
config.c:230: major_version = 7
config.c:231: minor_version = 1
config.c:232: block_size = 0
config.c:233: language = us_english
config.c:234: server_charset = iso_1
config.c:235: connect_timeout = 0
config.c:236: client_host_name = xxxx.xxx.xxx.xxx
config.c:237: client_charset = UTF-8
config.c:238: app_name = TSQL
config.c:239: user_name =
config.c:242: library = TDS-Library
config.c:243: bulk_copy = 0
config.c:244: suppress_language = 0
config.c:245: encrypt level = 0
config.c:246: query_timeout = 0
config.c:249: database =
config.c:250: dump_file = /tmp/freetds.log
config.c:251: debug_flags = 0
config.c:252: text_size = 64512
config.c:253: broken_dates = 0
config.c:254: emul_little_endian = 0
config.c:255: server_realm_name =
Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
The information in this e-mail is intended only for the person to whom it is
addressed. If you believe this e-mail was sent to you in error and the e-mail
contains patient information, please contact the Partners Compliance HelpLine at
http://www.partners.org/complianceline . If the e-mail was sent to you in error
but does not contain patient information, please contact the sender and properly
dispose of the e-mail.
What I am not able to do is have FreeTDS detect my current kerberos credentials and use those automatically. I am also not able to get FreeTDS to use a kerberos context that was loaded from a keytab file.
I am able to connect using tsql as long as I supply the username. Using this command, I am prompted for my password, after which the connection succeeds.
tsql -S ftdsdsn -U 'MYDOMAIN\myusername'
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1>
I would like to connect using just the dsn name, and have FreeTDS detect the existing kerberos credentials. When I try this I get Error 20002 (severity 9) Adaptive Server connection failed.
tsql -S ftdsdsn
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server
I can verify credentials using klist.
klist
Ticket cache: FILE:/tmp/krb5cc_16777216_A8R0lC
Default principal: myusername at MYDOMAIN.ORG
Valid starting Expires Service principal
09/12/14 15:51:59 09/13/14 01:51:58 krbtgt/MYDOMAIN.ORG at MYDOMAIN.ORG
renew until 09/19/14 15:51:59
I have also tried using kinit to load a user context from a keytab file. kinit succeeds, and klist shows the default principal, but tsql fails in same way.
Ultimately I will be using kinit and a keytab file to provide credentials in pyodbc, but I presume I should get tsql working before I delve into pyodbc.
Because I am able to login and execute queries, I believe the configuration of FreeTDS and unixODBC is correct as far as parameters for the SQL Server are concerned. The problem appears to be that tsql is not finding or sending the credentials to sql server.
I've really hammered on this a lot. I could use some help.
Wayne Raila
MGH Lab of Computer Science
Configuration info below.
CentOS release 6.5 (Final) (i686)
unixODBC-2.2.14-12.el6_3.i686
unixODBC-devel-2.2.14-12.el6_3.i686
freetds-0.91-2.el6.i686
krb5-libs-1.10.3-15.el6_5.1.i686
pam_krb5-2.3.11-9.el6.i686
krb5-devel-1.10.3-15.el6_5.1.i686
krb5-workstation-1.10.3-15.el6_5.1.i686
samba-common-3.6.9-169.el6_5.i686
samba-client-3.6.9-169.el6_5.i686
samba-winbind-clients-3.6.9-169.el6_5.i686
samba-3.6.9-169.el6_5.i686
samba-winbind-3.6.9-169.el6_5.i686
tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v0.91
freetds.conf directory: /etc
MS db-lib source compatibility: yes
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: 4.2
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: yes
/etc/odbcinst.ini
[ODBC]
Trace = Yes
TraceFile = /tmp/odbc.log
[FreeTDS]
Description = ODBC for SQL Server
Driver = /usr/lib/libtdsodbc.so.0
Setup = /usr/lib/libtdsS.so
FileUsage = 1
TDS Version = 7.1
/etc/odbc.ini
[odbcdsn]
Driver = FreeTDS
Servername = ftdsdsn
TDS_Version = 7.1
Trusted_Connection = Yes
~/.freetds.conf
[global]
tds version = 8.0
dump file = /tmp/freetds.log
text size = 64512
[ftdsdsn]
host = mysqlserver.mydomain.org
port = 1433
tds version = 7.1
a portion of tdsdumpconfig
config.c:301:Success: [ftdsdsn] defined in /home/MYDOMAIN/myusername/.freetds.conf.
config.c:224:Final connection parameters:
config.c:225: server_name = ftdsdsn
config.c:226: server_host_name = mysqlserver.mydomain.org
config.c:227: ip_addr = xxx.xxx.xxx.x
config.c:228: instance_name =
config.c:229: port = 1433
config.c:230: major_version = 7
config.c:231: minor_version = 1
config.c:232: block_size = 0
config.c:233: language = us_english
config.c:234: server_charset = iso_1
config.c:235: connect_timeout = 0
config.c:236: client_host_name = xxxx.xxx.xxx.xxx
config.c:237: client_charset = UTF-8
config.c:238: app_name = TSQL
config.c:239: user_name =
config.c:242: library = TDS-Library
config.c:243: bulk_copy = 0
config.c:244: suppress_language = 0
config.c:245: encrypt level = 0
config.c:246: query_timeout = 0
config.c:249: database =
config.c:250: dump_file = /tmp/freetds.log
config.c:251: debug_flags = 0
config.c:252: text_size = 64512
config.c:253: broken_dates = 0
config.c:254: emul_little_endian = 0
config.c:255: server_realm_name =
Microsoft SQL Server 2005 - 9.00.5000.00 (X64)
Dec 10 2010 10:38:40
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
The information in this e-mail is intended only for the person to whom it is
addressed. If you believe this e-mail was sent to you in error and the e-mail
contains patient information, please contact the Partners Compliance HelpLine at
http://www.partners.org/complianceline . If the e-mail was sent to you in error
but does not contain patient information, please contact the sender and properly
dispose of the e-mail.