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.
-
@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.
-
Of all of the things, I'd say that the encryption is actually of the second least concern. Technically, the client/server architecture is of least concern. The biggest item, but if you wanted to do client server well, you can (within reason.) You can secure it, make it work, even make it fast. It's basically like a SAN... if you have the resources and really know what you are doing, you can make it work. But if you don't, you will screw yourself.
Getting "some" data in flight by tapping the network is possible, but it's not in any way the big concern. It's the concern that scares managers, and it's a real concern, but it's trivial compared to the shared creds and the lack of access control. Those are the things that expose the entire system to not just theft, but also to tampering.
On a scale of 0-10 of security concern. I'd put the c/s at a 2, the encryption at a 7, the completely absence of access control at a full 10.
-
what I know for sure:
- An encrypted version of the SA password is stored on a .ini file that must be able to be read by client applications, which means that any user can at least see the file and open it, since the client application runs under the windows user account.
- The SQL instance name is in the same ini file. I dont know if this hurts the situation or is non issue.
- All the changes that are made within the application record the application user for auditing, but that field remains blank if I edit the database directly. Any malicious activity would have no way of tracing who did it, because it would only show the SA account.
- There is an application on the database server, but I suspect that it is just the master copy of the client application, and not doing any real work.
- I've just confirmed that the ODBC connection is not encrypted currently. I would assume this means that the queries and results from queries are sent in plain text over the wire?
- We don't store any customer related PII other than things like phone numbers and email addresses, but we do have HR records that have some information for employees.
-
well hell. I fired up wireshark and I can see the full queries, returns, and everything else in plain text. I cannot seem to find the user name or password, but everything else is there.
-
@Donahue said in SQL security over the LAN:
well hell. I fired up wireshark and I can see the full queries, returns, and everything else in plain text. I cannot seem to find the user name or password, but everything else is there.
No surprise. Sad. But not a surprise.
