Lock Down Enterprise Manager...

I am looking for any information on locking down EM, such as not allowing
access to certain areas (Security Folder, Management, etc), as well as
securing it so that they cannot even see certain DB's in the Databases
Folder. The security would ideally be tied into their SQL acct info, but NT
perms would work as well.
Can anyone provide some good primer info on this topic? Any hints or advice
would be greatly appreciated.
You can't really. There is no real metadata security in SQL2000 so the
obvious example is that a login with access to only 1 database can see all
databases in EM. Just because they can see stuff in EM doesn't mean they can
do anything. What you can do in EM is all controlled by what permissions you
have in SQL but just because they can see an object in EM doesn't mean they
can do anything to it. If a user has access to only one database they can't
do anything in EM other than in that one database.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rathael1" <Rathael1@.discussions.microsoft.com> wrote in message
news:FA39C12F-ACF6-4465-B464-3C66A53A65E3@.microsoft.com...
>I am looking for any information on locking down EM, such as not allowing
> access to certain areas (Security Folder, Management, etc), as well as
> securing it so that they cannot even see certain DB's in the Databases
> Folder. The security would ideally be tied into their SQL acct info, but
> NT
> perms would work as well.
> Can anyone provide some good primer info on this topic? Any hints or
> advice
> would be greatly appreciated.
|||That is what I was afraid of, and was pretty much what I have discovered
while researching this on my own. The example you just gave is exactly what
we're trying to prevent.
I am looking at some 3rd party solutions (AppSense, etc) that may help, just
in case anyone is running into anything similar. Also, I thought of creating
a web interface to EM, where we could control and filter the content
programatially, but that would be a fairly more involved solution than
getting something off the shelf.
Thanks for your response...if anyone else has any ideas, I'm all ears!
JD, MCSE, MCDBA
"Jasper Smith" wrote:

> You can't really. There is no real metadata security in SQL2000 so the
> obvious example is that a login with access to only 1 database can see all
> databases in EM. Just because they can see stuff in EM doesn't mean they can
> do anything. What you can do in EM is all controlled by what permissions you
> have in SQL but just because they can see an object in EM doesn't mean they
> can do anything to it. If a user has access to only one database they can't
> do anything in EM other than in that one database.
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Rathael1" <Rathael1@.discussions.microsoft.com> wrote in message
> news:FA39C12F-ACF6-4465-B464-3C66A53A65E3@.microsoft.com...
>
>
|||What exactly is your concern with the behaviour of EM ? Is this for some
sort of hosting scenario ?
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rathael1" <rathael1@.discussions.microsoft.com> wrote in message
news:04AFBDCC-4D63-4931-973E-2422E4396A30@.microsoft.com...[vbcol=seagreen]
> That is what I was afraid of, and was pretty much what I have discovered
> while researching this on my own. The example you just gave is exactly
> what
> we're trying to prevent.
> I am looking at some 3rd party solutions (AppSense, etc) that may help,
> just
> in case anyone is running into anything similar. Also, I thought of
> creating
> a web interface to EM, where we could control and filter the content
> programatially, but that would be a fairly more involved solution than
> getting something off the shelf.
> Thanks for your response...if anyone else has any ideas, I'm all ears!
> JD, MCSE, MCDBA
> "Jasper Smith" wrote:
|||Have a look at SQL Server Web Data Administrator
http://www.microsoft.com/downloads/d...displaylang=en
This seems to only show databases users have access to. It is extensible and
has documentation on the SqlAdmin class which you can use
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"rathael1" <rathael1@.discussions.microsoft.com> wrote in message
news:04AFBDCC-4D63-4931-973E-2422E4396A30@.microsoft.com...[vbcol=seagreen]
> That is what I was afraid of, and was pretty much what I have discovered
> while researching this on my own. The example you just gave is exactly
> what
> we're trying to prevent.
> I am looking at some 3rd party solutions (AppSense, etc) that may help,
> just
> in case anyone is running into anything similar. Also, I thought of
> creating
> a web interface to EM, where we could control and filter the content
> programatially, but that would be a fairly more involved solution than
> getting something off the shelf.
> Thanks for your response...if anyone else has any ideas, I'm all ears!
> JD, MCSE, MCDBA
> "Jasper Smith" wrote:
|||rathael1 typed:
> Also, I
> thought of creating a web interface to EM, where we could control and
> filter the content programatially, but that would be a fairly more
> involved solution than getting something off the shelf.
You should have a look at myLittleAdmin on
http://www.mylittletools.net/mla_sql
Live demo on http://www.mylittletools.net/livedemo/mla_sql
You can also download a lite edition
Hope this helps
Best regards
Elian Chrebor
// myLittleTools.net : web-based applications for ASP developers
// myLittleAdmin spotlight is available on
// http://www.mylittletools.net/spotlight
// webmaster@.mylittletools.net
[vbcol=seagreen]
> Thanks for your response...if anyone else has any ideas, I'm all ears!
> JD, MCSE, MCDBA
> "Jasper Smith" wrote:

Location for audit logs?

Hi,
I saw in this or the SQL Server Security news group that it's recommended to
store auditing logs on an unused disk drive because auditing logs could grow
wildly. But based on this article
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx,
SQL Server doesn't let you log auditable events to an alternative location.
<QUOTE>After you enable C2 auditing for the default database or for an
instance, the database server will log all activity to the data directory
that you specified during the installation process. (SQL Server doesn't let
you log auditable events to an alternative location.) This directory holds
the databases that SQL Server initially created. This directory is also the
default location for all new databases and their transaction log
files.</QUOTE>
Now I'm confused. I have data/transaction logs on one drive, I'm planning
to add additional disk drive specifically for auditing. Is it possible to
direct auditing logs to the new drive?
Thanks,
Bing
Hi Bing
"bing" wrote:

> Hi,
> I saw in this or the SQL Server Security news group that it's recommended to
> store auditing logs on an unused disk drive because auditing logs could grow
> wildly. But based on this article
> http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx,
> SQL Server doesn't let you log auditable events to an alternative location.
>
> <QUOTE>After you enable C2 auditing for the default database or for an
> instance, the database server will log all activity to the data directory
> that you specified during the installation process. (SQL Server doesn't let
> you log auditable events to an alternative location.) This directory holds
> the databases that SQL Server initially created. This directory is also the
> default location for all new databases and their transaction log
> files.</QUOTE>
> Now I'm confused. I have data/transaction logs on one drive, I'm planning
> to add additional disk drive specifically for auditing. Is it possible to
> direct auditing logs to the new drive?
> Thanks,
> Bing
If you change the default data and log directories in the Database
properties task in Enterprise Manager on the properties page of the instance
(right click) do new audit files get created their (you may need to restart)?
If not you can move the databases away from this location using
sp_detach_db/sp_attach_db or backup/restore see
http://support.microsoft.com/kb/314546. When you do the initial install, the
default location is the system disc e.g. C:\Program Files\Microsoft SQL
Server... this should be changed as filling up the system disc with audit
logs will make the system unusable.
You can move system databases (possibly to different spindles!) as well as
user databases http://support.microsoft.com/kb/224071/ and using different
discs for full text catalogs will also be a way of improving performance
http://support.microsoft.com/kb/240867/ Database data files and log files
should be on separate drive arrays as they perform different types of I/O
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
You should also consider what RAID level and resilience you require for
these disc subsystems.
John

Location for audit logs?

Hi,
I saw in this or the SQL Server Security news group that it's recommended to
store auditing logs on an unused disk drive because auditing logs could grow
wildly. But based on this article
http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx,
SQL Server doesn't let you log auditable events to an alternative location.
<QUOTE>After you enable C2 auditing for the default database or for an
instance, the database server will log all activity to the data directory
that you specified during the installation process. (SQL Server doesn't let
you log auditable events to an alternative location.) This directory holds
the databases that SQL Server initially created. This directory is also the
default location for all new databases and their transaction log
files.</QUOTE>
Now I'm confused. I have data/transaction logs on one drive, I'm planning
to add additional disk drive specifically for auditing. Is it possible to
direct auditing logs to the new drive?
Thanks,
BingHi Bing
"bing" wrote:
> Hi,
> I saw in this or the SQL Server Security news group that it's recommended to
> store auditing logs on an unused disk drive because auditing logs could grow
> wildly. But based on this article
> http://www.microsoft.com/technet/security/prodtech/sqlserver/sql2kaud.mspx,
> SQL Server doesn't let you log auditable events to an alternative location.
>
> <QUOTE>After you enable C2 auditing for the default database or for an
> instance, the database server will log all activity to the data directory
> that you specified during the installation process. (SQL Server doesn't let
> you log auditable events to an alternative location.) This directory holds
> the databases that SQL Server initially created. This directory is also the
> default location for all new databases and their transaction log
> files.</QUOTE>
> Now I'm confused. I have data/transaction logs on one drive, I'm planning
> to add additional disk drive specifically for auditing. Is it possible to
> direct auditing logs to the new drive?
> Thanks,
> Bing
If you change the default data and log directories in the Database
properties task in Enterprise Manager on the properties page of the instance
(right click) do new audit files get created their (you may need to restart)?
If not you can move the databases away from this location using
sp_detach_db/sp_attach_db or backup/restore see
http://support.microsoft.com/kb/314546. When you do the initial install, the
default location is the system disc e.g. C:\Program Files\Microsoft SQL
Server... this should be changed as filling up the system disc with audit
logs will make the system unusable.
You can move system databases (possibly to different spindles!) as well as
user databases http://support.microsoft.com/kb/224071/ and using different
discs for full text catalogs will also be a way of improving performance
http://support.microsoft.com/kb/240867/ Database data files and log files
should be on separate drive arrays as they perform different types of I/O
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
You should also consider what RAID level and resilience you require for
these disc subsystems.
John

Location for audit logs?

Hi,
I saw in this or the SQL Server Security news group that it's recommended to
store auditing logs on an unused disk drive because auditing logs could grow
wildly. But based on this article
http://www.microsoft.com/technet/se.../sql2kaud.mspx,
SQL Server doesn't let you log auditable events to an alternative location.
<QUOTE>After you enable C2 auditing for the default database or for an
instance, the database server will log all activity to the data directory
that you specified during the installation process. (SQL Server doesn't let
you log auditable events to an alternative location.) This directory holds
the databases that SQL Server initially created. This directory is also the
default location for all new databases and their transaction log
files.</QUOTE>
Now I'm confused. I have data/transaction logs on one drive, I'm planning
to add additional disk drive specifically for auditing. Is it possible to
direct auditing logs to the new drive?
Thanks,
BingHi Bing
"bing" wrote:

> Hi,
> I saw in this or the SQL Server Security news group that it's recommended
to
> store auditing logs on an unused disk drive because auditing logs could gr
ow
> wildly. But based on this article
> If you change the default data and log directories in the Databaseproperties task in Enterprise Manager on the properties page of the instance(right click) do new audit files get created their (you may need to restart)?If not you can move the databases away from this location usingsp_detach_db/sp_attach_db or backup/restore see[url]http://support.microsoft.com/kb/314546." target="_blank">http://www.microsoft.com/technet/se...com/kb/314546. When you do the initial install, the
default location is the system disc e.g. C:\Program Files\Microsoft SQL
Server... this should be changed as filling up the system disc with audit
logs will make the system unusable.
You can move system databases (possibly to different spindles!) as well as
user databases http://support.microsoft.com/kb/224071/ and using different
discs for full text catalogs will also be a way of improving performance
http://support.microsoft.com/kb/240867/ Database data files and log files
should be on separate drive arrays as they perform different types of I/O
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl]
You should also consider what RAID level and resilience you require for
these disc subsystems.
John

locate static data in xml file vs db table

