Query regression in SQL 2014 compared to earlier SQL versions

Lately I have had many queries like “I have a SQL server 2014 and a particular query does not complete in expected time frame, the same query was running without issues before migration in SQL Server2012/SQl2008R2” 

or 

“The Query that was running in SQL 2012/SQL 2008R2 stalls in SQL 2014 and never completes even though there are no resource issues”. 

In SQL 2014 Cardinality estimator for SQL Query optimizer has been modified for improved performance, although this may help most of your queries there are some queries which has shown regressive performance on SQL 2014 compared to earlier version. This is already a documented issue with Microsoft https://msdn.microsoft.com/en-us/library/dn600374(v=sql.120).aspx . The only way to overcome this is to test your workload before migration and modify your query.

SQL Server Kerberos Authentication Issues

Problem: - Not able to create Linked Server using current Windows security contest, The Linked server fails with “Login failed for user NT AUTHORITY\ANONYMOUS LOGIN”

OR

Unable to connect to SQL Server outside of the local server, SQL Server itself was up and running fine and we were able to connect the server when we RDP to the local server and access the SQL but when we connect SQL Server outside the local server were getting the following error,

“The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server)”

Troubleshooting:-

Usually this issue occurs when Kerberos Authentication Failure, Kerberos protocol uses SPN (Service Principal Name) for the authentication with the server and client.

Usually the SPN’s are created on SQL Start-up by using the SQL Server Service Account, but most of the cases we may fail to notice the SPN did not register on SQL start-up due to permission issues,

Even though we will not see any issues connecting the SQL Server as SQL will use NTLM protocol for Authentication, all the operation using Kerberos will fail(E.g.: Linked Server, OpenRowset etc.).

We can check the SQL Error log to find if the SPN was registered successfully on SQL Server Start-up. You will the below message if the SPN is not registered on SQL Server start-up,

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/TestServer.testdomain:<InstanceName> ] for the SQL Server service. Windows return code: 0x2098, state: 20. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [MSSQLSvc/TestServer.testdomain:<InstanceName>] for the SQL Server service. Windows return code: 0x2098, state: 20. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.

Resolution:-

This is documented error in Microsoft, you need to provide READ and WRITE permissions to the SQL server domain account on the AD so that new SPN’s can be created on the AD for SQL Server.

Detailed steps to be taken for resolution of this issue is mentioned in the Microsoft article http://support.microsoft.com/kb/811889 and  http://blogs.technet.com/b/mdegre/archive/2009/11/20/the-sql-network-interface-library-was-unable-to-register-spn.aspx. The resolution task can be done only someone who has Admin access to AD.

Once the SPN’s are created and registered successfully you will able to create Linked Servers