SQL Server Security Part 1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Chris Kempster » SQL Server Security Part 1

16 posts, Page 1 of 2

1 2 »»

SQL Server Security Part 1

Rate Topic

Display Mode

Topic Options

Author

Message

ckempste

Posted Friday, January 11, 2002 12:00 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 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"

Post #2197

Lhot

Posted Monday, January 14, 2002 12:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 21, 2004 10:54 AM
Points: 2, Visits: 1

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.

Post #26236

Hal_Smith

Posted Monday, January 14, 2002 3:39 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, July 13, 2007 10:34 AM
Points: 2, Visits: 1

quote:
Re-working the BUILTIN\Administrator SQLServer Login Account
This 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

Post #26237

ckempste

Posted Monday, January 14, 2002 5:03 PM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1

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"

Post #26238

ckempste

Posted Monday, January 14, 2002 5:12 PM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1

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"

Post #26239

sqldesigner

Posted Tuesday, July 16, 2002 1:44 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, December 15, 2002 12:00 AM
Points: 1, Visits: 1

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.

Post #26240

ckempste

Posted Wednesday, July 17, 2002 3:12 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1

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"

Post #26241

Klaas-Jan

Posted Wednesday, July 17, 2002 3:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 10, 2002 12:00 AM
Points: 135, Visits: 1

hi Chris,

How much impact has the encryption of the databasefiles on performance?
Klaas-Jan

Post #26242

ckempste

Posted Thursday, July 18, 2002 10:38 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1

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"

Post #26243

ckempste

Posted Wednesday, July 31, 2002 5:02 AM

SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1

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"

Post #26244

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions