March 24, 2016 at 9:26 pm
Comments posted to this topic are about the item Protecting sa
March 25, 2016 at 3:05 am
Fortunately for me I am not responsible for any system administration duties and have never had such responsibilities. So much so that I didn't even known, or at least could not remember, that you can rename the sa account. I have always recommended disabling mixed mode authentication (when I have had an opportunity) and the sa account, however, I will now recommend also renaming the sa account and setting its password.
Beyond that I look forward to considering the sage advice to follow from my fellow SSCers.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 25, 2016 at 4:00 am
Amazon don't allow SA access to RDS instances. If you are planning to query an instance over the Internet I'd expect VPN access and SQL Server in a specific pair of subnets (for resilience) protected by a security group that specifies that traffic can only come from explicit security groups over explicit port(s).
Protect the perimeter with Network ACLs and monitor the VPC Flow logs for strange behaviour.
I'm sure Azure has something similar.
March 25, 2016 at 5:30 am
Chalk up another one who didn't know you could rename sa.
We have mixed mode (the only account that uses it is sa) with a strong password. In addition I set an alert that emails me for failed logins against it. I can always tell when security is running a scan :-).
I haven't used it in forever, as I have my own account which gives me all the privileges I need.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
March 25, 2016 at 5:56 am
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.
March 25, 2016 at 7:47 am
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.
March 25, 2016 at 8:16 am
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.
March 25, 2016 at 8:28 am
Recently a network domain issue was preventing windows authentication for some SQL Server instances that I manage, but fortunately I had the option of falling back to using 'SA' account as a work around. Having that alternative authentication available is still relevent and probably will remain so for the forseeable future.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 25, 2016 at 8:58 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.
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.
March 25, 2016 at 9:28 am
Our SQL Server installs are all for software we have purchased from vendors. Where we can control the passwords we do. At one point some vendors required a particular sa password, and used it for all connections. Those vendors used the same password at every customer site of course, which truly gave me nightmares!
Currently I believe every system we have now has a password we control, although one never knows if someone hasn't installed an instance somewhere and not involved the DBAs. Of the ones I control, all have a minimum 15 character, truly pseudo random password. (there is no such thing as truly random in computers) I use a password generator that uses pretty much all characters on a keyboard, and I generate 20 samples. Then I pick one I like based on whatever hits my fancy that day.
I do the same thing for the network accounts we use to run SQL. Always a different password of course.
There are still lots of instances where a former DBA refused to change passwords, and used very short lengths, 8 or less, and I don't have control over these. Anyone on this post could guess her passwords in 5 minutes or less! Hopefully whoever inherits these exercises good practices ASAP.
Dave
March 25, 2016 at 9:36 am
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.
We do leave mixed mode on in most cases. That said, to comment on SQL Server providing the tools to handle security, Microsoft recommends not using mixed mode. Which kind of tells me they don't have confidence in security when using mixed mode.
Dave
March 25, 2016 at 10:49 am
djackson 22568 (3/25/2016)
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.We do leave mixed mode on in most cases. That said, to comment on SQL Server providing the tools to handle security, Microsoft recommends not using mixed mode. Which kind of tells me they don't have confidence in security when using mixed mode.
It tells me that in the Microsoft's ideal world everyone would work entirely within the MS stack.
March 25, 2016 at 10:55 am
ZZartin (3/25/2016)
djackson 22568 (3/25/2016)
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.We do leave mixed mode on in most cases. That said, to comment on SQL Server providing the tools to handle security, Microsoft recommends not using mixed mode. Which kind of tells me they don't have confidence in security when using mixed mode.
It tells me that in the Microsoft's ideal world everyone would work entirely within the MS stack.
Which, if you have heard of their disastrous attempt at AI with their "Tay" persona, makes me very, very worried. Microsoft simply doesn't have a history of stability across all products. I believe it is the opposite, most MS products have proven very flawed over the years. I used to argue it wasn't fair to compare them to other companies based on the age of the products, but that argument doesn't work any more. There are some MS products that are extremely good, such as SQL Server, but there are too many that simply aren't worth the bits they are saved on.
I LIKE competition!
Dave
March 25, 2016 at 11:49 am
djackson 22568 (3/25/2016)
Our SQL Server installs are all for software we have purchased from vendors. Where we can control the passwords we do. At one point some vendors required a particular sa password, and used it for all connections. Those vendors used the same password at every customer site of course, which truly gave me nightmares!Currently I believe every system we have now has a password we control, although one never knows if someone hasn't installed an instance somewhere and not involved the DBAs. Of the ones I control, all have a minimum 15 character, truly pseudo random password. (there is no such thing as truly random in computers) I use a password generator that uses pretty much all characters on a keyboard, and I generate 20 samples. Then I pick one I like based on whatever hits my fancy that day.
I do the same thing for the network accounts we use to run SQL. Always a different password of course.
There are still lots of instances where a former DBA refused to change passwords, and used very short lengths, 8 or less, and I don't have control over these. Anyone on this post could guess her passwords in 5 minutes or less! Hopefully whoever inherits these exercises good practices ASAP.
Renaming the real 'SA' account and then creating a new 'SA' account with reduced permissions can fake off 3rd party apps. When it comes to internal power users, (discretely) pulling them from sysadmin membership in this way can be an interim solution until you ween them onto a proper account.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 25, 2016 at 11:55 am
SA should be set with a random long password that is only kept by a very few individuals and changed with a certain frequency and not be used unless it is ultimately necessary.
I don't disable it, I rather have monitoring over any type of relevant changes on my servers and make sure these are subject to a controlled process.
I prefer Mixed Mode for the simple reason that it adds less variables to possible failures, if, for whatever reason, AD fails, having your apps connect with SQL logins will prevent the impact.
Viewing 15 posts - 1 through 15 (of 48 total)
You must be logged in to reply to this topic. Login to reply