Dubious SQL Login?

I want to centralize server logs. All these server services (IIS, ISA, etc)
all know how to log to ODBC, and I want to exploit this so that I have a
central place to analyze logs instead of trapsing through the file system
looking for all these silly log files.
So I made a ServerLogs database, and I plan to have a flat table for each
log. There are of course no relationships here, just a set of flat tables.
These servers that will log through ODBC will need to be able to connect to
SQL Server so I have to make some logins. I would prefer to use Windows
Only authentication because that forces any connection to be authenticated
by Active Directory. But if I do this, then I would have to make Logins in
SQL Server for "Local System" and "Network Service".
Now these logins would only have access to the ServerLogs database, and in
there, would only have "public" permissions. No permissions other than that
which is required to log their log records.
My question is, in this scenario, does it sound like a bad idea to give
LocalSystem and NetworkService access to SQL Server? I just want a reality
check before I do something that might be dangerous or stupid.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSenseiThe service startup account implicates sqlserver's ability to access
resources. Thus, if you do not want sqlserver to access any resources
outside of the box it's running on, you would use localsystem. If it needs
to access network resources, networkservice is obviously the choice.
Now, as far as users' permissions, you would just need to grant the domain
user (or group) to sqlserver and any desired databased.
e.g.
exec sp_grantlogin 'domain\user' --'domain\group'
go
use db
go
exec sp_grantdbaccess 'domain\user' --'domain\group'
-oj
"Mike Labosh" <mlabosh_at_hotmail_dot_com> wrote in message
news:Ox6%23epiOGHA.3896@.TK2MSFTNGP15.phx.gbl...
>I want to centralize server logs. All these server services (IIS, ISA,
>etc)
> all know how to log to ODBC, and I want to exploit this so that I have a
> central place to analyze logs instead of trapsing through the file system
> looking for all these silly log files.
> So I made a ServerLogs database, and I plan to have a flat table for each
> log. There are of course no relationships here, just a set of flat
> tables.
> These servers that will log through ODBC will need to be able to connect
> to
> SQL Server so I have to make some logins. I would prefer to use Windows
> Only authentication because that forces any connection to be authenticated
> by Active Directory. But if I do this, then I would have to make Logins
> in
> SQL Server for "Local System" and "Network Service".
> Now these logins would only have access to the ServerLogs database, and in
> there, would only have "public" permissions. No permissions other than
> that
> which is required to log their log records.
> My question is, in this scenario, does it sound like a bad idea to give
> LocalSystem and NetworkService access to SQL Server? I just want a
> reality
> check before I do something that might be dangerous or stupid.
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>|||> The service startup account implicates sqlserver's ability to access
> resources. Thus, if you do not want sqlserver to access any resources
> outside of the box it's running on, you would use localsystem. If it needs
> to access network resources, networkservice is obviously the choice.
Perhaps you misunderstood, or I misstated:
I want these servers: HTTP, FTP, NNTP, SMTP, POP3, ISA
to be able to connect to SQL Server to write their logs into SQL Server
tables.
The IIS and ISA related services that want to do ODBC logging to SQL Server
all run as LocalSystem or NetworkService. Using Windows Only authentication
in SQL Server, I would have to create SQL Server logins for LocalSystem and
NetworkService so that these other services can ODBC their log entries to
SQL.
My question is whether it's a bad idea to allow LocalSystem and
NetworkService to have logins at SQL Server on behalf of these other
servers, because I know these two important accounts are used for a
MULTITUDE of other things.
In other words, I want to do this:
EXEC sp_GrantLogin 'Local System'
EXEC sp_GrantLogin 'NT AUTHORITY\NetworkService'
GO
CREATE DATABASE ServerLogs
GO
USE ServerLogs
GO
EXEC sp_GrantDBAccess 'Local System'
EXEC sp_GrantDBAccess 'NT AUTHORITY\NetworkService'
GO
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||Ah, I see.
Granting any NT acct will only give it a public role. Thus, there is really
no harm. Though, in this case, these system accts are used in many services.
So, granting them access will also allow all others from accessing.
But then again, localsystem already has *full* access to the local
machine...
-oj
"Mike Labosh" <mlabosh_at_hotmail_dot_com> wrote in message
news:Ot91RFjOGHA.2604@.TK2MSFTNGP09.phx.gbl...
> Perhaps you misunderstood, or I misstated:
> I want these servers: HTTP, FTP, NNTP, SMTP, POP3, ISA
> to be able to connect to SQL Server to write their logs into SQL Server
> tables.
> The IIS and ISA related services that want to do ODBC logging to SQL
> Server
> all run as LocalSystem or NetworkService. Using Windows Only
> authentication
> in SQL Server, I would have to create SQL Server logins for LocalSystem
> and
> NetworkService so that these other services can ODBC their log entries to
> SQL.
> My question is whether it's a bad idea to allow LocalSystem and
> NetworkService to have logins at SQL Server on behalf of these other
> servers, because I know these two important accounts are used for a
> MULTITUDE of other things.
> In other words, I want to do this:
> EXEC sp_GrantLogin 'Local System'
> EXEC sp_GrantLogin 'NT AUTHORITY\NetworkService'
> GO
> CREATE DATABASE ServerLogs
> GO
> USE ServerLogs
> GO
> EXEC sp_GrantDBAccess 'Local System'
> EXEC sp_GrantDBAccess 'NT AUTHORITY\NetworkService'
> GO
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>|||> Granting any NT acct will only give it a public role. Thus, there is
really
> no harm. Though, in this case, these system accts are used in many
services.
> So, granting them access will also allow all others from accessing.
Agreed. And the only thing SQL Server will let them have permissions for is
"public" access to Just the ServerLogs database