How do people see the security of locating static data (such as config
settings) in an XML file (a copy is located on each web-server, or a single
copy is located on a specific server) , versus locating this data inside the
db ?
An XML file that contains all app config settings is much easier to maintain
and track from the standpoint of source-code-control , and it is much easier
to apply changes to settings in production environments (just swap the XML
file and restart the web-servers).
The replication and other redudancy advantages accorded data inside SS are
not really necessary for static, load-on-app-start, config settings.
If not possible for data in an XML file to achieve the security of a data in
a db , one option would be to locate the XML file in a SS05 col of XML
datatype.
Anyone have real-world experience making this sort of trade-off decision ,
or any insights ?
Thanks.When did go through a similar exercise for our application. One of the
things that we considered was to look at configuration as something that is
"configurable". This means that the location of the configuration file
should be abstracted from your code so that you are free to move it around.
This is where the Configuration Application Block from Microsoft comes into
play:
http://msdn.microsoft.com/library/d...i
g.asp.
This block abstracts the ability to read and write from the configuration
file and from a deployment perspective, the configuration can be in XML or
in SQL Server. Maybe future editions of this block might put it in an SQL2K5
XML column, but the block would abstract it from you and you would always
have a consistent way of programming.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:OnfTXLs0FHA.3812@.TK2MSFTNGP09.phx.gbl...
> How do people see the security of locating static data (such as config
> settings) in an XML file (a copy is located on each web-server, or a
> single copy is located on a specific server) , versus locating this data
> inside the db ?
> An XML file that contains all app config settings is much easier to
> maintain and track from the standpoint of source-code-control , and it is
> much easier to apply changes to settings in production environments (just
> swap the XML file and restart the web-servers).
> The replication and other redudancy advantages accorded data inside SS are
> not really necessary for static, load-on-app-start, config settings.
> If not possible for data in an XML file to achieve the security of a data
> in a db , one option would be to locate the XML file in a SS05 col of XML
> datatype.
> Anyone have real-world experience making this sort of trade-off decision ,
> or any insights ?
> Thanks.
>|||Problem I see with Configuration Application Block is that it assumes only
the simplest data structure is needed for config settings: discrete named
values.
To get around this, you can write your own transformers. But to leverage the
advantages that CAB offers, you should write them to work against multiple
data-providers/data-stores). This is quite a bit of work -- might be better
to spend that time writing an app-specific solution from scratch.
Also, I think it's not a good idea to avoid upfront analysis of the
app-specific trade-offs between different data-store solutions. CAB seems to
be saying "avoid the tough design decision" ... and, in the process,
sacrifice the advantages offered by a custom design.
But maybe I'm mistaken ...
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:O4Ervev0FHA.460@.TK2MSFTNGP15.phx.gbl...
> When did go through a similar exercise for our application. One of the
> things that we considered was to look at configuration as something that
> is "configurable". This means that the location of the configuration file
> should be abstracted from your code so that you are free to move it
> around. This is where the Configuration Application Block from Microsoft
> comes into play:
> http://msdn.microsoft.com/library/d...br />
fig.asp.
> This block abstracts the ability to read and write from the configuration
> file and from a deployment perspective, the configuration can be in XML or
> in SQL Server. Maybe future editions of this block might put it in an
> SQL2K5 XML column, but the block would abstract it from you and you would
> always have a consistent way of programming.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:OnfTXLs0FHA.3812@.TK2MSFTNGP09.phx.gbl...
>|||You're right in saying that CAB out of the box is for simple named values.
We have not tried writing our own formatters, thus, I cannot comment on the
complexity of the same. We did do an anslysis of an application specific
version, but found that the time it takes to provide a UI for the
configuration (like what CAB has) and the simple model of exposing the
configuration as properties of a class for our application looked like a
re-invention of the wheel.
Did you try posting your question to the GOTDOTNET site for Enterprise
Library? It is usually manned by the architects of the library and you may
get more specific answers.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"John Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:O71N%23oz0FHA.2132@.TK2MSFTNGP15.phx.gbl...
> Problem I see with Configuration Application Block is that it assumes only
> the simplest data structure is needed for config settings: discrete named
> values.
> To get around this, you can write your own transformers. But to leverage
> the advantages that CAB offers, you should write them to work against
> multiple data-providers/data-stores). This is quite a bit of work --
> might be better to spend that time writing an app-specific solution from
> scratch.
> Also, I think it's not a good idea to avoid upfront analysis of the
> app-specific trade-offs between different data-store solutions. CAB seems
> to be saying "avoid the tough design decision" ... and, in the process,
> sacrifice the advantages offered by a custom design.
> But maybe I'm mistaken ...
> "SriSamp" <ssampath@.sct.co.in> wrote in message
> news:O4Ervev0FHA.460@.TK2MSFTNGP15.phx.gbl...
>

Local Security Settings - maximum password age

We would like to use the SQL Server 2005 Express at our customers.

But now we have to meet the local security settings of the PC.

What is happening with the database users password (e.g. sa) when the "Maximum password age" in the "local security settings" for the password policy is to >0 (e.g. 30 days)?

Because this cause a frequently change of the passwords at the customers!!!!!!

Password policy are only applied to SQL Server 2005 which are installed in Windows 2003 Servers. The local settings on the WIndows machine wont effect the accounts at all.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Local Intranet mode versus Internet mode.

Hi,

Currently, our Report Builder is running on Local Intranet mode. I'm investigating what the security implications are in changing it to Internet mode. However, I've not been able to find any documentation on this.

Does anyone know of any documentation that addresses this issue or have experience that they can share with changing Report Builder security zone from Intranet mode to Internet mode?

Thanks very much.

Invest in firewalls/routers... both for your users and server at work.