SA account locked out/Single user turned on/SQL Server Agent stopped

  • jensohl94

    Valued Member

    Points: 64

    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!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717951

    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.

  • Mr. Brian Gale

    SSC-Insane

    Points: 22561

    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.

  • jensohl94

    Valued Member

    Points: 64

    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.

  • jensohl94

    Valued Member

    Points: 64

    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!

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717951

    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

  • Jeff Moden

    SSC Guru

    Points: 996046

    jensohl94 wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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