SQL Server Security: Security Admins

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritysecurityadmins.asp

    K. Brian Kelley
    @kbriankelley

  • 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

  • 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

  • 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

  • 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

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

  • 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

  • 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 was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • 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:-)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply