May 15, 2019 at 6:36 pm
Problem:
I have a server that has three instances installed and in all three when trying to open the SQL Error Log we get the following:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
-----
I am getting the following error in the Windows Application log
The description for event ID 17052 from source MSSQLSERVER cannot be found. Either the component that raises the is event is not installed on your local computer or the installation is corrupt. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event
Severity: 16 Error: -2146893022, OS: -2146893022 [Microsoft][SQL Server Native Client 11.0]SSL Provider: The target principal name is incorrect
----------
When trying to use exec xp_readerrorlog I get the following error
Failed to open loopback connection. Please see event log for more information
error log location not found
I have tried the following with No luck
-Executing a SQL Server Repair did not work - even a new instance installed on the same server does not work
-did NOT want to create the disableloopback entry in the registry so I did not do this
-Registry settings for the error log location is fine
-I added the virtual SQL Service account for the SQL Server Agent with full control of the program file folder where the log folder is and restarted the instance and - no luck
-I checked C:\WINDOWS\system32\drivers\etc\hosts and made sure 127.0.0.1 localhost server_name was there.
-I tried to turn off network encryption and no luck
-I tried to install ODBC Driver 13 and no luck
To me it does not seem like an SQL Issue because all three instances are doing the same thing. Its seams to me a server permission or setting.
Any help is appreciated.
Jeff
May 15, 2019 at 9:18 pm
As a crazy thought, what happens if you try opening the error log file outside of SQL? With the new instance you can shut it down and have a look at the log and just see if something is wonky with it.
Did a quick google on that error message (The target principal name is incorrect) and I think you likely don't have SPN's set up for the server and service.
To test if it MIGHT be an SPN issue, try logging into the SQL instance with a SQL account instead of a windows account. If that lets you look at the log, it would make me think more that it is an SPN issue.
This website has a bunch of different things to try, but the SPN would be one of the first I'd look at:
The specific post was by EM0:
I just ran into this and fixed it by doing 2 things:
Granting read/write servicePrincipalName permissions to the service account using ADSI Edit, as described in https://support.microsoft.com/en-us/kb/811889
Removing the SPNs that previously existed on the SQL Server computer account (as opposed to the service account) using
setspn -D MSSQLSvc/HOSTNAME.domain.name.com:1234 HOSTNAME
where 1234 was the port number used by the instance (mine was not a default instance).
In the event it isn't an SPN issue, this website looks like it'll be helpful too:
https://landonfowler.com/2018/09/sql-server-failed-to-open-loopback-connection/
Here, the solution is to turn on the SQL Browser service OR turn on shared memory.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 17, 2019 at 8:00 pm
Thank you very much for the information
As a crazy thought, what happens if you try opening the error log file outside of SQL? With the new instance you can shut it down and have a look at the log and just see if something is wonky with it.
-I checked the error log and it looks fine
-I can view the error logs using notepad just fine
-I tried replacing the service account from NT Service\MSSQL$instance to using the local Service - No luck
-I tried adding the virtual service account to the local administrators group - No luck
I will have to read up about the SPN issue, but do not think that is the problem. Its seams like SSMS cannot locate the file, but registry settings are fine
Jeff
May 17, 2019 at 8:01 pm
Quick question
I have installed both ODBC 13 and ODBC 17 drivers on the server. Is there something I need to do after that to let SSMS know that I have the latest ODBC driver installed.
Jeff
May 29, 2019 at 6:36 pm
Still no solution to the problem, but I did find out that some of my servers are not successfully registering the SPN. Strangely the server that cannot open the error log does register SPN just fine.
I started a new topic
https://www.sqlservercentral.com/forums/topic/spn-failed-to-register-virtual-service-accounts
any help would be appreciated.
Jeff
May 29, 2019 at 7:37 pm
Was the server renamed after SQL Server was installed?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply