What is the most secure method for an application to connect/authenticate to SQL Server 2008 R2?

Assume that we have a network deployed desktop application written in .NET. We don't want any users (except admins) to be able to manipulate data outside of the application. For example, normal users should not be able to use SQL Server Management Studio to modify/read data.

I know of 3 ways to authenticate to SQL Server, but which one would be the most secure for this situation?

1) Windows Authentication (Trusted Connection) - Authentication is done based on Windows user account, which means that we can't restrict access to the application only. SSMS could be used by any Windows user who has DB permissions.

2) SQL Server Authentication (User/Password) - We can create one login for the application which isn't tied to any specific user, but the Username/Password must be stored and used by the application, which, from what I understand, makes it difficul开发者_Go百科t (maybe impossible) to completely secure them.

3) Using an Approle - Has the same problem as #2 in that a password must be stored by the application.

Which of these 3 would be the most secure for this situation, or is there another option that is better?

The #1 is the most secure approach.

Advantages and Disadvantages

There are basically two ways to manage security. You can allow the application to be able to perform specific functions and use a single user (domain, local machine, SQL) or an application role that the application connects with. This way, no individual users have rights at all to the SQL Server and they cannot connect with SSMS.

This assumes you want only the app to connect and all functions are managed through the application.

Alternatively, if you want specified users to have specific permissions that differ from all other users, then you would assign the individual users (hopefully domain users) to sql server roles and set permissions based on roles (you probably do not want the administrative burden of managing users individually, so sql roles or domain groups alleviate administrative burden). Then your application connects using windows authentication (Ex. the context of the user that is using the application). This affords you the option to grant individual users different rights within the database. However, that exact thing can also be accomplished by a well designed application.

An alternative to using SQL Roles is that you might place domain users in a Windows AD group (Ex. Accounting Managers) and assign that domain group to a sql role or grant it specific permissions (data reader, writer, dbo, etc...) and you may even take this all the way to specific database objects (Ex. Only Accounting Managers can execute the "DELETE ENTRY" stored procedure). You can even remove all rights to direct table access so that when the user does connect to the database (for instance via SSMS) they cannot delete data directly and can ONLY do so by executing the "DELETE" stored procedure if they are in a group that has permissions to execute that stored procedure.

Further, if you really wanted to protect you data from deletion you would carry a flag in the table that indicates a record is active or inactive and you would not truly DELETE the record, even during a so called delete operation, rather you would only mark it as active or inactive.

In any case to answer the specific question, to limit the user from connecting via SSMS and ONLY through the app, this can be accomplished in several ways and is in no way a limitation.

I second Alex's take. If you want to be paranoid, you can additionally set permissions per application pool (in IIS) or per application id (windows/web apps). I think win auth is good enough.