Skip to Content.
Sympa Menu

freetds - Re: [freetds] kerberos credentials not detected by FreeTDS

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Raila, Wayne F." <Raila.Wayne AT mgh.harvard.edu>
  • To: 'FreeTDS Development Group' <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] kerberos credentials not detected by FreeTDS
  • Date: Fri, 31 Oct 2014 20:24:54 +0000

I have confirmed that the issue was with the configuration of SQL Server SPN
in the domain. Here are some details.

There are two authorization schemes to connect to SQL Server using an active
directory account. One is Kerberos, the other is NTLM. Which authorization
scheme you get depends on the syntax used with the tsql command. When you use
the -U parameter to supply the username, then tsql uses NTLM. When you do not
supply the username, tsql uses Kerberos. Here are two examples, with a select
statement that shows the authorization scheme.

$ tsql -S ftdsdsn -U 'MYDOMAIN\myusername'
Password:
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;
2> go
auth_scheme
NTLM
(1 row affected)

$ tsql -S ftdsdsn
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;
2> go
auth_scheme
KERBEROS
(1 row affected)

Why this is important: I had assumed, since I could connect to the database
by supplying my domain username, that Kerberos was correctly configured. That
was not the case. NTLM was properly configured, but not Kerberos.

How to diagnose: I used the KRB5_TRACE setting to log kerberos activity. In
this example I show a portion of the .freetds.conf file, the tsql command
with its result, and a few lines from the log file:

$ cat .freetds.conf
...
[ftdsdsn]
host = sqlservername.mydomain.org
port = 2004
tds version = 7.1

$ KRB5_TRACE=krb5.log tsql -S lcsclientdevsql
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

$ cat krb5.log
...
[17234] 1414783635.360320: Getting credentials myusername AT MYDOMAIN.ORG ->
MSSQLSvc/sqlservername.mydomain.org:2004 AT MYDOMAIN.ORG using ccache
FILE:/tmp/krb5cc_16777216_jBDqdj

...
[17234] 1414783635.361240: Requesting tickets for
MSSQLSvc/sqlservername.mydomain.org:2004 AT MYDOMAIN.ORG, referrals on

...
[17234] 1414783635.446168: TGS request result: -1765328377/Server not found
in Kerberos database

The key bit of information here is the string
"MSSQLSvc/sqlservername.mydomain.org:2004 AT MYDOMAIN.ORG". That is the Service
Principle Name, SPN, of the SQL Server, and it must be registered in the
domain for this to work. In my case, the SPN was not in the domain. Once I
added the SPN to the domain then Kerberos worked fine.

How to configure the SPN: Microsoft has this covered at
http://msdn.microsoft.com/en-us/library/ms191153.aspx.

Why was my SPN not in the domain? And why doesn't everybody run into this
problem? The SPN maps to the Windows account that started the SQL Server
instance service. By default, SQL Server uses a built-in account to start the
service, and this build-in account has permission to add the SPN to the
domain. When SQL Server starts up, that account adds the SPN. By using the
default configuration, with a built-in account, this problem does not arise.
In my case, I was using a domain account to start the service, and the domain
account did not have permission to update the domain. When SQL Server started
up, that account attempted to register the SPN, and failed due to lack of
authorization.

This problem can be fixed by authorizing the service account to make the
domain change, or by manually adding the SPN as documented in the MS article
above. I chose the latter approach.

That is what I think I know. Never can be sure. I hope this helps someone in
the future.

Wayne


-----Original Message-----
From: FreeTDS [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Raila,
Wayne F.
Sent: Sunday, September 21, 2014 5:19 PM
To: FreeTDS Development Group
Subject: Re: [freetds] 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 <freddy77 AT gmail.com> wrote:
>
> 2014-09-16 15:31 GMT+01:00 Raila, Wayne F. <Raila.Wayne AT mgh.harvard.edu>:
>
>> 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
> 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
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds



  • Re: [freetds] kerberos credentials not detected by FreeTDS, Raila, Wayne F., 10/31/2014

Archive powered by MHonArc 2.6.24.

Top of Page