SQL Server Security Part 1
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/ckempster/sql_server_security.asp
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
To the author,
I just wonder about this statement:
In production (and ideally in DEV and TEST), never install SQL Server whilst logged in as the administrator. Always create a domain or local user account with login as service rights to run the MSSQLServer service under. If you are using replication, use a domain user account over the local user account.
I would like to know the reason behind this. Thanks.
-
quote:
Re-working the BUILTIN\Administrator SQLServer Login AccountThis is well described in Brian Knight’s article “Removing NT Administrator as Sysadmins”. On installation of SQL Server a login account called “BUILTIN\Administrators” is created, anyone allocated to the Administrators NT local group on the server will have sysadmin access to the database server via natural Windows Authentication. This login will give the user DBO access to all system and user databases coupled with sysadmin access.
Chris,
As the "First" DBA in our shop, I have found many things that need to be fixed in this area. I have also had to work with our net admins using this to obatain access to systems that had been configured by persons no longer employed or contracted by our company.
We also, for some legacy reasons, have some domain admin accounts that have had their security compromised, (Some "white hats" came in, hoping to sell services, and showed that they could "crack" passwords. Why they had to put the complete password in the clear for all accounts I'll never know, but the FUD *was* effective, but the whole thing is another story..) and management hasn't decided on how to deal with it. In that case, it the SQL server is one that might be of interest to the people in our organization who were wrting all those passwords down, I have a QA shell that I run, filling in the appropriate domain\user that I wish to remove:
-- Deny_to_NTUser.sql Version 1.0, Release 1.0
-- Purpose: Denies NT User Rights
-- Hal Smith 2001-08-28 12:01:01.593
-- =============================================
-- setup
set nocount on
select getdate(), @@servername
go
-- execute
-- =============================================
-- Drop login from server role
-- =============================================
sp_dropsrvrolemember @loginame = N'<Windows_or_Sql_Server_login, sysname, REDMOND\john>', @rolename = N'<server_role, sysname, sysadmin>'
GO
-- =============================================
-- Deny Sql Server access to Windows user or group
-- =============================================
sp_denylogin @loginame = N'<Windows_user_or_group, sysname, REDMOND\john>'
GO
-- /\/\/\/\/-- Data Follows
/* I clip the queries' ouput out of the results window, and paste it in here. Then I save the whole query to the security matrix documentation for the particular server. I always try to script *all* actions like this.
-- Data Preceeds
-- /\/\/\/\/
-- cleanup
select getdate(), @@servername
set nocount off
go
-- End of Deny_to_NTUser.sql Version 1.0, Release 1.0
I do like your idea for builtin\administrator rework, and plan to do that next server visits scheduled with the net admins.
-hal
-
Hi Lhot
Its a simple matter of the priviligies that go with the administrator login at an NT level that is the issue. I recognise the fact that it still may be very difficult for someone to hack into your box in the first place, but if they did via a SQL Server security "hole" (ie. SA access other other methods discussed) they how have administrator privilige access to your server as well. It can be a pain to setup, but the service requires very little privilige to run as as such, there is not need to give it rights that are far beyond what is actually requires.
As for DEV and TEST, well, thats just personal preference.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Hi Hal
In my next article I chat about 3rd party software (like Crystral Reports 8.5) that insist on using the SA account and then retain its use (can be a right pain to alter). Auditing of security accounts is another issue as well, you do it once and forever dread the reauditing process (esp for global and local groups). I would be interested to hear about your paper level auditing and any script you use to easy the task but still remain "document friendly".
Cheers
Chris.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
I think you need to do some review on the portion discussing EFS. EFS is not based on the users profile,it is a PKI based structure. As the creator owner of a file or a designated Data Recovery Agent (local admin in a workgroup and domain admin in a domain environment)you can encrypt and decrpyt a file using your EFS key (certificate). A users profile only defines a users operating environment and has nothing to do with permissions or user rights.
-
Hi there
To honest, it was a very quick overview, and when i did I found the architecture somewhat confusing. Either way, I was convinced the key was part of the "profile" of the account, when I say profile, its more part of the actual login and its associated stored provilieges within AD. Also, I believe the MS doco stated that if you encrypted the database whilst logged in as, say a custom account "SQLServerUser", and alter the service account to run under another user, granting higher admin privs to the other account would still not work and the files remained encrpypted. I will endevour to trail some of these and see what we can come up with.
Perhaps you can write up paper on this with more drill down from your experiences??? this sort of thing would be of interest to many DBA's.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
hi Chris,
How much impact has the encryption of the databasefiles on performance?
Klaas-Jan
-
sqldesigner,
I will need to re-verify under a full domain model, but I dont believe you are correct re the data recovery admin group etc. The encryption key seems to be based around the SID of the user and the server's specific install. Therefore, NO other user can gain access to the file, no matter what you try, this applies to other 'administrators'. I will discuss this with some sysadmin friends of mine that use EFS extensively and see what they have to say.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Hi all
>>EFS and all SQL Server 2k encryption options for views, stored procedures etc utilises the Windows Crypto API.
This is incorrect. A number of high profile security sites have proved that the encryption option used for stored procs and views does not use the EFS crypto API.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
I'm looking into the restriction of access rights (via "guest" and "public") in the master database, and have a question. In the list of procedures to remove access to, you list "xp_perfmonitor". What is this procedure? What does it do? And why can't I find it in the master database, the Microsoft web site, or anywhere on Google that doesn't show the exact same list?
Oh.I (mostly) figured it out while writing the question, but will still post it for potential discussion purposes.
Philip
-
And that goes double for xp_schedulersignal!
Philip
-
I don't think the account you log into the machine under for the installation of SQL Server has anything to do with the account that the service(s) run under afterwards. If you create domain accounts for the SQL Server services and specify those during installation, those are the accounts the services are configured to use, not the account you're logged in as. Also, when you specify those accounts, SQL Server sets up the minimum necessary permissions for the accounts so you don't need to go through the trouble of doing it manually. Also, if you change the service account later through Enterprise Manager, it sets up the appropriate permissions though it probably does not remove them from the other account.
Also, file-system encryption does put a hit on performance. Putting database files in an encrypted location is not recommended unless the sensitivity of the data warrants the performance hit and, even then, there are probably better options such as third-party solutions. Encrypting backups is not bad idea but using file-level encryption is. NTFS encryption is too heavily dependant on the keys for the service accounts. If you want to encrypt your backups, go ahead and fork out some extra dough for LightSpeed for SQL Server (http://www.imceda.com) or something similar. It will perform better and has the added bonus of providing encryption and compression simulataneously which NTFS does not permit.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog -
Under the section labeled "The SA Account" you have the following statement regarding the "sysadmin" role, "Never grant this privilege to any other user, there is simply no reason to in a production environment.<O
> " I wonder if this holds true regardless of the environment and number of DBA's. </O><O
>In our environment we have three DBA's that share responsibility for mananging our production SQL Server environment. We all know the SA password, and could use it to perform production work if we chose to go that route. But instead each DBA has a SQL Server Logon for performing sysadmin duties. We have done this for audit trail purposes. Doing this allows us to identify which DBA has done what. If we shared the SA account we won't be able to determine which DBA has made a particular change. </O>Gregory A. Larsen, MVP
-
We have quite a few software packages running on SQL Server here. It is amazing when a tech. person comes to install their app. and they either create or already have an extremely simple ID with a matching password or one letter password. AND or use 'sa' or grant 'sa' rights to a new ID..... When I question this and or say I want a different password assigned to the ID they look at me like I have two heads....
Personally, I believe Microsoft needs to really change the thinking about security in SQL Server as with it being so open today small companies do this type of stuff and some of this poor security we are stuck with due to the design of the application. I know some of this has been addressed in SS2005 but I am not sure how much nor to what degree either.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply