December 23, 2015 at 9:44 am
Eric M Russell (12/23/2015)
Indianrock (12/23/2015)
How is SA ( with good password properly managed and not used by applications ) any worse than your own "admin" sysadmin login?Because it can be shared across multiple users.
We know who MYCORP\JoeBlack is, and we know who members of MYCORP\DBAProduction are. We take Joe out the SYSADMIN role or we can add or drop members from the MYCORP\DBAProduction domain group as needed. However, we don't know for sure who has the credentials for the 'SA' account, and when the password changes, we don't know what applications, linked servers, or services need to be updated in tandem with the new 'SA' password, so it's a maintenance nightmare.
Also, developers have this tendency to check check SSIS packages and .config files into source control with the credentials for the 'SA' account in plain text. In an organizations where applications and ETL packages login using 'SA', if you have access to the source control system (which is everyone in IT), then you know how to login to the database server as SYSADMIN. You know you're not supposed to, but if you're determined to do it anyway, the credentials for the 'SA' account are right there.
Another important point about it is that it's a well-known SQL login with sysadmin privs. If an attacker knows a login, they can use it in an attack and gain access to the SQL Server. I've seen it done...quickly. From there, they're in with sysadmin privs and can do whatever they want.
I completely agree with the concept of least privs and not sharing logins that Eric mentioned. However, the attacker doesn't really care. They care about getting at your data. With a known SQL login, they have a known way in. If they learn of another SQL login, they can attack using it instead, but why would they? If they're intent on stealing data or doing damage, the sa login gives them everything they need.
December 23, 2015 at 10:38 am
You can do a brute force dictionary query against the sys.sql_logins table to discover the password for any sqlserver login, including 'SA'.
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;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 23, 2015 at 10:40 am
One way to use the "SA" account is as a last ditch fallback account.
Assign it a large, truly random password, known by the DBA's, and then only use it for agent job and database ownership.
Until AD is having trouble authenticating for whatever reason, and then there's an easy way into the server without having to RDP in and start sql server in single user mode.
December 23, 2015 at 2:16 pm
Here is the way the problem usually develops
1) Developers begin developing an application and use sa on their personal copy of mssql.
2) DBA warns the developers that once they go to production, sa is not an option. Therefore develop and test using limited account.
3) Developers acknowledge DBA's concern and promise to follow his/her decree.
4) Developers go ahead and do all development as sa with "stated intention" to refactor to a limited account at the end of project.
5) Project becomes late and overdue. Developers rush their product into production still requiring sa ownership.
6) DBA attempts to block promotion to production reiterating security requirements agreed to at the beginning of the project.
7) Developers protest that they didn't have time to do this "last step". They go around the DBA and make their case to management who just wants the project completed and the app made available.
8) Management orders DBA to allow sa use "temporarily this one time" to get the app in production and says that developers will refactor as soon as possible.
9) As soon as possible gets forgotten as Developers begin developing the next application and use sa on their personal copy of mssql.
10) Repeat ad nausem.
11) App is compromised and the DBA is called on the carpet for not enforcing principle of least privilege.
MORAL OF THE STORY: Document everything and save the copies somewhere safe.
December 23, 2015 at 2:29 pm
Also, if your company has a security team, make them your friends and allies. They can be incredibly useful in such a scenario.
December 23, 2015 at 2:59 pm
Ed Wagner (12/23/2015)
Another important point about it is that it's a well-known SQL login with sysadmin privs. If an attacker knows a login, they can use it in an attack and gain access to the SQL Server. I've seen it done...quickly. From there, they're in with sysadmin privs and can do whatever they want.I completely agree with the concept of least privs and not sharing logins that Eric mentioned. However, the attacker doesn't really care. They care about getting at your data. With a known SQL login, they have a known way in. If they learn of another SQL login, they can attack using it instead, but why would they? If they're intent on stealing data or doing damage, the sa login gives them everything they need.
Have you ever seen it done quickly with a password like
oG7uzpf_!cIm^$?y7YlTrOp~glIK1alrUp!wT=!:8wYQe2ywI*!.sa)QVyQaryBLC]t5wxOaH-=F|z=RKoh|n5}uTO2xGRe:o8/]nbq\\TENQvSQlbwD@]LR|Ls5={mt
instead of passwords like the ones in Eric's example?
As with everything, if you share the sa password with anyone, it's bad. If you share DOMAIN\DBA's password, it's equally bad.
Likewise, if a DBA allows a developer's application to simply change their connection string to use sa with a different password, rather than change to a new username and password and simply experiment with permissions, that's bad - I've seen only a bare handful of applications that need more than db_datareader + db_datawriter + db_ddladmin + EXECUTE database level rights. Almost all of those that need more are satisfied with db_owner. Only a rare few (including a prominent financial app) require true SA level permissions; letting those share a SQL instance with any other apps, well, again, it's bad.
December 23, 2015 at 3:54 pm
loyd.gravitt (12/23/2015)
Here is the way the problem usually develops1) Developers begin developing an application and use sa on their personal copy of mssql.
2) DBA warns the developers that once they go to production, sa is not an option. Therefore develop and test using limited account.
3) Developers acknowledge DBA's concern and promise to follow his/her decree.
4) Developers go ahead and do all development as sa with "stated intention" to refactor to a limited account at the end of project.
5) Project becomes late and overdue. Developers rush their product into production still requiring sa ownership.
6) DBA attempts to block promotion to production reiterating security requirements agreed to at the beginning of the project.
7) Developers protest that they didn't have time to do this "last step". They go around the DBA and make their case to management who just wants the project completed and the app made available.
8) Management orders DBA to allow sa use "temporarily this one time" to get the app in production and says that developers will refactor as soon as possible.
9) As soon as possible gets forgotten as Developers begin developing the next application and use sa on their personal copy of mssql.
10) Repeat ad nausem.
11) App is compromised and the DBA is called on the carpet for not enforcing principle of least privilege.
MORAL OF THE STORY: Document everything and save the copies somewhere safe.
The other option here, which is also what happens in vendor companies, is that the developer doesn't understand how security works or why it doesn't. They start by switching to trying sa during development and when things work, they move on rather than learning.
December 24, 2015 at 12:11 am
Steve Jones - SSC Editor (12/23/2015)
loyd.gravitt (12/23/2015)
Here is the way the problem usually develops1) Developers begin developing an application and use sa on their personal copy of mssql.
2) DBA warns the developers that once they go to production, sa is not an option. Therefore develop and test using limited account.
3) Developers acknowledge DBA's concern and promise to follow his/her decree.
4) Developers go ahead and do all development as sa with "stated intention" to refactor to a limited account at the end of project.
5) Project becomes late and overdue. Developers rush their product into production still requiring sa ownership.
6) DBA attempts to block promotion to production reiterating security requirements agreed to at the beginning of the project.
7) Developers protest that they didn't have time to do this "last step". They go around the DBA and make their case to management who just wants the project completed and the app made available.
8) Management orders DBA to allow sa use "temporarily this one time" to get the app in production and says that developers will refactor as soon as possible.
9) As soon as possible gets forgotten as Developers begin developing the next application and use sa on their personal copy of mssql.
10) Repeat ad nausem.
11) App is compromised and the DBA is called on the carpet for not enforcing principle of least privilege.
MORAL OF THE STORY: Document everything and save the copies somewhere safe.
The other option here, which is also what happens in vendor companies, is that the developer doesn't understand how security works or why it doesn't. They start by switching to trying sa during development and when things work, they move on rather than learning.
Briefly putting a dev hat on, part of the issue is that the security responses from the server are not very useful. Naturally on a production server you wouldn't want possibly privileged information returned but for a development setup a response indicating not enough security rights for a given option and hinting (or detailing even) what is needed would help considerably. Not for all developers of course, as many don't seem to be aware of the concept of error handling or graceful failure, but just some help would be good. It would help to instil the concept of security in depth starting with minimal rights.
December 24, 2015 at 4:03 am
Ivanova (12/21/2015)
Slightly off-topic; while working for a large financial institution, I was approached by one of the security team. He was a highly-paid and allegedly very security-savvy contractor. His request (although phrased more as a demand) was for me to give sysadmin rights on most of our production instances to the penetration testers coming on-site as part of his project.It's fair to say that he was disappointed, and also that he didn't handle disappointment particularly well.
I have a mate who is now retired, but was a very highly qualified and regarded security consultant (CLAS), who would actually ask questions of that type. Yours being the only acceptable response.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
December 24, 2015 at 4:41 am
Indianrock (12/23/2015)
...I'll have to test if a disabled SA account can actually own and run sql agent jobs. If so I don't see why SA couldn't be disabled.
This works fine, in fact I'm pretty sure running Windows only actually does this under the hood (but please don't bet the life of a loved one on that). But where we have to have mixed authentication, this is exactly what we do in our shop. SA gets a long, complex password, then gets disabled and jobs run no problem
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
December 24, 2015 at 8:04 am
Ivanova (12/21/2015)
Slightly off-topic; while working for a large financial institution, I was approached by one of the security team. He was a highly-paid and allegedly very security-savvy contractor. His request (although phrased more as a demand) was for me to give sysadmin rights on most of our production instances to the penetration testers coming on-site as part of his project.It's fair to say that he was disappointed, and also that he didn't handle disappointment particularly well.
I know of a 3rd party penetration testing company that will ask for elevated privileges and mark you down as a fail if you comply with their request. I LOVE those guys:-D
The best thing to do is sit down with your security guys and penetration testers to agree the scope of the test and what they are trying to prove.
Make sure that the 3rd party has signed an non-disclosure agreement and that it has also been signed by a suitable authority in your company. Don't be afraid to insist on being shown a copy.
If the 3rd party are attempting to simulate an external attack then they don't need any specific privileges.
If they are trying to find out what damage an internal user could do then they will be need to cycle through the various roles and/or users to get this info. It's actually very useful info (ammunition) to find out internal users can do stuff they shouldn't be able to.
Depending on what you are paying for a 3rd party may require limited privileges. If you want a full warts and all report then they will need broader privileges.
The best of them will actually pair up with you and talk you through what they are doing. It can be both educational and scary at the same time.
December 24, 2015 at 1:08 pm
andrew gothard (12/24/2015)
Ivanova (12/21/2015)
Slightly off-topic; while working for a large financial institution, I was approached by one of the security team. He was a highly-paid and allegedly very security-savvy contractor. His request (although phrased more as a demand) was for me to give sysadmin rights on most of our production instances to the penetration testers coming on-site as part of his project.It's fair to say that he was disappointed, and also that he didn't handle disappointment particularly well.
I have a mate who is now retired, but was a very highly qualified and regarded security consultant (CLAS), who would actually ask questions of that type. Yours being the only acceptable response.
Glad I passed 😛
December 24, 2015 at 1:19 pm
Another experience of 'sa abuse' was when we upgraded a certain vendor product at the same company. Its previous incarnation (on SQL 2000) required a management-level user to be given sa rights whenever they needed give a new staff member access to the product! Needless to say, it was detested by the DBAs, whose management had leaned on them to do whatever the customer needs..
Fast forward a number of years and we were installing the latest version with a technical consultant from the vendor company on-site. It, of course, required sa rights for the installation. I asked the consultant why, and he admitted he had no idea, so we installed it on a test instance while I ran a SQL trace. I then told him what it actually did and needed. The other major area I insisted we look at was the way they had implemented user maintenance. To lock it down, we implemented AD groups (of course) and I got the consultant's agreement to let me rewrite the part of the vendor's code which dealt with logon creation and deletion in our particular circumstances.
The guy was bemused about the security-related questions I asked and said at the time that no client had ever asked them so many questions. Most just installed the product as seen. As the product was only sold to financial institutions, this did not strike me as a good thing.
I felt completely vindicated a few months later when the same consultant emailed me to say they were now implementing the product for a US bank and their DBAs were asking the same questions as I had asked him. And he had the right answers!
Result: a more educated vendor and ultimately let's hope a more secure product.
December 25, 2015 at 9:16 am
Steve Jones - SSC Editor (12/23/2015)
loyd.gravitt (12/23/2015)
Here is the way the problem usually develops1) Developers begin developing an application and use sa on their personal copy of mssql.
2) DBA warns the developers that once they go to production, sa is not an option. Therefore develop and test using limited account.
3) Developers acknowledge DBA's concern and promise to follow his/her decree.
4) Developers go ahead and do all development as sa with "stated intention" to refactor to a limited account at the end of project.
5) Project becomes late and overdue. Developers rush their product into production still requiring sa ownership.
6) DBA attempts to block promotion to production reiterating security requirements agreed to at the beginning of the project.
7) Developers protest that they didn't have time to do this "last step". They go around the DBA and make their case to management who just wants the project completed and the app made available.
8) Management orders DBA to allow sa use "temporarily this one time" to get the app in production and says that developers will refactor as soon as possible.
9) As soon as possible gets forgotten as Developers begin developing the next application and use sa on their personal copy of mssql.
10) Repeat ad nausem.
11) App is compromised and the DBA is called on the carpet for not enforcing principle of least privilege.
MORAL OF THE STORY: Document everything and save the copies somewhere safe.
The other option here, which is also what happens in vendor companies, is that the developer doesn't understand how security works or why it doesn't. They start by switching to trying sa during development and when things work, they move on rather than learning.
The other option is to not install local SQL Server instances on the workstations and have the developers do their development on the development server. Of course, since the development server is under the authority of the DBA, the developers won't be able to even start out using the sa login because it's disabled. The rest of the steps (which are very insightful, BTW) become irrelevant.
December 25, 2015 at 9:22 am
Ivanova (12/23/2015)
Also, if your company has a security team, make them your friends and allies. They can be incredibly useful in such a scenario.
That's a huge benefit. If you get the team to agree (or even just a couple of key people in positions of authority) to agree to certain things, then you can write policy. When people "go rogue" and ignore policy, you can always lean on the published policy. The end result is that you don't have to make exceptions every time and your whole environment is more secure.
Viewing 15 posts - 76 through 90 (of 95 total)
You must be logged in to reply to this topic. Login to reply