Re: login security question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Sunanda (Sunanda_at_discussions.microsoft.com)
Date: 06/30/04 

Date: Wed, 30 Jun 2004 07:15:02 -0700

Bob,

Thanks for your reply. I kinda understand this Application Role approach. Could you please explain the following:

1. Say the password for the Application role is found, can a user access the database through the Query Analyser or Enterprise Manager using the application rolde/password.

2. At present the application tracks the user who makes the changes to the database through the application. The application passes the userid to the stored procedures. But if I put in a Applciation role in between, will I still have the actual userid to track who actually did the inserts and updates through the front-end.

Thanks in advance,
Sunanda.

"Bob Simms" wrote:

> "Sunanda" <Sunny@discussions.microsoft.com> wrote in message
> news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@microsoft.com...
> > Hi,
> >
> > I have an application which connects to the SQL server. We have several
> users logging into this application. All of their user-id, passwords are
> validated and converted to an owner profile, which is then used throughout
> the application.
> >
> > My problem is, this owner profile should be prevented from accessing the
> database directly using Enterprise Manager or Query Analyser. The database
> should be accessible only from the application for this owner/global
> profile.
> >
> > How do I go about achieving this. The application was set up like this by
> a person long time back who is not with us anymore. Also, I do not know SQL
> Server Administration. So, please detail out what information I have to look
> up and what steps I will have to follow.
>
> If you can alter the code in the client application, you can use application
> roles.
>
> 1) Use Enterprise Manager to access the database / roles. New Role. click
> the Application Role radio button and give it a nice secure, obscure
> password.
>
> 2) Give the Application Role the appropriate permissions.
>
> 3) Revoke the users' permissions
>
> 4) in the code of the application, put in a call to a stored procedure
> called (I think, from memory) sp_setAppRole (F1 for application role to see
> what the stored proc is called) using the secret password for the App Role
> (which you don't share with the end users).
>
> Now your users will have the appropriate permissions when using your app,
> but not when using QA or any other app.
>
> On an entirely different tack, you can try Group Policies. Use a GP to tie
> down their desktop so that they are not allowed to run Quey Analyzer or
> Enterprise Manager.
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
>
>

Relevant Pages

  • Re: login security question
    ... > I have an application which connects to the SQL server. ... Use Enterprise Manager to access the database / roles. ... Give the Application Role the appropriate permissions. ... what the stored proc is called) using the secret password for the App Role ...
    (microsoft.public.sqlserver.server)
  • Re: SQL 2000 Windows Authentication - Same User Multiple Groups
    ... functionality using our WinForm app and read-only functionality using ... SQL Server permissions are not application-aware. ... App1 contains CRUD functionality using stored procs (EXEC perms on ...
    (microsoft.public.sqlserver.security)
  • Re: Copying Files from Network to Local
    ... working using Code Access Security, ... website has less trust associated with it, and less permissions assigned ... However, when the app resides on a network drive, copying ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: ASP App Hangs. ALL FIXED!
    ... It seems that when you disable Script Blocking in NAV (actually SYM ... > Up until the other day, the app worked without problems. ... > volume and has the correct permissions assigned and have not ...
    (microsoft.public.inetserver.asp.general)
  • Re: HELP please - I think this is a permissions problem - ASP.Net application
    ... Have you checked the permissions for the file itself? ... Ur I.T. Mate Group ... > Trying to run this app. ... > - Windows Media Services Guest Account ...
    (microsoft.public.inetserver.iis)