SQL Authentication and Win NT Authentication Issues

Posted on 2016-10-04
Last Modified: 2016-11-07
Okay, I am at a loss on this issue. I have a client who has three servers. A 2012R2 DC, A 2008R2 SQL Server, and a 2012R2 Terminal Server.  The SQL Server is running SQL 2008
The VAR software vendor that runs a SQL app on the server server did an upgrade to their software. During the upgrade process someone evidently logged into the SQL server app. Everything on the network, domain and SQL server and this app was working fine with no issues before this upgrade. After the upgrade many things with their app and SQL stopped working.  They blame it on the users logging into the SQL app during the upgrade and are saying it is a domain, DNS or AD issue even though everything worked before their upgrade and nothing was changed at all with the domain, AD or DNS.  The main issue is you can no longer log into their SQL app using Win NT Authentication from anywhere other than on the SQL server itself. You can use their app to login to the SQL server on the physical SQL server using Win NT Auth just fine. If you try to do it from any pc or the other two servers you get "could not establish a connection to the database" error.  You can use their SQL app to login from anywhere if you use SQL auth and the sa credentials.

You can log into the SQL server with domain credentials just fine, just as you can any pc or the other servers. Users have no issues logging into the domain with their domain credentials, locally or even remotely. You can add new users, new computers, etc to the domain just fine.  You can ping the SQL server with ip and name no problems from the other servers and all pc's.  DNS and nslookup commands on the SQL server work from the other servers and all pc's.  SQL server has named pipes and IP enabled. Remote connections is enabled. The ip's are correct.  Using the port query tool and running query on SQL service of the SQL server from either of the other servers all pass.
Looking at permissions on the database the domain users account has permissions to the db.
The SQL server service is using the Local System account for login.  There is only one instance - the default

I have another client using this same setup, same VAR software and version who got the upgrade but they work fine. I have tried to compare all settings in the domain and SQL between the two to find a difference but have not yet.

Any suggestions on more things I can look at or try? Anything I can run to do more tests? I am not a SQL expert by any means and have looked at everything I know. Thanks.
Question by:JeffJCA
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 7

Author Comment

ID: 41829194
I found one support site that said to look at ADSIEDIT.msc and users on the DC.
I did this and found something interesting and not sure if this has anything to do with it.
I found several MSSQL user accounts that for some reason say SQL 2005 and have the old domain controller name instead of the new domain controller name.  We removed the old domain controller which was 2003 and installed a new DC that is 2012R2. Now we did this over 6 months before the issues started so that is why I am not sure this is part of the issue. The old DC was named CFP_Server1 and the old SQL was 2005. These were both replaced 6 months before the issue started. The DC has a new name and SQL is 2008 now. I have attached a screen shot.adsieditusers.jpg
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41829386

Author Comment

ID: 41830499
Sorry, thought I put that in there... it is mixed, both SQL and WIN NT auth
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users


Author Comment

ID: 41831185
In talking with someone tonight I was referred to this instance of another case... this sounds very much like the issue the VAR vendor had when they upgraded their SQL app and the problems started.  I am thinking this is most likely our issue.  What do others think?

Here, I talk about one extreme situation: SQL server was running under Local System and was shutdown accidentally. The user then decides to run SQL server under a different account, e.g local account, domain account etc., for whatever reasons. Then he/she hit this “Cannot Generate SSPI Context” error when the client tries to connect the server. Keep in mind this only happens when TCP is enabled for the SQL server and is used by the client to connect the server.

What happened here is: When SQL server ran under Local System, it had successfully registered the Service Principle Name (SPN) for the service. The SPN is kept in the Active Directory and should be de-registered when the server is shutdown. Due to the accidental shutdown, SQL server failed to de-register the SPN. When the client connects to the server using TCP, it can find the SPN in the Active Directory and Kerberos will be used to perform the security delegation. However, the new account is not the correct container of the SPN, and Kerberos will fail.  

When this happens, some people may choose to reinstall SQL Server or even the whole OS. They may be frustrated by the fact that the problem is still there if local or domain account is again chosen as the service account. The SPN in the Active Directory won’t go away even if you reinstall the OS.

Setspn.exe can be used to register/de-register SPNs. One can register the same SPN for the same container more than one time. The registration beyond the first registration may not do anything. One de-registration will remove the SPN from Active Directory totally. Because of this, the easiest first step to troubleshoot “Cannot Generate SSPI Context” is to run SQL server under Local System account and gracefully shut it down. You can then change your service account to whatever you want. SPN will not be registered and clients will fallback to use NTLM.
Also note that, if you made any change related to SPN or service account on the server, the cached information on the clients may need a couple of minutes to go away. You may see some inconsistent information during this period. Just wait several minutes in this case.
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41831252
When SQL server ran under Local System, it had successfully registered the Service Principle Name (SPN) for the service.
That's not correct. SPN should only work with AD accounts so when Local System is used (a very bad option, I may say) a SPN can't be set.

When this happens, some people may choose to reinstall SQL Server or even the whole OS.
That's wrong. When that happens you must set the SPN correctly. Remove old ones and add the new one.

Author Comment

ID: 41832287
Thanks for the response Vitor, so you think since SQL Server is running under Local System account that the SPN is not the issue?  What do you think I should look at or test?
LVL 51

Accepted Solution

Vitor Montalvão earned 500 total points
ID: 41833105
Local System is like the name says an account that works only locally.
SPN is used for machines and accounts registered in an Active Directory.

My suggestion is that you change the SQL Server service account to an AD account and stop using Local System since you're working in a network. Then get the necessary SPN correctly registered.

Author Comment

ID: 41833823
The odd thing is I have another client that they also have this same setup that is having no issues at all and working. SQL there is also running under local system but working fine. I have compared everything between the two but cannot find anything different yet. The SQL servers are both 2008 and 2008R2 servers. The only difference is the one working the DC is 2003 Server still where this one not working has a 2012R2 DC.

Author Comment

ID: 41833824
The VAR is saying and blaming the DC saying there is a problem with the DC causing it but I cannot find any issues with it as everything else works fine but their SQL app.
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41833835
The only difference is the one working the DC is 2003 Server still where this one not working has a 2012R2 DC.
This may justify the difference. I'm not sure if Kerberos authentication were already available in 2003. Can you check the SPN for that client?

Author Comment

ID: 41833896
If both of the clients SQL servers service account is set to local system, will there be an SPN for it?

Author Comment

ID: 41833936
I had asked them to install a new SQL server on another server with a blank DB to see if that worked. They said it did not but with different errors which I am looking to see what the other error is.
Interesting enough they did not use local system on that install instead used the Domain admin account.
When run the SPN query on the two different servers I get this...
C:\Users\Administrator.CFP>setspn -L CFP-EMR
Registered ServicePrincipalNames for CN=CFP-EMR,OU=servers,OU=CFP,DC=cfp,DC=local:


C:\Users\Administrator.CFP>setspn -L CFPserver2
Registered ServicePrincipalNames for CN=CFPSERVER2,CN=Computers,DC=cfp,DC=local:


Author Comment

ID: 41833960
Here is the SPN on the DC...

C:\Users\Administrator.CFP>setspn -L CFPDomain
Registered ServicePrincipalNames for CN=CFPDOMAIN,OU=Domain Controllers,DC=cfp,D

I noticed that for some reason when they created the network and setup the DC they created the OU=CFP with additional OU's under it and the DC is under the OU=Domain Controllers as seen in attachment... wondering if that is also creating any issuesAD.jpg
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41836682
When run the SPN query on the two different servers I get this...
So no SPN entry for SQL Server service (MSSQLSvc).

Author Comment

ID: 41837255
Correct, but that is due to the SQL service logging in as LOCAL SYSTEM. Correct?

I just changed the SQL services to logon as the domain admin account and now most of the authentications work. However, if I run the setspn -L CFP-EMR command it still does not show an SPN for SQL server.

Author Comment

ID: 41837262
Yet, if I go into ADSI Edit and look at the domain admin account I see this as in the screenshotadsi-pic.jpg

Author Comment

ID: 41837278
Also, just fyi - both named pipes and TCP/IP are enabled and in this order
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41837920
if I run the setspn -L CFP-EMR command it still does not show an SPN for SQL server.
You'll need to add it manually with SETSPN -A parameter.
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 41858111
Jeff, any update on this question?

Author Closing Comment

ID: 41877601
Thanks for the help. Changing the account SQL logs in with has fixed the issue. It must be something with having a 2012 DC since they had this same setup with a 2003 DC and it worked.  Thanks again.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question