> But then again, localsystem already has *full* access to the local
> machine...
Ha. Yes, I forgot about that. I was just trying to get a reality check
before I accidentally unzipped my fly on the public internet or something
like that.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||>
> My question is, in this scenario, does it sound like a bad idea to give
> LocalSystem and NetworkService access to SQL Server? I just want a
> reality
> check before I do something that might be dangerous or stupid.
>
There's nothing wrong with giving software access to SQL Server, just make
sure that the security is set so that it can't do anything that it
shouldn't. You'll also need to make sure that it's not possible to inject
stuff that you shouldn't be able to.
As a different idea, maybe you should take a look at the SSIS (SQL Server
Integration Services) This is the all new DTS replacement. Using SSIS, you
can pull the information direct from the file system, perform some
transformation and insert the files into SQL. It can be setup on a schedule
so the entire process can be automated, and executed by SQL Server! No need
to give IIS and all those other nasties access to SQL at all.
Regards
Colin Dawson
www.cjdawson.com|||> There's nothing wrong with giving software access to SQL Server, just make
> sure that the security is set so that it can't do anything that it
> shouldn't. You'll also need to make sure that it's not possible to inject
> stuff that you shouldn't be able to.
Thank you, that's precisely what I was looking for.

> As a different idea, maybe you should take a look at the SSIS (SQL Server
> Integration Services) This is the all new DTS replacement. Using SSIS,
you
> can pull the information direct from the file system, perform some
> transformation and insert the files into SQL. It can be setup on a
schedule
> so the entire process can be automated, and executed by SQL Server! No
need
> to give IIS and all those other nasties access to SQL at all.
If you are referring to SQL 2005, I cannot do that because I am not up to
speed on it yet, but that does sound like it is more secure.
In any case, if I am reading you correctly, it sounds like I am OK as long
as LocalSystem and NetworkService have limited permissions.
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||>
> If you are referring to SQL 2005, I cannot do that because I am not up to
> speed on it yet, but that does sound like it is more secure.
> In any case, if I am reading you correctly, it sounds like I am OK as long
> as LocalSystem and NetworkService have limited permissions.
>
You could do it with permissions. I wouldn't worry about not being up to
speed on SQL 2005, if you wait for that it'll be a year or so before you can
truly say that. (maybe longer!) The Integrations services are a replacement
for DTS, hence why I'm saying not to bother with it. But surely if security
is a major consideration, it's worth spending a few hours getting up to
speed on part of the SQL Server Business intelligence studio.
Specifically, it's the Integrations service project that you need to look
at.
In a nutshell you can split the task into several stages, such as read a
file, convert the format, perform some aggregations, split the data into
distinct subsets, write the data into a table. Keeping each step distinct
and seperate will allow the SSIS to split the taks up into many threads, so
you get a performance boost using multi threading, but SQL will do all the
work for you.
Regards
Colin Dawson
www.cjdawson.com