the account running the service

[masds]

I read a article that explains how to secure a SQL Server database, it says that hackers could
control the SQL Server service, so if the account that run the service has sysadmin server role,
the hacker could do harm to the operation system, base on that, the author of this article suggests
that use an NT account that doesn't have sysadmin role to run the service, in the real world, is this recommended?

 

[Catadmin]

Actually, the article has probably confused you.

The SysAdmin role on SQL Server has no connection to the Operating System of the machine it is on UNLESS you deliberately map the Sysadmin account to an OS or Domain Administrator's account.

If you install SQL Server with the default Local System account, it does tend to belong to the local machines Administrators group, but you don't have to install it that way. Instead, you can create a normal windows user account (or domain user account) and map that to the SQL Server service account (and SQL Server Agent service account) during install. If you choose not to do it during install, you can always create the account afterwards, map it to a SQL Server SysAdmin fixed server role and change the accounts the Services use to log in as.

The benefits of doing this are better security on your machine/domain. The pitfalls can be jobs or replication not working correctly if you don't assign the proper User Rights to the windows/Domain account for what you need it to do.

If SQL Server isn't communicating with other SQL Servers or doing Log Shipping or Replication, you can get away with putting a regular user account on the service mapped to the SysAdmin account without anything fancy such as "Log on as a part of the operating system".

The thing to watch out for is if a hacker does actually get into SQL Server, they can still destroy your data. So your best policy is to choose a password for whatever account you use (Local System or regular User) and make it as complicated as possible, never write it down and NEVER give it to anyone else.

If you're really being paranoid about hack attacks, of course, you could just unplug the server, lock it in a closet and sleep easy at night. But that would probably cause a minor problem with the people who actually have a legitimate excuse to get on the Server. @=)



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[Catadmin]

BTW, I forgot to mention that you DO need a SQL Server account with SysAdmin rights somewhere. Otherwise, you won't be able to get half the things done in SQL that you need to.

But you don't have to give it Administrative rights on the machine, which could cause the problems it sounds like this author is talking about.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[masds]

Thanks catadmin,

Here is the link:

http://www.windowsecurity.com/articles/Secure_SQL_Server.html

Maybe I'm confused by the author, my understanding is that any user has sysadmin server role could run xp_cmdshell and that could do harm to OS. Is this the point that author try to say?

 

[Catadmin]

Yes, any user with Sysadmin rights could very well run xp_cmdshell. However, I think there is something missing to the author's notes.

1) Unless I'm wrong, there's no way to use xp_cmdshell to create a Windows Administrator account to wreck havoc on the PC. Sure, it can be used to delete files or edit files or find out what files are on the machine. You can even FTP to an outside source to "fetch" a virus unless there's a firewall in place blocking the PC from requesting FTP files. But, as far as I know, you cannot grant yourself higher user privledges using that command unless the PC is a very old OS. Think of this command as giving you access to ye ol' DOS prompt. They call it the Command prompt these days, but it still processes DOS like commands. Anything you could do in DOS (or almost anything), the xp_cmdshell command can do.

2) The author mentions hacking into the PC to use Admin privledges to get into SQL Server. If you're running the PC and SQL Server/SQL Server Agent off a normal user's account, they're not going to have admin rights over the machine anyway.

3) You'll notice the author mentions using things like "log on as a service" right on the account in question and that there are circumstances which require SysAdmin privledges. He is absolutely correct. You need to weigh your security needs verses your practical "every day work" needs. Only grant your logins the minimum permissions they need in order to accomplish their jobs. No more.

SQL Server security is not only SQL Server security. It is directly related to, and part of, network security and windows security. It is my advice that you never consider any of the three in a vacuum without the other two. If you have your security set up correctly between all three, you'll have a firewall (possibly two), strong Windows security with ordinary user accounts that have a minimum of needed permissions and the same with SQL server accounts. Traffic won't be able to get in unless you allow it, traffic won't get out unless you allow it (open / closed port designs, etc).

And, if you live in Perfect, like the Walgreens stores do, none of your users will leave their passwords lying around or give them to anyone else or try to wreck havoc on the network internally because they got torqued off... @=)

Unforuntately, Perfect is a hard place to find. You will find, that even if you have your network tightened up properly so no one can get in from the outside, you'll still have the wonderful internal security problem with people bringing things into the network (viruses, worms, friends who know how to hack and are waiting for the employee to get off of work so they can party) or just plain ol' John Doe who got fired and wants to screw the company over...

Did any of this help you or did I just confuse the issue more?



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[masds]

Thanks catadmin,

You need to weigh your security needs verses your practical "every day work" needs.

So you mean it's possible to not to grant sysadmin role to the service account, but to do that I need to make sure that won't affact the regular works, am I right?

 

[mrdenny]

Correct. Strickly speaking the service account does not need sysadmin rights to the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)