Blocking Connection to Server

Question

Blocking Connection to Server

thomaspsimon

SSC Veteran

Points: 268
More actions
October 28, 2024 at 2:38 pm

#4474584
Good Afternoon,
We recently procured a service management software for our company, which uses SQL server 2019( 15.0.2125.1) as backend.
The client application uses sql usernames to login into the application . I find this as a security issue, as any internal user can directly connect to the server either through ODBC or through SSMS itself using the application username and password given to them.
Ideally application users have to be handled within the application and clients will not be using sql usernames to login into the application, bit surprised why this application is designed this way.
Can anyone please throw some light into how this can be sorted out ? Is there any firewall available which can be configured in such a way that it will look for the name of the application from the incoming connection ?
Thank You!
- This topic was modified 3 weeks, 5 days ago by thomaspsimon.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

A poor man's solution: logon-triggers https://www.sqlshack.com/prevent-sql-server-login-authentication-scope-using-logon-trigger/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

I'd also have a meeting with the 3rd party creators of the software and ask them to fix it.

As a bit of a sidebar, this is one of the many things I check for before ok-ing 3rd party software.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thomaspsimon SSC Veteran Points: 268 More actions · Answer 3

Thank you Jeff and JO for the valuable replies.

I will be interested to know whether this is a very common approach of designing an architecture or a low level designing, because in software like MS Dynamics users are handled with in the application.

What the vendor suggests is, split the DB and application between two servers and let the people connect to application server through TP plus remote Access utility. And to restrict software which can be run from the application server only to this software. I am not happy with this approach of being in the same LAN and connecting to the server through a remote app.

Second option, is there any firewall available which can filter traffic at application level so that we can configure it in such a way that only traffic coming from this application will be allowed.

Thank You!

Ross McMicken SSCarpal Tunnel Points: 4436 More actions · Answer 4

Yeah, this is a supremely bad design for access.

Take a look at logon triggers and reject logons that do not come from the application server host name. I think you can also get the client ip address and use that as a filter.

TL SSC-Addicted Points: 469 More actions · Answer 5

"I will be interested to know whether this is a very common approach of designing an architecture"

In my experience it doesn't seem that uncommon in old applications, but it really is a bad way to do things. Actually getting a vendor to fix it seems to border on the impossible though. The couple applications we have that have where security is done this way use login triggers to prevent connections from SSMS from anyone or anywhere not on an exceptions list. Its a bandaid on a major wound though.

EdVassie SSC Guru Points: 60472 More actions · Answer 6

The long term solution is to move away from that application. It is unlikely you will be able to do this on your own. A business case will need to be built to do this.

Speak to your manager, then speak to the architecture team. Make sure they understand the security problems of the current situation. This covers both the problems you see with SQL logins you posted about, plus the poor security awareness shown by the vendor in using these implies there may be other issues you don't yet know about. If there is a security breach via that app it is your organisation that will bear the consequences.

Best of luck getting this done, even if the business case is accepted it could easily take 3 years or more from today before the replacement is live.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara