Re: Many Installations of MSSQLSERVER.

• Previous message: gypman: "chkdsk"
• In reply to: Karl Levinson, mvp: "Re: Many Installations of MSSQLSERVER."
• Messages sorted by: [ date ] [ thread ]

Date: Sat, 4 Dec 2004 10:13:44 -0700

"Karl Levinson, mvp" <levinson_k@despammed.com> wrote in message
news:uM8hgEg2EHA.1564@TK2MSFTNGP09.phx.gbl...
> You can use Group Policy [for example, via Active Directory] to set
> MSSQLSERVER service on Windows 2000, XP and 2003 systems to be disabled by
> default. This is probably easier than using Group Policy Software
> Restrictions Policy [though if they are local Administrators on their
> computers, it might be less secure, I don't really know]. On a computer

We have pretty much beaten that avenue to death Karl.
The issue is that the registered name of the service is not
a predictable constant.

> running the MSSQLSERVER service, run MMC, add the Security Templates MMC,
> create a new GP template where the only change is to the startup value of
> the service and possibly the permissions of who can manage the service.
> Save the template, apply the security template to the workstations in
> question [e.g. use a batch file with the SECEDIT command to create a new
> database and import and apply the template on each target workstation, OR
> import the template into your active directory and apply that template to
> target workstations by putting the workstations into an OU and/or
> configuring ACL permissions on which workstations can read that GP. If
you
> use AD GP, be sure not to accidentally apply the policy to your legitimate
> SQL servers.]
>
> If these people's accounts are in the local Administrators or Power Users
> group on the workstations, they can undo just about anything you do.
Group
> Policy will change their changes back, but I'm not sure this will stop the
> SQL service once they start it. It's hard to prevent Administrators or
> Power Users from doing what they want to do. There are some ways Power
> Users can escalate their privileges to Administrator if they know how.
>
> Unless something has changed in the latest version of MS SQL Server, SQL
> Server cannot be installed on Windows workstations, only servers. AFAIK,
> you can install MSDE on workstation, or if you install the SQL server CD,
it
> installs the SQL administrator tools and not the server itself.

One may install either on non-servers.
The "personal" install of SQL Server on a workstation is
effectively a full standard edition install with some minor
imposed limitations and some licensing differences.

>
>
> "The Poster" <nospam@nospam_dontyoudare.net> wrote in message
> news:eEP$onR2EHA.3236@TK2MSFTNGP15.phx.gbl...
> > Thanks for your detailed post Roger.
> >
> > Would you believe that I've got the DBA and Project Managers support on
> this
> > one? This motion is driven by the DBA group and the Project group so as
> far
> > as support is concerned I'm on a winner. Question: Do you think that
> Client
> > side 'full installations' of SQL Server are costly, dangerous (remember
> > slammer?), and system intensive? The bottom line is that I have to
figure
> > out some way of disabling the MSSQLSERVER service and all named
instances
> > (and in time remove it) - to facilitate compliance I will be conducting
> > regular SQLScan's, where non-compliance will be dealth with. I have made
> > provisions for Client Tools (Query Analyzer, Enterprise Manager, etc) on
> the
> > DEvelopers systems - I appreciate these are essential tools that are
> needed
> > to perform there day to day duties.
> >
> > In the longer term, I'm planning on revoking Developers Admin level
> > priviledge on all systems. I've been reading alot of what Keith Brown
has
> > been saying with regards to Security, and I (like our Software
Architect)
> > have been converted to the Longhorn concept of Least Priviledged User
> > account - that is the most secure way to go. I can even use our own
Chief
> > Software Architect as an example, he had his system rebuilt 6 months
ago,
> > and since that day he has manged to do 99% of his development work as a
> > Standard user.
> >
> > Regards,
> > Steve.
> >
> >
> > "Roger Abell" <mvpNOSpam@asu.edu> wrote in message
> > news:erAlTPR2EHA.1124@tk2msftngp13.phx.gbl...
> > > If you try using services control from GPO, to disable the
> > > SQL services, what you will likely find is that developers
> > > are resouceful and will just install a named instance of
> > > SQL as the main service is named for the instance name.
> > > You could cripple the other services which are not named
> > > in an instance sensitive way, but SQL itself would escape
> > > your net.
> > >
> > > Also, whether setting the service to disabled through the
> > > services portion of GPO or through direct tweaking of the
> > > start key in the reg, this does not prevent them from installing
> > > and running it. As they can install, they are apparently admins,
> > > and as admins they can change the service to manual or auto
> > > long enough to start it. When policy reapplies and sets it back
> > > to disabled it does not necessarily also stop it. Now, you
> > > may be able to combine setting the service to disabled with
> > > software restriction policy so that they cannot start it, but if
> > > you cannot be sure of the service instance name to disable it
> > > then they could get it started by the system at boot.
> > >
> > > To answer your actual question, I am not aware of a template
> > > for use in GPO to manage SQL or its internal state.
> > >
> > > So, what to do?
> > > First - make sure you understand why the devs have SQL
> > > installed. If your environment has legal VStudio on their
> > > machines, it is very possible that they legally have the
> > > dev version of SQL installed. Also, what are they working
> > > on? Perhaps these are MSDE version of SQL and will ship
> > > out packaged with what they are building.
> > > It might be more simple to just force those machines to
> > > have their patch level up-to-date. It might be better to
> > > provide them with the client tools for SQL but require
> > > them to use MSDE (which can be made to speak only
> > > machine-locally and be unresponsive over the wire).
> > > It would seem to me that the last thing one would want
> > > to do is make their job harder. I can imagine a number
> > > of dev scenarios in which forcing them to share a remote
> > > SQL could complicate their lives, but then I am the one
> > > with SQL server alway installed on the laptop ! On the
> > > other hand, making use of a SQL server remote from the
> > > apps they dev/test might be of value for the quality of
> > > what they will ship. It may be worth exploring whether
> > > this is so and you could find support in your objective
> > > from their project mgmt, as with their being admins it
> > > will be hard to make it so without also having cooperation.
> > >
> > > --
> > > Roger Abell
> > > Microsoft MVP (Windows Security)
> > > MCSE (W2k3,W2k,Nt4) MCDBA
> > > "The Poster" <nospam@nospam_dontyoudare.net> wrote in message
> > > news:OYDIBvH2EHA.2196@TK2MSFTNGP14.phx.gbl...
> > > > G/day forum,
> > > >
> > > > I've got a problem where all my Development users have full
> > installations
> > > of
> > > > MSSQLServer. I want to disable this (for obvious security and
> financial
> > > > reasons) and allow SQL Server on a few dedicated (managed)
Development
> > SQL
> > > > servers.
> > > >
> > > > This is what I'm thinking:
> > > > 1) Active Directory - Group Policy Object. The problem is I cant
find
> a
> > > > relevant template that incorporates the SQL Services.
> > > > 2) Through Registry file modification of the Start value -
> > > > HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER.
> > > >
> > > > Either way I'll be deploying the change through Group Policy, and
> > ideally
> > > I
> > > > would have a template that incorporated the SQL service, that way I
> > > wouldn't
> > > > have to directly go tampering with any registry files..
> > > >
> > > > Your ideas and/or comments would be greatly appreciated.
> > > >
> > > > Regards,
> > > > Steve.
> > > >
> > > >
> > >
> > >
> >
> >
>
>

• Previous message: gypman: "chkdsk"
• In reply to: Karl Levinson, mvp: "Re: Many Installations of MSSQLSERVER."
• Messages sorted by: [ date ] [ thread ]