Database security issue

Hello guys,
I have a database that users can log on with windows authorization mode from their application. I need to secure the database e prevent the users to download sql server or from Excel  connect to my database and retrieve data. Is their any way to grant access only to the specific application?

thank you
con13wAsked:
Who is Participating?
 
tigin44Commented:
you can define database level trigger to control the applications accessing to the database.

APP_NAME() fuction will give you the application name that is accessing to the sql server...

0
 
skaraiCommented:
yes that's what the application role (login) is for e.g.
CREATE APPLICATION ROLE application_role_name
    WITH PASSWORD = 'password' [ , DEFAULT_SCHEMA = schema_name ]
then assign permissions to the role.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you would need to do this:
1) create an application role
http://msdn.microsoft.com/en-us/library/ms181491.aspx

2) in the application, activate the role:
http://msdn.microsoft.com/en-us/library/ms188908.aspx

3) grant that role all the permissions you need, and deny all to the windows login except connecting and running that proc

however, if your application does not allow modification, you cannot solve this.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
con13wAuthor Commented:
the problem is that my application doesn't allow code modification
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then you are out of options, sorry.
0
 
con13wAuthor Commented:
ok guys thank you.. but what is the most efficient way to secure my data?

is windows authentication enough ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as you said: with any sql tool, people could connect and retrieve all the data at once. ...
so, you would need to secure somehow against such sessions, but I am not aware of such tools.
0
 
Scott PletcherSenior DBACommented:
Yes.  You can create a Logon trigger that rejects any Login to that db unless it is from your Application.  This is easier to do if you pass a specific App Name from your application, but you can do it even without that.
0
 
Scott PletcherSenior DBACommented:
And of course you can provide an override to allow *you* to run other front-ends against your db :-) .
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:

<<Is their any way to grant access only to the specific application? >>
Yes, you can do the following:
> Create separate Active Directory groups that map to your application such as EXEC-APP1-PRODUCTION
> Create an anonymous service account and put it in the previously created group
> Make your application use the account created
> Create a SQL login named EXEC-APP1-PRODUCTION
> In the database that needs to be accessed by the application, create a user EXEC-APP1-PRODUCTION that maps to EXEC-APP1-PRODUCTION login.  Grant it any necessary right to allow the application to run according to your security policy (on need basis is best)
> You can repeat the process by creating READ-APP1-PRODUCTION by grant only datareader priviledges on the data.

<<is windows authentication enough ?>>
Actually it is the only effective way to really secure your data.

Hope this helps...
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<the problem is that my application doesn't allow code modification>>
If you can't secure your data because no one can accept that a login to modified then you need to talk to your boss and build a case onto what are the effects of not securing data.  
0
 
con13wAuthor Commented:
ok guys thanks a lot I will try to do your suggestions
0
 
skaraiCommented:
In case SQL is still in mixed mode authentication switching to Windows only authentication mode is the recommended way to go since you are minmizing attack surface.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.