SQL security over the LAN
-
so more digging. I have found both the SA password and my application password being passed encrypted, I did not find any clear text versions of those. Man, there is a lot of extra junk that goes over the wire. Simple actions in the application generate a lot of requests to SQL, for stuff that isnt even related to the request. Other times, it get a lot of data that may be too much from a security standpoint. When I open one of my timecards, it pulls a list of every employee for example. It probably does this to cache it or something, but that seems unnecessary.
I tried several things to try and see if I could gain confidential information based just on what was already traveling over the wire. There is some good news and bad news. The good news seems to be that it appears like all numeric or date type fields are obscured or encrypted in some way. I see a lot of "dummyTS" and "dummy textptr" where the results from those columns should be. This means that when I look up things like payroll, I cannot see actual amounts. But the bad news is that all string types look to be sent in the clear, so with the payroll example, I would be able to see if someone had to pay child support, just not how much. It got worse when I checked my employee record. I can see basically all of my personal information include SSN and full bank details because we use optional direct deposit.
-
@Donahue said in SQL security over the LAN:
so more digging. I have found both the SA password and my application password being passed encrypted, I did not find any clear text versions of those. Man, there is a lot of extra junk that goes over the wire. Simple actions in the application generate a lot of requests to SQL, for stuff that isnt even related to the request. Other times, it get a lot of data that may be too much from a security standpoint. When I open one of my timecards, it pulls a list of every employee for example. It probably does this to cache it or something, but that seems unnecessary.
I tried several things to try and see if I could gain confidential information based just on what was already traveling over the wire. There is some good news and bad news. The good news seems to be that it appears like all numeric or date type fields are obscured or encrypted in some way. I see a lot of "dummyTS" and "dummy textptr" where the results from those columns should be. This means that when I look up things like payroll, I cannot see actual amounts. But the bad news is that all string types look to be sent in the clear, so with the payroll example, I would be able to see if someone had to pay child support, just not how much. It got worse when I checked my employee record. I can see basically all of my personal information include SSN and full bank details because we use optional direct deposit.
Wow, that's still so very not good.
Is anyone really surprised that they're incompetent writing SQL Queries as well?
-
I don't know this this is to be expected, but a lot of the traffic is also smb2
-
@Donahue said in SQL security over the LAN:
I don't know this this is to be expected, but a lot of the traffic is also smb2
Since SQL Server 2008 you can use SQL over SMB2 rather than just TCP/IP or named pipes or shared memory. So I imagine that's how they're doing it, seems like needless overhead but based on everything else that's to be expected.
-
@tonyshowoff said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
I don't know this this is to be expected, but a lot of the traffic is also smb2
Since SQL Server 2008 you can use SQL over SMB2 rather than just TCP/IP or named pipes or shared memory. So I imagine that's how they're doing it, seems like needless overhead but based on everything else that's to be expected.
Curious - why would you want to do it over SMB?
-
@Dashrender said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
I don't know this this is to be expected, but a lot of the traffic is also smb2
Since SQL Server 2008 you can use SQL over SMB2 rather than just TCP/IP or named pipes or shared memory. So I imagine that's how they're doing it, seems like needless overhead but based on everything else that's to be expected.
Curious - why would you want to do it over SMB?
It's sort of pseudo-configureless, you need not worry about ports or IPs and just go by name. The other side of that is you have to deal with SMB locking and other problems and it slows things down sometimes significantly.
-
@tonyshowoff said in SQL security over the LAN:
@Dashrender said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
I don't know this this is to be expected, but a lot of the traffic is also smb2
Since SQL Server 2008 you can use SQL over SMB2 rather than just TCP/IP or named pipes or shared memory. So I imagine that's how they're doing it, seems like needless overhead but based on everything else that's to be expected.
Curious - why would you want to do it over SMB?
It's sort of pseudo-configureless, you need not worry about ports or IPs and just go by name. The other side of that is you have to deal with SMB locking and other problems and it slows things down sometimes significantly.
plus, it probably ties the customer in tighter into the MS ecosystem.
-
@Donahue said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@Dashrender said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
I don't know this this is to be expected, but a lot of the traffic is also smb2
Since SQL Server 2008 you can use SQL over SMB2 rather than just TCP/IP or named pipes or shared memory. So I imagine that's how they're doing it, seems like needless overhead but based on everything else that's to be expected.
Curious - why would you want to do it over SMB?
It's sort of pseudo-configureless, you need not worry about ports or IPs and just go by name. The other side of that is you have to deal with SMB locking and other problems and it slows things down sometimes significantly.
plus, it probably ties the customer in tighter into the MS ecosystem.
Exactly, same reason they have all that COM+ garbage and other things that just add layers and layers of complexity to things. Ever wonder why Exchange Server and AD are such a bitch to get working and their clones aren't? Part of the justification for all these extra products is Microsoft tries to put them to use in their own, making them more and more bloated and difficult to get working. They've started to fix this in recent years by abandoning their own product lines and just doing things a more proper way. But if you installed Exchange 2003 or 2007 and had issues with it or with AD around that time, and all the tons of countless tools, scripts, etc you had to use, you'd get why these things happen to Windows but not as often on the same protocols hosted elsewhere.
-
Ok, so I have this vulnerability. Short of stopping use of this application, what can be done to mitigate the risk this presents?
-
@Donahue said in SQL security over the LAN:
Ok, so I have this vulnerability. Short of stopping use of this application, what can be done to mitigate the risk this presents?
Not if the application does not have it built in, such as TLS/SSL connections. There are way to mitigate it over the network such as a tunnel between the client and server, but on the client there's no defence at all, and that's really your vulnerable part.
-
Setup a TS and run the app from there. RDP into TS.
-
@Dashrender said in SQL security over the LAN:
Setup a TS and run the app from there. RDP into TS.
That doesn't fix the problem because the weak point is the client, it would just be moving the problem to another place, in fact it may be worse because then they could see the traffic of all the clients in a single place.
-
@tonyshowoff said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
Ok, so I have this vulnerability. Short of stopping use of this application, what can be done to mitigate the risk this presents?
Not if the application does not have it built in, such as TLS/SSL connections. There are way to mitigate it over the network such as a tunnel between the client and server, but on the client there's no defence at all, and that's really your vulnerable part.
that's what I thought.
I'm trying SQL injections now
-
@Dashrender said in SQL security over the LAN:
Setup a TS and run the app from there. RDP into TS.
we already do that for half the users.
-
@tonyshowoff said in SQL security over the LAN:
@Dashrender said in SQL security over the LAN:
Setup a TS and run the app from there. RDP into TS.
That doesn't fix the problem because the weak point is the client, it would just be moving the problem to another place, in fact it may be worse because then they could see the traffic of all the clients in a single place.
Right so keep that on its own network and only allow rdp traffic through to that network.
-
SQL Server Configuration Manager – SQL Server Network Configuration – right click “Protocols for ...” - Properties – Under the Flags tab, set Force Encryption to Yes
MS SQL Server will then use a self-signed certificate for encrypting communications. SQL clients have supported encryption for ages, so whatever they're using for the client will probably just work with it.
If you want to use your own trusted certificate, you have to set the SQL clients on each machine to force encryption and to only trust trusted certificates
-
And I doubt that the account being used actually has to be SA, but it would still have to use an account with full permissions on the database
-
Also using an encrypted password in the ini file is at least better than using Windows Authentication and each user actually having full permission on the database and could alter data with with a connection to SQL outside of the application
-
@Dashrender said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@Dashrender said in SQL security over the LAN:
Setup a TS and run the app from there. RDP into TS.
That doesn't fix the problem because the weak point is the client, it would just be moving the problem to another place, in fact it may be worse because then they could see the traffic of all the clients in a single place.
Right so keep that on its own network and only allow rdp traffic through to that network.
That's still the same attack vector. If the client is on RDP and you watch within the RDP session, it doesn't matter if it's separate or not. In fact, as I said, it widens the amount of traffic you can listen to because you'll be able to spy on all clients on that RDP server.
-
@flaxking That may work and is worth a try, but it's likely not to work because the client is passing along to SQL Server and it's not known whether or not they implemented, or allow, encrypted traffic within their SQL Server connection library. Even if implemented in the library, it doesn't mean the client allows it, and even may be intentionally disabled for God only knows what reason. It isn't an SQL client, it's an application which just connects to SQL Server or passes raw SQL along to an application server to avoid client connection licensing limits.
