January 22, 2016 at 12:20 pm
We are having an issue on multiple servers where running the below command using the 2014 version of sqlcmd.exe returns NTLM but using an earlier version returns Kerberos even when using the -E switch
sqlcmd -E -S [SERVERNAME] -Q "SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id=@@SPID"
On my laptop (which has multiple versions installed) If I call the executable directly from the 2012 directory:
"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd"
The Result is Kerberos
but from the 2014 directory:
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\sqlcmd"
The result is NTLM
This isn't a double hop issue since it is just a straight select and we have already checked SPNs on the server.
Anyone seen this before or have any ideas where else to look?
January 25, 2016 at 1:56 pm
Still no luck with this. We had to deploy the executables for SQLCMD 11 to multiple servers to fix the error.
Can somebody just try to run the query and see if they get Kerberos or NTLM?
January 26, 2016 at 2:45 am
In your scenario is SERVERNAME a remote machine from where you are running SqlCmd or are you testing this by running SqlCmd on the same server where the database instance resides?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 26, 2016 at 1:57 pm
Orlando Colamatteo (1/26/2016)
In your scenario is SERVERNAME a remote machine from where you are running SqlCmd or are you testing this by running SqlCmd on the same server where the database instance resides?
I am running SqlCmd from the machine that I am connected to (local or RDP) and [SERVERNAME] is a different/remote host.
I have tried replacing [SERVERNAME] with hosts that run different versions of SQL (2008/2012) from my 2014 box and the result is always the same.
Also I have tried running SqlCmd from multiple 2014 installs and I get NTLM every time. Yesterday, I upgraded my laptop to the latest SP to see if that helped but the version of SqlCmd did not change.
Thanks for the reply!
January 26, 2016 at 2:34 pm
Thanks for posting back. I wanted to rule out a local connection attempt because that will always use NTLM.
There are specialized tools to debug Kerberos issues and sometimes it's a needle-in-a-haystack. I wish I had more to offer in the way of suggestions but there are too many conditions and variables involved where Kerberos can stop working to delve into in a forum setting.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 27, 2016 at 7:41 am
Thanks for your help.
I'll look into some kerberos tools. I just find it odd that we only have the issue from SqlCmd version 12. Versions 10 and 11 work just fine. It's also odd that we have seen it on every SQL 14 instance that I have tested it on.
Can you confirm that when you ran the query using SqlCmd version 12, that you got Kerberos?
January 27, 2016 at 8:17 am
I get Kerberos from version 11 (SQL 2012). I do not have version 12 tools setup anywhere at the moment that I could use to test.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 27, 2016 at 9:22 am
Orlando Colamatteo (1/27/2016)
I get Kerberos from version 11 (SQL 2012). I do not have version 12 tools setup anywhere at the moment that I could use to test.
Thanks for checking. I'll see if I can find someone with version 12. I just want to see if someone else sees the same issue or if it is something with our environment.
I appreciate all your help. If this was Reddit, I would be sending you some gold.
June 20, 2016 at 10:22 am
Its "by design"
Complete and utter failure as far as I am concerned. The SQL Engine that can register/de-register SPN's (if allowed), doesn't do this at least in my environment - thus its a bit of a bugger if you have dynamic port assignments (that's anyone with a secondary instance with a default setup).
It will have people reverting to SQLOS (which is unaffected) or replacing SQLCMD with the 2012 version.
Oh and guess what SSMS is also unaffected.
Its almost like they've not discussed internally with what they are doing.
I am currently awaiting more info from Microsoft as to their reasoning.
:angry:
June 21, 2016 at 4:27 am
I’ve spun up a Windows 2016 Technical Preview 5 box just for fun, I could have used any compatible OS admittedly but I didn’t want to mess anything up I had in place. I’ve installed from the SQL 2016 Feature Pack msodbcsql.msi and MsSqlCmdLnUtils.msi from https://www.microsoft.com/en-us/download/details.aspx?id=52676 (incidentally got caught out with sqlncli.msi from the same download page as it is 2012 component and found SQLCMD no longer uses it as of 2014 media).
And Kerberos works…
:w00t:
It must be the :alien:'s
EDIT
Or it turned out to be a flawed test, see next post.
June 21, 2016 at 4:44 am
I've raised this as a bug...
EDIT
Shame I can't edit the bug as clearly I got it wrong about SQLCMD and 2016. The only thing I can do on it appears to make the bug private or public when I edit, so feel free to comment on it. 🙂
June 21, 2016 at 1:31 pm
I wonder if that MSDN article came out of the support ticket we opened with Microsoft?
I meant to post the solution after we heard back from them so thanks for taking care of that for me 🙂
We were not happy to find out the solution was to manually create a SPN for every hostname or have everyone use fully qualified domain names when they connect. The problem in our environment is the developers use CNAMES for everything. Most wouldn't even be able to figure out how to get the fully qualifed name if they wanted to. We have been copying the 2012 version of SQLCMD to all the application servers as a work around.
I'm glad to hear that the issues seems to be resolved in 2016. Too bad we are in the process of moving everything to 2014!
June 21, 2016 at 4:17 pm
:blush:
It's when you put your head down to go to sleep after a busy day that clarity can often shine..
I may have jumped the gun with saying that it works in 2016...my test was flawed as the additional SPN's were present on the instance I checked against. Will test tomorrow when I am back in the office.
June 22, 2016 at 2:11 am
Can you clarify something about the "automatic" creation of SPNs which you use.
In a Active Directory environment, the service account (running the MSSQL engine) needs significant permissions to register its own SPNs. These rights are never given in our environment. All SPNs are always manually created, during the initial setup of MSSQL. Yes, this means dynamic ports won't work, but we never use dynamic ports - something to do with security and firewall rules 🙁
Have you found a secure way for MSSQL to register its own SPNs?
Andy
June 22, 2016 at 2:48 am
Ok first thing... I've re-tested with SQL2016 SQLCMD against a clustered 2014 server. Its also not using Kerberos authentication when using the non fully-qualified computer account.
So that sucks... :doze:
Out of curiousity I am going to test OSQL and PowerShell to see what behaviours they have in SQL2016, just I need to install the stuff first as had previously only bothered with just the feature pack to play with.
----
Second...
Firewalls... yes not good having dynamic ports working with those. Internally however fine. At the end of the day, it all depends on your environment. We have 500+ SQL servers, some are fixed, most are dynamic.
As far as how a SQL Engine can register/de-register its own SPN's, a bit off topic but... I wouldn't necessarily say its a high privileged operation if done in the correct way, the engine account first has to be running under a domain account, secondly it needs both read and write permissions on servicePrincipalName.
A small warning though, at least for SQL 2008R2 clusters, if you have a lot of domain controllers (the company I work for is global, with a lot), you may find the SPN's go AWOL - say a two node cluster as the example, the first node may use one domain controller the second uses another domain controller, and there is a failover from node 1 to node 2, then back again in a short window. The de-register SPN command may overtake the register SPN command as it replicates through the domain and hence take a leave of absence, if you do have this issue then I would personally stick to having clusters on fixed ports whom can't manipulate SPN's. Non-clustered servers are unaffected, so knock your socks off. This has allegedly been remedied in SQL 2012, and I've not seen as yet at least on our SQL 2014 clusters, but there aren't that many of them.
Self SPN instructions (there is another method to do this as well that sets the same permissions, but this is the easiest):
Run ADSIEdit as a domain admin - so that's likely you may have to pass this bit onto a different team.
•Find the SQL Server engine domain account, right click, properties.
•Select the Security tab, click Advanced.
•Click Add. Type “SELF” in the object box. Click OK.
•Select the Properties Tab.
•Scroll down and check the “Allow” box for “Read servicePrincipalName” and “Write servicePrincipalName”
•Click OK. Click OK. Click OK.
•Restart your SQL Engine service – if AD has replicated from where you made the change – all should be resolved.
Ripped and edited from: http://blogs.technet.com/b/kevinholman/archive/2007/12/13/system-center-operations-manager-sdk-service-failed-to-register-an-spn.aspx
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply