Share via

How can i deny access to any sql management from lan and public network?

Stefanos Spyridakis 21 Reputation points
2020-08-30T16:20:32.52+00:00

Hello,

Can i deny access to any sql management from lan and public network?
I mean the user SA to be able to be used to read and write in the bases but not to connect with the sql management or similar app.

Thank you,
Giorgos

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,171 questions
0 comments
Accepted answer
  1. tibor_karaszi@hotmail.com 4,306 Reputation points
    2020-09-01T14:23:35.297+00:00

    No, you can't specify access at the application level. If a person knows how your trading program logs in to SQL Server, then that person can use the same login credential to login using SSMS and work with those same privileges. I suggest you re-read Erland's post carefully. Erland missed in "e" after a "not", so it should be "note" instead of "not", btw.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 104.9K Reputation points MVP
    2020-08-30T17:22:56.517+00:00

    You cannot deny sysadmin anything.

    Nor can you deny anyone access per application.

    If you know want no one to be able to access the server from outside the server, that is very simple: just go into the SQL Server Configuration Manager and disable the TCP protocol.

    It also possible to deny non-sysadmin users remote access by revoking public access on the TCP endpoint. But not that this will affect SSMS, applications, Excel etc alike.

    You can set up a logon trigger that checks the value of app_name(). However, a knowledgeable user can easily work around this by changing the connection properties in SSMS. And an incorrectly written logon trigger can make the server unavailable for everyone, so I recommend against this.

    0 comments
  2. CathyJi-MSFT 21,116 Reputation points Microsoft Vendor
    2020-08-31T07:28:21.347+00:00

    Hi @giorgos ,

    Did you want to no one could connect to SQL server though SSMS or other applications?

    For local SQL instance, you can’t achieve this goal for sa account. For other users, you can remove the user account from SSMS login to deny other users to access SQL server instance from SSMS.

    For remote SQL server instance, as Erland mentioned, you can disable TCP protocol from SQL Server Configuration Manager to achieve this goal. SQL Server Configuration Manager>SQL server Network Configuration > SQL instance name >Right click TCP protocol >Disable

    If i misunderstood your issue, please let me know.

    Best regards,
    Cathy

    ===============================================

    If the response helped, do "Accept Answer" and upvote it.

    0 comments
  3. Stefanos Spyridakis 21 Reputation points
    2020-09-01T14:17:23.287+00:00

    Hello again,

    Thank you for your reply. Both of your answers helped me to understand.
    So i can keep the password secretely for the sa and administrator and create one new user which will not have acess on the sql management but allow acess with the trading program. Have i understood correctly?

    Thanks a lot,
    Giorgos