none
make program or process not to access to our sql server RRS feed

  • Question

  • I want to make limit to connecting our SQL Server. Because this SQL instance is only for our application. But some 3rd office is using our database to get our information. Sometime 3rd office acquire our 'sa' password. Although our password was leaked any way, we want to block their program access to our database.
    ultimately, can we block program or process from accessing our sql server instance?
    ex) EntityFramework, can access. Another process should be blocked access.

    Kiran

    Tuesday, November 26, 2019 2:04 PM

All replies

  • Change the password for 'sa' would be the obvious answer.  The better answer is create a proper login in SQL that your application uses and disable the 'sa' account altogether.

    "3rd office" - is that within your company or external hack?  If within, is corporate security being violated here?


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Tuesday, November 26, 2019 2:24 PM
  • You might want to use a logon trigger and then not allow the login if the hostname is coming from the problem host, or if the program name is unique you could block the user by looking up the program_name in sysprocesses for this spid.

    You can also block this user in the firewall by ip, port of program name.

    Tuesday, November 26, 2019 3:17 PM
  • Hi Hilary,

    Thanks for your response. How to implement it practically? Any T-SQL code? Could you please help


    Kiran

    Tuesday, November 26, 2019 4:46 PM
  • block the user by looking up the program_name in sysprocesses for this spid.

    Whereby the client can pass any application name within the connection string, so this is not very reliable.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, November 26, 2019 4:52 PM
  • You have to be very careful doing this as a mistake can lock everyone out of your server. here is an example.

    https://codingsight.com/logon-triggers-in-sql-server/

    Query hostname and program_name from sys.sysprocesses

    Tuesday, November 26, 2019 4:53 PM
  • I want to make limit to connecting our SQL Server. Because this SQL instance is only for our application. But some 3rd office is using our database to get our information. Sometime 3rd office acquire our 'sa' password. Although our password was leaked any way, we want to block their program access to our database.
    ultimately, can we block program or process from accessing our sql server instance?
    ex) EntityFramework, can access. Another process should be blocked access.

    Obviously you need to keep your sa password under control. Or disable sa altogether. That's number one.

    You cannot grant permissions based on the application. You can use a logon trigger as suggested by Hilary, but as Olaf points out, both the hostname and application name can easily be spoofed. Possibly you can check by IP address. This can be spoofed too, I believe, but it is more difficult to do.

    If the application uses an application server (which could be a web server), your odds are better, because here you can control which network addresses that can access SQL Server. There are a couple of options here depending on your config.

    But none of these are going to help if you keep leaking the sa password, so that is the first thing to address.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, November 26, 2019 10:50 PM
  • Hi Kiran,

    It seems that the proper way is to disable the 'sa',and create a new login account for your application to use.

    For information about Logon Trigger,please refer to this blog.

    Best regards,
    Cris


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, November 27, 2019 7:28 AM
  • Our office is doubt that 3rd office is snipping our password when customer is using computer.

     

    For example, when customer is using ‘SSMS’ and 3rd office tried to snipping password from hacking program. 

     

    I am not sure you can help to me with some solution. But if you have any idea and solution, please let me know.

     

    Surely it is ok with not clear solution. I want to get just idea.


    Kiran


    • Edited by juniorkiran Tuesday, December 3, 2019 8:39 AM
    Tuesday, December 3, 2019 8:38 AM
  • It is difficult to give advice without full understanding of your situation and configuration. The suggestions I give may be completely off-target, because there are things I don't know.

    If I understand this correctly, this "3rd office" run an application which accesses SQL Server, and no other access from 3rd office should be possible.

    First of all, you say that you are afraid that 3rd office may have been hacked and there may be program in place which steals passwords. Obviously, then, your application is just as much as possible victim as is SSMS. So if the application connects with "sa" that is the first thing to change.

    But better is if the application does not connect to SQL Server at all, but connects to an application server (which could be a web server), which in its turn connects to SQL Server. Then you can sever all network connections from 3rd office to SQL Server.

    I realise that this is a big change to the application, and it may not be realistic. I discuss a few more options in this chapter: http://www.sommarskog.se/grantperm.html#interlude. (That is part of a longer article, but maybe it can give you some ideas nevertheless.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, December 3, 2019 10:16 PM