Cant connect to sql server anymore due to SSPI context error

Yrrag Jerios (Accenture) 1 Reputation point
2022-10-05T20:03:22.59+00:00

247891-issue.png

with the above error, both my SSMS and an excel file connected to the sql server could not be use anymore. How is this fix?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,470 questions
{count} votes

5 answers

Sort by: Most helpful
  1. JPG 5 Reputation points
    2023-11-09T06:43:08.39+00:00

    Had the same issue, Checked the SPNs with the Kerberos Config Manager tool and all was good. Reverted back to running the SQL Service as local account which allowed me to connect remotely.

    Started looking at the service account and ticked these to settings under the Account Tab > Account Options

    User's image

    Remote Connection to DB started working immediately without needing to restart SQL Service.

    1 person found this answer helpful.

  2. Shivam Kumar 541 Reputation points
    2022-10-05T20:25:36.753+00:00

    Hi,

    This happens when sql server fails to register the spn due to insufficient permissions of sql server service account or sometimes due to network failovers etc.
    Run this on a command prompt after replacing server name or account names or ports with actual names:

    SETSPN -L <Domain\SQL Service Account Name>   
    

    and see SPN for this server is getting listed or not if not add it manually using

    SETSPN -S MSSQLSvc/<fqdn of server>:<sql port of server> <Domain\SQL Server Account>  
    SETSPN –S MSSQLSvc/<computer name>:1433 <Domain\SQL Server Account>  
    

    Make sure your sql service account has permissions to register spn.

    Link for further reading
    Link for understanding the perimissions required by sql server service account which is being used to work properly

    Code to test and verify which auth scheme server is using once you are connected to server.

    SELECT net_transport, auth_scheme     
    FROM sys.dm_exec_connections     
    WHERE session_id = @@SPID;  
    

  3. Yrrag Jerios (Accenture) 1 Reputation point
    2022-10-05T21:21:45.477+00:00

    @Shivam Kumar im not so sure with the instruction above, but is this error related to a windows update?


  4. Wondi Wolde 1 Reputation point
    2022-10-05T21:35:16.053+00:00

    @YrragJeriosAccenture
    this error happens for various reasons, the Kerberos connection fails, bad SPN configurations,name resolution ..
    So you can use the Kerberos config manager tool from MS to resolve the issue.
    https://www.microsoft.com/en-us/download/details.aspx?id=39046

    0 comments No comments

  5. PandaPan-MSFT 1,926 Reputation points
    2022-10-06T01:58:25.81+00:00

    Hi @Yrrag Jerios (Accenture) ,
    This situation can also result from that SSPI uses Kerberos authentication to delegate over TCP/IP and Kerberos authentication cannot complete the necessary operations to successfully delegate the user security token to the destination computer that is running SQL Server.

    To solve this problem the easiest way is to download the Kerberos Configuration Manager in this link: https://www.microsoft.com/en-us/download/details.aspx?id=39046, which can help you troubleshooting SPN and delegation issues.

    After your downloading the Kerberos Configuration Manager you can check this link :https://learn.microsoft.com/en-us/troubleshoot/sql/connect/cannot-generate-sspi-context-error#fix-the-error-with-kerberos-configuration-manager-recommended and follow the guidance on it.

    Or you can use the method on this link:https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver15#Manual to register SPN manually.


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.