Solved

SQL Authentication and Win NT Authentication Issues

Posted on 2016-10-04
20
41 Views
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.
0
Comment
Question by:JeffJCA
  • 13
  • 7
20 Comments
 

Author Comment

by:JeffJCA
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
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41829386
0
 

Author Comment

by:JeffJCA
ID: 41830499
Sorry, thought I put that in there... it is mixed, both SQL and WIN NT auth
0
 

Author Comment

by:JeffJCA
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.
>>>
0
 
LVL 45

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.
0
 

Author Comment

by:JeffJCA
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?
0
 
LVL 45

Accepted Solution

by:
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.
0
 

Author Comment

by:JeffJCA
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.
0
 

Author Comment

by:JeffJCA
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.
0
 
LVL 45

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?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

Author Comment

by:JeffJCA
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:

        TERMSRV/CFP-EMR
        TERMSRV/CFP-EMR.cfp.local
        WSMAN/CFP-EMR
        WSMAN/CFP-EMR.cfp.local
        RestrictedKrbHost/CFP-EMR
        HOST/CFP-EMR
        RestrictedKrbHost/CFP-EMR.cfp.local
        HOST/CFP-EMR.cfp.local

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

        MSSQLSvc/CFPServer2.cfp.local:1433
        MSSQLSvc/CFPServer2.cfp.local
        TERMSRV/CFPSERVER2
        TERMSRV/CFPServer2.cfp.local
        WSMAN/CFPServer2
        WSMAN/CFPServer2.cfp.local
        RestrictedKrbHost/CFPSERVER2
        HOST/CFPSERVER2
        RestrictedKrbHost/CFPServer2.cfp.local
        HOST/CFPServer2.cfp.local
0
 

Author Comment

by:JeffJCA
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
C=local:
        DNS/CFPDomain.cfp.local
        HOST/CFPDomain.cfp.local/CFP
        RPC/40f22dbb-52d9-46c8-97b3-cd6dfbd164a2._msdcs.cfp.local
        GC/CFPDomain.cfp.local/cfp.local
        HOST/CFPDomain.cfp.local/cfp.local
        HOST/CFPDOMAIN/CFP
        ldap/CFPDOMAIN/CFP
        ldap/40f22dbb-52d9-46c8-97b3-cd6dfbd164a2._msdcs.cfp.local
        ldap/CFPDomain.cfp.local/CFP
        ldap/CFPDOMAIN
        ldap/CFPDomain.cfp.local
        ldap/CFPDomain.cfp.local/ForestDnsZones.cfp.local
        ldap/CFPDomain.cfp.local/DomainDnsZones.cfp.local
        ldap/CFPDomain.cfp.local/cfp.local
        E3514235-4B06-11D1-AB04-00C04FC2DCD2/40f22dbb-52d9-46c8-97b3-cd6dfbd164a
2/cfp.local
        NtFrs-88f5d2bd-b646-11d2-a6d3-00c04fc9b232/CFPDomain.cfp.local
        Dfsr-12F9A27C-BF97-4787-9364-D31B6C55EB04/CFPDomain.cfp.local
        TERMSRV/CFPDOMAIN
        TERMSRV/CFPDomain.cfp.local
        WSMAN/CFPDomain
        WSMAN/CFPDomain.cfp.local
        RestrictedKrbHost/CFPDOMAIN
        HOST/CFPDOMAIN
        RestrictedKrbHost/CFPDomain.cfp.local
        HOST/CFPDomain.cfp.local


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
0
 
LVL 45

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).
0
 

Author Comment

by:JeffJCA
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.
0
 

Author Comment

by:JeffJCA
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
0
 

Author Comment

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

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.
0
 
LVL 45

Expert Comment

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

Author Closing Comment

by:JeffJCA
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.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now