SQLCMD 2014 fails to authenticate via Kerberos

Published Jan 15 2019 05:00 PM 1,359 Views
First published on MSDN on Apr 27, 2016


Understanding of the issue :

When we try to execute a query on a SQL server from a remote machine using SQLCMD.exe, the connection goes via NTLM rather than KERBEROS even though all the pre-requisites for the Kerberos authentication is fulfilled.





When using SQLCMD of version 2012 and below, the connection goes via Kerberos provided the prerequisites are fulfilled.





This behavior is seen only in SQLCMD, but when the same query is run through SQL Server Management Studio (SSMS), it goes via Kerberos irrespective of the SSMS version.







Cause:

It is a by design behavior with SQL 2014 that the SQLCMD requires SPNs with a NETBIOS name in order to go through Kerberos.



Resolution:

Create a SPN with the HOSTNAME/ NETBIOS name along with the default SPNs with Fully Qualified Domain Name (FQDN)







In case you are still facing the same issue, please reach out to CSS team.



Author: Chetan KT – SQL Server BI-ONE Developer team, Microsoft

Reviewer: Sunil Kumar B.S. Escalation Engineer, SQL Server BI-ONE Developer team, Microsoft







%3CLINGO-SUB%20id%3D%22lingo-sub-318745%22%20slang%3D%22en-US%22%3ESQLCMD%202014%20fails%20to%20authenticate%20via%20Kerberos%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318745%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3EFirst%20published%20on%20MSDN%20on%20Apr%2027%2C%202016%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CB%3E%20Understanding%20of%20the%20issue%20%3C%2FB%3E%20%3A%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20When%20we%20try%20to%20execute%20a%20query%20on%20a%20SQL%20server%20from%20a%20remote%20machine%20using%20SQLCMD.exe%2C%20the%20connection%20goes%20via%20NTLM%20rather%20than%20KERBEROS%20even%20though%20all%20the%20pre-requisites%20for%20the%20Kerberos%20authentication%20is%20fulfilled.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68410iDCD1C63ADF01034C%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20When%20using%20SQLCMD%20of%20version%202012%20and%20below%2C%20the%20connection%20goes%20via%20Kerberos%20provided%20the%20prerequisites%20are%20fulfilled.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68411i28E8D2ED4CD47895%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20This%20behavior%20is%20seen%20only%20in%20SQLCMD%2C%20but%20when%20the%20same%20query%20is%20run%20through%20SQL%20Server%20Management%20Studio%20(SSMS)%2C%20it%20goes%20via%20Kerberos%20irrespective%20of%20the%20SSMS%20version.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68412i499A15336D3E97E2%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CB%3ECause%3A%20%3C%2FB%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20It%20is%20a%20by%20design%20behavior%20with%20SQL%202014%20that%20the%20SQLCMD%20requires%20SPNs%20with%20a%20NETBIOS%20name%20in%20order%20to%20go%20through%20Kerberos.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CB%3EResolution%3A%20%3C%2FB%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20Create%20a%20SPN%20with%20the%20HOSTNAME%2F%20NETBIOS%20name%20along%20with%20the%20default%20SPNs%20with%20Fully%20Qualified%20Domain%20Name%20(FQDN)%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68413i13E070E8BBA2BC17%22%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20In%20case%20you%20are%20still%20facing%20the%20same%20issue%2C%20please%20reach%20out%20to%20CSS%20team.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3EAuthor%3A%20%3C%2FSTRONG%3E%20Chetan%20KT%20%E2%80%93%20SQL%20Server%20BI-ONE%20Developer%20team%2C%20Microsoft%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3E%20Reviewer%3A%20%3C%2FSTRONG%3E%20Sunil%20Kumar%20B.S.%20Escalation%20Engineer%2C%20SQL%20Server%20BI-ONE%20Developer%20team%2C%20Microsoft%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318745%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Apr%2027%2C%202016%20%26nbsp%3BUnderstanding%20of%20the%20issue%3AWhen%20we%20try%20to%20execute%20a%20query%20on%20a%20SQL%20server%20from%20a%20remote%20machine%20using%20SQLCMD.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318745%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Econnectivity%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESecurity%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%202014%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 05:00 PM
Updated by: