Re: kerberos credentials not detected by FreeTDS

Thanks for the tip, Frediano. 

At the moment I am working on the theory that the problem lies not with the Linux client but with the Service Principle Name, SPN, of the SQL Server. Our SQL Servers do not have the requisite SPNs configured in Active Directory. The Kerberos logs on the Linux client indicate this is where the failure happens. 

Now I am waiting for the domain admins to update Active Directory. I will post a follow-up once I've confirmed this works.

Wayne


> On Sep 20, 2014, at 8:19 AM, Frediano Ziglio <[email protected]> wrote:
> 
> 2014-09-16 15:31 GMT+01:00 Raila, Wayne F. <[email protected]>:
> 
>> 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: [email protected]
>> Valid starting     Expires            Service principal
>> 09/12/14 15:51:59  09/13/14 01:51:58  krbtgt/[email protected]
>>        renew until 09/19/14 15:51:59
> Try to use
> 
> tsql -S ftdsdsn -U '' -P ''
> 
> Also try to enable TDSDUMP and TDSDUMPCONFIG and see if this helps
> 
> Frediano
> 
> 
>> 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.
> _______________________________________________
> FreeTDS mailing list
> [email protected]
> http://lists.ibiblio.org/mailman/listinfo/freetds