Block Excel and Access connections to SQL

SQL Server Administration
1

Hello,
Is there a way to block connections to SQL from "non-approved" applications? Or is there a way to set what applications to except?

I have looked at ApplicationRole and sp_setapprole, but they may not be the best way. If I read it right I would have to add that to all processes??????

So any suggestions on where to look? Is ApplicationRole what I need?

2

Best way to control access is using the double-abstraction technique.

  1. Create AD Groups by role/function (e.g. ReadOnly, ReadWrite, whatever)
    2, Create database roles that match to match groups in step 1.
  2. Add specific, required permissions to DB roles from step 2.
    a. Use the principle of least privilege here
  3. Add the AD Groups to the database roles from step 2.

With that set up, you control the access by changing users in the AD groups.

BTW there is at least one "missing" role in SQL Server: db_executor. I usually create one

CREATE ROLE [db_executor] AUTHORIZATION [dbo];
GO
GRANT EXECUTE TO db_executor;
GO

Handy when you want to give read and execute permissions, but not owner e.g.

3

Thanks @gbritton.
So currently we use Windows security for UI access. If Joe has permission to read/write then he can actually use Excel to access the database even though we would like to deny this.

Thus the problem.

Note permission is currently through AD Group.

I needed to create the execute role a while ago during one of the security tightenings.

4

If joe can write, Excel is not the problem. He could use anything to write to the database -- even write his own .net program.

Application roles are not bullet proof. They are best used in an n-tier environment. So joe logs onto a web site, the website talks to the db and uses an application role with the required permissions.

in a 2-tier (Joe's workstation and the server), Joe can always change the connection to use his own login.

Better to ask, "Why does Joe have permission to write (directly) to the database at all?"

5

You have a point. Security is one of MY/our many weak points.

What I came up with for a band aid is a logon trigger. I guess the point I should take from this is the next version of the program really needs better security and to look at what can be done in the mean time. The one good thing is we do not have a web interface.

6

Typically Excel and Access set a specific value in APP_NAME() [as does SSMS, btw]. If so, you can use a logon trigger to cancel any logins that come from Excel or Access by checking for the name pattern in that value.