Post reply

Fix for SSPI context error uses RC4 - any alternatives?

  • October 31, 2022 at 7:34 pm

    #4106951

    Hello experts,

    I've been trying to resolve an issue that seems to happen when SQL Server 2019 runs on Windows Server 2019. This may be actually an issue with our Systems-mandated security settings to lock down the servers, but I am wondering if you have some advice for other ways to work around the issue.

    The issue:

    Currently when we have a SQL Server 2019 running on Windows Server 2019, the following happens:

    1. If you try to log in from an external client, such as SSMS, this error occurs: "The target principal name is incorrect.  Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)"
    2. The SSPI error does NOT happen if a SQL login is used for the connection.
    3. The SSPI error does NOT happen if we run the SQL Service using the Local System account.
    4. The SSPI error does NOT happen when you log in from the SQL host itself, whether using AD or SQL login.
    5. This error happens even when Kerberos Configuration Manager reports that all the SPNs are Good.

    We opened a Microsoft case, and they ultimately recommended these steps:

    "Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options

    Open Properties for "Network security: Configure encryption types allowed for Kerberos"

    And then please select RC4 , AES 128 , AES256 then click on ok .

    Then please do the GPupdate/ force and reboot the server ."

    That works - but in our case it required checking the RC4 box. RC4 is a cipher known to be broken and unsafe, so for good reasons our security team may reject this exception request.

    Does anyone know why RC4 is needed for the AD logins to work, and can anyone recommend any alternate way of circumventing this error? Also, has anyone else seen this problem in their environments?

    FWIW, we currently have many remaining SQL 2016 servers, and the rollout of Windows Server 2019 has started picking up speed only within the past 6 months or so. Is it possible some of the AD accounts or policies need to be updated?

    Thanks for any help.

    -- webrunner

    • This topic was modified 2 years ago by  webrunner.
    • This topic was modified 2 years ago by  webrunner.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • October 31, 2022 at 8:53 pm

    #4106966

    it has to do with the passwords for some logins being encrypted using that cypher - so only way to allow those clients to connect is for the server to have the same protocol enabled.

     

    There may be a way of forcing all clients to change to a better protocol on password change - but until all are changed you may have to stick with having RC4 enabled.

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

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