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.
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.
-
The AD authentication process is secure... Kerberos and all that which MS SQL server can use. After that point I don't know if there is a maintained secure channel from the authenticated app to the SQL server.
-
@Obsolesce said in SQL security over the LAN:
The AD authentication process is secure... Kerberos and all that which MS SQL server can use. After that point I don't know if there is a maintained secure channel from the authenticated app to the SQL server.
Not if you don't use one.
-
@tonyshowoff said in SQL security over the LAN:
@Obsolesce said in SQL security over the LAN:
The AD authentication process is secure... Kerberos and all that which MS SQL server can use. After that point I don't know if there is a maintained secure channel from the authenticated app to the SQL server.
Not if you don't use one.
One of what? AD? MS SQL? Authenticated app?
I didn't read all 50 replies yet.
-
After that point I don't know if there is a maintained secure channel from the authenticated app to the SQL server.
Not if you don't use one of those. Having the option doesn't mean it's setup automatically, which I imagine you know, but I'm saying it rhetorically to point out the unfortunate situation.
-
@tonyshowoff said in SQL security over the LAN:
@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.
How would that avoid licencing? The MS SQL licencing doesn't care how a user connects, you have to get CALs for the actual users using it no matter the method used. (Unless using SQL Express)
-
I think a lot of the aspects of this application are catering to customers that may only use SQL express. i've been looking, but I have not found yet, if express allows for encrypted connections. I have read that some of the encryption methods such as TDE are not in express though, and I wonder if they (the application developers) do what they do because they want to also let the application be used by express. I get the feeling that probably about half of their customers use SQL express and not the full meal deal.
I got a reply from their support this morning basically telling me that "security was solely my responsibility" and I am just SOL. It's about what I expected.
-
@flaxking said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@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.
How would that avoid licencing? The MS SQL licencing doesn't care how a user connects, you have to get CALs for the actual users using it no matter the method used. (Unless using SQL Express)
Because it opens one connection between the application server and the SQL Server rather than a new one for every single client. You can avoid user CAL issues because it's one connection from one user. This isn't really as big of an issue today as the licensing has become much more free, but many years ago there was a much harder limit on user connection count and how many different users could be connected and it was limited to version of both SQL Server and sometimes even the OS, and the licensing that, that implies. My guess is they may be having their application work with a newer version of SQL Server but I doubt they're using any features not also available on SQL Server 7 from 1998... and that isn't meant to be a joke, from what I've seen that tends to be the standard.
-
@tonyshowoff said in SQL security over the LAN:
My guess is they may be having their application work with a newer version of SQL Server but I doubt they're using any features not also available on SQL Server 7 from 1998...
I fully expect this to be correct. The minimum requirements specify SQL 2012 and newer, but I would bet money they it would run on older versions too, at least to 2005.
-
@Donahue said in SQL security over the LAN:
I think a lot of the aspects of this application are catering to customers that may only use SQL express. i've been looking, but I have not found yet, if express allows for encrypted connections. I have read that some of the encryption methods such as TDE are not in express though, and I wonder if they (the application developers) do what they do because they want to also let the application be used by express. I get the feeling that probably about half of their customers use SQL express and not the full meal deal.
I got a reply from their support this morning basically telling me that "security was solely my responsibility" and I am just SOL. It's about what I expected.
Given the extreme limits of Express, that feels unlikely.
-
@scottalanmiller said in SQL security over the LAN:
@Donahue said in SQL security over the LAN:
I think a lot of the aspects of this application are catering to customers that may only use SQL express. i've been looking, but I have not found yet, if express allows for encrypted connections. I have read that some of the encryption methods such as TDE are not in express though, and I wonder if they (the application developers) do what they do because they want to also let the application be used by express. I get the feeling that probably about half of their customers use SQL express and not the full meal deal.
I got a reply from their support this morning basically telling me that "security was solely my responsibility" and I am just SOL. It's about what I expected.
Given the extreme limits of Express, that feels unlikely.
can you elaborate? what part felt unlikely?
-
SQL Express supports the transport encryption method I gave instructions for.
-
@tonyshowoff said in SQL security over the LAN:
@flaxking said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@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.
How would that avoid licencing? The MS SQL licencing doesn't care how a user connects, you have to get CALs for the actual users using it no matter the method used. (Unless using SQL Express)
Because it opens one connection between the application server and the SQL Server rather than a new one for every single client. You can avoid user CAL issues because it's one connection from one user.
I can't speak to there possibility being a point in time when this was true, but it is not true now. You have to get CALs for each actual person, even if they themselves are not in direct communication with the MS SQL Server
-
@flaxking said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@flaxking said in SQL security over the LAN:
@tonyshowoff said in SQL security over the LAN:
@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.
How would that avoid licencing? The MS SQL licencing doesn't care how a user connects, you have to get CALs for the actual users using it no matter the method used. (Unless using SQL Express)
Because it opens one connection between the application server and the SQL Server rather than a new one for every single client. You can avoid user CAL issues because it's one connection from one user.
I can't speak to there possibility being a point in time when this was true, but it is not true now. You have to get CALs for each actual person, even if they themselves are not in direct communication with the MS SQL Server
I agree, CAL's are about a legal obligation, but they don't actually affect the functionality of SQL. If we had an application server that was the only thing that directly accessed the database, we would still be obligated to have CAL's that cover the use of the application. In our case however, we are licensed for core on SQL and do not use CAL's.
