Re: SQL Security
From: Patman (p_hoard_at_yahoo.com)
Date: 04/01/04
- Next message: Joseph Geretz: "Question regarding Instance"
- Previous message: Erschwitz: "RE: Service Pack 3A problems"
- Messages sorted by: [ date ] [ thread ]
Date: 31 Mar 2004 16:01:50 -0800
Keith,
Thanks for the tip on Application Roles. This seems to be what I need
except I'm having problems making it work in a script. It works fine
using my account (my account has dba permissions on the server) but
when I try to run it from an account regular 'domain user' permissions
I get a COM exception error when it tries to write the recordset to
the db.
Below is an example of how I'm calling the application role in my
script: (scripts edited to make more readable)
Example of KIX script using a trusted connection, everything works
great: (Except the data is wide open to everyone)
;First, set variables for the connection to the database
$x=SetOption("WrapAtEOL","On")
$DSN="Driver={SQL Server};"
$DSN=$DSN+"Server=SERVER1;"
$DSN=$DSN+"Database=DB1;"
$DSN=$DSN+"Trusted_Connection=yes"
;Set properties of DB objects and open connection to database
$Connection.ConnectionString = $DSN
$Connection.Open()
$Command.ActiveConnection = $Connection
Example of KIX script using Application Role:
;First, set variables for the connection to the database
$x=SetOption("WrapAtEOL","On")
$DSN="Driver={SQL Server};"
$DSN=$DSN+"Server=SERVER1;"
$DSN=$DSN+"Database=DB1;"
$use = "USE DB1"
$sql = "EXEC sp_setapprole 'APP_ROLE, 'APP_ROLE_PWD'"
;Set properties of DB objects and open connection to database
$Connection.ConnectionString = $DSN
$Connection.Open()
$Command.ActiveConnection = $Connection
$use = CreateObject("ADODB.Command")
$sql = CreateObject("ADODB.Command")
This is the error message when it is run as a typical user:
WorkstationID : -2147352567: COM exception error "Fields"
(ADODB.Recordset - Ite
m cannot be found in the collection corresponding to the requested
name or ordin
al.) [-2147352567/80020009]
It seems to be real close but obviously I missing something here!
Thanks for any advice you may have,
Pat
"Keith Kratochvil" <sqlguy.back2u@comcast.net> wrote in message news:<e2xtgapFEHA.3456@tk2msftngp13.phx.gbl>...
> Perhaps you could look into using application roles. Here is a snippet
> from Books Online (within the SQL Server program group):
>
> Establishing Application Security and Application Roles
> New Information - SQL Server 2000 SP3.
>
> The security system in Microsoft SQL ServerT is implemented at the
> lowest level: the database itself. This is the best method for
> controlling user activities regardless of the application used to
> communicate with SQL Server. However, sometimes security controls must
> be customized to accommodate the special requirements of an individual
> application, especially when dealing with complex databases and
> databases with large tables.
>
> Additionally, you may want users to be restricted to accessing data only
> through a specific application (for example using SQL Query Analyzer or
> Microsoft Excel) or to be prevented from accessing data directly.
> Restricting user access in this way prohibits users from connecting to
> an instance of SQL Server using an application such as SQL Query
> Analyzer and executing a poorly written query, which can negatively
> affect the performance of the whole server.
>
> --
> Keith
>
>
> "Patman" <p hoard@yahoo.com> wrote in message
> news:2a0255b0.0403301228.72bb7128@posting.google.com...
> > I'm kind of new to SQL so if this question has been answered recently
> > I apologize.
> >
> > I have a login script that caputures user and computer info and writes
> > it to a SQL db. To avoid a login/password in my text logon script I
> > using a trusted connection with "Domain users" having
> > select/insert/update permissions in the db. My problem is that any
> > authenicated user can view this data from Access, SQL Enterprise
> > Manager, etc.
> >
> > Is is possible to restrit this db so that authenicated users can still
> > select/write to the db but not be able to view from other ODBC related
> > sources?
> >
> > TIA,
> > Pat
- Next message: Joseph Geretz: "Question regarding Instance"
- Previous message: Erschwitz: "RE: Service Pack 3A problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
