SQL security over the LAN
-
I am trying to figure out if my setup is less than ideal security wise. I am gathering information to make a case that our ERP must evolve, or we must move on.
I am specifically looking at security from the point of view of a rogue device that is already on my LAN. My ERP application, being a legacy 2 tier design, has a direct connection from every client straight to the SQL database, going over the LAN. All the clients connect using the SA account, and all security and permissions are handled inside the application. What I am wanting to know is if the traffic being sent to and from the database is able to be sniffed and if this vulnerability would go away if we had a more modern ERP. What are the chances that this is all being sent in the clear, over the wire?
I am not well versed in wireshark, so I am not sure how to measure this directly yet. Is this a known thing that DBA's talk about and discuss, or am I making bad assumptions?
-
@Donahue said in SQL security over the LAN:
All the clients connect using the SA account,
WOW - do you have to give the user the SA account or do you get to enter it once and the app remembers it? In either case that seems really crazy demand - from this outsider who completely and totally admits to not understanding how security works in that situation.
-
The user never sees this, they only see their application specific login info. Everything is all setup from the initial install. But the application uses the SA account for all it's communications with SQL under the hood.
-
@Donahue said in SQL security over the LAN:
The user never sees this, they only see their application specific login info. Everything is all setup from the initial install. But the application uses the SA account for all it's communications with SQL under the hood.
That's still not how it works. If they missed just one place where someone could inject code..... your boned.
-
@travisdh1 said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
The user never sees this, they only see their application specific login info. Everything is all setup from the initial install. But the application uses the SA account for all it's communications with SQL under the hood.
That's still not how it works. If they missed just one place where someone could inject code..... your boned.
Yeah - one area where they don't check the data - someone could field input drop table and bye bye.
-
@Donahue said in SQL security over the LAN:
What I am wanting to know is if the traffic being sent to and from the database is able to be sniffed ....
By default, it's wide open. There's no "intended production-quality use case" that would allow connections like this, so it isn't the database's job to provide any protection. The connections are supposed to be behind a layer of security on an isolated channel (client / server apps don't really allow that.)
If your stuff is up to date enough and you control enough of the settings, you can enable encryption for this traffic. But the app has to support it.
-
@Dashrender said in SQL security over the LAN:
@travisdh1 said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
The user never sees this, they only see their application specific login info. Everything is all setup from the initial install. But the application uses the SA account for all it's communications with SQL under the hood.
That's still not how it works. If they missed just one place where someone could inject code..... your boned.
Yeah - one area where they don't check the data - someone could field input drop table and bye bye.
yeah, I would be surprised if there was any santiation going on within the program. I've never seen any yet.
-
@scottalanmiller said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
What I am wanting to know is if the traffic being sent to and from the database is able to be sniffed ....
By default, it's wide open. There's no "intended production-quality use case" that would allow connections like this, so it isn't the database's job to provide any protection. The connections are supposed to be behind a layer of security on an isolated channel (client / server apps don't really allow that.)
If your stuff is up to date enough and you control enough of the settings, you can enable encryption for this traffic. But the app has to support it.
Where is the app in this case? Is it the thing installed on the client machines? or is there an application also on the DB server, or some other server sitting in front of the DB server?
-
@travisdh1 said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
The user never sees this, they only see their application specific login info. Everything is all setup from the initial install. But the application uses the SA account for all it's communications with SQL under the hood.
That's still not how it works. If they missed just one place where someone could inject code..... your boned.
Yes this is a possible f****ing disaster.
-
I would assume the connection is encrypted, but with such poor database practice, who knows.
How does the client store the SA account information?
-
@Donahue said in SQL security over the LAN:
The user never sees this, they only see their application specific login info. Everything is all setup from the initial install. But the application uses the SA account for all it's communications with SQL under the hood.
A risk there is that just getting access to the end user equipment puts the creds at risk. Pop out one of the drives, read the connection details, and now you have unfettered access to everything in the database from anywhere.
-
@IRJ said in SQL security over the LAN:
I would assume the connection is encrypted, but with such poor database practice, who knows.
How does the client store the SA account information?
I would assume not, if this was good devs doing a good job, sure, I'd expect that it was. But this is so not that.
-
@Dashrender said in SQL security over the LAN:
@scottalanmiller said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
What I am wanting to know is if the traffic being sent to and from the database is able to be sniffed ....
By default, it's wide open. There's no "intended production-quality use case" that would allow connections like this, so it isn't the database's job to provide any protection. The connections are supposed to be behind a layer of security on an isolated channel (client / server apps don't really allow that.)
If your stuff is up to date enough and you control enough of the settings, you can enable encryption for this traffic. But the app has to support it.
Where is the app in this case? Is it the thing installed on the client machines? or is there an application also on the DB server, or some other server sitting in front of the DB server?
Client / Server (aka two tier) means "fat client", the app sits on the end user machines, only a database connects them together. If the app sat in front of the DB, it wouldn't be client/server.
-
@Donahue said in SQL security over the LAN:
@Dashrender said in SQL security over the LAN:
@travisdh1 said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
The user never sees this, they only see their application specific login info. Everything is all setup from the initial install. But the application uses the SA account for all it's communications with SQL under the hood.
That's still not how it works. If they missed just one place where someone could inject code..... your boned.
Yeah - one area where they don't check the data - someone could field input drop table and bye bye.
yeah, I would be surprised if there was any santiation going on within the program. I've never seen any yet.
That suggests that anyone can inject code with unlimited authority. Basically, everyone runs as the admin, all the time. Anything that can read the disk can act as the admin.
-
@Donahue said in SQL security over the LAN:
... and if this vulnerability would go away if we had a more modern ERP.
Correct, in modern systems (n-tier design), as long as you do the most minimum of standard practices like using usernames, passwords, and SSL (HTTPS) you have none of these risks. Standard modern design mitigates that particular, enourmous risk naturally and completely. It's simply not something someone should ever have to worry about today (or, for about 20 years now.)
-
Here is what a focused attack on the network would look like...
- Get to any fat client device physically (or remotely if you can get in that way.)
- Read the connection details for ODBC.
- Set up another piece of software that connects to the database from anywhere on the network, doesn't have to be one of the pre-assigned fat clients.
- Do literally anything you want, you have unlimited power at this point. No security restrictions, no limits, no nothing. They can do anything from dumping the entire database, modifying it, deleting it, corrupting it, slowing it, etc.
-
Things that either are wrong, or are likely wrong...
- Fundamental design. Client server is a ridiculous way to be making, or to have been making software.
- Using shared accounts to connect to the database. Modern database systems have addressed this and this shouldn't be how this is working.
- Using the SA account rather than a limited use account.
- Encrypted communications end to end for ODBC (this might exist, but seems very unlikely.)
It's a lot for them to have gotten wrong. This is all stuff that anyone building software like this should have known from day one not to do. None of these mistakes are acceptable for the vendor to have made in isolation, let alone to have compounded, let alone to have kept after decades of running this software.
-
I couldn't imagine it not being encrypted connection using SA account. If it truly is an unencrypted connection with admin credentials it's unfathomable even for 20 years ago.
Either way using a service account with no accountability is fucking train wreck. Using SA account as a service account is 10x worse.
Honestly, I'd document everything and bring it to senior management. Because if it's really unencrypted using the SA account it is beyond reckless. C levels could actually be held personally liable in a breach if any PII is stolen.
-
@IRJ said in SQL security over the LAN:
I couldn't imagine it not being encrypted connection using SA account. If it truly is an unencrypted connection with admin credentials it's unfathomable even for 20 years ago.
That's the point, though, everything else matches "unfathomable for twenty years ago." That the SA account is used, that it is shared, that it is client/server... all match unencrypted.
-
@IRJ said in SQL security over the LAN:
Honestly, I'd document everything and bring it to senior management. Because if it's really unencrypted using the SA account it is beyond reckless. C levels could actually be held personally liable in a breach if any PII is stolen.
Easily. I don't think that they have any PII there, so it's not likely that kind of risk. But if that kind of stuff was stored in there, yeah, it would be quite the risk to personal liability.
I think in this case, the risk is just having corporate financials or details stolen and/or a competitor use it to disrupt operations. Or, of course, makes it trivial for an employee to steal or damage the company with no way to trace back who did it as the connections are anonymous.
