1

Here is the back story. A developer created an EXE program. The exe program has the ability to access a SQL Servre database. In order to do that we create an Active Directory group. Let's call this group AppAdmin. The EXE program use windows integrated security to connect to the SQL Server database.

In order to use this EXE program, the user must belong to the APPAdmin group. The AppAdmin group then now set the membership as DB_Owner for the database. This setup has been like this for a few years. and it is running fine. The user is only limited to what the EXE program can do.

Now here is the complication. One user one day decided to install SQL Server Management Studio Express. Let's call this guy JOE. and JOE is a member of APPAdmin group.

Now I am assigned to find away to protect the database from JOE breaking it using SQL Server Management Studio.

I am wondering if there is a way to restrict JOE when he is connecting using SQL Server Management Studio?

What are you suggestion ??

Changing this EXE program seems to be out of the question because it is big and I might break it if I start changing how the EXE program connect to the SQL Server database

Thank you

marc_s
  • 9,052
  • 6
  • 46
  • 52
BobNoobGuy
  • 413
  • 3
  • 7
  • 11

1 Answers1

2

Changing this EXE program seems to be out of the question

That makes using application roles not an option. However, here's the link anyway since it's good to know: Application roles

Your second option would be using logon triggers. Link for more info What you do is, once somebody logs in, you check which application name they are using and if it's not your EXE, you deny access by aborting the logon..

However, this is not bullet proof since the "application" string can be spoofed. It might however be "good enough" for stopping the curious people.

Security should be designed in such a way that people that are able to log in to a database can only do what they are allowed to do. So your best option (but the most complicated to implement at this stage) Would be to revoke the DBO permission an give more granular permissions. If you need business logic to be forced your only option is to implement that in stored procedures and only give execute permissions to those stored procedures.. However, that might again mean chaning the EXE.

Edward Dortland
  • 2,865
  • 19
  • 24