0

Ok, so everything I read says best practice is to us NT authentication in SQL server. And I can see some advantages to that but using NT auth how do I force users to access the data using only my application and not some other tool such as SSMS? It would seem to me that it would be better to allow only my application to connect to SQL server using SQL auth and password the user does not know and controlling security through the application. Am I missing something here? Is there a way to block SSMS and other database tools from connecting to my application's database?

Improve this question

1 Answer 1

Reset to default
1

Ok, so everything I read says best practice is to us NT authentication in SQL server

Read more. Please. Because while there are good reasons to do that in certain scenarios, there are good reasons NOT to do it in other scenarios.

And I can see some advantages to that but using NT auth how do I force users to access the data using only my application and not some other tool such as SSMS?

Since maybe 25 years or so a standard approach to program applications is to have a SERVER. The user connects to the SERVER and ONLY the server connects to the database. Problem solved.

Am I missing something here?

Stating the obvious: A lot more reading than you did so far.

Is there a way to block SSMS and other database tools from connecting to my application's database?

Your reading did not get to application passwords in the documentation (https://msdn.microsoft.com/en-us/library/ms181491.aspx is titled CREATE APPLICATION ROLE) which would allow an application to post-authenticate with a password. Not that this is a good approach - but it sis AN approach that was introduce some versions back. This way rights could be on an application, and unless the user knows the role password of the application - out of luck.

But really, the standard approach is to use an application server and only the app server can connect. And often this does not use NT authentication as the connection password is coded in a server config file.

Improve this answer
1
  • I am not sure that I agree that an intermediate application is the "standard" approach. It is a good approach in some situations. It won't work for us because we do not control that part of the application design. And I agree that the Application Role is not necessarily a good approach.
    – rbarc
    May 26, 2016 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.