Cannot Open SQL Error Login using SSMS

  • jayoub

    SSCertifiable

    Points: 6795

    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

  • bmg002

    SSC-Insane

    Points: 22201

    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:

    https://stackoverflow.com/questions/32309922/the-target-principal-name-is-incorrect-cannot-generate-sspi-context

    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.

  • jayoub

    SSCertifiable

    Points: 6795

    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

  • jayoub

    SSCertifiable

    Points: 6795

    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

  • jayoub

    SSCertifiable

    Points: 6795

    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

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88020

    Was the server renamed after SQL Server was installed?

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply