right, was wondering if maybe something having to do with DNS or the krb5
config generated by IPA. Here's what I'm seeing:
[root@pyodbc ~]# env KRB5_TRACE=/dev/stdout python3.6 test.py
[28353] 1534350643.20552: ccselect module realm chose cache
KEYRING:persistent:0:0 with client principal freeipa3(a)ENT2K12.DOMAIN.COM
for server principal MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@
ENT2K12.DOMAIN.COM
[28353] 1534350643.20553: Getting credentials freeipa3(a)ENT2K12.DOMAIN.COM
-> MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@ENT2K12.DOMAIN.COM using
ccache KEYRING:persistent:0:0
[28353] 1534350643.20554: Retrieving freeipa3(a)ENT2K12.DOMAIN.COM ->
MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@ENT2K12.DOMAIN.COM from
KEYRING:persistent:0:0 with result: -1765328243/Matching credential not
found
[28353] 1534350643.20555: Retrieving freeipa3(a)ENT2K12.DOMAIN.COM -> krbtgt/
ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM from KEYRING:persistent:0:0 with
result: 0/Success
[28353] 1534350643.20556: Starting with TGT for client realm:
freeipa3(a)ENT2K12.DOMAIN.COM -> krbtgt/ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM
[28353] 1534350643.20557: Requesting tickets for
MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@ENT2K12.DOMAIN.COM, referrals on
[28353] 1534350643.20558: Generated subkey for TGS request: aes256-cts/48E6
[28353] 1534350643.20559: etypes requested in TGS request: aes256-cts,
aes128-cts, aes256-sha2, aes128-sha2, des3-cbc-sha1, rc4-hmac,
camellia128-cts, camellia256-cts
[28353] 1534350643.20561: Encoding request body and padata into FAST request
[28353] 1534350643.20562: Sending request (1796 bytes) to
ENT2K12.DOMAIN.COM
[28353] 1534350643.20563: Resolving hostname
adfs.ent2k12.domain.com.
[28353] 1534350643.20564: Resolving hostname
adfs.ent2k12.domain.com.
[28353] 1534350643.20565: Initiating TCP connection to stream
192.168.2.75:88
[28353] 1534350643.20566: Sending TCP request to stream 192.168.2.75:88
[28353] 1534350643.20567: Received answer (1731 bytes) from stream
192.168.2.75:88
[28353] 1534350643.20568: Terminating TCP connection to stream
192.168.2.75:88
[28353] 1534350643.20569: Response was not from master KDC
[28353] 1534350643.20570: Decoding FAST response
[28353] 1534350643.20571: FAST reply key: aes256-cts/21CB
[28353] 1534350643.20572: TGS reply is for freeipa3(a)ENT2K12.DOMAIN.COM ->
MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@ENT2K12.DOMAIN.COM with session
key rc4-hmac/4633
[28353] 1534350643.20573: TGS request result: 0/Success
[28353] 1534350643.20574: Received creds for desired service
MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@ENT2K12.DOMAIN.COM
[28353] 1534350643.20575: Storing freeipa3(a)ENT2K12.DOMAIN.COM ->
MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@ENT2K12.DOMAIN.COM in
KEYRING:persistent:0:0
[28353] 1534350643.20577: Retrieving freeipa3(a)ENT2K12.DOMAIN.COM -> krbtgt/
ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM from KEYRING:persistent:0:0 with
result: 0/Success
[28353] 1534350643.20578: Get cred via TGT krbtgt/
ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM after requesting krbtgt/
ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM (canonicalize off)
[28353] 1534350643.20579: Generated subkey for TGS request: aes256-cts/88D1
[28353] 1534350643.20580: etypes requested in TGS request: rc4-hmac
[28353] 1534350643.20582: Encoding request body and padata into FAST request
[28353] 1534350643.20583: Sending request (1752 bytes) to
ENT2K12.DOMAIN.COM
[28353] 1534350643.20584: Resolving hostname
adfs.ent2k12.domain.com.
[28353] 1534350643.20585: Resolving hostname
adfs.ent2k12.domain.com.
[28353] 1534350643.20586: Initiating TCP connection to stream
192.168.2.75:88
[28353] 1534350643.20587: Sending TCP request to stream 192.168.2.75:88
[28353] 1534350643.20588: Received answer (1672 bytes) from stream
192.168.2.75:88
[28353] 1534350643.20589: Terminating TCP connection to stream
192.168.2.75:88
[28353] 1534350643.20590: Response was not from master KDC
[28353] 1534350643.20591: Decoding FAST response
[28353] 1534350643.20592: FAST reply key: aes256-cts/64A9
[28353] 1534350643.20593: TGS reply is for freeipa3(a)ENT2K12.DOMAIN.COM ->
krbtgt/ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM with session key rc4-hmac/C871
[28353] 1534350643.20594: Got cred; 0/Success
[28353] 1534350643.20596: Creating authenticator for
freeipa3(a)ENT2K12.DOMAIN.COM -> MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@
ENT2K12.DOMAIN.COM, seqnum 466799469, subkey rc4-hmac/AFCE, session key
rc4-hmac/4633
[28353] 1534350643.20597: Negotiating for enctypes in authenticator:
aes256-cts, aes128-cts, aes256-sha2, aes128-sha2, des3-cbc-sha1, rc4-hmac,
camellia128-cts, camellia256-cts
[28353] 1534350643.20610: ccselect module realm chose cache
KEYRING:persistent:0:0 with client principal freeipa3(a)ENT2K12.DOMAIN.COM
for server principal MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@
ENT2K12.DOMAIN.COM
[28353] 1534350643.20611: Getting credentials freeipa3(a)ENT2K12.DOMAIN.COM
-> MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@ENT2K12.DOMAIN.COM using
ccache KEYRING:persistent:0:0
[28353] 1534350643.20612: Retrieving freeipa3(a)ENT2K12.DOMAIN.COM ->
MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@ENT2K12.DOMAIN.COM from
KEYRING:persistent:0:0 with result: 0/Success
[28353] 1534350643.20614: Retrieving freeipa3(a)ENT2K12.DOMAIN.COM -> krbtgt/
ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM from KEYRING:persistent:0:0 with
result: 0/Success
[28353] 1534350643.20615: Get cred via TGT krbtgt/
ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM after requesting krbtgt/
ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM (canonicalize off)
[28353] 1534350643.20616: Generated subkey for TGS request: aes256-cts/5F07
[28353] 1534350643.20617: etypes requested in TGS request: rc4-hmac
[28353] 1534350643.20619: Encoding request body and padata into FAST request
[28353] 1534350643.20620: Sending request (1752 bytes) to
ENT2K12.DOMAIN.COM
[28353] 1534350643.20621: Resolving hostname
adfs.ent2k12.domain.com.
[28353] 1534350643.20622: Resolving hostname
adfs.ent2k12.domain.com.
[28353] 1534350643.20623: Initiating TCP connection to stream
192.168.2.75:88
[28353] 1534350643.20624: Sending TCP request to stream 192.168.2.75:88
[28353] 1534350643.20625: Received answer (1671 bytes) from stream
192.168.2.75:88
[28353] 1534350643.20626: Terminating TCP connection to stream
192.168.2.75:88
[28353] 1534350643.20627: Response was not from master KDC
[28353] 1534350643.20628: Decoding FAST response
[28353] 1534350643.20629: FAST reply key: aes256-cts/3BB5
[28353] 1534350643.20630: TGS reply is for freeipa3(a)ENT2K12.DOMAIN.COM ->
krbtgt/ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM with session key rc4-hmac/0FE4
[28353] 1534350643.20631: Got cred; 0/Success
[28353] 1534350643.20633: Creating authenticator for
freeipa3(a)ENT2K12.DOMAIN.COM -> MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@
ENT2K12.DOMAIN.COM, seqnum 673120205, subkey rc4-hmac/C59D, session key
rc4-hmac/4633
[28353] 1534350643.20634: Negotiating for enctypes in authenticator:
aes256-cts, aes128-cts, aes256-sha2, aes128-sha2, des3-cbc-sha1, rc4-hmac,
camellia128-cts, camellia256-cts
Traceback (most recent call last):
File "test.py", line 2, in <module>
cn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};Server=
sqlserver2.ent2k12.domain.com; DATABASE=testdb;Trusted_Connection=yes;')
pyodbc.Error: ('HY000', '[HY000] [unixODBC][Microsoft][ODBC Driver 13 for
SQL Server]SSPI Provider: Message stream modified (851968)
(SQLDriverConnect)')
Only ticket in the cache before running was the freeipa3 tgt:
[root@pyodbc ~]# klist
Ticket cache: KEYRING:persistent:0:0
Default principal: freeipa3(a)ENT2K12.DOMAIN.COM
Valid starting Expires Service principal
08/15/2018 12:29:56 08/15/2018 22:29:56 krbtgt/
ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM
renew until 08/16/2018 12:29:54
Thanks
Marc
On Wed, Aug 15, 2018 at 12:24 PM Alexander Bokovoy <abokovoy(a)redhat.com>
wrote:
On ke, 15 elo 2018, Marc Boorshtein via FreeIPA-users wrote:
>I've got a FreeIPA domain in a domain trust with a windows 2012r2 domain.
>SQL Server 2014 is a member of the windows domain and is configured to
>accept both windows and native authentication. From a box thats a member
>of my FreeIPA domain I'm able to get a ticket as a user from the windows
>domain and connect to the server without issue, if I use the sql server's
>fqdn.
>
>Where I run into an issue is where I create a CNAME for my sql server. I
>add an SPN to AD:
> setspn -A MSSQLSvc/sqlserver2.ent2k12.domain.com:1433
ENT2K12\sqlserverspn
>
>when i try to access the db using the CNAME I get the following error:
>cn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};Server=
>sqlserver2.ent2k12.domain.com; DATABASE=testdb;Trusted_Connection=yes;')
>pyodbc.Error: ('HY000', '[HY000] [unixODBC][Microsoft][ODBC Driver 13
for
>SQL Server]SSPI Provider: Message stream modified (851968)
>(SQLDriverConnect)')
>
>I know this error usually means that the case of the domain is off but I
>can't seem to create an SPN that kerberos likes. Here's my klist:
>Ticket cache: KEYRING:persistent:0:0
>Default principal: freeipa3(a)ENT2K12.DOMAIN.COM
>
>Valid starting Expires Service principal
>08/15/2018 11:22:16 08/15/2018 21:21:36
>MSSQLSvc/sqlserver2.ent2k12.domain.com:1433@ENT2K12.DOMAIN.COM
>renew until 08/16/2018 11:21:32
>08/15/2018 11:21:36 08/15/2018 21:21:36 krbtgt/
>ENT2K12.DOMAIN.COM(a)ENT2K12.DOMAIN.COM
>renew until 08/16/2018 11:21:32
>
>So I'm getting the ticket. not really sure where to go from here. Any
>help would be greatly appreciated.
Since in this case you have basically no involvement from FreeIPA side
at all (notice there is no FreeIPA's realm ticket in your ccache).
Can you provide a KRB5_TRACE of a pyodbc.connect() operation? Make sure
you have no tickets in the ccache other than a TGT before that.
--
/ Alexander Bokovoy
Sr. Principal Software Engineer
Security / Identity Management Engineering
Red Hat Limited, Finland