Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Security: Security Admins Expand / Collapse
Author
Message
Posted Friday, June 13, 2003 12:00 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritysecurityadmins.asp

K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #13202
Posted Friday, June 27, 2003 6:02 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 9:05 AM
Points: 976, Visits: 59
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
SQL Programmer
Ipreo
Post #66560
Posted Friday, June 27, 2003 6:42 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #66561
Posted Friday, June 27, 2003 11:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:19 PM
Points: 33,094, Visits: 15,201
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
Post #66562
Posted Sunday, July 18, 2004 8:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 22, 2005 11:13 PM
Points: 22, 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

Post #126834
Posted Sunday, July 18, 2004 9:03 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, July 10, 2014 7:02 AM
Points: 739, Visits: 206

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
Post #126836
Posted Thursday, October 7, 2004 1:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 13, 2011 11:45 AM
Points: 221, Visits: 55

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...




Post #140478
Posted Thursday, October 7, 2004 2:01 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #140483
Posted Tuesday, September 7, 2010 10:30 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:01 AM
Points: 3,461, Visits: 1,785
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 Fisher
I 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
Post #981698
Posted Wednesday, August 29, 2012 11:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 9:58 AM
Points: 62, Visits: 244
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
Post #1351775
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse