Hi,
we are testing upgrading an application to run SQL 2005. One issue we
are having is that the one application role they have has a 'weak'
password. We are trying to change the password and it seem to be
inforcing the 'strong password policy from windows. I cannot see a way
around this. Do all application roles need strong passwords? Is there
any way around it?Yes...they should all be strong passwords. Using strong
passwords would be the first option.
If for some reason you can't and need time until you can use
strong passwords, One way around it is to use the old stored
procedures which are only there for backwards compatibility.
sp_addapprole, sp_approlepassword, etc.
-Sue
On 16 Apr 2007 18:15:02 -0700, rjvanzanten
<rvanzant@.premierbankcard.com> wrote:
>Hi,
>we are testing upgrading an application to run SQL 2005. One issue we
>are having is that the one application role they have has a 'weak'
>password. We are trying to change the password and it seem to be
>inforcing the 'strong password policy from windows. I cannot see a way
>around this. Do all application roles need strong passwords? Is there
>any way around it?|||Thanks Sue, I know that a better password would be preferred. But its
a difficult option for us.
Thanks for the tip on the sp_addapprole. It should get us through
this.
Ron
On Apr 16, 11:26 pm, Sue Hoegemeier <S...@.nomail.please> wrote:
> Yes...they should all be strong passwords. Using strong
> passwords would be the first option.
> If for some reason you can't and need time until you can use
> strong passwords, One way around it is to use the old stored
> procedures which are only there for backwards compatibility.
> sp_addapprole, sp_approlepassword, etc.
> -Sue
> On 16 Apr 2007 18:15:02 -0700, rjvanzanten
>
> <rvanz...@.premierbankcard.com> wrote:
> - Show quoted text -|||Sue Hoegemeier (Sue_H@.nomail.please) writes:
> Yes...they should all be strong passwords. Using strong
> passwords would be the first option.
> If for some reason you can't and need time until you can use
> strong passwords, One way around it is to use the old stored
> procedures which are only there for backwards compatibility.
> sp_addapprole, sp_approlepassword, etc.
sp_addrole uses CREATE APPLICATION ROLE, so that wouldn't be any different.
I am not able to run sp_helptext on sp_approlepassword, but I would not
expect it be possible to use password that does not pass the rules.
The only way out would be to modify the Windows policy as the password
for the application role is changed, and then change back.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yeah...I see that in the stored proc but I'm pretty sure the
password check is bypassed - thought it was on one of the
blogs but only found this reference:
http://forums.microsoft.com/MSDN/Sh...576174&SiteID=1
I'm not on a box where I can test it, not until tomorrow.
-Sue
On Tue, 17 Apr 2007 22:10:41 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Sue Hoegemeier (Sue_H@.nomail.please) writes:
>sp_addrole uses CREATE APPLICATION ROLE, so that wouldn't be any different.
>I am not able to run sp_helptext on sp_approlepassword, but I would not
>expect it be possible to use password that does not pass the rules.
>The only way out would be to modify the Windows policy as the password
>for the application role is changed, and then change back.|||Sue Hoegemeier (Sue_H@.nomail.please) writes:
> Yeah...I see that in the stored proc but I'm pretty sure the
> password check is bypassed - thought it was on one of the
> blogs but only found this reference:
> http://forums.microsoft.com/MSDN/Sh...576174&SiteID=1
> I'm not on a box where I can test it, not until tomorrow.
Indeed. I tested it at a server at work, and the password check did
not catch my lousy password. Hm, I wonder how they do that...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yeah...I thought I remember testing while back when I first
read it - I still can't find where I originally read it. I
had not looked at the stored proc though - which does make
it look odd that it works.
-Sue
On Thu, 19 Apr 2007 22:15:18 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:
>Sue Hoegemeier (Sue_H@.nomail.please) writes:
>Indeed. I tested it at a server at work, and the password check did
>not catch my lousy password. Hm, I wonder how they do that...
Showing posts with label passwords. Show all posts
Showing posts with label passwords. Show all posts
Wednesday, March 7, 2012
Application Roles across databases in SQL Server 2000
Hello
I have 2 databases that run application role security
(different role names and passwords), users access these
databases only from within different Visual Basic
applications.
I require to be able to request data from both
databases. I have read in SQL Server help that if you
enable the guest user account and then give it the
relevant permissions the system will only allow the other
database to get to these objects.
I have created a stored procedure on one of the databases
that calls a table in the database with the guest account
enabled. I have not given the guest account access to
this table but I can still get to the data in the table.
Please can someone explain why this is and what I need to
do to prevent this.
Thank you
Caroline> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
This is due to ownership chaining behavior. As long as all objects are
owned by the same login, permissions are not checked on indirectly
referenced objects. Additionally, you need to enable cross database
chaining for ownership chains to apply to cross-database access. This
appears to be the case in your environment.
As long as you access data only via views and procedures, you don't need to
grant any permissions to guest. This allows you to leverage ownership
chains as a security mechanism. See Ownership Chains in the Books Online
for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Caroline" <anonymous@.discussions.microsoft.com> wrote in message
news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
> Hello
> I have 2 databases that run application role security
> (different role names and passwords), users access these
> databases only from within different Visual Basic
> applications.
> I require to be able to request data from both
> databases. I have read in SQL Server help that if you
> enable the guest user account and then give it the
> relevant permissions the system will only allow the other
> database to get to these objects.
> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
> Thank you
> Caroline|||> and what I need to do to prevent this.
Only grant execute permissions on the procedure to those users/roles whom
you want to access the underlying data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23fcOSPDYEHA.3596@.tk2msftngp13.phx.gbl...
> This is due to ownership chaining behavior. As long as all objects are
> owned by the same login, permissions are not checked on indirectly
> referenced objects. Additionally, you need to enable cross database
> chaining for ownership chains to apply to cross-database access. This
> appears to be the case in your environment.
> As long as you access data only via views and procedures, you don't need
to
> grant any permissions to guest. This allows you to leverage ownership
> chains as a security mechanism. See Ownership Chains in the Books Online
> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Caroline" <anonymous@.discussions.microsoft.com> wrote in message
> news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
>
I have 2 databases that run application role security
(different role names and passwords), users access these
databases only from within different Visual Basic
applications.
I require to be able to request data from both
databases. I have read in SQL Server help that if you
enable the guest user account and then give it the
relevant permissions the system will only allow the other
database to get to these objects.
I have created a stored procedure on one of the databases
that calls a table in the database with the guest account
enabled. I have not given the guest account access to
this table but I can still get to the data in the table.
Please can someone explain why this is and what I need to
do to prevent this.
Thank you
Caroline> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
This is due to ownership chaining behavior. As long as all objects are
owned by the same login, permissions are not checked on indirectly
referenced objects. Additionally, you need to enable cross database
chaining for ownership chains to apply to cross-database access. This
appears to be the case in your environment.
As long as you access data only via views and procedures, you don't need to
grant any permissions to guest. This allows you to leverage ownership
chains as a security mechanism. See Ownership Chains in the Books Online
for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Caroline" <anonymous@.discussions.microsoft.com> wrote in message
news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
> Hello
> I have 2 databases that run application role security
> (different role names and passwords), users access these
> databases only from within different Visual Basic
> applications.
> I require to be able to request data from both
> databases. I have read in SQL Server help that if you
> enable the guest user account and then give it the
> relevant permissions the system will only allow the other
> database to get to these objects.
> I have created a stored procedure on one of the databases
> that calls a table in the database with the guest account
> enabled. I have not given the guest account access to
> this table but I can still get to the data in the table.
> Please can someone explain why this is and what I need to
> do to prevent this.
> Thank you
> Caroline|||> and what I need to do to prevent this.
Only grant execute permissions on the procedure to those users/roles whom
you want to access the underlying data.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23fcOSPDYEHA.3596@.tk2msftngp13.phx.gbl...
> This is due to ownership chaining behavior. As long as all objects are
> owned by the same login, permissions are not checked on indirectly
> referenced objects. Additionally, you need to enable cross database
> chaining for ownership chains to apply to cross-database access. This
> appears to be the case in your environment.
> As long as you access data only via views and procedures, you don't need
to
> grant any permissions to guest. This allows you to leverage ownership
> chains as a security mechanism. See Ownership Chains in the Books Online
> for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Caroline" <anonymous@.discussions.microsoft.com> wrote in message
> news:2512601c46019$5e372e20$a501280a@.phx
.gbl...
>
Subscribe to:
Posts (Atom)