December 16, 2015 at 9:08 am
Hello,
Whenever I do a SQL server install I tend to enable mixed mode authentication and the “sa” account is setup with a strong password (15 characters, numbers, letters, special characters, upper/lower case, etc). Once this is setup it is hardly (if ever) used.
I have been told how this is very old fashioned and there’s no need to use mixed mode authentication anymore. My reason for doing this is as a DBA I want to have a safety net in case the server loses connectivity with the domain controller or for whatever reason is unable to authenticate Windows users I know I always have the “sa” account to fall back on.
Is this bad practice? Can someone convince me (even with a very strong password) the “sa” account should not be used?
Thanks
December 16, 2015 at 9:17 pm
The "SA" account is well known to all hackers and the best recommendation is to give a strong password and then disable it. It's OK to build a back door into the server (obviously, with a very strong password) but it shouldn't be the "SA" account.
As for the rest of the stuff, there's nothing wrong with "old fashioned" in my book. There's a whole lot of old fashioned stuff (especially in SQL Server) that works head and shoulders above some of the new junk they've come out with. And, yeah... I used "Mixed" authentication, as well. There are some logins that I just don't want in AD.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2015 at 10:22 pm
I used to rename sa to avoid brute force password attacks it. I haven't tried this in a while though (not sure you can even still rename it) because in the last few shops I've just disabled the sa Login and relied on the backdoor of starting the instance in Single-user mode if needed. Can't think on one exception off the top where I didn't end up enabling Mixed-mode. Usually someone will take it up as a challenge to justify the need for a SQL Server Login/Password and it only takes one to have to enable it for the instance.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 17, 2015 at 1:55 am
Yes, it's bad practice. SA is well known, and with the brute-forcing capabilities these days, 15 characters is not a strong password. It's known to be an admin account, it cannot have its permission levels changed, Basically it's the first thing that people will try.
sa should be disabled. I prefer not renaming it, as I've seen that cause problems with upgrades before. But definitely disabled. That way any one trying to brute force can bang their heads against the server from now until eternity if they like, they won't log in with sa.
If you want a SQL authentication admin account, create one with a different name (and not admin/administrator), then disable sa. But it's not really necessary. Under Windows authent you can create an admin account from a local machine login, then it won't matter if AD's down, it's a local machine account. Worst case, if you restart SQL in single user mode, all windows accounts that are local administrator get sysadmin permissions to ensure that you can't be locked out of a server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2015 at 3:32 am
Thanks for your replies. I had never thought of setting up an alternative to "sa". Now that you mention it, it seems very obvious! Will start making that the default approach from now on.
December 18, 2015 at 3:35 am
GilaMonster (12/17/2015)
Worst case, if you restart SQL in single user mode, all windows accounts that are local administrator get sysadmin permissions to ensure that you can't be locked out of a server.
Is this still the case? I know there's a hack to get yourself added to sysadmin role if you have access to the local machine and getting "NT AUTHORITY\SYSTEM" to run a script. I was under the impression local administrators were no longer added as sysadmins by default.
We don't do that during installation be default. A dedicated AD group for DBA's are granted sysadmin access and no one else.
December 18, 2015 at 3:39 am
feersum_endjinn (12/18/2015)
I was under the impression local administrators were no longer added as sysadmins by default.
They're not.
If you restart SQL in single user mode, then, while SQL is in single user mode, any member of the local administrators group has sysadmin access to SQL. This is to ensure that you cannot be locked out of a SQL instance, even if you disable sa and drop the last admin user, there's always a way back in for someone with sufficient permissions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2015 at 4:25 am
OK, I didn't know that. So start in single user mode with: -m"SQLCMD" and using SQLCMD to connect to the instance from the server itself will do the trick?
December 18, 2015 at 4:42 am
The parameter -m starts SQL in single user mode, you can connect with any client tool.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 18, 2015 at 6:38 am
Whether you change the name or not (consider what Gail said about upgrades) disable it and leave it disabled.
I got to see a SQL Saturday presentation on hacking SQL Server once. It was a great presentation, but very scary how easy it was to hack the password. It didn't take long at all to crack and computing power certainly isn't going down. The morale of the story is that if the attacker knows the SQL login name, it isn't safe, so limit your SQL logins and lock them down to the least permissions they require.
December 18, 2015 at 7:00 am
It's not an issue of fashion; there are some scenarios where SQL Server account authentication is required (or at least practical), like when linking to server instances on a different windows server domain.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 18, 2015 at 7:54 am
GilaMonster (12/17/2015)
I prefer not renaming it, as I've seen that cause problems with upgrades before.
I never had it cause problems (with SPs and other patches but never tried a version or edition upgrade) while I was in the habit of renaming sa but once I learned of the possibility I switched my approach by renaming the Login back to sa, creating a new Login with the chosen name in its place and then disabling sa getting the same effect.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 18, 2015 at 8:14 am
Renaming the real 'SA' account, and then creating a new (so called) 'SA' account with least privilege can be useful in those situations where developers and 3rd party tools think they need it. 😉
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 18, 2015 at 8:18 am
Ed Wagner (12/18/2015)
but very scary how easy it was to hack the password. It didn't take long at all to crack and computing power certainly isn't going down.
Just out of interest how long did it take and how long/complex were the passwords. I use LastPass to generate passwords (as I use it for personal use) and this is an example password I'd use for "sa" - [font="Courier New"]BD@g@!fw&t652S6[/font]
The demo you mentioned isn't available to view online is it? 🙂
December 18, 2015 at 8:23 am
So to clarify - advise is to disable built-in "sa" account, setup a replacement for it, call it something not so obvious. I take it that this is done by creating new SQL login (with complex password) and adding it to sysadmin group?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply