Many Installations of MSSQLSERVER.

Archived from groups: microsoft.public.sqlserver.security,microsoft.public.win2000.security,microsoft.public.win2000.windows_update,microsoft.public.windows.group_policy (More info?)

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.
4 answers Last reply
More about many installations mssqlserver
  1. Archived from groups: microsoft.public.sqlserver.security,microsoft.public.win2000.security,microsoft.public.win2000.windows_update,microsoft.public.windows.group_policy (More info?)

    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.
    >
    >
  2. Archived from groups: microsoft.public.sqlserver.security,microsoft.public.win2000.security,microsoft.public.win2000.windows_update,microsoft.public.windows.group_policy (More info?)

    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.
    > >
    > >
    >
    >
  3. Archived from groups: microsoft.public.sqlserver.security,microsoft.public.win2000.security,microsoft.public.win2000.windows_update,microsoft.public.windows.group_policy (More info?)

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


    "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.
    > > >
    > > >
    > >
    > >
    >
    >
  4. Archived from groups: microsoft.public.sqlserver.security,microsoft.public.win2000.security,microsoft.public.win2000.windows_update,microsoft.public.windows.group_policy (More info?)

    "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.
    > > > >
    > > > >
    > > >
    > > >
    > >
    > >
    >
    >
Ask a new question

Read More

Policy Security Microsoft Windows