Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Database security issue

Posted on 2011-10-27
15
Medium Priority
?
342 Views
Last Modified: 2012-08-29
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
0
Comment
Question by:con13w
  • 3
  • 3
  • 2
  • +3
13 Comments
 
LVL 26

Accepted Solution

by:
tigin44 earned 400 total points
ID: 37043053
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
 
LVL 7

Assisted Solution

by:skarai
skarai earned 400 total points
ID: 37043120
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
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 37043124
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:con13w
ID: 37043170
the problem is that my application doesn't allow code modification
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37043721
then you are out of options, sorry.
0
 

Author Comment

by:con13w
ID: 37044124
ok guys thank you.. but what is the most efficient way to secure my data?

is windows authentication enough ?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37044885
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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 37045323
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37045421
And of course you can provide an override to allow *you* to run other front-ends against your db :-) .
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 400 total points
ID: 37052712

<<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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 37052715
<<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
 

Author Comment

by:con13w
ID: 37053000
ok guys thanks a lot I will try to do your suggestions
0
 
LVL 7

Expert Comment

by:skarai
ID: 37155162
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Viewers will learn how the fundamental information of how to create a table.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question