May 20, 2020 at 5:42 pm
Did patch Tuesday yesterday across 6 UCS blades. Servers are 2019 datacenter in a cluster. Each blade has 4 instances so 4 availability groups of 6 members each. One of the 2017 instances is in a bad state. I've tried a repair, I've tried removing updates (even though the other 5 prod blades are fine, and the Dev/QA clusters are fine also. The SQL service is running, agent is not (the reason is obvious with the error). I can NOT connect to the instance with SQL or Windows auth, even locally.
A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The Local Security Authority cannot be contacted) (Microsoft SQL Server, Error: -2146893052)
The repair fails because the process needs to connect. The other three instances on this blade are fine, so I don't want to have to do a bulk restore (more of a pain than rebuilding the instance). I have searched high and low and I'm not finding much help. In the many years I've been a member, I try to fix my own issues, but this one has me stumped.
Thanks in advance for any guidance you can give me.
May 20, 2020 at 8:10 pm
MAY have a solution to that:
https://bindujanga.blogspot.com/2016/03/sql-server-connectivity-issue.html
It isn't specific to SQL 2017, but may be something to look into.
To quote the article:
Windows server Event Log has many SChannel errors as below:
The following fatal alert was generated: 51. The internal error state is 602.
This error was caused by a bad update of windows server related to a security configuration/spoofing.
To resolve this:
Install security update from https://technet.microsoft.com/library/security/MS15-121
or
1. Go to regedit
2. Locate and then click the following subkey in the registry: HKLM\System\CurrentControlSet\Control\SecurityProviders\Schannel
3. On the Edit menu, point to New, and then click DWORD Value.
4. For the computer that is receiving the connection request, type DisableServerExtendedMasterSecret: REG_DWORD for the name of the DWORD, and then press ENTER.
5. For the computer that is initiating the connection request, type DisableClientExtendedMasterSecret: REG_DWORD for the name of the DWORD, and then press ENTER.
6. Right-click the new DWORD entry, and then click Modify. Type 1 or a non zero value.
Note : Make sure that Regedit values are added correctly. Server restart is not required.
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 20, 2020 at 8:32 pm
That reg fix was one of the first things I found, but didn't really apply. I did fix the issue though, and wanted to be sure to update this thread instead of just leaving it without a fix.
I opened the SQL Server Configuration Manager,
SQL Server Network Configuration,
Protocols for *instance*,
Properties,
Certificate,
On all of the other 23 instances, that is blank. This instance had the ConfigMgr SQL Server Identification Certificate selected. I hit "Clear", and apply...restarted the services and we are up and happy. Been a SQL pro for over 20 years, and had never seen this error. Anyway, it's fixed, on to the next disaster.
May 20, 2020 at 10:00 pm
Thanks for posting back especially including the solution.
Good luck with the disasters. Hopefully you do not have too many.
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 28, 2020 at 7:49 am
Have you checked the SQL Error Logs for the update and subsequent restart attempts? If your instance had a problem while in Script Update mode you could get similar symptoms to what you describe.
We have had a problem with Script Update mode maybe twice in the past 4 years. Depending on the cause the solution varies, but ddg normally finds a good solution.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply