Protecting sa

Question

Protecting sa

Viewing 15 posts - 31 through 45 (of 48 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

ZZartin (3/28/2016)
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?

I saw an excellent SQL Saturday presentation by Dustin Prescott called "Hacking SQL Server: A Peek Into the Dark Side" where he used automated tools to attack SQL Server over it's listening port and brute-force the password. He used SA because it's well-known. Once the network was found, one tool found the SQL Server. Another tool determined the custom port on which SQL Server was listening. Another tool ran until it cracked the password, which was not short. It was very straightforward and completely skipped the normal SQL Server tools. They key is to use a known SQL login that's enabled.

My takeaway (from the presentation and the discussion afterwards) was to simply disable the SA login. You can't enumerate the logins without being authenticated and authorized, and authentication can't happen unless you crack a login.

Is there a way I don't know about to prevent a brute-force on a login?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Steve Jones - SSC Editor (3/28/2016)
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.

What type of network controls? Yes, I'm intrigued.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Eric M Russell SSC Guru Points: 125560 More actions · Answer 3

Speaking of brute forcing SQL Server account logins, it can be easily done by querying the sys.sql_logins table and leveraging the pwdcompare function to join the pwtext hash column against a password dictionary table. For the dictionary, you can find online lists of TOP 10,000 most common passwords and then unionize that set with a derived table consisting of instance user names (sys.server_principals) catesian joined with all alpha-numeric combinations of XXXX, which can be done overnight. This is just a query against sys.sql_logins, not an actual authentication attempt, so it can make an unlimited number of attempts at the speed of 100,000 joins per second.

declare @PW table (pwtext varchar(180) not null primary key);

insert into @PW (pwtext)

values ('password'), ('123456'), ('12345678'), ('1234'), ('qwerty'), ('12345');

select l.name, pw.pwtext

from sys.sql_logins l

join @PW pw on pwdcompare(pw.pwtext, l.password_hash) = 1;

Permissions: Any SQL Server authentication login can see their own login name, and the sa login. To see other logins, requires ALTER ANY LOGIN, or a permission on the login.

https://msdn.microsoft.com/en-us/library/ms174355.aspx

Give it a try.

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

Eric M Russell SSC Guru Points: 125560 More actions · Answer 4

null

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

Eric M Russell SSC Guru Points: 125560 More actions · Answer 5

null

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

Jeff Moden SSC Guru Points: 1004301 More actions · Answer 6

Steve Jones - SSC Editor (3/28/2016)
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.

Heh... not true. 😉 At least not on SQL Server 2005. We just went through some massive and incredibly penetration testing and SA was locked out because of it on every machine that the penetration testing was done on.

I can't wait to see what happens on the 2012 boxes next year. I hope it DOES do a lockout because that's a great safety feature.

--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)

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Ed Wagner (3/28/2016)
ZZartin (3/28/2016)
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?
I saw an excellent SQL Saturday presentation by Dustin Prescott called "Hacking SQL Server: A Peek Into the Dark Side" where he used automated tools to attack SQL Server over it's listening port and brute-force the password. He used SA because it's well-known. Once the network was found, one tool found the SQL Server. Another tool determined the custom port on which SQL Server was listening. Another tool ran until it cracked the password, which was not short. It was very straightforward and completely skipped the normal SQL Server tools. They key is to use a known SQL login that's enabled.
My takeaway (from the presentation and the discussion afterwards) was to simply disable the SA login. You can't enumerate the logins without being authenticated and authorized, and authentication can't happen unless you crack a login.
Is there a way I don't know about to prevent a brute-force on a login?

A tool that easily discovers the info on SQL Server is nmap. Use it all the time. It is considered a hacking tool, so don't use without proper authorization.

As far as preventing brute force on the login, not sa. Not if it's enabled. At least, not through SQL Server. Most network IPS products should detect the brute force attempts and be able to shut down the connection. This is a case where the best way to stop it is to simply prevent the traffic from ever getting there.

K. Brian Kelley
@kbriankelley

Ed Wagner SSC Guru Points: 287026 More actions · Answer 8

K. Brian Kelley (3/28/2016)
Ed Wagner (3/28/2016)
ZZartin (3/28/2016)
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?
I saw an excellent SQL Saturday presentation by Dustin Prescott called "Hacking SQL Server: A Peek Into the Dark Side" where he used automated tools to attack SQL Server over it's listening port and brute-force the password. He used SA because it's well-known. Once the network was found, one tool found the SQL Server. Another tool determined the custom port on which SQL Server was listening. Another tool ran until it cracked the password, which was not short. It was very straightforward and completely skipped the normal SQL Server tools. They key is to use a known SQL login that's enabled.
My takeaway (from the presentation and the discussion afterwards) was to simply disable the SA login. You can't enumerate the logins without being authenticated and authorized, and authentication can't happen unless you crack a login.
Is there a way I don't know about to prevent a brute-force on a login?
A tool that easily discovers the info on SQL Server is nmap. Use it all the time. It is considered a hacking tool, so don't use without proper authorization.
As far as preventing brute force on the login, not sa. Not if it's enabled. At least, not through SQL Server. Most network IPS products should detect the brute force attempts and be able to shut down the connection. This is a case where the best way to stop it is to simply prevent the traffic from ever getting there.

Thank you, Brian. I'm glad there wasn't something simple I was missing.

I agree that it's best if the IPS do it's job, but I don't want to rely on it. Disabling the login precludes the target from being hit because no shots can ever reach it. Layers of defense is, IMHO, the best approach.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Eric M Russell SSC Guru Points: 125560 More actions · Answer 9

It should be considered best practice to drop the 'SA' account. However, I still create a MSSQL authenticated account with SYSADMIN membership, because there are a handful of occasions where it's needed, like when AD or the domain controller is malfunctioning.

The method I described above to brute force sys.syslogins only works for 'SA' specifically. If you create a differently named SYSADMIN account, then it's invisible to users with elevated privillages. For example:

CREATE LOGIN MySA WITH PASSWORD = 'fsdj5nsrfsdfdsF4SnEgaSSXBcK7W5myATJ6hb6IfuM';

ALTER SERVER ROLE sysadmin ADD MEMBER MySA;

Why SQL Server would allow any MSSQL authenticated user to see (and query) 'SA' is a mystery, because there is no normal functional dependency on it.

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

eric.merrill SSC Rookie Points: 38 More actions · Answer 10

A few years ago as part of our DOD audit we had to disable the account. As a secondary check we renamed it as well although we discovered that during our SQL upgrade that account needs to be set to 'sa' and enabled. It seems that account is used to execute some scripts as part of the upgrade. Now this was back in the SQL 2008 - 2012 upgrades so I'm not sure if it is still the case with 2014. Worse part of it was the upgrade got to about 90% complete till it rolled back with an error indicated an sa account problem.

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 11

Ed Wagner (3/28/2016)
Steve Jones - SSC Editor (3/28/2016)
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.
What type of network controls? Yes, I'm intrigued.

Firewalling to limit access, potential black lists to prevent 1433 (or whatever port) access for TDS, limits to shut down an IP (or range) if you detect an attack

Steve Jones - SSC Editor SSC Guru Points: 736105 More actions · Answer 12

Jeff Moden (3/28/2016)
Steve Jones - SSC Editor (3/28/2016)
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.
Heh... not true. 😉 At least not on SQL Server 2005. We just went through some massive and incredibly penetration testing and SA was locked out because of it on every machine that the penetration testing was done on.
I can't wait to see what happens on the 2012 boxes next year. I hope it DOES do a lockout because that's a great safety feature.

Ah, you are correct and I'm wrong. This was changed in 2005. sa should follow local account lockout policy if that's checked.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 13

eric.merrill (3/28/2016)
A few years ago as part of our DOD audit we had to disable the account. As a secondary check we renamed it as well although we discovered that during our SQL upgrade that account needs to be set to 'sa' and enabled. It seems that account is used to execute some scripts as part of the upgrade. Now this was back in the SQL 2008 - 2012 upgrades so I'm not sure if it is still the case with 2014. Worse part of it was the upgrade got to about 90% complete till it rolled back with an error indicated an sa account problem.

I've heard of this exact problem before. It's a known problem of disabling and renaming the sa login. It isn't difficult to restore the name and enable it if you know about it ahead of time. I don't think it would be that hard to do a check when checking the requirements to upgrade.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

m_swetz Ten Centuries Points: 1069 More actions · Answer 14

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.

I would like to see that.

mrj8115 Grasshopper Points: 20 More actions · Answer 15

I require a minimum of 16 characters for the password including uppercase and special characters. Then we disable the account. A daily security report is run which includes the status of the sa account.