Showing posts with label configuration. Show all posts
Showing posts with label configuration. Show all posts

Friday, March 30, 2012

Manage remote instances using SQL Server 2005 tools

I'm trying to manage remote instances using the following SQL Server 2005 tools:

SQL Server Configuration Manager (SSCM)

SQL Server Surface Area Configuration (SSSAC)

It seems that SSSAC is designed to manage remote instances since it has the "change computer" link. On the other hand, SSCM does not have such link or something similar. I read this MS article http://msdn2.microsoft.com/en-US/library/ms190622.aspx so it seems that this is a way to run SSCM to manage remote instance. I was having problem to show SQL Server 2005 Services by following that article. However, the problem is now gone (don't know why yet).

What are the reasons that the SSCM is not designed to manage remote instances but SSSAC is? If SSCM is really designed not to manage remote instances, what is the purpose of having the word (Local) next to the SQL Server Configuration Manager?

If I understand correctly, the only main difference between SSCM and SSSAC is that SSCM does not have the functionality to turn on and off features according to http://www.microsoft.com/technet/prodtechnol/sql/2005/mgsqlexpwssmse.mspx

So, if I don't need to turn on and off features, SSCM will be the only tool needed if it can manage remote instance.

With SQL Server Management Studio (SSMS) open and Registered Servers visible, right-click a remote reristered server and click SQL Server Configuration Manager. You will see SQL Server Configuration Manager(remote server name).|||

Hi lkh,

Thanks for the information. I wonder why MS does not implement the change computer in SSCM. Is there a way to run SSSAC in SSMS?

|||

it is a command prompt utility see it can be run in Query Editor in sqlcmd mode

See SQL Server 2005 Books Online topic:

sac Utility

http://msdn2.microsoft.com/en-us/library/ms162800.aspx

you can add in in SSMS under Tools\External Tools. Its located at C:\Program Files\Microsoft SQL Server\90\Shared\SqlSAC.exe

Wednesday, March 28, 2012

Making SSIS Replace instead of Add on

I am working on a configuration database in SSIS. One of the modules in the package is giving me endless amounts of grief. The module is for some reason set up to add data onto the end of any data already stored in the table. Instead, I would like it to replace this data. I have tried an Execute SQL Task that should delete all of the rows in the table, but this isn't working. Is there a more efficient way to do this?

-Kyle

No, sounds like you're on the right track. Destinations only add new data. If you want to remove the old data first then you should use an Execute SQL Task with a DELETE or TRUNCATE statement. Are you getting an error?
|||

Would a truncate table work (from an execute sql task or ole db command transform)?

truncate table myTable

|||

EWisdahl wrote:

Would a truncate table work (from an execute sql task or ole db command transform)?

truncate table myTable

Yes! Provided you have permissions to truncate.

Monday, March 12, 2012

Major SQL Server 2k5 x64 configuration problem

Dear All,
I've just installed two brand new SQL Server servers with Windows 2003
Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
I now need to configure the network protocols for SQL Server on these
machines, but on both machines the SQL Server instances are not showing up in
the "SQL Server 2005 Configuration manager"
On two other x64 SQL Server 2005 servers which we installed last year I see
a total of 5 icons in the "SQL Server 2005 Configuration manager":
- SQL Server 2005 Services
- SQL Server 2005 Network configuration (32-bit)
- SQL Server 2005 Native Client Configuration (32 Bit)
- SQL Server 2005 Network configuration
- SQL Server 2005 Native Client Configuration
but on the two new servers only the first 3 groups are showing up.
Normaly the x64 Instances are listed in the group as "- SQL Server 2005
Network configuration", but this group is not showing up.
On both servers I only have configured a default (non-named) instance.
Does anybody have a clue what can be wrong or have a hint as where to start
searching for a solution?
Because we posted this question earlier and have not received a single
response I've resorted to crossposting to the following groups:
microsoft.public.sqlserver.server
microsoft.public.sqlserver.tools
microsoft.public.sqlserver.security
microsoft.public.sqlserver.setup
Kind regards,
Tom and Nick, Reeleezee
Solved: through security access failure auditing we saw that user "NETWORK
SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
Server\90\Shared\sqlmgmprovider.dll.
This was because we ourselves routinely implemented our security lock-down
policy of denying Read/Execute access to "C:\Program Files" and "C:\Program
Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
through being an authenticated user.
We hadn't suspected such a security issue when running the tool as
administrator but apparently the Configuration Tool asks WMI to enumerate the
SQL Server instances and WMI does this under the NETWORK SERVICE account.
(not so for the 32-bit nodes?)
We have had no other issue whatsoever from applying our lock-down policy. I
hope this gets into the MS knowledge base.
Nick
"Nick" wrote:

> Dear All,
> I've just installed two brand new SQL Server servers with Windows 2003
> Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
> I now need to configure the network protocols for SQL Server on these
> machines, but on both machines the SQL Server instances are not showing up in
> the "SQL Server 2005 Configuration manager"
> On two other x64 SQL Server 2005 servers which we installed last year I see
> a total of 5 icons in the "SQL Server 2005 Configuration manager":
> - SQL Server 2005 Services
> - SQL Server 2005 Network configuration (32-bit)
> - SQL Server 2005 Native Client Configuration (32 Bit)
> - SQL Server 2005 Network configuration
> - SQL Server 2005 Native Client Configuration
> but on the two new servers only the first 3 groups are showing up.
> Normaly the x64 Instances are listed in the group as "- SQL Server 2005
> Network configuration", but this group is not showing up.
> On both servers I only have configured a default (non-named) instance.
> Does anybody have a clue what can be wrong or have a hint as where to start
> searching for a solution?
> Because we posted this question earlier and have not received a single
> response I've resorted to crossposting to the following groups:
> microsoft.public.sqlserver.server
> microsoft.public.sqlserver.tools
> microsoft.public.sqlserver.security
> microsoft.public.sqlserver.setup
>
> Kind regards,
> Tom and Nick, Reeleezee
|||File it as a bug on Connect (Connect.microsoft.com) and it will make it to
the dev team. Whether this makes it all the way to KB article is an
entirely different matter.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <SaintNick@.nospam.nospam> wrote in message
news:0EC7C9F4-EB90-466A-ABC9-BB72E74FECA9@.microsoft.com...[vbcol=seagreen]
> Solved: through security access failure auditing we saw that user "NETWORK
> SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
> Server\90\Shared\sqlmgmprovider.dll.
> This was because we ourselves routinely implemented our security lock-down
> policy of denying Read/Execute access to "C:\Program Files" and
> "C:\Program
> Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
> through being an authenticated user.
> We hadn't suspected such a security issue when running the tool as
> administrator but apparently the Configuration Tool asks WMI to enumerate
> the
> SQL Server instances and WMI does this under the NETWORK SERVICE account.
> (not so for the 32-bit nodes?)
> We have had no other issue whatsoever from applying our lock-down policy.
> I
> hope this gets into the MS knowledge base.
> Nick
> "Nick" wrote:
|||Nick,
Another set of problems may arise if you also locked down WINDOWS\SysWOW64
(the 32-bit stuff) or WINDOWS\System32 (the 64-bit stuff). For example, this
can prevent the appropriate version of cscript from running, should you have
a SQL Agent job with a cscript step. (And the error message will not be
obvious.)
RLF
"Nick" <SaintNick@.nospam.nospam> wrote in message
news:0EC7C9F4-EB90-466A-ABC9-BB72E74FECA9@.microsoft.com...[vbcol=seagreen]
> Solved: through security access failure auditing we saw that user "NETWORK
> SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
> Server\90\Shared\sqlmgmprovider.dll.
> This was because we ourselves routinely implemented our security lock-down
> policy of denying Read/Execute access to "C:\Program Files" and
> "C:\Program
> Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
> through being an authenticated user.
> We hadn't suspected such a security issue when running the tool as
> administrator but apparently the Configuration Tool asks WMI to enumerate
> the
> SQL Server instances and WMI does this under the NETWORK SERVICE account.
> (not so for the 32-bit nodes?)
> We have had no other issue whatsoever from applying our lock-down policy.
> I
> hope this gets into the MS knowledge base.
> Nick
> "Nick" wrote:

Major SQL Server 2k5 x64 configuration problem

Dear All,
I've just installed two brand new SQL Server servers with Windows 2003
Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
I now need to configure the network protocols for SQL Server on these
machines, but on both machines the SQL Server instances are not showing up in
the "SQL Server 2005 Configuration manager"
On two other x64 SQL Server 2005 servers which we installed last year I see
a total of 5 icons in the "SQL Server 2005 Configuration manager":
- SQL Server 2005 Services
- SQL Server 2005 Network configuration (32-bit)
- SQL Server 2005 Native Client Configuration (32 Bit)
- SQL Server 2005 Network configuration
- SQL Server 2005 Native Client Configuration
but on the two new servers only the first 3 groups are showing up.
Normaly the x64 Instances are listed in the group as "- SQL Server 2005
Network configuration", but this group is not showing up.
On both servers I only have configured a default (non-named) instance.
Does anybody have a clue what can be wrong or have a hint as where to start
searching for a solution?
Because we posted this question earlier and have not received a single
response I've resorted to crossposting to the following groups:
microsoft.public.sqlserver.server
microsoft.public.sqlserver.tools
microsoft.public.sqlserver.security
microsoft.public.sqlserver.setup
Kind regards,
Tom and Nick, Reeleezee
Solved: through security access failure auditing we saw that user "NETWORK
SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
Server\90\Shared\sqlmgmprovider.dll.
This was because we ourselves routinely implemented our security lock-down
policy of denying Read/Execute access to "C:\Program Files" and "C:\Program
Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
through being an authenticated user.
We hadn't suspected such a security issue when running the tool as
administrator but apparently the Configuration Tool asks WMI to enumerate the
SQL Server instances and WMI does this under the NETWORK SERVICE account.
(not so for the 32-bit nodes?)
We have had no other issue whatsoever from applying our lock-down policy. I
hope this gets into the MS knowledge base.
Nick
"Nick" wrote:

