Re: SQL 2000 - Application role

"...not be easily obtained by the user..."

This also means that the password is not easily changed if it is
compromised. Embedding passwords it not a good security practice. While
native accounts are not easily avoidable in every situation, it is
certainly far superior to use Windows Authentication whenever possible.

Smith Dano wrote:

>Yes, the password is the key in this case.
>
>But most-likely if the key is hard-coded into the compiled executable it
>will not be easily obtained by the users.
>
>And for best-practices, plan on changing the password regularly. (60-90
>days ?)
>
>This is why it is a good idea for the "Authentication" to be in a
>separate .DLL then the auth.dll can be replaced without a complete
>recompile of the app...
>
>Dano
>
>
>-----Original Message-----
>From: Uday Shivamurthy
>[mailto:mssqldba-ezmlmshield-x72271372.[Email address protected]
>Sent: Wednesday, October 26, 2005 8:49 PM
>To: LazyDBA Discussion
>Subject: RE: SQL 2000 - Application role - Help!!!
>Importance: Low
>
>Keith,
>Yes, you are right. Usage of the sp_setapprole is really just relying
>on anyone having >>its<< password isn't it?
>Thanks.
>
>Regards,
>Uday
>.....................................
>Direct: +61-2-98059739
>Mobile: +61-401 705907
>Email: [Email address protected]
>
>-----Original Message-----
>From: Moore Keith
>[mailto:mssqldba-ezmlmshield-x41354950.[Email address protected]
>Sent: Thursday, 27 October 2005 10:11 AM
>To: LazyDBA Discussion
>Subject: RE: SQL 2000 - Application role - Help!!!
>
>End-users still need to be able to connect to the database in order to
>call sp_setapprole.
>
>I was able to call sp_setapprole on a database to which I only had
>connect access.
>
>The security around application roles is dependent on the password. If
>someone obtained this password they would effectively have access to the
>database (depending on the permissions of the approle).
>
>I'm not saying don't do it, just be aware of the consequences.
>
>_______________________________________________________________
>Keith Moore
>
>-----Original Message-----
>From: Uday Shivamurthy
>[mailto:mssqldba-ezmlmshield-x2454679.[Email address protected]
>Sent: Thursday, 27 October 2005 9:52
>To: LazyDBA Discussion
>Subject: RE: SQL 2000 - Application role - Help!!!
>
>Keith,
>Two questions on this note:
>Why can't I just drop all end-user logins. Retain only developer login
>accounts using integrated security. This way I don't have to go to the
>extent of creating groups and assigning the app-role login to those
>groups.
>
>Are you sure a user with *just* a login to sql and *no* permissions at
>all can issue the sp_setapprole after connecting successfully?
>
>Regards,
>Uday
>.....................................
>Direct: +61-2-98059739
>Mobile: +61-401 705907
>Email: [Email address protected]
>
>-----Original Message-----
>From: Moore Keith
>[mailto:mssqldba-ezmlmshield-x18080405.[Email address protected]
>Sent: Thursday, 27 October 2005 9:08 AM
>To: LazyDBA Discussion
>Subject: RE: SQL 2000 - Application role - Help!!!
>
>Maybe a better approach would be to create groups and assign proper
>permissions to the database objects for those groups. Using the
>application role approach a user could still connect to the database
>using QA and the call sp_setapprole. If there is only one application
>role with full permissions to the database this could be a serious
>security violation.
>
>_______________________________________________________________
>Keith Moore
>
>-----Original Message-----
>From: Smith Dano
>[mailto:mssqldba-ezmlmshield-x33219179.[Email address protected]
>Sent: Thursday, 27 October 2005 2:53
>To: LazyDBA Discussion
>Subject: RE: SQL 2000 - Application role - Help!!!
>
>I will grossly over-simplify it for you.
>
>Create an app role - See BOL for syntax.
>
>Everywhere the application opens a connection, The connection object has
>to have the sp_setapprole procedure called to "switch" the context to
>the application role with escalated rights in the DB's.
>
>So if you are lucky there is a class that has a method "GetConnection"
>where this can be done in one place.
>If the application is not using a common place to "get" a connection,
>everywhere a connection is opened the sp_setapprole will need to be
>executed immediately after the cn.Open() call.
>
>Then, in the db, you allow DOM\Users to be valid logins only, with no
>rights.
>Assign all rights to the app-role. While a user could still connect
>with QA, they could not do anything in the db....
>
>
>Dano Smith
>
>
>
>
>-----Original Message-----
>From: Natalia Brett
>[mailto:mssqldba-ezmlmshield-x52326139.[Email address protected]
>Sent: Wednesday, October 26, 2005 11:55 AM
>To: LazyDBA Discussion
>Subject: SQL 2000 - Application role - Help!!!
>Importance: Low
>
>
>Hi,
>our company has in-house application ("IDT") working with SQL 2000
>database.
>We use windows authentification.
>I need to stop staff from accessing SQL server via any others
>applications (such as Query Analyser, Enterprise Manager or Excel).
>
>As I understand the Application role of SQL server should help, but I
>dont undestand how to implement it.
>
>Please,
>Help!!!
>
>The information within this e-mail is confidential and intended solely
>for the use of the recipient(s). If you are not the intended recipient
>then please notify Natalia Brett via email at Natalia.[Email address
>protected] or by telephone on +44(0)1244 319912. Then delete the email
>from your system. Please do not read, copy, print, forward, disclose, or
>use the information contained within this email. Note that any views or
>opinions presented in this email are solely those of the author and do
>not necessarily represent those of Aktiv Kapital. Finally, the recipient
>should check this email and any attachments for the presence of viruses.
>The company accepts no liability for any damage caused by any virus
>transmitted by this email.
>
>Aktiv Kapital (UK) Ltd
>Merchants House
>Hamilton Place
>Chester
>CH12BE
>UK
>www.AktivKapital.co.uk
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>---------------------------------------------------------
>Legal Notice: This electronic mail and its attachments are intended
>solely
>for the person(s) to whom they are addressed and contain information
>which
>is confidential or otherwise protected from disclosure, except for the
>purpose they are intended to. Dissemination, distribution, or
>reproduction
>by anyone other than their intended recipients is prohibited and may be
>illegal. If you are not an intended recipient, please immediately inform
>the
>sender and send him/her back the present e-mail and its attachments and
>destroy any copies which may be in your possession.
>---------------------------------------------------------
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>---------------------------------------------------------
>Legal Notice: This electronic mail and its attachments are intended
>solely
>for the person(s) to whom they are addressed and contain information
>which
>is confidential or otherwise protected from disclosure, except for the
>purpose they are intended to. Dissemination, distribution, or
>reproduction
>by anyone other than their intended recipients is prohibited and may be
>illegal. If you are not an intended recipient, please immediately inform
>the
>sender and send him/her back the present e-mail and its attachments and
>destroy any copies which may be in your possession.
>---------------------------------------------------------
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>---------------------------------------------------------------------
>TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
>Website : http://www.LazyDBA.com
>To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>

MS Sql Server LazyDBA home page

Re: SQL 2000 - Application role - Help!!!

Re: SQL 2000 - Application role - Help!!!