SQL Server security thinking 2

[masds]

So many resources and articles talking about SQL Server security, also the microsoft training books. They all fail to give me a clear and manageable strategy to setup a security SQL Server database, many concepts are fuzzy and never be clarified for me.

For example, the setup program will set appropriate security permissions for install directory and registry after install that only local administrators and SQL Server service account have read and write permission on above. Then most articles will recommend not to use local administrator as SQL Server sysadmin, that means the SQL Server sysadmin doesn't have the above permission to the files and registry but the local administrators have. This makes no sense to me! This is just one example, I believe there are many.

Security seems a easy topic, but actually it's absolutely not! In my point of view, it's full of concepts in conflict that always confuse user.

 

[masds]

Any security strategy maybe arguable, I guess here I need successful DBA experience when setting up security policy. I'm tired of reading unlimited of materials from internet and unsure which one I should choose from.

 

[mrdenny]

The reason that it is recommend to have your SQL Service account not be a local admin on the server is in case someone breaks into the SQL Server and gets access.

If the SQL Service has admin rights, this intruder can now create a new machine account on the server with admin rights, and basically take over the server. They would also be able to download and install any software they wanted to.

If the SQL Server doesn't have admin rights it is much harder for the intruder to do these things.

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)

 

[masds]

Thanks denny, is it neccessary to allow the SQL Server service to access the SQL Server installed files and registry? why?

 

[mrdenny]

If the SQL Server service doesn't have access to the files then it can't use the files to start the SQL Server service, or access the data files, or log files, etc.

The SQL Server service is a user account, just like an other user account. If the account doesn't have writes to something then it won't be able to use the files.

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)

 

[masds]

Thanks denny!

Another security related question:
I know I can turn on the audit in the security tab of the server in enterprise Manager, I guess it will tracing all the unsuccessful login to the SQL Server, so I want to know where it puts the auditing reports, is there actually a report somewhere?

 

[maswien]

I just wrote a FAQ on this, please look it and feel free to comment here.

 

[maswien]

faq962-5777

 

[masds]

Thanks mjia, it's good article.