SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server Security: Security Admins


SQL Server Security: Security Admins

Author
Message
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25216 Visits: 1917
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritysecurityadmins.asp

K. Brian Kelley
@‌kbriankelley
Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2965 Visits: 113
Good article. I have a question. You gave the SQL to set the sa password. Can you run this while a server only allows NT accounts to login?

Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25216 Visits: 1917
Yes, you can. The sp_password will work on SQL Server logins even when the SQL Server is set to Windows Authentication.


K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@‌kbriankelley
Steve Jones
Steve Jones
SSC Guru
SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)SSC Guru (148K reputation)

Group: Administrators
Points: 148490 Visits: 19444
Excellent Brian.

Steve Jones
sjones@sqlservercentral.com
http://www.sqlservercentral.com/columnists/sjones
www.dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Chami Rstnayake
Chami Rstnayake
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 1

hi Brian,

Keep up the good work!!! It is excellent !

I have another question for you some what relate to this article. In my organisation DBA (my self ) and System admin. is 2 diffrent people.

as sql server with active direc. DC now and company does not give me domain admin priviledges (domain admin a/c being removed) and i can not do

sql login transfers to other servers , can not monitor remotely sql server with terminal server ... I am given only the local admin for sql server.

this is really a problem for me i need to document and ask for all permission I required and way it suppose to be between system admin of network and dba priviledges. can you suggest me to do my dba work properly and to avoid using AD domain admin priviledges what permission i need exactly.

thanks

great help!

chem


Steven.
Steven.
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1899 Visits: 247

Good article.

I have found that adding in the nt authority\system is a must, if you use enterprise backup software (brightstor or backupexec) as they don't log into the server as a domain account very easily.

Also Knowing the SA password is a must, when you remove the builtin\admin account.

For example. You are at your DR site and restore the master database on a machine which is not on your domain.

Suddenly you have lost your rights over the sql server aless you know the SA password or can join the server to the correct domain and log in



Steven
Gé Brander
Gé Brander
SSC Eights!
SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)

Group: General Forum Members
Points: 883 Visits: 56

Good article. It is clear. But with keeping BUILTIN\Administrators with sa rights, there is one thing we cannot forget. If your network is being attacked by a hacker who is getting on the server with local administrators rights, you have automatically also a potential problem with the data in your SQL Server.

Not giving BUILTIN\Administrators sa rights the chance on this is less.

Just my 2 cents...





K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25216 Visits: 1917
If the server is compromised by an attacker who manages to get local administrator rights, the data is already compromised even with BUILTIN\Administrators removed.

Attack Vector 1:

Stop SQL Server service. Copy off all user database files. Restart SQL Server service. And the attacker merely needs to re-attach the files to his/her own SQL Server. Even with EFS, the local administrator usually has the ability to recover the encrypted files for a given server. With local admin rights, the attacker resets the administrator password and then uses it to get to the database files. Other 3rd party encryption tools are handy here.

Attack Vector 2:

Grab backup file (most backups are going to go to the file system at least initially). Copy backup file. Restore it. Even if password protected, this doesn't encrypt the backup file. While restore may be more difficult, the data is plain to see. Here is where a product like SQL Litespeed is handy.

Attack Vector 3:

Install sniffer to monitor the traffic passing across the NIC to/from SQL Server. Since we're only interested in the traffic for this server, the NIC doesn't have to go into promiscuous mode. Unless stream is encrypted with SSL, data goes across in a TDS packet in a known format. SQL Server logins do as well and the encryption is trivial to crack.

Attack Vector 4:

Take advantage of Act as Part of the Operating System right to impersonate the SQL Server service account without requiring authentication. Force connection to SQL Server and use sp_grantlogin and sp_addsrvrolemember to place BUILTIN\Administrators back into the sysadmin fixed server role.

Description of Act as part of the operating system

K. Brian Kelley
@‌kbriankelley
Kenneth Fisher
Kenneth Fisher
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6413 Visits: 2060
Excellent article. I was wondering if you had any additional comments regarding SQL 2005 and 2008? I realize that in general the situation is the same. An administrator on the box can get at the data, unless encrypted, but why make it easy? So does encryption in general make a difference? Are there any other changes in how security works on the newer versions that should be taken into account?

Thanks
Kenneth

Kenneth FisherI strive to live in a world where a chicken can cross the road without being questioned about its motives.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/Link to my Blog Post --> www.SQLStudies.com
stiej1977
stiej1977
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 282
Hi,

Regarding the bit about Fulltext, does that really only apply on clustered fulltext situations? what about it not being clustered? do i not need to worry about nt authority\system being there as a login in the instance in those cases?

shouldn't i just ensure that if full text is there (used or not, clustered or not) that nt authority\system is also there with syadmin?

many thanks and and excellent article:-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search