none
To Restrict SQL account from accessing Sql server via SSMS RRS feed

  • Question

  • Hi All,

    We are planning to tighten the security of our SQL Server.

    In the initial phase, we want to restrict all the SQL accounts(except sa) from accessing SQL Server via SSMS

    Since the SQL passwords are used in the connection strings(plain text) of our .NET Applications, developers are able to see it and they are accessing SQL server through SSMS with the credentials in the connection string.

    The requirement is, SQL accounts should only be accessing the databases through .NET applications and not through any other applications like SSMS, SQLCMD...etc

    1) We tried "Logon Trigger", but later we came to know that there is security breach in it.

    2) Application Roles - Password is plain text, again back to square one.

    We are looking for an alternate. Please share some ideas.

    Thanks & Regards,

    K.P.Senthil Kumar

    Thursday, December 18, 2014 4:41 PM

Answers

  • The basic presumption here is that there is on way you can tie a connection to an application as such. There is app_name(), but since this is passed from the application, the application can call itself whatever you want.

    As long as it is only a matter of keeping business users out, you can solve the issue with some three-tiered solution. Either by having a true middle layer, or just having a web server, or the application running on Terminal Server or Citrix.

    But you want to keep the developers out who work with the code. That makes it difficult to lock them out of the middle layer.

    Then again, you say "our SQL Server" is that singular? Don't you have more than one SQL Server? One for developement, one for test and one for production? If you only want to keep the devs out from development, you have different usernames and password for different environments, and they are read from config files. The config files for production should be well-protected.

    By the way, only permitting sa from logging in from SSMS is bad idea. Rather, you should disable sa, and everyone should log in with their individual Windows account. And those who should perform system-administration tasks should be member of sysadmin.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, December 18, 2014 10:18 PM