Cannot login into an SQL server remotely through ssms when using windows credentials. This reoccurs after exactly a week and stops after a reboot.

Question

• We have a SQL Server 2008 R2 which locks all remote windows connections after a week. We get this error message
• The target principle name is incorrect. Cannot generate SSPI context.
• Upon reboot, connections are successful but this reoccurs exactly after 7days
• We use both windows an SQL authentication, but only windows accounts are not able to connect
• We have checked the registered SPNs which seem just find. No duplicates,
• KCM shows the SPNs are ok
• When pinged, the IP address resolves DNS.

Your recommendations will be greatly valued. Thanks in advance

Reply

Answer 1

Hi Eudias Kifem,

I agree with Erland that the low version could be an issue. This means you may not have a new version of the fix package. And SQL Server 2018 R2 is no longer supported.

Upon reboot, connections are successful but this reoccurs exactly after 7days

At the beginning of the seven days windows an SQL authentication can be successful, right? If so, this is a bit unusual. Given that you've troubleshot SPN, you might want to check the Windows logs and pay your attention to the Error Level log.

By the way, SQL Server network protocols can be viewed in SQL Server Configuration Manager, and you can verify that your protocols are properly enabled.

Best regards,

Seeya

---

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

Answer 2

• The target principle name is incorrect. Cannot generate SSPI context.

That's a Kerberos / not registered SPN = Server Principal Name issue; use the tool to fix it

Microsoft® Kerberos Configuration Manager for SQL Server®

P.S.: SQL Server version 2008 R2 ist out-of-support since a very long time.