Recognize application connected to SQL Server 2005

Question

I'd like to know what application connected to DB and executing SPs.
(I want to limit SP execution to only my App - all other - eg MS SSMS would be ignored)

So is there a way to find out connected client name?

If not maybe you have other suggestion how to ensure only dedicated app is used (App is using Windows' integrated security)

EDIT: What problem I'm attempting to solve:

I'm working on built in app user right & roles module.
So, app receives from DB (via SPs) all fields and than business logic decides what to show (or not) for which user.

I afraid about more experienced user: he can run own app, connect to DB and omit all security restriction.
That's why I want tu ensure DB talks only with this app.

Connection string param is some solution but quite easy to sniff - I wonder if there would be another option

Answer 1

The app name can be part of the connection string, but can be spoofed. If you need to enforce this, then I would abstract data access to an app-server, and do data access from the client via this (presumably using web services). Then you app-server can use a known account to talk to the server (as a "trusted subsystem"), and the individual clients don't actually need access to the database (win:win, especially when firewalls get involved).

Personally, I tend to make this my default model anyway... it adds a lot of future scalability from the start, and is hard to add after-the-fact.

Answer 2

To pass the name of the client app to SQL Server, set the "Application Name" parameter in the connection string. You can retrieve this value inside SQL Server with the app_name() function.

However, if you want to enforce different permissions for different apps, then you will have to define a standard login with a password for each app, instead of using an integrated login.

Answer 3

Have you looked at using Application Roles within SQL Server?

You can have all necessary permissions granted only to the Application Role and the database logins will just members of Public with no other permissions. That way even if someone gains access via Management Studio or some other direct connection they have no way of doing anything within your database without knowing the application role credentials.

Edit: added below section on encryption to try to address Maicej's comment.

For field level granularity you can use the build in encryption functionality in SQL 2005 and above to limit the read access of various roles to certain fields. This is only optimal for a few encrypted fields and not for an entire row. In this answer to a question on encrypting SSN/Credit Card information I provide a code sample of how to use the encryption functionality to ensure that only certain users can decrypt data in fields. The example is currently set up to use database users but can be adapted to Application Roles as well. If you choose to encrypt keys with passwords you can also prevent DBA's with sysadmin privileges from being able to decrypt the encrypted data as well.

Answer 4

The only thing that can be authenticated on Windows is a user. There is not secure way to establish the identity of an application. As a result any attempt to restrict access to a specific application can be defeated by a sufficiently motivated attacker.

You can keep a accidental login honest by adding a logon trigger. In the trigger you can check the declared "application name" from the connection string and close the connection if is not. This would deter somebody accidentally connecting to your server. However it would not deter a motivated admin from accessing the data, as the application name can be spoofed. Also if you have any sort of administrative and maintenance tasks, the administrators and the maintenance tasks need access to your database.

A slightly better approach is to control access to the data via an approle. The approle enables access for the application, you would still use your custom application logic for your content management restrictions (the 'column' restriction and other you quote). This would elevate slightly the bar so that only your application can modify and access the data. It would deter non-admins from accessing your data, but a privileged admin will be (will always be) able to do whatever he or she wants.

And lastly there is a even higher bar by deploying encryption. It takes a really determined administrator to surpass this and it takes an administrator that will have to take specific steps to find out your key passwords, he cannot discover them accidentally. As I said, a dedicated administrator will always be capable of accessing your data.

An alternative is to not deploy any barrier but instead use auditing to monitor the data. Tamper evident auditing can be done in SQL Server and advertising the audit is usually a strong enough deterrent.