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?

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.

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 '16 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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