March 25, 2020 at 6:24 pm
Hey there all! So we have recently been having an issue at work. Started with our sqladmin account being locked out randomly. Searched the logs and we cannot find out where this is happening at. Now...We came into work our SQL server was changed to Single User mode. Next issue......SQL Server Agent decided to stop. And now trying to remote desktop to it for just one admin trying to RDP to it.....
Gives him the "Identity of the remote computer cannot be verified" Due to problems with the security certificate. We don't have any SSL's on this server. Any ideas?
It seems that is is possessed these last 2 months. We are desperate!
March 25, 2020 at 6:56 pm
I'd set up auditing or xevents to try and capture what's happening. If this is some automated process, you should capture some items. If this is a person, they might be able to disable things, but you'll know things like auditing are disabled.
Create a write only folder for the logs for xevents or SQL Audit, remove any permissions to change.
March 25, 2020 at 9:36 pm
To address the security certificate message, that is semi normal. Computers generate a certificate to be used for RDP connections. It is used to encrypt the connections. It should have been issued to the PC when it joined the domain and as long as your machine trusts your CA (which it should if they are on the same domain), it is likely that the certificate expired. I get this message at work on a lot of machines and I just click on "connect anyway" as I trust the PC even if the certificate is expired.
That being said, you should be able to do analysis of this problem remotely. What are you looking at that you need to connect to the PC to work on? Checking the event log or starting services can (and generally should) be done remotely without the added overhead of starting an RDP session on the server. Look into the tool "mmc.exe" that comes with all windows computers. You can use MMC to verify the certificate as well. If it is expired, you just need to renew it.
As a thought, is the SQL instance internet facing? If it is presented to the internet, there is a chance someone got in and is changing these things.
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.
April 1, 2020 at 3:36 am
We have Extended Events.....We are on SS 2016. The weird thing is.....our sa account was getting locked out over 30 times today. None of us can figure this out. Making sure nobody has mapped drives to anything....Other than that. Baffled? It originally started happening about 3 months ago. But now it is getting more and more common. Causing quite the stir because our ETL jobs keep failing due to the account locking.
April 1, 2020 at 6:31 am
Update.....I'm staying up watching the logs! Just saw something in there. Invalid login with the admin account to ReportServer. Guessing this in an SSRS thing? Have had issues with this in the past as far as deploying reports. Never thought twice about it. Meaning that it would lock out the admin account on the server. Yikes. Still learning people!
April 1, 2020 at 2:15 pm
You should not have sa being used for SSRS, ETL, etc. It's a recipe for problems, and it's a security risk. Create separate, non-sysadmin, accounts for each access.
If you're struggling with security, we have some resources to help you get learn more: https://www.sqlservercentral.com/stairways/stairway-to-sql-server-security
April 1, 2020 at 6:17 pm
Update.....I'm staying up watching the logs! Just saw something in there. Invalid login with the admin account to ReportServer. Guessing this in an SSRS thing? Have had issues with this in the past as far as deploying reports. Never thought twice about it. Meaning that it would lock out the admin account on the server. Yikes. Still learning people!
The sysadmin user (SA) on the server should be (MUST BE) disabled to begin with.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply