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

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

 

  

You are absolutely right, NT auth is superior.
However, NT Auth is not viable as it pertains to application roles.

So....
If an app role is decided on, do your best, encrypt the password in a
separate body of code from the main executable body. Make it long and
hard, change it regularly... Yada yada .....

The reason I recommend the "Connections" come from a separate DLL or
assembly is so that
"It can be changed easily" with minimal impact on the living system...

Dano

-----Original Message-----
From: Jay Butler
[mailto:mssqldba-ezmlmshield-x41578946.[Email address protected]
Sent: Thursday, October 27, 2005 9:27 AM
To: LazyDBA Discussion
Subject: Re: SQL 2000 - Application role - Help!!!
Importance: Low

"...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
>
>
>
>
>


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