> Dear All,
> I've just installed two brand new SQL Server servers with Windows 2003
> Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
> I now need to configure the network protocols for SQL Server on these
> machines, but on both machines the SQL Server instances are not showing up in
> the "SQL Server 2005 Configuration manager"
> On two other x64 SQL Server 2005 servers which we installed last year I see
> a total of 5 icons in the "SQL Server 2005 Configuration manager":
> - SQL Server 2005 Services
> - SQL Server 2005 Network configuration (32-bit)
> - SQL Server 2005 Native Client Configuration (32 Bit)
> - SQL Server 2005 Network configuration
> - SQL Server 2005 Native Client Configuration
> but on the two new servers only the first 3 groups are showing up.
> Normaly the x64 Instances are listed in the group as "- SQL Server 2005
> Network configuration", but this group is not showing up.
> On both servers I only have configured a default (non-named) instance.
> Does anybody have a clue what can be wrong or have a hint as where to start
> searching for a solution?
> Because we posted this question earlier and have not received a single
> response I've resorted to crossposting to the following groups:
> microsoft.public.sqlserver.server
> microsoft.public.sqlserver.tools
> microsoft.public.sqlserver.security
> microsoft.public.sqlserver.setup
>
> Kind regards,
> Tom and Nick, Reeleezee
|||File it as a bug on Connect (Connect.microsoft.com) and it will make it to
the dev team. Whether this makes it all the way to KB article is an
entirely different matter.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <SaintNick@.nospam.nospam> wrote in message
news:0EC7C9F4-EB90-466A-ABC9-BB72E74FECA9@.microsoft.com...[vbcol=seagreen]
> Solved: through security access failure auditing we saw that user "NETWORK
> SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
> Server\90\Shared\sqlmgmprovider.dll.
> This was because we ourselves routinely implemented our security lock-down
> policy of denying Read/Execute access to "C:\Program Files" and
> "C:\Program
> Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
> through being an authenticated user.
> We hadn't suspected such a security issue when running the tool as
> administrator but apparently the Configuration Tool asks WMI to enumerate
> the
> SQL Server instances and WMI does this under the NETWORK SERVICE account.
> (not so for the 32-bit nodes?)
> We have had no other issue whatsoever from applying our lock-down policy.
> I
> hope this gets into the MS knowledge base.
> Nick
> "Nick" wrote:
|||Nick,
Another set of problems may arise if you also locked down WINDOWS\SysWOW64
(the 32-bit stuff) or WINDOWS\System32 (the 64-bit stuff). For example, this
can prevent the appropriate version of cscript from running, should you have
a SQL Agent job with a cscript step. (And the error message will not be
obvious.)
RLF
"Nick" <SaintNick@.nospam.nospam> wrote in message
news:0EC7C9F4-EB90-466A-ABC9-BB72E74FECA9@.microsoft.com...[vbcol=seagreen]
> Solved: through security access failure auditing we saw that user "NETWORK
> SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
> Server\90\Shared\sqlmgmprovider.dll.
> This was because we ourselves routinely implemented our security lock-down
> policy of denying Read/Execute access to "C:\Program Files" and
> "C:\Program
> Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
> through being an authenticated user.
> We hadn't suspected such a security issue when running the tool as
> administrator but apparently the Configuration Tool asks WMI to enumerate
> the
> SQL Server instances and WMI does this under the NETWORK SERVICE account.
> (not so for the 32-bit nodes?)
> We have had no other issue whatsoever from applying our lock-down policy.
> I
> hope this gets into the MS knowledge base.
> Nick
> "Nick" wrote:

Major SQL Server 2k5 x64 configuration problem

