Protecting sa

  • DBA_Rob (3/25/2016)


    Yes we do set a password before we disable it. As you point out we have had to enable it for 3rd party applications to be installed. Generally for that we enable the account, set the password to something new; install the application; reset the password again and disable it.

    That's what I do. I think that's the best practice.

  • In our environment (several hundred SQL instances geographically dispersed around North America) we were required to randomize the SA password by audit long ago. We have a process which randomizes the SA password for each instance every morning. If the password is required for some reason (most often installing vendor software) it can be changed to a known value for that instance for the installation. The next morning it is automatically randomized again.

  • Tony Lanterman (3/25/2016)


    In our environment (several hundred SQL instances geographically dispersed around North America) we were required to randomize the SA password by audit long ago. We have a process which randomizes the SA password for each instance every morning. If the password is required for some reason (most often installing vendor software) it can be changed to a known value for that instance for the installation. The next morning it is automatically randomized again.

    That's interesting. And a good idea. Want to write up an article on the process and why? I think others would like this.

  • We use Thycotic Secret Server as a repository for passwords, keys, etc. In addition to the web based management console, it has a domain authenticated webservice API for integration with SQL Server install scripts or other automation tools, and it can auto generate the audit compliant passwords and user activity logging. No more hunting down or losing passwords.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • ZZartin (3/25/2016)


    Hmm... a good password on the sa account should sufficient, I wouldn't want to disable it in the event it's actually needed. And I certainly wouldn't want to turn off mixed authentication for a couple reasons, first a lot of applications still need native authentication and won't work with just windows authentication and what happens if for whatever reason your AD system is down and you need to get in? SQL Server provides the tools to handle security properly.

    Even with Mixed mode off, you can use the DAC locally to get in in most cases.

    I think it's worth turning things off that you don't need. You can always enable this back.

  • Steve Jones - SSC Editor (3/25/2016)


    Tony Lanterman (3/25/2016)


    In our environment (several hundred SQL instances geographically dispersed around North America) we were required to randomize the SA password by audit long ago. We have a process which randomizes the SA password for each instance every morning. If the password is required for some reason (most often installing vendor software) it can be changed to a known value for that instance for the installation. The next morning it is automatically randomized again.

    That's interesting. And a good idea. Want to write up an article on the process and why? I think others would like this.

    Unfortunately my employer would frown upon too detailed a write up. In broad strokes we grab a list of server/instance names out of our central management server. Using that and a randomization function to get the new password, the SP generates a password change command for each instance and then executes it on the remote system.

    This was all required to satisfy audit requirements for use of superuser accounts which were not tied to an individual person's account back when SOX was a new thing.

  • Steve Jones - SSC Editor (3/25/2016)


    Tony Lanterman (3/25/2016)


    In our environment (several hundred SQL instances geographically dispersed around North America) we were required to randomize the SA password by audit long ago. We have a process which randomizes the SA password for each instance every morning. If the password is required for some reason (most often installing vendor software) it can be changed to a known value for that instance for the installation. The next morning it is automatically randomized again.

    That's interesting. And a good idea. Want to write up an article on the process and why? I think others would like this.

    +1. I like the idea, would you mind sharing the procedure, Tony?

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Tony Lanterman (3/25/2016)


    Steve Jones - SSC Editor (3/25/2016)


    Tony Lanterman (3/25/2016)


    In our environment (several hundred SQL instances geographically dispersed around North America) we were required to randomize the SA password by audit long ago. We have a process which randomizes the SA password for each instance every morning. If the password is required for some reason (most often installing vendor software) it can be changed to a known value for that instance for the installation. The next morning it is automatically randomized again.

    That's interesting. And a good idea. Want to write up an article on the process and why? I think others would like this.

    Unfortunately my employer would frown upon too detailed a write up. In broad strokes we grab a list of server/instance names out of our central management server. Using that and a randomization function to get the new password, the SP generates a password change command for each instance and then executes it on the remote system.

    This was all required to satisfy audit requirements for use of superuser accounts which were not tied to an individual person's account back when SOX was a new thing.

    Thanks, Tony, to me this is enough detail to write up a script. I deal with a few instances only, so connecting to each of them is no problem since I have access to all of them.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • It's not just "sa" that needs protecting... it's ANY AND ALL logins, especially those with SysAdmin or Control Server privs. There absolutely must be good password policies setup and if you don't do penetration testing at least once a year, you're just asking to get hacked because anyone can figure out who the DBAs are for most companies and what their logins are.

    --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.
    "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)
    Intro to Tally Tables and Functions

  • I wish SQL Server had the option to support multifactorial authentication.

    I know later editions of Active Directory support it but that presumes Windows only authentication.

    As to the comment about Microsoft's ideal world being one where you stay in their stack. Imagine you are a software vendor whose portfolio covers most if not all of what your business customers require. Wouldn't you want that? It's not as if Microsoft products don't play well with other vendors products.

    Because I pre-date Microsoft I remember what it was like in the early PC era so I've never understood the rabid Microsoft hatred. I quite like Linux but I'm not offering to have it's babies. I quite like some Oracle products but detest the licencing approach, particularly with regard to big data. In fact most vendors have something to offer even if it isn't what they want to offer you!

  • I must use mixed-mode authentication in my environment on all SQL Servers. There's a password for all sa logins and they're them all disabled. Given how much damage can be done by an attacker getting in with that login, I have a procedure that checks it frequently and sends me an email and a text if it's ever enabled.

    I've really thought about renaming it and creating a honeypot sa login like Eric suggested, but decided against it.

    We don't have many third-party databases, but we do have a few. I won't even consider enabling it for any of them. If the provider insists, then they've made the decision that they don't really want our business. Besides, any application that requires the sa login probably isn't one we want anyway. I suppose if there was a third-party product we really needed, the honeypot would work.

  • Steve Jones - SSC Editor (3/25/2016)


    DBA_Rob (3/25/2016)


    I can't think of a SQL Server environment where I didn't end up needing to turn on mixed authentication, but when it comes to the sa account I usually just disable it. The accounts with the sysadmin role are always Windows/Active Directory accounts in my environments.

    Do you set a password? This is like having Windows Auth and getting it changed. What if someone enables "sa" because a software packages needs it and forgets? I'd always set a password.

    A nice long password.:-)

  • Iwas Bornready (3/28/2016)


    Steve Jones - SSC Editor (3/25/2016)


    DBA_Rob (3/25/2016)


    I can't think of a SQL Server environment where I didn't end up needing to turn on mixed authentication, but when it comes to the sa account I usually just disable it. The accounts with the sysadmin role are always Windows/Active Directory accounts in my environments.

    Do you set a password? This is like having Windows Auth and getting it changed. What if someone enables "sa" because a software packages needs it and forgets? I'd always set a password.

    A nice long password.:-)

    It's still a well-known login with sysadmin privs, so it's vulnerable if it's enabled.

  • Ed Wagner (3/28/2016)


    Iwas Bornready (3/28/2016)


    Steve Jones - SSC Editor (3/25/2016)


    DBA_Rob (3/25/2016)


    I can't think of a SQL Server environment where I didn't end up needing to turn on mixed authentication, but when it comes to the sa account I usually just disable it. The accounts with the sysadmin role are always Windows/Active Directory accounts in my environments.

    Do you set a password? This is like having Windows Auth and getting it changed. What if someone enables "sa" because a software packages needs it and forgets? I'd always set a password.

    A nice long password.:-)

    It's still a well-known login with sysadmin privs, so it's vulnerable if it's enabled.

    Why is your system letting someone try to brute force the password?

  • sa can't be locked out, so it's susceptible to brute force. If you run mixed mode, you do need network controls to try and prevent constant attacks.

Viewing 15 posts - 16 through 30 (of 49 total)

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