SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
Ron
You will also have to turn on the /PAE switch in BOOT.INI. Use sp_configure
to turn on AWE and to set the max server memory.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
Ron
|||Do not use /3gb switch if you want sql server to use 4GB. You could
probably go up to 5.5-6GB or so if this is a dedicated machine.
Occasionally observe pages/sec performance monitor counter to see if sql is
taking too much ram.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> Server has 8GB RAM - SQL Server currently takes 1.7GB
> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> posts and replies and I think I may have all the info, taking a little
> from
> several posts. I'm just not positive as I haven't seen a step by step (in
> the proper order) document or posting. Not sure if Microsoft has a step
> by
> step. I've been through books on-line and have seen all these topics.
> Are these the steps in the proper order?
> 1-Update the boot.ini to add the /3gb switch
> 2-Enable awe (via SSMS)
> 3-Select running values or configured values? (via SSMS)
> 4-Set the SQL Server max memory to 4gb (via SSMS)
> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> 6-Reboot server
> Anything else?
> Thanks
> Ron
>
>
|||While I also have to ask why you want to limit it to 4GB when you have 8GB
total I don't agree with the statement not to use the /3GB. Since you will
have to use AWE to access anything over 3GB anyway that is not a factor. The
question is can you benefit from the extra 1GB of directly addressable
memory or not. Since the data buffer pool is the only thing that can use AWE
memory you need to see if you could use that extra GB for things like proc
cache, connection memory etc.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13stt0f7jhtnk14@.corp.supernews.com...
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql
> is taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>
|||If something should not work correctly can I simply change these settings
back to the original configuration and reboot?
Thanks, I'll remove the /3gb and replace it with the /pae.
"TheSQLGuru" wrote:
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql is
> taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>
>
|||Your right, I should have said I wanted to get it to a minimum of 4gb. I
will be setting it at 5.5gb as recommended.
So do I want both /3gb and /pae?
"Andrew J. Kelly" wrote:
> While I also have to ask why you want to limit it to 4GB when you have 8GB
> total I don't agree with the statement not to use the /3GB. Since you will
> have to use AWE to access anything over 3GB anyway that is not a factor. The
> question is can you benefit from the extra 1GB of directly addressable
> memory or not. Since the data buffer pool is the only thing that can use AWE
> memory you need to see if you could use that extra GB for things like proc
> cache, connection memory etc.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13stt0f7jhtnk14@.corp.supernews.com...
>
|||Depending on what server you have, you may or may not need to specify PAE in
boot.ini. For instance, most, if not all the, current HP ProLiant servers
support hot add memory, and don't require PAE. Regardless, if you can already
see 8GB at the OS level, you are all set, and don't have to be bothered with
setting PAE or not.
See this KB for additional info: http://support.microsoft.com/kb/283037
For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
slightly more to the buffer pool, and generally don't use 3GB unless
otherwise needed.
Linchi
"Ron" wrote:
[vbcol=seagreen]
> Your right, I should have said I wanted to get it to a minimum of 4gb. I
> will be setting it at 5.5gb as recommended.
> So do I want both /3gb and /pae?
>
> "Andrew J. Kelly" wrote:
|||Linchi,
That's interesting. The /PAE is an operating system feature so how does the
new Proliant machines get around not having to set this? I know Windows 2003
has the ability to set it automatically but I haven't heard of the hardware
doing this. Can you elaborate?
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C00A3183-DE64-4F34-93D8-0FE342EC411E@.microsoft.com...[vbcol=seagreen]
> Depending on what server you have, you may or may not need to specify PAE
> in
> boot.ini. For instance, most, if not all the, current HP ProLiant servers
> support hot add memory, and don't require PAE. Regardless, if you can
> already
> see 8GB at the OS level, you are all set, and don't have to be bothered
> with
> setting PAE or not.
> See this KB for additional info: http://support.microsoft.com/kb/283037
> For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
> slightly more to the buffer pool, and generally don't use 3GB unless
> otherwise needed.
> Linchi
> "Ron" wrote:
Showing posts with label windows. Show all posts
Showing posts with label windows. Show all posts
Thursday, March 8, 2012
Another memory question
SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
RonYou will also have to turn on the /PAE switch in BOOT.INI. Use sp_configure
to turn on AWE and to set the max server memory.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
Ron|||Do not use /3gb switch if you want sql server to use 4GB. You could
probably go up to 5.5-6GB or so if this is a dedicated machine.
Occasionally observe pages/sec performance monitor counter to see if sql is
taking too much ram.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> Server has 8GB RAM - SQL Server currently takes 1.7GB
> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> posts and replies and I think I may have all the info, taking a little
> from
> several posts. I'm just not positive as I haven't seen a step by step (in
> the proper order) document or posting. Not sure if Microsoft has a step
> by
> step. I've been through books on-line and have seen all these topics.
> Are these the steps in the proper order?
> 1-Update the boot.ini to add the /3gb switch
> 2-Enable awe (via SSMS)
> 3-Select running values or configured values? (via SSMS)
> 4-Set the SQL Server max memory to 4gb (via SSMS)
> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> 6-Reboot server
> Anything else?
> Thanks
> Ron
>
>|||While I also have to ask why you want to limit it to 4GB when you have 8GB
total I don't agree with the statement not to use the /3GB. Since you will
have to use AWE to access anything over 3GB anyway that is not a factor. The
question is can you benefit from the extra 1GB of directly addressable
memory or not. Since the data buffer pool is the only thing that can use AWE
memory you need to see if you could use that extra GB for things like proc
cache, connection memory etc.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13stt0f7jhtnk14@.corp.supernews.com...
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql
> is taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
>> Server has 8GB RAM - SQL Server currently takes 1.7GB
>> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
>> posts and replies and I think I may have all the info, taking a little
>> from
>> several posts. I'm just not positive as I haven't seen a step by step
>> (in
>> the proper order) document or posting. Not sure if Microsoft has a step
>> by
>> step. I've been through books on-line and have seen all these topics.
>> Are these the steps in the proper order?
>> 1-Update the boot.ini to add the /3gb switch
>> 2-Enable awe (via SSMS)
>> 3-Select running values or configured values? (via SSMS)
>> 4-Set the SQL Server max memory to 4gb (via SSMS)
>> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
>> 6-Reboot server
>> Anything else?
>> Thanks
>> Ron
>>
>>
>|||If something should not work correctly can I simply change these settings
back to the original configuration and reboot?
Thanks, I'll remove the /3gb and replace it with the /pae.
"TheSQLGuru" wrote:
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql is
> taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> > SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> > Server has 8GB RAM - SQL Server currently takes 1.7GB
> >
> > I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> > posts and replies and I think I may have all the info, taking a little
> > from
> > several posts. I'm just not positive as I haven't seen a step by step (in
> > the proper order) document or posting. Not sure if Microsoft has a step
> > by
> > step. I've been through books on-line and have seen all these topics.
> >
> > Are these the steps in the proper order?
> >
> > 1-Update the boot.ini to add the /3gb switch
> > 2-Enable awe (via SSMS)
> > 3-Select running values or configured values? (via SSMS)
> > 4-Set the SQL Server max memory to 4gb (via SSMS)
> > 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> > 6-Reboot server
> >
> > Anything else?
> >
> > Thanks
> >
> > Ron
> >
> >
> >
> >
>
>|||Your right, I should have said I wanted to get it to a minimum of 4gb. I
will be setting it at 5.5gb as recommended.
So do I want both /3gb and /pae?
"Andrew J. Kelly" wrote:
> While I also have to ask why you want to limit it to 4GB when you have 8GB
> total I don't agree with the statement not to use the /3GB. Since you will
> have to use AWE to access anything over 3GB anyway that is not a factor. The
> question is can you benefit from the extra 1GB of directly addressable
> memory or not. Since the data buffer pool is the only thing that can use AWE
> memory you need to see if you could use that extra GB for things like proc
> cache, connection memory etc.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13stt0f7jhtnk14@.corp.supernews.com...
> > Do not use /3gb switch if you want sql server to use 4GB. You could
> > probably go up to 5.5-6GB or so if this is a dedicated machine.
> > Occasionally observe pages/sec performance monitor counter to see if sql
> > is taking too much ram.
> >
> > --
> > Kevin G. Boles
> > Indicium Resources, Inc.
> > SQL Server MVP
> > kgboles a earthlink dt net
> >
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> >> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> >> Server has 8GB RAM - SQL Server currently takes 1.7GB
> >>
> >> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> >> posts and replies and I think I may have all the info, taking a little
> >> from
> >> several posts. I'm just not positive as I haven't seen a step by step
> >> (in
> >> the proper order) document or posting. Not sure if Microsoft has a step
> >> by
> >> step. I've been through books on-line and have seen all these topics.
> >>
> >> Are these the steps in the proper order?
> >>
> >> 1-Update the boot.ini to add the /3gb switch
> >> 2-Enable awe (via SSMS)
> >> 3-Select running values or configured values? (via SSMS)
> >> 4-Set the SQL Server max memory to 4gb (via SSMS)
> >> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> >> 6-Reboot server
> >>
> >> Anything else?
> >>
> >> Thanks
> >>
> >> Ron
> >>
> >>
> >>
> >>
> >
> >
>|||Depending on what server you have, you may or may not need to specify PAE in
boot.ini. For instance, most, if not all the, current HP ProLiant servers
support hot add memory, and don't require PAE. Regardless, if you can already
see 8GB at the OS level, you are all set, and don't have to be bothered with
setting PAE or not.
See this KB for additional info: http://support.microsoft.com/kb/283037
For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
slightly more to the buffer pool, and generally don't use 3GB unless
otherwise needed.
Linchi
"Ron" wrote:
> Your right, I should have said I wanted to get it to a minimum of 4gb. I
> will be setting it at 5.5gb as recommended.
> So do I want both /3gb and /pae?
>
> "Andrew J. Kelly" wrote:
> > While I also have to ask why you want to limit it to 4GB when you have 8GB
> > total I don't agree with the statement not to use the /3GB. Since you will
> > have to use AWE to access anything over 3GB anyway that is not a factor. The
> > question is can you benefit from the extra 1GB of directly addressable
> > memory or not. Since the data buffer pool is the only thing that can use AWE
> > memory you need to see if you could use that extra GB for things like proc
> > cache, connection memory etc.
> >
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> >
> >
> > "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> > news:13stt0f7jhtnk14@.corp.supernews.com...
> > > Do not use /3gb switch if you want sql server to use 4GB. You could
> > > probably go up to 5.5-6GB or so if this is a dedicated machine.
> > > Occasionally observe pages/sec performance monitor counter to see if sql
> > > is taking too much ram.
> > >
> > > --
> > > Kevin G. Boles
> > > Indicium Resources, Inc.
> > > SQL Server MVP
> > > kgboles a earthlink dt net
> > >
> > >
> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > > news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> > >> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> > >> Server has 8GB RAM - SQL Server currently takes 1.7GB
> > >>
> > >> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> > >> posts and replies and I think I may have all the info, taking a little
> > >> from
> > >> several posts. I'm just not positive as I haven't seen a step by step
> > >> (in
> > >> the proper order) document or posting. Not sure if Microsoft has a step
> > >> by
> > >> step. I've been through books on-line and have seen all these topics.
> > >>
> > >> Are these the steps in the proper order?
> > >>
> > >> 1-Update the boot.ini to add the /3gb switch
> > >> 2-Enable awe (via SSMS)
> > >> 3-Select running values or configured values? (via SSMS)
> > >> 4-Set the SQL Server max memory to 4gb (via SSMS)
> > >> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> > >> 6-Reboot server
> > >>
> > >> Anything else?
> > >>
> > >> Thanks
> > >>
> > >> Ron
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
> >|||Linchi,
That's interesting. The /PAE is an operating system feature so how does the
new Proliant machines get around not having to set this? I know Windows 2003
has the ability to set it automatically but I haven't heard of the hardware
doing this. Can you elaborate?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C00A3183-DE64-4F34-93D8-0FE342EC411E@.microsoft.com...
> Depending on what server you have, you may or may not need to specify PAE
> in
> boot.ini. For instance, most, if not all the, current HP ProLiant servers
> support hot add memory, and don't require PAE. Regardless, if you can
> already
> see 8GB at the OS level, you are all set, and don't have to be bothered
> with
> setting PAE or not.
> See this KB for additional info: http://support.microsoft.com/kb/283037
> For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
> slightly more to the buffer pool, and generally don't use 3GB unless
> otherwise needed.
> Linchi
> "Ron" wrote:
>> Your right, I should have said I wanted to get it to a minimum of 4gb. I
>> will be setting it at 5.5gb as recommended.
>> So do I want both /3gb and /pae?
>>
>> "Andrew J. Kelly" wrote:
>> > While I also have to ask why you want to limit it to 4GB when you have
>> > 8GB
>> > total I don't agree with the statement not to use the /3GB. Since you
>> > will
>> > have to use AWE to access anything over 3GB anyway that is not a
>> > factor. The
>> > question is can you benefit from the extra 1GB of directly addressable
>> > memory or not. Since the data buffer pool is the only thing that can
>> > use AWE
>> > memory you need to see if you could use that extra GB for things like
>> > proc
>> > cache, connection memory etc.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> > Solid Quality Mentors
>> >
>> >
>> > "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> > news:13stt0f7jhtnk14@.corp.supernews.com...
>> > > Do not use /3gb switch if you want sql server to use 4GB. You could
>> > > probably go up to 5.5-6GB or so if this is a dedicated machine.
>> > > Occasionally observe pages/sec performance monitor counter to see if
>> > > sql
>> > > is taking too much ram.
>> > >
>> > > --
>> > > Kevin G. Boles
>> > > Indicium Resources, Inc.
>> > > SQL Server MVP
>> > > kgboles a earthlink dt net
>> > >
>> > >
>> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> > > news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>> > >> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
>> > >> Server has 8GB RAM - SQL Server currently takes 1.7GB
>> > >>
>> > >> I'd like to get SQL Server to about 4gb RAM. I've been reading all
>> > >> the
>> > >> posts and replies and I think I may have all the info, taking a
>> > >> little
>> > >> from
>> > >> several posts. I'm just not positive as I haven't seen a step by
>> > >> step
>> > >> (in
>> > >> the proper order) document or posting. Not sure if Microsoft has a
>> > >> step
>> > >> by
>> > >> step. I've been through books on-line and have seen all these
>> > >> topics.
>> > >>
>> > >> Are these the steps in the proper order?
>> > >>
>> > >> 1-Update the boot.ini to add the /3gb switch
>> > >> 2-Enable awe (via SSMS)
>> > >> 3-Select running values or configured values? (via SSMS)
>> > >> 4-Set the SQL Server max memory to 4gb (via SSMS)
>> > >> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL
>> > >> user
>> > >> 6-Reboot server
>> > >>
>> > >> Anything else?
>> > >>
>> > >> Thanks
>> > >>
>> > >> Ron
>> > >>
>> > >>
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
RonYou will also have to turn on the /PAE switch in BOOT.INI. Use sp_configure
to turn on AWE and to set the max server memory.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
Server has 8GB RAM - SQL Server currently takes 1.7GB
I'd like to get SQL Server to about 4gb RAM. I've been reading all the
posts and replies and I think I may have all the info, taking a little from
several posts. I'm just not positive as I haven't seen a step by step (in
the proper order) document or posting. Not sure if Microsoft has a step by
step. I've been through books on-line and have seen all these topics.
Are these the steps in the proper order?
1-Update the boot.ini to add the /3gb switch
2-Enable awe (via SSMS)
3-Select running values or configured values? (via SSMS)
4-Set the SQL Server max memory to 4gb (via SSMS)
5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
6-Reboot server
Anything else?
Thanks
Ron|||Do not use /3gb switch if you want sql server to use 4GB. You could
probably go up to 5.5-6GB or so if this is a dedicated machine.
Occasionally observe pages/sec performance monitor counter to see if sql is
taking too much ram.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Ron" <Ron@.discussions.microsoft.com> wrote in message
news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> Server has 8GB RAM - SQL Server currently takes 1.7GB
> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> posts and replies and I think I may have all the info, taking a little
> from
> several posts. I'm just not positive as I haven't seen a step by step (in
> the proper order) document or posting. Not sure if Microsoft has a step
> by
> step. I've been through books on-line and have seen all these topics.
> Are these the steps in the proper order?
> 1-Update the boot.ini to add the /3gb switch
> 2-Enable awe (via SSMS)
> 3-Select running values or configured values? (via SSMS)
> 4-Set the SQL Server max memory to 4gb (via SSMS)
> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> 6-Reboot server
> Anything else?
> Thanks
> Ron
>
>|||While I also have to ask why you want to limit it to 4GB when you have 8GB
total I don't agree with the statement not to use the /3GB. Since you will
have to use AWE to access anything over 3GB anyway that is not a factor. The
question is can you benefit from the extra 1GB of directly addressable
memory or not. Since the data buffer pool is the only thing that can use AWE
memory you need to see if you could use that extra GB for things like proc
cache, connection memory etc.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13stt0f7jhtnk14@.corp.supernews.com...
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql
> is taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
>> Server has 8GB RAM - SQL Server currently takes 1.7GB
>> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
>> posts and replies and I think I may have all the info, taking a little
>> from
>> several posts. I'm just not positive as I haven't seen a step by step
>> (in
>> the proper order) document or posting. Not sure if Microsoft has a step
>> by
>> step. I've been through books on-line and have seen all these topics.
>> Are these the steps in the proper order?
>> 1-Update the boot.ini to add the /3gb switch
>> 2-Enable awe (via SSMS)
>> 3-Select running values or configured values? (via SSMS)
>> 4-Set the SQL Server max memory to 4gb (via SSMS)
>> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
>> 6-Reboot server
>> Anything else?
>> Thanks
>> Ron
>>
>>
>|||If something should not work correctly can I simply change these settings
back to the original configuration and reboot?
Thanks, I'll remove the /3gb and replace it with the /pae.
"TheSQLGuru" wrote:
> Do not use /3gb switch if you want sql server to use 4GB. You could
> probably go up to 5.5-6GB or so if this is a dedicated machine.
> Occasionally observe pages/sec performance monitor counter to see if sql is
> taking too much ram.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Ron" <Ron@.discussions.microsoft.com> wrote in message
> news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> > SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> > Server has 8GB RAM - SQL Server currently takes 1.7GB
> >
> > I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> > posts and replies and I think I may have all the info, taking a little
> > from
> > several posts. I'm just not positive as I haven't seen a step by step (in
> > the proper order) document or posting. Not sure if Microsoft has a step
> > by
> > step. I've been through books on-line and have seen all these topics.
> >
> > Are these the steps in the proper order?
> >
> > 1-Update the boot.ini to add the /3gb switch
> > 2-Enable awe (via SSMS)
> > 3-Select running values or configured values? (via SSMS)
> > 4-Set the SQL Server max memory to 4gb (via SSMS)
> > 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> > 6-Reboot server
> >
> > Anything else?
> >
> > Thanks
> >
> > Ron
> >
> >
> >
> >
>
>|||Your right, I should have said I wanted to get it to a minimum of 4gb. I
will be setting it at 5.5gb as recommended.
So do I want both /3gb and /pae?
"Andrew J. Kelly" wrote:
> While I also have to ask why you want to limit it to 4GB when you have 8GB
> total I don't agree with the statement not to use the /3GB. Since you will
> have to use AWE to access anything over 3GB anyway that is not a factor. The
> question is can you benefit from the extra 1GB of directly addressable
> memory or not. Since the data buffer pool is the only thing that can use AWE
> memory you need to see if you could use that extra GB for things like proc
> cache, connection memory etc.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13stt0f7jhtnk14@.corp.supernews.com...
> > Do not use /3gb switch if you want sql server to use 4GB. You could
> > probably go up to 5.5-6GB or so if this is a dedicated machine.
> > Occasionally observe pages/sec performance monitor counter to see if sql
> > is taking too much ram.
> >
> > --
> > Kevin G. Boles
> > Indicium Resources, Inc.
> > SQL Server MVP
> > kgboles a earthlink dt net
> >
> >
> > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> >> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> >> Server has 8GB RAM - SQL Server currently takes 1.7GB
> >>
> >> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> >> posts and replies and I think I may have all the info, taking a little
> >> from
> >> several posts. I'm just not positive as I haven't seen a step by step
> >> (in
> >> the proper order) document or posting. Not sure if Microsoft has a step
> >> by
> >> step. I've been through books on-line and have seen all these topics.
> >>
> >> Are these the steps in the proper order?
> >>
> >> 1-Update the boot.ini to add the /3gb switch
> >> 2-Enable awe (via SSMS)
> >> 3-Select running values or configured values? (via SSMS)
> >> 4-Set the SQL Server max memory to 4gb (via SSMS)
> >> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> >> 6-Reboot server
> >>
> >> Anything else?
> >>
> >> Thanks
> >>
> >> Ron
> >>
> >>
> >>
> >>
> >
> >
>|||Depending on what server you have, you may or may not need to specify PAE in
boot.ini. For instance, most, if not all the, current HP ProLiant servers
support hot add memory, and don't require PAE. Regardless, if you can already
see 8GB at the OS level, you are all set, and don't have to be bothered with
setting PAE or not.
See this KB for additional info: http://support.microsoft.com/kb/283037
For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
slightly more to the buffer pool, and generally don't use 3GB unless
otherwise needed.
Linchi
"Ron" wrote:
> Your right, I should have said I wanted to get it to a minimum of 4gb. I
> will be setting it at 5.5gb as recommended.
> So do I want both /3gb and /pae?
>
> "Andrew J. Kelly" wrote:
> > While I also have to ask why you want to limit it to 4GB when you have 8GB
> > total I don't agree with the statement not to use the /3GB. Since you will
> > have to use AWE to access anything over 3GB anyway that is not a factor. The
> > question is can you benefit from the extra 1GB of directly addressable
> > memory or not. Since the data buffer pool is the only thing that can use AWE
> > memory you need to see if you could use that extra GB for things like proc
> > cache, connection memory etc.
> >
> > --
> > Andrew J. Kelly SQL MVP
> > Solid Quality Mentors
> >
> >
> > "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> > news:13stt0f7jhtnk14@.corp.supernews.com...
> > > Do not use /3gb switch if you want sql server to use 4GB. You could
> > > probably go up to 5.5-6GB or so if this is a dedicated machine.
> > > Occasionally observe pages/sec performance monitor counter to see if sql
> > > is taking too much ram.
> > >
> > > --
> > > Kevin G. Boles
> > > Indicium Resources, Inc.
> > > SQL Server MVP
> > > kgboles a earthlink dt net
> > >
> > >
> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
> > > news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
> > >> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
> > >> Server has 8GB RAM - SQL Server currently takes 1.7GB
> > >>
> > >> I'd like to get SQL Server to about 4gb RAM. I've been reading all the
> > >> posts and replies and I think I may have all the info, taking a little
> > >> from
> > >> several posts. I'm just not positive as I haven't seen a step by step
> > >> (in
> > >> the proper order) document or posting. Not sure if Microsoft has a step
> > >> by
> > >> step. I've been through books on-line and have seen all these topics.
> > >>
> > >> Are these the steps in the proper order?
> > >>
> > >> 1-Update the boot.ini to add the /3gb switch
> > >> 2-Enable awe (via SSMS)
> > >> 3-Select running values or configured values? (via SSMS)
> > >> 4-Set the SQL Server max memory to 4gb (via SSMS)
> > >> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL user
> > >> 6-Reboot server
> > >>
> > >> Anything else?
> > >>
> > >> Thanks
> > >>
> > >> Ron
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> >
> >|||Linchi,
That's interesting. The /PAE is an operating system feature so how does the
new Proliant machines get around not having to set this? I know Windows 2003
has the ability to set it automatically but I haven't heard of the hardware
doing this. Can you elaborate?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C00A3183-DE64-4F34-93D8-0FE342EC411E@.microsoft.com...
> Depending on what server you have, you may or may not need to specify PAE
> in
> boot.ini. For instance, most, if not all the, current HP ProLiant servers
> support hot add memory, and don't require PAE. Regardless, if you can
> already
> see 8GB at the OS level, you are all set, and don't have to be bothered
> with
> setting PAE or not.
> See this KB for additional info: http://support.microsoft.com/kb/283037
> For a dedicated SQL Server with 8GB of RAM, we generally give 6GB or
> slightly more to the buffer pool, and generally don't use 3GB unless
> otherwise needed.
> Linchi
> "Ron" wrote:
>> Your right, I should have said I wanted to get it to a minimum of 4gb. I
>> will be setting it at 5.5gb as recommended.
>> So do I want both /3gb and /pae?
>>
>> "Andrew J. Kelly" wrote:
>> > While I also have to ask why you want to limit it to 4GB when you have
>> > 8GB
>> > total I don't agree with the statement not to use the /3GB. Since you
>> > will
>> > have to use AWE to access anything over 3GB anyway that is not a
>> > factor. The
>> > question is can you benefit from the extra 1GB of directly addressable
>> > memory or not. Since the data buffer pool is the only thing that can
>> > use AWE
>> > memory you need to see if you could use that extra GB for things like
>> > proc
>> > cache, connection memory etc.
>> >
>> > --
>> > Andrew J. Kelly SQL MVP
>> > Solid Quality Mentors
>> >
>> >
>> > "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> > news:13stt0f7jhtnk14@.corp.supernews.com...
>> > > Do not use /3gb switch if you want sql server to use 4GB. You could
>> > > probably go up to 5.5-6GB or so if this is a dedicated machine.
>> > > Occasionally observe pages/sec performance monitor counter to see if
>> > > sql
>> > > is taking too much ram.
>> > >
>> > > --
>> > > Kevin G. Boles
>> > > Indicium Resources, Inc.
>> > > SQL Server MVP
>> > > kgboles a earthlink dt net
>> > >
>> > >
>> > > "Ron" <Ron@.discussions.microsoft.com> wrote in message
>> > > news:BCCACC36-2B29-4491-87B8-83DE8FD13BB5@.microsoft.com...
>> > >> SQL2005 SP2, 32-bit Windows Server 2003 EE SP2
>> > >> Server has 8GB RAM - SQL Server currently takes 1.7GB
>> > >>
>> > >> I'd like to get SQL Server to about 4gb RAM. I've been reading all
>> > >> the
>> > >> posts and replies and I think I may have all the info, taking a
>> > >> little
>> > >> from
>> > >> several posts. I'm just not positive as I haven't seen a step by
>> > >> step
>> > >> (in
>> > >> the proper order) document or posting. Not sure if Microsoft has a
>> > >> step
>> > >> by
>> > >> step. I've been through books on-line and have seen all these
>> > >> topics.
>> > >>
>> > >> Are these the steps in the proper order?
>> > >>
>> > >> 1-Update the boot.ini to add the /3gb switch
>> > >> 2-Enable awe (via SSMS)
>> > >> 3-Select running values or configured values? (via SSMS)
>> > >> 4-Set the SQL Server max memory to 4gb (via SSMS)
>> > >> 5-Turn on LOCK PAGES IN MEMORY option and give permissions to SQL
>> > >> user
>> > >> 6-Reboot server
>> > >>
>> > >> Anything else?
>> > >>
>> > >> Thanks
>> > >>
>> > >> Ron
>> > >>
>> > >>
>> > >>
>> > >>
>> > >
>> > >
>> >
>> >
Wednesday, March 7, 2012
Anonymous connection to a remote server
I have a SQL server running on a Win2k Domain Controller set for Windows Aut
hentication only.
I have a Windows 2003 Web Edition server that is a member of the domain.
When permissions are correct I can open and update databases on the server f
rom the Web Edition server.
I want to allow anyone that hits a web site on the 2003 server to be able to
update a specific database on the SQL server. The
users on that site are running under the local IIS anonymous user id.
I tried the obvious and simple connection
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Ini
tial Catalog=WebRedirectLog;Data Source=ZEUS;
Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=ROY;Use Encryption for Data=False;
Tag with column collation when possible=False");
(which I expected to fail) and it did telling me
Microsoft OLE DB Provider for SQL Server error '80004005'
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Se
rver connection.
(I did NOT supply a userid in the call to ADODB.Connection.Open
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not
a domain account) as authenticated.
I do NOT want to allowed mixed mode authentication on this server.
Please bear in mind, I am still a novice at administering SQL, so please mak
e your response a little detailed.
Thanks
---
Roy Chastain
KMSystems, Inc.Hi Roy,
Welcome to MSDN newsgroup.
Regarding on the problem you mentioned, I think the it'll be a bit
difficult to meet all your requirement. Here are some of my understandings:
First, ASP will always impersonate the anonymous account( IIS's default
IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
Then, when our asp page try accessing any protected resource, the
IUSR_machine account will be the authenticated and executing account of our
asp page's thread. Then, return back to the quesitons you mentioned:
==============
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not
a domain account) as authenticated
==============
1) I think the most standard means for allow all users to access db is to
use SQLServer Autehntcaiton(provide the username account in
connectionstring. This will require the SQLServer db to allow SQL
authentication. In fact, this is limited by the ASP , in asp.net we can
impersonate a certain fixed account so as to use t hat account to access
the database through integrated windows authentication.
2) If the webserver and SqlServer's database server is the same box, we
can simply grant the IIS's IUSR_MACHINE account the permisson to access
sqlserver db. However, as you said the DB server is a remote server to the
webserver, the IUSR_machine(local account ) on webserver is not valid on DB
server. For such scenario, there are two options:
a. use a Domain Account as your IIS virtual dir's anonymous account.
(Seems you didn't want to use DomainAcount
)
b. create a duplicate local account on the SQLServer 's machine which has
the same username and password with the IIS virtual dir's anonymous account
(on the webserver box). However, the IIS's default anonymous account(
IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
we need to either explicitly set IUSR_MACHINE's password or create a
custom local account and replace the IUSR_machine as the virtual dir's
anonymous account.
Anyway, since there hasn't any means which will satisfy all the
requirement, we may need to make our decision according to the actual
situation. Please have a look of all the above things and feel free to let
us know if you have any ideas.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Here is what I did and it is working and I think secure for what I want.
I created a new domain account and gave it insert and select access to the d
atabase.
I set anonymous access for the web site to that account.
I created a Application Pool on the web server and have it running under tha
t account.
I set the web site to use the newly created Application Pool.
From my point of view that gives all anonymous users of this one web site an
onymous access to the SQL database for the purposes of
inserting and selecting. I would have been easier is SQL supported some sor
t of anonymous access setting that said don't validate
this user, just let them do this, but...
Please let me know if you see any flaws in my reasoning.
Thanks
On Thu, 14 Apr 2005 05:18:35 GMT, v-schang@.online.microsoft.com (Steven Chen
g[MSFT]) wrote:
>Hi Roy,
>Welcome to MSDN newsgroup.
>Regarding on the problem you mentioned, I think the it'll be a bit
>difficult to meet all your requirement. Here are some of my understandings:
>First, ASP will always impersonate the anonymous account( IIS's default
>IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
>Then, when our asp page try accessing any protected resource, the
>IUSR_machine account will be the authenticated and executing account of our
>asp page's thread. Then, return back to the quesitons you mentioned:
>==============
>What is the correct way to do one of the following.
>1) - Allow all users (authenticated or not) to update a database?
>2) - Set up the SQL server so that it can treat the 2003 local account (not
>a domain account) as authenticated
>==============
>1) I think the most standard means for allow all users to access db is to
>use SQLServer Autehntcaiton(provide the username account in
>connectionstring. This will require the SQLServer db to allow SQL
>authentication. In fact, this is limited by the ASP , in asp.net we can
>impersonate a certain fixed account so as to use t hat account to access
>the database through integrated windows authentication.
>2) If the webserver and SqlServer's database server is the same box, we
>can simply grant the IIS's IUSR_MACHINE account the permisson to access
>sqlserver db. However, as you said the DB server is a remote server to the
>webserver, the IUSR_machine(local account ) on webserver is not valid on DB
>server. For such scenario, there are two options:
>a. use a Domain Account as your IIS virtual dir's anonymous account.
>(Seems you didn't want to use DomainAcount )
>b. create a duplicate local account on the SQLServer 's machine which has
>the same username and password with the IIS virtual dir's anonymous account
>(on the webserver box). However, the IIS's default anonymous account(
>IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
>we need to either explicitly set IUSR_MACHINE's password or create a
>custom local account and replace the IUSR_machine as the virtual dir's
>anonymous account.
>Anyway, since there hasn't any means which will satisfy all the
>requirement, we may need to make our decision according to the actual
>situation. Please have a look of all the above things and feel free to let
>us know if you have any ideas.
>Thanks,
>Steven Cheng
>Microsoft Online Support
>Get Secure! www.microsoft.com/security
>(This posting is provided "AS IS", with no warranties, and confers no
>rights.)
>
>
---
Roy Chastain
KMSystems, Inc.|||Glad to hear from you Roy,
I think it's OK since ASP will impersonate the authenticated user by
default( if allow anonymous then impersonate the anonymous user). And
using Integrated windows authentication at back end db is also what we
recommend.
BTW, is there any future action plan that you'll migrate your web
application from classic ASP to ASP.NET. The asp.net web app framework will
have more strong support for stable and high performance web application.
Also, as for security, the asp.net can let the asp.net running under the
process idenity (the application pool identity in IIS6) together with allow
anonymous in IIS. Thus, we can still keep the IIS's anonymous account as a
very restricted account.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
hentication only.
I have a Windows 2003 Web Edition server that is a member of the domain.
When permissions are correct I can open and update databases on the server f
rom the Web Edition server.
I want to allow anyone that hits a web site on the 2003 server to be able to
update a specific database on the SQL server. The
users on that site are running under the local IIS anonymous user id.
I tried the obvious and simple connection
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Ini
tial Catalog=WebRedirectLog;Data Source=ZEUS;
Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=ROY;Use Encryption for Data=False;
Tag with column collation when possible=False");
(which I expected to fail) and it did telling me
Microsoft OLE DB Provider for SQL Server error '80004005'
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Se
rver connection.
(I did NOT supply a userid in the call to ADODB.Connection.Open
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not
a domain account) as authenticated.
I do NOT want to allowed mixed mode authentication on this server.
Please bear in mind, I am still a novice at administering SQL, so please mak
e your response a little detailed.
Thanks
---
Roy Chastain
KMSystems, Inc.Hi Roy,
Welcome to MSDN newsgroup.
Regarding on the problem you mentioned, I think the it'll be a bit
difficult to meet all your requirement. Here are some of my understandings:
First, ASP will always impersonate the anonymous account( IIS's default
IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
Then, when our asp page try accessing any protected resource, the
IUSR_machine account will be the authenticated and executing account of our
asp page's thread. Then, return back to the quesitons you mentioned:
==============
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not
a domain account) as authenticated
==============
1) I think the most standard means for allow all users to access db is to
use SQLServer Autehntcaiton(provide the username account in
connectionstring. This will require the SQLServer db to allow SQL
authentication. In fact, this is limited by the ASP , in asp.net we can
impersonate a certain fixed account so as to use t hat account to access
the database through integrated windows authentication.
2) If the webserver and SqlServer's database server is the same box, we
can simply grant the IIS's IUSR_MACHINE account the permisson to access
sqlserver db. However, as you said the DB server is a remote server to the
webserver, the IUSR_machine(local account ) on webserver is not valid on DB
server. For such scenario, there are two options:
a. use a Domain Account as your IIS virtual dir's anonymous account.
(Seems you didn't want to use DomainAcount
)b. create a duplicate local account on the SQLServer 's machine which has
the same username and password with the IIS virtual dir's anonymous account
(on the webserver box). However, the IIS's default anonymous account(
IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
we need to either explicitly set IUSR_MACHINE's password or create a
custom local account and replace the IUSR_machine as the virtual dir's
anonymous account.
Anyway, since there hasn't any means which will satisfy all the
requirement, we may need to make our decision according to the actual
situation. Please have a look of all the above things and feel free to let
us know if you have any ideas.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Here is what I did and it is working and I think secure for what I want.
I created a new domain account and gave it insert and select access to the d
atabase.
I set anonymous access for the web site to that account.
I created a Application Pool on the web server and have it running under tha
t account.
I set the web site to use the newly created Application Pool.
From my point of view that gives all anonymous users of this one web site an
onymous access to the SQL database for the purposes of
inserting and selecting. I would have been easier is SQL supported some sor
t of anonymous access setting that said don't validate
this user, just let them do this, but...
Please let me know if you see any flaws in my reasoning.
Thanks
On Thu, 14 Apr 2005 05:18:35 GMT, v-schang@.online.microsoft.com (Steven Chen
g[MSFT]) wrote:
>Hi Roy,
>Welcome to MSDN newsgroup.
>Regarding on the problem you mentioned, I think the it'll be a bit
>difficult to meet all your requirement. Here are some of my understandings:
>First, ASP will always impersonate the anonymous account( IIS's default
>IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
>Then, when our asp page try accessing any protected resource, the
>IUSR_machine account will be the authenticated and executing account of our
>asp page's thread. Then, return back to the quesitons you mentioned:
>==============
>What is the correct way to do one of the following.
>1) - Allow all users (authenticated or not) to update a database?
>2) - Set up the SQL server so that it can treat the 2003 local account (not
>a domain account) as authenticated
>==============
>1) I think the most standard means for allow all users to access db is to
>use SQLServer Autehntcaiton(provide the username account in
>connectionstring. This will require the SQLServer db to allow SQL
>authentication. In fact, this is limited by the ASP , in asp.net we can
>impersonate a certain fixed account so as to use t hat account to access
>the database through integrated windows authentication.
>2) If the webserver and SqlServer's database server is the same box, we
>can simply grant the IIS's IUSR_MACHINE account the permisson to access
>sqlserver db. However, as you said the DB server is a remote server to the
>webserver, the IUSR_machine(local account ) on webserver is not valid on DB
>server. For such scenario, there are two options:
>a. use a Domain Account as your IIS virtual dir's anonymous account.
>(Seems you didn't want to use DomainAcount
)>b. create a duplicate local account on the SQLServer 's machine which has
>the same username and password with the IIS virtual dir's anonymous account
>(on the webserver box). However, the IIS's default anonymous account(
>IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
>we need to either explicitly set IUSR_MACHINE's password or create a
>custom local account and replace the IUSR_machine as the virtual dir's
>anonymous account.
>Anyway, since there hasn't any means which will satisfy all the
>requirement, we may need to make our decision according to the actual
>situation. Please have a look of all the above things and feel free to let
>us know if you have any ideas.
>Thanks,
>Steven Cheng
>Microsoft Online Support
>Get Secure! www.microsoft.com/security
>(This posting is provided "AS IS", with no warranties, and confers no
>rights.)
>
>
---
Roy Chastain
KMSystems, Inc.|||Glad to hear from you Roy,
I think it's OK since ASP will impersonate the authenticated user by
default( if allow anonymous then impersonate the anonymous user). And
using Integrated windows authentication at back end db is also what we
recommend.
BTW, is there any future action plan that you'll migrate your web
application from classic ASP to ASP.NET. The asp.net web app framework will
have more strong support for stable and high performance web application.
Also, as for security, the asp.net can let the asp.net running under the
process idenity (the application pool identity in IIS6) together with allow
anonymous in IIS. Thus, we can still keep the IIS's anonymous account as a
very restricted account.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Anonymous connection to a remote server
I have a SQL server running on a Win2k Domain Controller set for Windows Authentication only.
I have a Windows 2003 Web Edition server that is a member of the domain.
When permissions are correct I can open and update databases on the server from the Web Edition server.
I want to allow anyone that hits a web site on the 2003 server to be able to update a specific database on the SQL server. The
users on that site are running under the local IIS anonymous user id.
I tried the obvious and simple connection
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=WebRedirectLog;Data Source=ZEUS;
Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ROY;Use Encryption for Data=False;
Tag with column collation when possible=False");
(which I expected to fail) and it did telling me
Microsoft OLE DB Provider for SQL Server error '80004005'
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
(I did NOT supply a userid in the call to ADODB.Connection.Open
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not a domain account) as authenticated.
I do NOT want to allowed mixed mode authentication on this server.
Please bear in mind, I am still a novice at administering SQL, so please make your response a little detailed.
Thanks
Roy Chastain
KMSystems, Inc.
Hi Roy,
Welcome to MSDN newsgroup.
Regarding on the problem you mentioned, I think the it'll be a bit
difficult to meet all your requirement. Here are some of my understandings:
First, ASP will always impersonate the anonymous account( IIS's default
IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
Then, when our asp page try accessing any protected resource, the
IUSR_machine account will be the authenticated and executing account of our
asp page's thread. Then, return back to the quesitons you mentioned:
==============
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not
a domain account) as authenticated
==============
1) I think the most standard means for allow all users to access db is to
use SQLServer Autehntcaiton(provide the username account in
connectionstring. This will require the SQLServer db to allow SQL
authentication. In fact, this is limited by the ASP , in asp.net we can
impersonate a certain fixed account so as to use t hat account to access
the database through integrated windows authentication.
2) If the webserver and SqlServer's database server is the same box, we
can simply grant the IIS's IUSR_MACHINE account the permisson to access
sqlserver db. However, as you said the DB server is a remote server to the
webserver, the IUSR_machine(local account ) on webserver is not valid on DB
server. For such scenario, there are two options:
a. use a Domain Account as your IIS virtual dir's anonymous account.
(Seems you didn't want to use DomainAcount )
b. create a duplicate local account on the SQLServer 's machine which has
the same username and password with the IIS virtual dir's anonymous account
(on the webserver box). However, the IIS's default anonymous account(
IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
we need to either explicitly set IUSR_MACHINE's password or create a
custom local account and replace the IUSR_machine as the virtual dir's
anonymous account.
Anyway, since there hasn't any means which will satisfy all the
requirement, we may need to make our decision according to the actual
situation. Please have a look of all the above things and feel free to let
us know if you have any ideas.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Here is what I did and it is working and I think secure for what I want.
I created a new domain account and gave it insert and select access to the database.
I set anonymous access for the web site to that account.
I created a Application Pool on the web server and have it running under that account.
I set the web site to use the newly created Application Pool.
From my point of view that gives all anonymous users of this one web site anonymous access to the SQL database for the purposes of
inserting and selecting. I would have been easier is SQL supported some sort of anonymous access setting that said don't validate
this user, just let them do this, but...
Please let me know if you see any flaws in my reasoning.
Thanks
On Thu, 14 Apr 2005 05:18:35 GMT, v-schang@.online.microsoft.com (Steven Cheng[MSFT]) wrote:
>Hi Roy,
>Welcome to MSDN newsgroup.
>Regarding on the problem you mentioned, I think the it'll be a bit
>difficult to meet all your requirement. Here are some of my understandings:
>First, ASP will always impersonate the anonymous account( IIS's default
>IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
>Then, when our asp page try accessing any protected resource, the
>IUSR_machine account will be the authenticated and executing account of our
>asp page's thread. Then, return back to the quesitons you mentioned:
>==============
>What is the correct way to do one of the following.
>1) - Allow all users (authenticated or not) to update a database?
>2) - Set up the SQL server so that it can treat the 2003 local account (not
>a domain account) as authenticated
>==============
>1) I think the most standard means for allow all users to access db is to
>use SQLServer Autehntcaiton(provide the username account in
>connectionstring. This will require the SQLServer db to allow SQL
>authentication. In fact, this is limited by the ASP , in asp.net we can
>impersonate a certain fixed account so as to use t hat account to access
>the database through integrated windows authentication.
>2) If the webserver and SqlServer's database server is the same box, we
>can simply grant the IIS's IUSR_MACHINE account the permisson to access
>sqlserver db. However, as you said the DB server is a remote server to the
>webserver, the IUSR_machine(local account ) on webserver is not valid on DB
>server. For such scenario, there are two options:
>a. use a Domain Account as your IIS virtual dir's anonymous account.
>(Seems you didn't want to use DomainAcount )
>b. create a duplicate local account on the SQLServer 's machine which has
>the same username and password with the IIS virtual dir's anonymous account
>(on the webserver box). However, the IIS's default anonymous account(
>IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
>we need to either explicitly set IUSR_MACHINE's password or create a
>custom local account and replace the IUSR_machine as the virtual dir's
>anonymous account.
>Anyway, since there hasn't any means which will satisfy all the
>requirement, we may need to make our decision according to the actual
>situation. Please have a look of all the above things and feel free to let
>us know if you have any ideas.
>Thanks,
>Steven Cheng
>Microsoft Online Support
>Get Secure! www.microsoft.com/security
>(This posting is provided "AS IS", with no warranties, and confers no
>rights.)
>
>
Roy Chastain
KMSystems, Inc.
|||Glad to hear from you Roy,
I think it's OK since ASP will impersonate the authenticated user by
default( if allow anonymous then impersonate the anonymous user). And
using Integrated windows authentication at back end db is also what we
recommend.
BTW, is there any future action plan that you'll migrate your web
application from classic ASP to ASP.NET. The asp.net web app framework will
have more strong support for stable and high performance web application.
Also, as for security, the asp.net can let the asp.net running under the
process idenity (the application pool identity in IIS6) together with allow
anonymous in IIS. Thus, we can still keep the IIS's anonymous account as a
very restricted account.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
I have a Windows 2003 Web Edition server that is a member of the domain.
When permissions are correct I can open and update databases on the server from the Web Edition server.
I want to allow anyone that hits a web site on the 2003 server to be able to update a specific database on the SQL server. The
users on that site are running under the local IIS anonymous user id.
I tried the obvious and simple connection
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=WebRedirectLog;Data Source=ZEUS;
Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=ROY;Use Encryption for Data=False;
Tag with column collation when possible=False");
(which I expected to fail) and it did telling me
Microsoft OLE DB Provider for SQL Server error '80004005'
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
(I did NOT supply a userid in the call to ADODB.Connection.Open
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not a domain account) as authenticated.
I do NOT want to allowed mixed mode authentication on this server.
Please bear in mind, I am still a novice at administering SQL, so please make your response a little detailed.
Thanks
Roy Chastain
KMSystems, Inc.
Hi Roy,
Welcome to MSDN newsgroup.
Regarding on the problem you mentioned, I think the it'll be a bit
difficult to meet all your requirement. Here are some of my understandings:
First, ASP will always impersonate the anonymous account( IIS's default
IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
Then, when our asp page try accessing any protected resource, the
IUSR_machine account will be the authenticated and executing account of our
asp page's thread. Then, return back to the quesitons you mentioned:
==============
What is the correct way to do one of the following.
1) - Allow all users (authenticated or not) to update a database?
2) - Set up the SQL server so that it can treat the 2003 local account (not
a domain account) as authenticated
==============
1) I think the most standard means for allow all users to access db is to
use SQLServer Autehntcaiton(provide the username account in
connectionstring. This will require the SQLServer db to allow SQL
authentication. In fact, this is limited by the ASP , in asp.net we can
impersonate a certain fixed account so as to use t hat account to access
the database through integrated windows authentication.
2) If the webserver and SqlServer's database server is the same box, we
can simply grant the IIS's IUSR_MACHINE account the permisson to access
sqlserver db. However, as you said the DB server is a remote server to the
webserver, the IUSR_machine(local account ) on webserver is not valid on DB
server. For such scenario, there are two options:
a. use a Domain Account as your IIS virtual dir's anonymous account.
(Seems you didn't want to use DomainAcount
)b. create a duplicate local account on the SQLServer 's machine which has
the same username and password with the IIS virtual dir's anonymous account
(on the webserver box). However, the IIS's default anonymous account(
IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
we need to either explicitly set IUSR_MACHINE's password or create a
custom local account and replace the IUSR_machine as the virtual dir's
anonymous account.
Anyway, since there hasn't any means which will satisfy all the
requirement, we may need to make our decision according to the actual
situation. Please have a look of all the above things and feel free to let
us know if you have any ideas.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Here is what I did and it is working and I think secure for what I want.
I created a new domain account and gave it insert and select access to the database.
I set anonymous access for the web site to that account.
I created a Application Pool on the web server and have it running under that account.
I set the web site to use the newly created Application Pool.
From my point of view that gives all anonymous users of this one web site anonymous access to the SQL database for the purposes of
inserting and selecting. I would have been easier is SQL supported some sort of anonymous access setting that said don't validate
this user, just let them do this, but...
Please let me know if you see any flaws in my reasoning.
Thanks
On Thu, 14 Apr 2005 05:18:35 GMT, v-schang@.online.microsoft.com (Steven Cheng[MSFT]) wrote:
>Hi Roy,
>Welcome to MSDN newsgroup.
>Regarding on the problem you mentioned, I think the it'll be a bit
>difficult to meet all your requirement. Here are some of my understandings:
>First, ASP will always impersonate the anonymous account( IIS's default
>IUSR_machine account) if we enable anonymous access in our IIS virtual dir.
>Then, when our asp page try accessing any protected resource, the
>IUSR_machine account will be the authenticated and executing account of our
>asp page's thread. Then, return back to the quesitons you mentioned:
>==============
>What is the correct way to do one of the following.
>1) - Allow all users (authenticated or not) to update a database?
>2) - Set up the SQL server so that it can treat the 2003 local account (not
>a domain account) as authenticated
>==============
>1) I think the most standard means for allow all users to access db is to
>use SQLServer Autehntcaiton(provide the username account in
>connectionstring. This will require the SQLServer db to allow SQL
>authentication. In fact, this is limited by the ASP , in asp.net we can
>impersonate a certain fixed account so as to use t hat account to access
>the database through integrated windows authentication.
>2) If the webserver and SqlServer's database server is the same box, we
>can simply grant the IIS's IUSR_MACHINE account the permisson to access
>sqlserver db. However, as you said the DB server is a remote server to the
>webserver, the IUSR_machine(local account ) on webserver is not valid on DB
>server. For such scenario, there are two options:
>a. use a Domain Account as your IIS virtual dir's anonymous account.
>(Seems you didn't want to use DomainAcount
)>b. create a duplicate local account on the SQLServer 's machine which has
>the same username and password with the IIS virtual dir's anonymous account
>(on the webserver box). However, the IIS's default anonymous account(
>IUSR_MACHINE) 'S password is controled by machine rather than ourself. So
>we need to either explicitly set IUSR_MACHINE's password or create a
>custom local account and replace the IUSR_machine as the virtual dir's
>anonymous account.
>Anyway, since there hasn't any means which will satisfy all the
>requirement, we may need to make our decision according to the actual
>situation. Please have a look of all the above things and feel free to let
>us know if you have any ideas.
>Thanks,
>Steven Cheng
>Microsoft Online Support
>Get Secure! www.microsoft.com/security
>(This posting is provided "AS IS", with no warranties, and confers no
>rights.)
>
>
Roy Chastain
KMSystems, Inc.
|||Glad to hear from you Roy,
I think it's OK since ASP will impersonate the authenticated user by
default( if allow anonymous then impersonate the anonymous user). And
using Integrated windows authentication at back end db is also what we
recommend.
BTW, is there any future action plan that you'll migrate your web
application from classic ASP to ASP.NET. The asp.net web app framework will
have more strong support for stable and high performance web application.
Also, as for security, the asp.net can let the asp.net running under the
process idenity (the application pool identity in IIS6) together with allow
anonymous in IIS. Thus, we can still keep the IIS's anonymous account as a
very restricted account.
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Saturday, February 25, 2012
Anonymous access in Reporting Services
Trying to set up a Reporting Services / Windows Sharepoint services demo
site and I am a little confused about the best way to allow anonymous
access. I would like to allow annonymous users to run reports but not set
properties, create subscriptions, or upload reports. I have seen some posts
that indicate that this is difficult to do correctly and that I should build
a custom authentication module. What is the state of the art here? Does
using SP1 or waiting for SP2 help?
Thanks,
SteveYou can assign a user to the anonymous account. Give this user just enough
rights to do what you want.
--
| From: "Stephen Walch" <swalch@.online.nospam>
| Subject: Anonymous access in Reporting Services
| Date: Mon, 14 Feb 2005 11:35:35 -0500
| Lines: 13
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <OoCR3MrEFHA.2568@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 69-164-66-20.lndnnh.adelphia.net 69.164.66.20
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:35873
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Trying to set up a Reporting Services / Windows Sharepoint services demo
| site and I am a little confused about the best way to allow anonymous
| access. I would like to allow annonymous users to run reports but not
set
| properties, create subscriptions, or upload reports. I have seen some
posts
| that indicate that this is difficult to do correctly and that I should
build
| a custom authentication module. What is the state of the art here? Does
| using SP1 or waiting for SP2 help?
|
| Thanks,
|
| Steve
|
|
|
site and I am a little confused about the best way to allow anonymous
access. I would like to allow annonymous users to run reports but not set
properties, create subscriptions, or upload reports. I have seen some posts
that indicate that this is difficult to do correctly and that I should build
a custom authentication module. What is the state of the art here? Does
using SP1 or waiting for SP2 help?
Thanks,
SteveYou can assign a user to the anonymous account. Give this user just enough
rights to do what you want.
--
| From: "Stephen Walch" <swalch@.online.nospam>
| Subject: Anonymous access in Reporting Services
| Date: Mon, 14 Feb 2005 11:35:35 -0500
| Lines: 13
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <OoCR3MrEFHA.2568@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 69-164-66-20.lndnnh.adelphia.net 69.164.66.20
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:35873
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Trying to set up a Reporting Services / Windows Sharepoint services demo
| site and I am a little confused about the best way to allow anonymous
| access. I would like to allow annonymous users to run reports but not
set
| properties, create subscriptions, or upload reports. I have seen some
posts
| that indicate that this is difficult to do correctly and that I should
build
| a custom authentication module. What is the state of the art here? Does
| using SP1 or waiting for SP2 help?
|
| Thanks,
|
| Steve
|
|
|
Sunday, February 19, 2012
Anaylsis Services over HTTP
Hi guys,
I am hoping this will be a quick one. I am using Analysis Services 2000 with SP4 on Windows XP. It is the Developer Edition so I should have all the functionality of the Enterprise Edition without the scalability.
Ok, so I have followed the “INF: How to Connect to Analysis Server 2000 By Using HTTP Connection” (http://support.microsoft.com/?kbid=279489) article on MSDN. On completing the steps I did see a blank page, which suggests everything is working properly. However, when I try to connect to Analysis Services via HTTP it is unable to see the OLAP database. I have tried this with the MDX Sample Application, Excel and an OLAP Report web application and neither can connect to the database using HTTP. I can, however, connect to the OLAP database if I connect to the server without using HTTP.
I could well be missing something very simple and if that’s the case then great. I have tried changing the security of IIS to use anonymous, basic authentication and windows integrated authentication but neither affect the visibility of the OLAP database. I have also examined the IIS log files but there is nothing that indicates errors.
Any help would be gratefully appreciated.
Let's try to get this straight:
- you can connect to the server over HTTP
- but you can't see any databases
Is that accurate? If yes, then most likely it is a permissions issue -- turn *off* the Anonymous access and turn *on* Integrated authentication. Then try again.
HTH,
Akshai
Thursday, February 16, 2012
Analysis services?
Hi!
I am confused ...downloaded Microsoft SQL server 2005 (for reporting services) to my Windows 2002 (32-bit systems), but it asks me to install the service packs as well...
I get the following answer though "Your OS does nor support the Service Pack required for this SQL server release."
I am confused ...downloaded Microsoft SQL server 2005 (for reporting services) to my Windows 2002 (32-bit systems), but it asks me to install the service packs as well...
So Windows XP Service Pack 2 is already installed.
And I need to download Windows server 2000 or 2003 R2, but where could I find a free trial version?
Do I also need Asp.net and IIS?
I would be very grateful for some help... to clarify which components needed.
Since you're wanting RS, you'll need to install SQL Server Express with Advanced Services. Here's the link:
http://msdn.microsoft.com/vstudio/express/sql/compare/default.aspx
This product is supported on XP SP2, so you won't need to do any OS upgrades.
Thanks,
Sam Lester (MSFT)
I get the following answer though "Your OS does nor support the Service Pack required for this SQL server release."
So my OS is 2002...
How do I check which SP required?
I did not find any .net Framework 2.0 SP.|||I managed to download SQL Server 2005 Express Edition with Advanced
Services SP1, and noticed Reporting services are available but how about "Analysis services" ?
|||
Analysis Services does not ship with any of the Express SKUs. It is part of the other SKUs (Enterprise, Standard, etc). If you want to play around with it, you can download the evaluation version found here:
http://www.microsoft.com/sql/downloads/trial-software.mspx
Thanks,
Sam Lester (MSFT)
Analysis Services through Management Studio: cannot connect. Possible workaround.
I have a SQL server 2005 developer edition installation on my Windows XP SP2 workstation. SQL Server was installed after Visual Studio 2005 pro, and therefore after SQL Express. From what I recall, SQL Browser was also installed with SQL Express using the Local System account as the Browser service account.
As per best-security practice, I have installed 2005 developer edition with the accounts for the services as local accounts (different account for each service). Obviously, due to the VS2005 install, Browser was installed using the Local System account. I therefore created a local account for the Browser, and put it into the SQLServer2005SQLBrowserUser$MACHINENAME security group. I then switched the Browser service account to the local account using Configuration Manager, which therefore restarted the Browser service.
Next, when accessing Analysis Services (SSAS) through the management studio (SSMS) I got the following error:
Cannot connect to MACHINENAME\INSTANCENAME
|
--A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)
|
--No connection could be made because the target machine actively refused it (System)
Changing the Browser logon account back to Local System allowed me to connect to SSAS through SSMS (after a Browser service restart).
Reading BOL for 2005, it states that the security permissions required for the Browser service account are:
90\shared\msmdlocal.ini
Read
90\shared
Read, Execute
90\shared\Errordumps
Read, Write
Looking at those folders, I could see that this was the set correctly, as implied through the SQLServer2005SQLBrowserUser$MACHINENAME security group membership which SYSTEM (set through the installation of SQL express, presumably) and the local account were members of. msmdlocal.ini does not exist on my installation.
However, the major clue that I could see was that SYSTEM account had Full access to the Shared and ErrorDumps folders by default; so I then elevated the SQLServer2005SQLBrowserUser$MACHINENAME security permissions to Full on the Shared and ErrorDumps folders, set the Browser service back to the local user account and restarted it. Bingo! I can now access the SSAS through SSMS.
Better still, I immediately reduced the SQLServer2005SQLBrowserUser$MACHINENAME to the recommended settings, restarted the Browser service, and I can still access SSAS through SSMS.
My question is ... why? What happened during my restart under elevated permissions that let the Browser service work properly? I suspect it is something along the lines of the local account taking ownership of something :-s, although I really have no idea. Is this a recognised problem? Is there a KB article on it? Do we have to do similar things to this when we change service accounts for other SQL services?
Andy
Did you use SQL Server Configuration Manager to change account or Windows Computer Manager?
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Configuration Manager. Though I take it you mean the Service console snap-in rather than Computer Manager directly? However the answer is still SSCM.
Andy
|||In this case I dont see any explanation to the behavior you describe.
Can you please try an contact Customer support about this situation.
Thanks.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
There was something in your original description that may explain what is going on. According to your 1st post, you:
1. Change the account of the SQL Browser
2. Then added the account to the SQL Browser User group.
3. Then updated the account through SQL Computer Manger.
You should have only done step 3.
By doing 1 and 2, the SQL Computer Manger could have been tricked into thinking you only wished to changes the password. SQL Computer Manager needs to do other operation, individual to each service, than change the account and update the user group.
This also fits with the problem "going away" after you "fixed" the problem.
Does this seem like a possibility?
|||I had the exact problem as you described, SQL Browser started fine, allowed access to a SQL instance, but actively refused connection to a SSAS instance.
Switching to System solved the problem, but the permanent fix was to increase the permissions to the Shared + Error dump folders as described above.
|||Thanks so much for posting the details of your experience! I had the same problem and was able to fix it because of your post.
Cheers!
Analysis Services through Management Studio: cannot connect. Possible workaround.
I have a SQL server 2005 developer edition installation on my Windows XP SP2 workstation. SQL Server was installed after Visual Studio 2005 pro, and therefore after SQL Express. From what I recall, SQL Browser was also installed with SQL Express using the Local System account as the Browser service account.
As per best-security practice, I have installed 2005 developer edition with the accounts for the services as local accounts (different account for each service). Obviously, due to the VS2005 install, Browser was installed using the Local System account. I therefore created a local account for the Browser, and put it into the SQLServer2005SQLBrowserUser$MACHINENAME security group. I then switched the Browser service account to the local account using Configuration Manager, which therefore restarted the Browser service.
Next, when accessing Analysis Services (SSAS) through the management studio (SSMS) I got the following error:
Cannot connect to MACHINENAME\INSTANCENAME
|
--A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)
|
--No connection could be made because the target machine actively refused it (System)
Changing the Browser logon account back to Local System allowed me to connect to SSAS through SSMS (after a Browser service restart).
Reading BOL for 2005, it states that the security permissions required for the Browser service account are:
90\shared\msmdlocal.ini
Read
90\shared
Read, Execute
90\shared\Errordumps
Read, Write
Looking at those folders, I could see that this was the set correctly, as implied through the SQLServer2005SQLBrowserUser$MACHINENAME security group membership which SYSTEM (set through the installation of SQL express, presumably) and the local account were members of. msmdlocal.ini does not exist on my installation.
However, the major clue that I could see was that SYSTEM account had Full access to the Shared and ErrorDumps folders by default; so I then elevated the SQLServer2005SQLBrowserUser$MACHINENAME security permissions to Full on the Shared and ErrorDumps folders, set the Browser service back to the local user account and restarted it. Bingo! I can now access the SSAS through SSMS.
Better still, I immediately reduced the SQLServer2005SQLBrowserUser$MACHINENAME to the recommended settings, restarted the Browser service, and I can still access SSAS through SSMS.
My question is ... why? What happened during my restart under elevated permissions that let the Browser service work properly? I suspect it is something along the lines of the local account taking ownership of something :-s, although I really have no idea. Is this a recognised problem? Is there a KB article on it? Do we have to do similar things to this when we change service accounts for other SQL services?
Andy
Did you use SQL Server Configuration Manager to change account or Windows Computer Manager?
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Configuration Manager. Though I take it you mean the Service console snap-in rather than Computer Manager directly? However the answer is still SSCM.
Andy
|||In this case I dont see any explanation to the behavior you describe.
Can you please try an contact Customer support about this situation.
Thanks.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
There was something in your original description that may explain what is going on. According to your 1st post, you:
1. Change the account of the SQL Browser
2. Then added the account to the SQL Browser User group.
3. Then updated the account through SQL Computer Manger.
You should have only done step 3.
By doing 1 and 2, the SQL Computer Manger could have been tricked into thinking you only wished to changes the password. SQL Computer Manager needs to do other operation, individual to each service, than change the account and update the user group.
This also fits with the problem "going away" after you "fixed" the problem.
Does this seem like a possibility?
|||I had the exact problem as you described, SQL Browser started fine, allowed access to a SQL instance, but actively refused connection to a SSAS instance.
Switching to System solved the problem, but the permanent fix was to increase the permissions to the Shared + Error dump folders as described above.
|||Thanks so much for posting the details of your experience! I had the same problem and was able to fix it because of your post.
Cheers!
Sunday, February 12, 2012
Analysis Services Cube Processing Timing
We are currently using Analysis Services 2000 sp3 on Windows 2003 sp1. We have a large cube that is partitioned and is processed nightly using Parallel Processing utility.
It takes over 4 hours to process that cube, however if the processing is killed after first few minutes and then Olap server is restarted and the cube processing is restarted it only takes 55 minutes.
Has anybody encountered similar situation?
Thank you,
Alex
Looks like you are hitting a timing issue. Try changing number of partitions you process in parallel.
You can also try and upgrade to the latest service pack ( SP4 ).
If still doesn't help, please open a case with Customer Support.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Thursday, February 9, 2012
Analysis Services 2005 is not getting connected
All,
I am running Windows 2003 Server Standard Edition. Installed SQL Server 2000 and SP4 with default instance.
Installed SQL Server 2005 including analysis services with named instance (SQL2005).
Able to connect to SQL Server 2005. But when i am trying to connect to analysis services with
Windows authentication and server name as "UKPRESALES1\SQL2005".
Getting an Error : "A connection cannot be made to redirector. ensure that sqlbrowser service is running."
Pls suggest me. Finally I ended up in un-installing everything and trying out.
Raja.
Have you read the following paper on AS connectivity problems?
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspx
It could be a firewall problem...
HTH,
Chris
|||Indeed, please make sure that both Analysis Services 2005 (msmdsrv.exe) and SQL Browser are allowed in the firewall to accept connections (related article here: http://msdn2.microsoft.com/en-us/library/ms175638.aspx).
|||hello chris,
Windows Firewall have been disabled already. That too i am trying to connect in the local machine. Even the Sql browser service is running under local account.
As of now i am trying to install sql 2005 in default instance. Later I will install Sql 2000 with named instance.
Pls help me if any other solution is available.
Rajas.
|||So, to be clear - you can't connect even from the same server that you've installed AS on?Analysis Services 2005 is not getting connected
All,
I am running Windows 2003 Server Standard Edition. Installed SQL Server 2000 and SP4 with default instance.
Installed SQL Server 2005 including analysis services with named instance (SQL2005).
Able to connect to SQL Server 2005. But when i am trying to connect to analysis services with
Windows authentication and server name as "UKPRESALES1\SQL2005".
Getting an Error : "A connection cannot be made to redirector. ensure that sqlbrowser service is running."
Pls suggest me. Finally I ended up in un-installing everything and trying out.
Raja.
Have you read the following paper on AS connectivity problems?
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/CISQL2005ASCS.mspx
It could be a firewall problem...
HTH,
Chris
|||Indeed, please make sure that both Analysis Services 2005 (msmdsrv.exe) and SQL Browser are allowed in the firewall to accept connections (related article here: http://msdn2.microsoft.com/en-us/library/ms175638.aspx).
|||hello chris,
Windows Firewall have been disabled already. That too i am trying to connect in the local machine. Even the Sql browser service is running under local account.
As of now i am trying to install sql 2005 in default instance. Later I will install Sql 2000 with named instance.
Pls help me if any other solution is available.
Rajas.
|||So, to be clear - you can't connect even from the same server that you've installed AS on?
Subscribe to:
Posts (Atom)