Dear All,
I've just installed two brand new SQL Server servers with Windows 2003
Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
I now need to configure the network protocols for SQL Server on these
machines, but on both machines the SQL Server instances are not showing up in
the "SQL Server 2005 Configuration manager"
On two other x64 SQL Server 2005 servers which we installed last year I see
a total of 5 icons in the "SQL Server 2005 Configuration manager":
- SQL Server 2005 Services
- SQL Server 2005 Network configuration (32-bit)
- SQL Server 2005 Native Client Configuration (32 Bit)
- SQL Server 2005 Network configuration
- SQL Server 2005 Native Client Configuration
but on the two new servers only the first 3 groups are showing up.
Normaly the x64 Instances are listed in the group as "- SQL Server 2005
Network configuration", but this group is not showing up.
On both servers I only have configured a default (non-named) instance.
Does anybody have a clue what can be wrong or have a hint as where to start
searching for a solution?
Because we posted this question earlier and have not received a single
response I've resorted to crossposting to the following groups:
microsoft.public.sqlserver.server
microsoft.public.sqlserver.tools
microsoft.public.sqlserver.security
microsoft.public.sqlserver.setup
Kind regards,
Tom and Nick, ReeleezeeSolved: through security access failure auditing we saw that user "NETWORK
SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
Server\90\Shared\sqlmgmprovider.dll.
This was because we ourselves routinely implemented our security lock-down
policy of denying Read/Execute access to "C:\Program Files" and "C:\Program
Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
through being an authenticated user.
We hadn't suspected such a security issue when running the tool as
administrator but apparently the Configuration Tool asks WMI to enumerate the
SQL Server instances and WMI does this under the NETWORK SERVICE account.
(not so for the 32-bit nodes')
We have had no other issue whatsoever from applying our lock-down policy. I
hope this gets into the MS knowledge base.
Nick
"Nick" wrote:
> Dear All,
> I've just installed two brand new SQL Server servers with Windows 2003
> Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
> I now need to configure the network protocols for SQL Server on these
> machines, but on both machines the SQL Server instances are not showing up in
> the "SQL Server 2005 Configuration manager"
> On two other x64 SQL Server 2005 servers which we installed last year I see
> a total of 5 icons in the "SQL Server 2005 Configuration manager":
> - SQL Server 2005 Services
> - SQL Server 2005 Network configuration (32-bit)
> - SQL Server 2005 Native Client Configuration (32 Bit)
> - SQL Server 2005 Network configuration
> - SQL Server 2005 Native Client Configuration
> but on the two new servers only the first 3 groups are showing up.
> Normaly the x64 Instances are listed in the group as "- SQL Server 2005
> Network configuration", but this group is not showing up.
> On both servers I only have configured a default (non-named) instance.
> Does anybody have a clue what can be wrong or have a hint as where to start
> searching for a solution?
> Because we posted this question earlier and have not received a single
> response I've resorted to crossposting to the following groups:
> microsoft.public.sqlserver.server
> microsoft.public.sqlserver.tools
> microsoft.public.sqlserver.security
> microsoft.public.sqlserver.setup
>
> Kind regards,
> Tom and Nick, Reeleezee|||File it as a bug on Connect (Connect.microsoft.com) and it will make it to
the dev team. Whether this makes it all the way to KB article is an
entirely different matter.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <SaintNick@.nospam.nospam> wrote in message
news:0EC7C9F4-EB90-466A-ABC9-BB72E74FECA9@.microsoft.com...
> Solved: through security access failure auditing we saw that user "NETWORK
> SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
> Server\90\Shared\sqlmgmprovider.dll.
> This was because we ourselves routinely implemented our security lock-down
> policy of denying Read/Execute access to "C:\Program Files" and
> "C:\Program
> Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
> through being an authenticated user.
> We hadn't suspected such a security issue when running the tool as
> administrator but apparently the Configuration Tool asks WMI to enumerate
> the
> SQL Server instances and WMI does this under the NETWORK SERVICE account.
> (not so for the 32-bit nodes')
> We have had no other issue whatsoever from applying our lock-down policy.
> I
> hope this gets into the MS knowledge base.
> Nick
> "Nick" wrote:
>> Dear All,
>> I've just installed two brand new SQL Server servers with Windows 2003
>> Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
>> I now need to configure the network protocols for SQL Server on these
>> machines, but on both machines the SQL Server instances are not showing
>> up in
>> the "SQL Server 2005 Configuration manager"
>> On two other x64 SQL Server 2005 servers which we installed last year I
>> see
>> a total of 5 icons in the "SQL Server 2005 Configuration manager":
>> - SQL Server 2005 Services
>> - SQL Server 2005 Network configuration (32-bit)
>> - SQL Server 2005 Native Client Configuration (32 Bit)
>> - SQL Server 2005 Network configuration
>> - SQL Server 2005 Native Client Configuration
>> but on the two new servers only the first 3 groups are showing up.
>> Normaly the x64 Instances are listed in the group as "- SQL Server 2005
>> Network configuration", but this group is not showing up.
>> On both servers I only have configured a default (non-named) instance.
>> Does anybody have a clue what can be wrong or have a hint as where to
>> start
>> searching for a solution?
>> Because we posted this question earlier and have not received a single
>> response I've resorted to crossposting to the following groups:
>> microsoft.public.sqlserver.server
>> microsoft.public.sqlserver.tools
>> microsoft.public.sqlserver.security
>> microsoft.public.sqlserver.setup
>>
>> Kind regards,
>> Tom and Nick, Reeleezee|||Nick,
Another set of problems may arise if you also locked down WINDOWS\SysWOW64
(the 32-bit stuff) or WINDOWS\System32 (the 64-bit stuff). For example, this
can prevent the appropriate version of cscript from running, should you have
a SQL Agent job with a cscript step. (And the error message will not be
obvious.)
RLF
"Nick" <SaintNick@.nospam.nospam> wrote in message
news:0EC7C9F4-EB90-466A-ABC9-BB72E74FECA9@.microsoft.com...
> Solved: through security access failure auditing we saw that user "NETWORK
> SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
> Server\90\Shared\sqlmgmprovider.dll.
> This was because we ourselves routinely implemented our security lock-down
> policy of denying Read/Execute access to "C:\Program Files" and
> "C:\Program
> Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
> through being an authenticated user.
> We hadn't suspected such a security issue when running the tool as
> administrator but apparently the Configuration Tool asks WMI to enumerate
> the
> SQL Server instances and WMI does this under the NETWORK SERVICE account.
> (not so for the 32-bit nodes')
> We have had no other issue whatsoever from applying our lock-down policy.
> I
> hope this gets into the MS knowledge base.
> Nick
> "Nick" wrote:
>> Dear All,
>> I've just installed two brand new SQL Server servers with Windows 2003
>> Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
>> I now need to configure the network protocols for SQL Server on these
>> machines, but on both machines the SQL Server instances are not showing
>> up in
>> the "SQL Server 2005 Configuration manager"
>> On two other x64 SQL Server 2005 servers which we installed last year I
>> see
>> a total of 5 icons in the "SQL Server 2005 Configuration manager":
>> - SQL Server 2005 Services
>> - SQL Server 2005 Network configuration (32-bit)
>> - SQL Server 2005 Native Client Configuration (32 Bit)
>> - SQL Server 2005 Network configuration
>> - SQL Server 2005 Native Client Configuration
>> but on the two new servers only the first 3 groups are showing up.
>> Normaly the x64 Instances are listed in the group as "- SQL Server 2005
>> Network configuration", but this group is not showing up.
>> On both servers I only have configured a default (non-named) instance.
>> Does anybody have a clue what can be wrong or have a hint as where to
>> start
>> searching for a solution?
>> Because we posted this question earlier and have not received a single
>> response I've resorted to crossposting to the following groups:
>> microsoft.public.sqlserver.server
>> microsoft.public.sqlserver.tools
>> microsoft.public.sqlserver.security
>> microsoft.public.sqlserver.setup
>>
>> Kind regards,
>> Tom and Nick, Reeleezee

Major SQL Server 2k5 x64 configuration problem

Dear All,
I've just installed two brand new SQL Server servers with Windows 2003
Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
I now need to configure the network protocols for SQL Server on these
machines, but on both machines the SQL Server instances are not showing up i
n
the "SQL Server 2005 Configuration manager"
On two other x64 SQL Server 2005 servers which we installed last year I see
a total of 5 icons in the "SQL Server 2005 Configuration manager":
- SQL Server 2005 Services
- SQL Server 2005 Network configuration (32-bit)
- SQL Server 2005 Native Client Configuration (32 Bit)
- SQL Server 2005 Network configuration
- SQL Server 2005 Native Client Configuration
but on the two new servers only the first 3 groups are showing up.
Normaly the x64 Instances are listed in the group as "- SQL Server 2005
Network configuration", but this group is not showing up.
On both servers I only have configured a default (non-named) instance.
Does anybody have a clue what can be wrong or have a hint as where to start
searching for a solution?
Because we posted this question earlier and have not received a single
response I've resorted to crossposting to the following groups:
microsoft.public.sqlserver.server
microsoft.public.sqlserver.tools
microsoft.public.sqlserver.security
microsoft.public.sqlserver.setup
Kind regards,
Tom and Nick, ReeleezeeSolved: through security access failure auditing we saw that user "NETWORK
SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
Server\90\Shared\sqlmgmprovider.dll.
This was because we ourselves routinely implemented our security lock-down
policy of denying Read/Execute access to "C:\Program Files" and "C:\Program
Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
through being an authenticated user.
We hadn't suspected such a security issue when running the tool as
administrator but apparently the Configuration Tool asks WMI to enumerate th
e
SQL Server instances and WMI does this under the NETWORK SERVICE account.
(not so for the 32-bit nodes')
We have had no other issue whatsoever from applying our lock-down policy. I
hope this gets into the MS knowledge base.
Nick
"Nick" wrote:

> Dear All,
> I've just installed two brand new SQL Server servers with Windows 2003
> Server 2003 R2 + SP2 and SQL Server 2005 Standard Edition + SP2.
> I now need to configure the network protocols for SQL Server on these
> machines, but on both machines the SQL Server instances are not showing up
in
> the "SQL Server 2005 Configuration manager"
> On two other x64 SQL Server 2005 servers which we installed last year I se
e
> a total of 5 icons in the "SQL Server 2005 Configuration manager":
> - SQL Server 2005 Services
> - SQL Server 2005 Network configuration (32-bit)
> - SQL Server 2005 Native Client Configuration (32 Bit)
> - SQL Server 2005 Network configuration
> - SQL Server 2005 Native Client Configuration
> but on the two new servers only the first 3 groups are showing up.
> Normaly the x64 Instances are listed in the group as "- SQL Server 2005
> Network configuration", but this group is not showing up.
> On both servers I only have configured a default (non-named) instance.
> Does anybody have a clue what can be wrong or have a hint as where to star
t
> searching for a solution?
> Because we posted this question earlier and have not received a single
> response I've resorted to crossposting to the following groups:
> microsoft.public.sqlserver.server
> microsoft.public.sqlserver.tools
> microsoft.public.sqlserver.security
> microsoft.public.sqlserver.setup
>
> Kind regards,
> Tom and Nick, Reeleezee|||File it as a bug on Connect (Connect.microsoft.com) and it will make it to
the dev team. Whether this makes it all the way to KB article is an
entirely different matter.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Nick" <SaintNick@.nospam.nospam> wrote in message
news:0EC7C9F4-EB90-466A-ABC9-BB72E74FECA9@.microsoft.com...[vbcol=seagreen]
> Solved: through security access failure auditing we saw that user "NETWORK
> SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
> Server\90\Shared\sqlmgmprovider.dll.
> This was because we ourselves routinely implemented our security lock-down
> policy of denying Read/Execute access to "C:\Program Files" and
> "C:\Program
> Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
> through being an authenticated user.
> We hadn't suspected such a security issue when running the tool as
> administrator but apparently the Configuration Tool asks WMI to enumerate
> the
> SQL Server instances and WMI does this under the NETWORK SERVICE account.
> (not so for the 32-bit nodes')
> We have had no other issue whatsoever from applying our lock-down policy.
> I
> hope this gets into the MS knowledge base.
> Nick
> "Nick" wrote:
>|||Nick,
Another set of problems may arise if you also locked down WINDOWS\SysWOW64
(the 32-bit stuff) or WINDOWS\System32 (the 64-bit stuff). For example, this
can prevent the appropriate version of cscript from running, should you have
a SQL Agent job with a cscript step. (And the error message will not be
obvious.)
RLF
"Nick" <SaintNick@.nospam.nospam> wrote in message
news:0EC7C9F4-EB90-466A-ABC9-BB72E74FECA9@.microsoft.com...[vbcol=seagreen]
> Solved: through security access failure auditing we saw that user "NETWORK
> SERVICE" was getting access denieds for C:\Program Files\Microsoft SQL
> Server\90\Shared\sqlmgmprovider.dll.
> This was because we ourselves routinely implemented our security lock-down
> policy of denying Read/Execute access to "C:\Program Files" and
> "C:\Program
> Files (x86)" to the local USERS group which NETWORK SERVICE belongs to
> through being an authenticated user.
> We hadn't suspected such a security issue when running the tool as
> administrator but apparently the Configuration Tool asks WMI to enumerate
> the
> SQL Server instances and WMI does this under the NETWORK SERVICE account.
> (not so for the 32-bit nodes')
> We have had no other issue whatsoever from applying our lock-down policy.
> I
> hope this gets into the MS knowledge base.
> Nick
> "Nick" wrote:
>