SQL User Account Lock Down for remote access RRS feed

  • Question

  • Hello,

    We have SQL Server 2014 Instance on Network server1. Also we have created SQL users on it.

    Now we could able to access that Instance remotely from other LAN servers by SQL account.

    other LAN servers are having SQL 2014 Management Studio only.

    We need to lock remote SQL access for particular SQL user account from other LAN servers on SQL Management Studio.

    Our Actual aim is,  We should not connect that SQL server Instance from other servers using by particular SQL user account by management studio.

    Please give us the solution for the same.


    Wednesday, February 7, 2018 12:11 PM

All replies

  • You can try it with an Logon Triggers , but be carefull not to logout all users incl. yourself.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, February 7, 2018 12:25 PM
  • This is difficult to enforce. For instance the application name is defined by the application, in the connection string. I connected using management studio, and in the connection dialog, I selected "Options >>" and on the "Additional Connection Parameters", I specified: 

    Application Name=ERP-System;

    Now I'm not connected using SQL Server Management Studio application name anymore.

    I suggest that you instead focus on securing by means of traditional methods instead. I.e., make sure that only the right persons has the password for this "particular SQL user account".

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, February 7, 2018 12:41 PM
  • Hello,

    Thanks for your reply.

    Is it possible, can we do something on Management Studio on Remote servers.


    Can we do any changes on Gpedit.msc


    Is there any script to disable particular SQL user logon on Management studio from remote servers.

    We have tried Denied already on SQL server 2014 instance as per attached image.


    But it affects websites which we used this SQL account in web.config file.

    So please help us to lock down this SQL user only from Local network servers by Management Studio

    Thank you.

    Wednesday, February 7, 2018 12:53 PM
  • As I already replied in my earlier reply, this isn't doable in a reliable way. Even with a logon trigger (see the link that Olaf posted on how to use such a trigger), the users can fool your trigger into thinking that you aren't using SSMS.

    See my earlier reply. Make sure that these persons you want to protect yourself from do not have the account details that you have in the web config file. Why would they have that? Assuming this is a production server and these persons are developers, why do thy have access to the production server in the first place? Etc. Work with the traditional security mechanism. There is no way to (reliably) restrict connecting from some app, if the person has a login and the password!

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Wednesday, February 7, 2018 1:12 PM
  • You have asked several questions on this same thought.

    You need to abandon this entire effort.  A user either has access to the SQL Server or does not.  Access is not controlled per application.  Once a user has access, they have access.

    You can use a logon trigger to stop certain things.  However, I would highly recommend against it and instead rethink what you are trying to do.

    Wednesday, February 7, 2018 1:53 PM
    • Create another endpoint for different protocols to allow approved IPs access.
    • Create LOGON Trigger. Be careful of using it. Limiting access by identifying application name is not strong enough. If they want, they can use Powershell or any ODBC connection. In potential, you might lock yourself access though.
    • Ask your system team to secure the Firewall and local accounts.
    • Create an alert for yourself.
    Wednesday, March 7, 2018 7:08 PM