2

We are currently looking to update an old application security structure and would like to go for an approach where the application connects to SQL via one Windows login with the required Read/Write access.

We don't want to give access to the DB to all users otherwise they might connect via another application (SSMS or Excel) and run commands on SQL. I know we can use Application Roles to achieve this however, the application connects to many databases and we wouldn't like to enable the Guest user.

Do you have any other suggested approaches or in that case we would need to use an SQL encrypted login instead of a Windows login?

Improve this question
2
  • 2
    Why don't you give only necessary permissions to all the users? If they have only read/write access they cannot do DDL, for example. Or you want give them the write access to do modifications only through your application? In this case you can give them only EXEC permissions to your procedures and don't give direct read/write access to the tables
    – sepupic
    Sep 6, 2017 at 13:55
  • We want to remove user access from the database so they can only access the database through the application. Unfortunately specific permissions is not possible because the application is old and does not have many stored procedures so user would require permissions to write directly. Thanks for your answer, do you have any other suggestions?
    – Ron
    Sep 8, 2017 at 14:25

1 Answer 1

Reset to default
0

If you're using IIS manager for application pools to configure identity for your application and your servers are Windows 2012 or later, you can look into using either a Managed Service Account or a Group Managed Service Account to run your app. Either approach will eliminate the need to deal with passwords yet maintain a secure application login.

The comment provided by sepupic above is spot on though in that you should manage user permissions directly against the database separately, and how that's done will heavily depend upon what level of permissions are needed.

Hopefully this provides an approach to your situation though.

Improve this answer
1
  • Not sure if we are using IIS manager for application pools however I will look into this maybe using Managed Service Account could work for us. Thanks!
    – Ron
    Sep 8, 2017 at 14:28

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.