March 25, 2016 at 11:59 am
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.
March 25, 2016 at 12:04 pm
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.
March 25, 2016 at 12:12 pm
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.
March 25, 2016 at 12:13 pm
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
March 25, 2016 at 12:14 pm
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.
March 25, 2016 at 1:08 pm
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.
March 25, 2016 at 2:16 pm
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?
March 25, 2016 at 2:34 pm
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.
March 25, 2016 at 4:46 pm
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
Change is inevitable... Change for the better is not.
March 26, 2016 at 4:34 am
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!
March 26, 2016 at 8:24 am
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.
March 28, 2016 at 6:56 am
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.:-)
March 28, 2016 at 7:06 am
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.
March 28, 2016 at 7:23 am
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?
March 28, 2016 at 7:48 am
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 48 total)
You must be logged in to reply to this topic. Login to reply