If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Security to prevent external tools access to SQL Server

We are setting up a security plan for accessing SQL Server, using Windows domain groups and Windows authentication. Data access would be closed down to using stored procedures and views. Our in-house written applications and reports would typically be the direct consumers of these stored procedures and views. That would also be the way our users would primarily access SQL Server data. However, it is my understanding (please correct me if I'm wrong), that anyone with a recognized Windows login, whose permissions grant him access to SQL Server stored procedures/views via using our in-house applications, could just as easily use some external tool such as Excel, Access, or Crystal Reports - if they knew what they were doing - to access the very same stored procedures and views.

A. Is this correct?
B. Is there a way to limit or control access from external tools, within the framework I've described above? Or, is there a preferred way of handling this?

You are correct if your application architecture is a two-tier architecture where your database server is accessible from the user's desktop. However, it is very common these days for applications to use a tiered architecture, either because they are browser-based or because they use a server-side service layer to do all database access. If you use a tiered architecture then you can stop users accessing the database directly just by not opening up the DBMS's ports to end users' desktops.

If you do need users to access the database directly then you can still limit their access to the activities permitted by procs and object-level permissions. That same level of access applies equally to your application and to those external tools. So if you design procs and permissions accordingly those other apps don't necessarily have to be seen as a "threat".

If you have "problem users", you can set up a login trigger to query the program name, and kill the connection, if it is not one of the standard ones you want. I played around with a sample of one. If I can remember where I stashed it, I will post it, but I believe there are samples in BOL, as well. Look for Server Triggers.

If you have "problem users", you can set up a login trigger to query the program name, and kill the connection, if it is not one of the standard ones you want. I played around with a sample of one. If I can remember where I stashed it, I will post it, but I believe there are samples in BOL, as well. Look for Server Triggers.

That might thwart some casual users but since the application name is specified in the connection string it's a fairly trivial matter for an intelligent and determined user to set the application name to the same one used for the "real" application.

As Pootle suggests, application roles may offer a marginally better alternative but they still rely on a "hidden" password and if your application is on the desktop then that password could be accessible in various ways (decompiling the source code, reading the binaries or wherever else you cache the password, watching memory or the protocol stack, etc). Just be aware that app roles are a "security by obscurity" method.

If you have people crafting connection strings in Access, and Excel with custom Application Names, then you don't just have "problem users", in my opinion. You have openly hostile users. Get the network guys to shut off their network line. Should only cost you a bottle of scotch.