• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Securing access to MS Sql 2000 database when AD is attached as an access for primary application.

have a MS SQL Server 2000 database that we are opening to a Primary application to connect via OLEDB and AD. the question is how to keep other Office Apps or MS Utilities i.e. Excel, Query Analizer from the users being able to see, read and change data in the database thru anything but the primary application.
0
BlackBoxVS
Asked:
BlackBoxVS
  • 2
2 Solutions
 
dportasCommented:
Is your application a web app or similar N-tier architecture? If so, then you just need to restrict access on the SQL Server port to the web server / application server only.

If it's a 2-tier desktop app on the other hand, then by definition you must allow server access from the desktop. Ultimately, that architecture is always vulnerable to access from outside your application. You can mitigate the risk by using an Application Role (see Books Online) but that is only a quite thin facade of protection.

The bottom line is that if you are relying on your desktop app to implement security then your server is insecure by design. That's one reason why enterprises who care about security tend to favour server apps and services-oriented architectures.
0
 
dportasCommented:
Note that stored procedures should be used to reduce the surface area of your database. You can deny access to tables, views, etc and perform all data access through procs. On its own though, this won't stop other applications accessing those same procs.
0
 
Jim P.Commented:
When you say AD -- you are talking Active Directory and Windows Authentication?

Essentially there is no really good way to limit what application can connect to a SQL db.

Application Roles can limit some of what they can do, but there isn't much you can do with out crippling the office apps and/or the PCs with overwhelming security.
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.

Join & Write a Comment

Featured Post

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!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now