10 Steps to Securing your SQL Server

  • Brian Knight

    One Orange Chip

    Points: 27249

    Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bknight/10securingyoursqlserver.asp

  • Philip Kelley

    SSCertifiable

    Points: 6351

    What I'd like to know is, where do you find out about these secret registry keys? I've always wanted to be able to bump the number of error logs kept, was confident that there was some way to do so (a very typical Microsoft feature), but never stumbled across the methods. In my defence, I never tried very hard... but where would you start looking for this stuff? (I'm assuming it's nowehere in BOL.)

    In any case, thanks for posting this and the rest. Good to review what we've already done, and find out about what we've overlooked!

    Philip Kelley

  • Andy Warren

    SSC Guru

    Points: 119676

    You can set in EM by right clicking the error log folder. Profiling that reveals the following:

    xp_instance_regwrite N'HKEY_LOCAL_MACHINE', SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorlogs', REG_DWORD, 8

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • mjpins

    Valued Member

    Points: 70

    Here's a VBScript to check for SA accounts with no password or a password of "SA". Found base code on a Microsoft newsgroup and modified it slightly. This is limited to searching a subnet but came in very handy recently. Save code as AUDITSA.VBS, then execute using the following:

    CSCRIPT AUDITSA.VBS SRVLIST.TXT

    This creates a text file (SRVLIST.TXT) that identifies the servers at risk...

    Contents of AUDITSA.VBS:

    ------------------------

    'Audit subnet for Servers with blank sa password

    Dim oApp

    Dim oServer

    Dim oDatabase

    Dim oNames

    Dim oName

    Dim oTotalSvr

    Dim oTotalBlank

    Dim oTotalSA

    oTotalSvr = 0

    oTotalBlank = 0

    oTotalSA = 0

    Set oApp = CreateObject("SQLDMO.Application")

    Set oNames = oApp.ListAvailableSQLServers()

    On Error Resume Next

    For Each oName In oNames

    Set oServer = CreateObject("SQLDmo.SqlServer")

    oTotalSvr = oTotalSvr + 1

    oServer.LoginSecure = False

    oServer.LoginTimeout= 30

    oServer.Connect oName,"sa",""

    If Err.Number=0 Then

    WScript.Echo "!!!Server " & oName & " has a blank sa password"

    WScript.Echo oServer.VersionString

    WScript.Echo ""

    oTotalBlank = oTotalBlank + 1

    End If

    If Err.Number<>0 Then

    oServer.Connect oName,"sa","sa"

    If Err.Number=0 Then

    WScript.Echo "!!!Server " & oName & " has a sa password equal to SA"

    WScript.Echo oServer.VersionString

    WScript.Echo ""

    oTotalSA = oTotalSA + 1

    End If

    End If

    oServer.DisConnect

    Set oServer = Nothing

    Err.Clear

    Next

    Wscript.Echo "Total Servers Checked: " & oTotalSvr

    Wscript.Echo "Total Servers w/Blank Password: " & oTotalBlank

    Wscript.Echo "Total Servers w/Password of SA: " & oTotalSA

    oApp.Quit

    Set oApp = Nothing

    Wscript.Quit

  • Brian Knight

    One Orange Chip

    Points: 27249

    quote:


    You can set in EM by right clicking the error log folder. Profiling that reveals the following:

    xp_instance_regwrite N'HKEY_LOCAL_MACHINE', SOFTWARE\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorlogs', REG_DWORD, 8


    Cool! I never noticed that EM feature before! 🙂 I too found it by doing a profiler trace one day. The key is nice to know when you're trying to roll it out to lots of servers, but I like the EM method that Andy shows for lowering the risk.

    Brian Knight

    bknight@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bknight

  • danw

    SSCommitted

    Points: 1842

    Nice article,

    Here is a site dedicated to sql security: http://www.sqlsecurity.com

    You'll definately want to run a tool to scan for easily guessed passwords too.

    I found a few on my servers.

    I've removed the extended stored procedures that they recomend without any major functionality being removed from EM. EM is mostly useless anyways. If you can't live without it you probably should learn a bit more about MSSQL before becoming a DBA.

    Also check out SQLPing if you want to scan your subnet for insecure servers.

    Thanks,

    Dan

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    The Microsoft Security Baseline Scanner will scan for blank or weak SQL Server passwords (it also handles IIS and the OS) in addition to checking for service packs and hot fixes (http://www.microsoft.com/security). With respect to systems which are vulnerable to SQLSnake, eEye Digital Security has put out scanners to include class A address spaces (http://www.eeye.com).

    K. Brian Kelley

    bkelley@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/bkelley/

    K. Brian Kelley
    @kbriankelley

  • Jonr

    SSCrazy

    Points: 2566

    Excellent advice Brian. Particularly liked the point about removing BuiltIn\Administrators - we find that the vast majority of our problems over the last six months have been caused by knowledgeable sysadmins 'playing' around in SQL Server without realising the consequences of their actions. Gives backing to the idea that it's those within that are at least as great a threat as those outside.

    Edited by - jonreade on 04/04/2003 03:18:22 AM


    Jon

  • BABs

    SSC Veteran

    Points: 248

    Thanks Brian!

    Bettyann Bowes

  • Tatsu

    SSCertifiable

    Points: 7824

    Updated link to the Retina Sapphire utility:

    http://www.eeye.com/html/Research/Tools/register.html?file=RetinaSapphireSQL

    I think they caught on and want to get everyone's personal information now. Linking directly to the exe doesn't appear to work.

    Bryant E. Byrd, MCDBA

    SQL Server DBA/Systems Engineer

    Intellithought, Inc.

    bbyrd@intellithought.com

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • Tatsu

    SSCertifiable

    Points: 7824

    One other thing in reference to "Remove BUILTIN/Administrators". If the SQL Server is clustered, you will need to create a login for the service account that the Cluster Server service runs under. According to Microsoft (http://support.microsoft.com/default.aspx?scid=kb;[LN];Q263712) this account needs SA rights. Also mentioned in this article is that "NT Authority/System" needs SA rights if you are using Full-text Search.

    Many people using Microsoft Operations Manager (MOM) to monitor their servers also make the MOM service account a domain admin. If they are relying on this for access to SQL Server to determine service availability, you will need to grant a login for that account as well. This account, however, doesn't need any special rights. Just creating the login gives it permission to run "select @@version" which is all it does to check to make sure connections are available on the server.

    Bryant E. Byrd, MCDBA

    SQL Server DBA/Systems Engineer

    Intellithought, Inc.

    bbyrd@intellithought.com

    [font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
    Business Intelligence Administrator
    MSBI Administration Blog

  • Juanita

    SSCertifiable

    Points: 5300

    I have a question about the changing the startup account. The article says to create a login with very little rights. What are the minimun rights as far as the server roles and database access?

    Thanks!

    Juanita

  • Mark Humble

    SSC Enthusiast

    Points: 124

    Stored Procedures & Extended Stored Procedures

    One quite importnat thing a lot of organisations do is also remove\(drop) the XP_cmdshell. The reasons for this is also well enough documented

    http://www.sqlsecurity.com

    Some sources go on to suggest dropping a raft of others which is fine in principle - but have found that quite often some core sql server functionality will break.

    Through trial and error I've found only some can really be dropped without breaking functionality to EM or the other SQL Tools. You can actally re-add them if you make boob.  A backup of the master db to start is always a good starting point. There's also a train of thought that rather than drop any Xp's or sp's just use the revoke use of these.

    However the security of the sql server is probably only as good as the weakest chain in the link so should form part of an overall server hardening strategy inc. (OS) as well as any third party tools e.g Compaq web agents etc. Use\adapt the NSA (National Security Agency) Windows Security templates to include sql server. Interestingly, last time I looked seemed they'd developed stuff for Securing Microsoft Exchange yet no SQL Server one - c'mon guys...

    The above source is pretty good also goes into other areas including removing use of unnecessary\unused network libraries to further tighten up. 

    However as the Author goes into the use of Windows Authentication/Kerberos (which is much much stronger) than mere sql server userid's and password - is the 1st main consideration - if securing your server or application can be either way choose Windows thats why one of the reasons there not just to lessen the account admin burden on DBA's.

  • M. Laurie Smith

    SSC Enthusiast

    Points: 165

    I think it is worthwhile to point out that enabling SQL Server login auditing as a properties is a very limited method for auditing logins.  We have used this, and found that you get so little information on the connection and have no control of the detination, and therefore other auditing was required.  We are now implementing auditing using security audit event options.  (allowing us to obtain client machine and osuser information for the SQL Server login connection failure).

  • Steven E. Williams

    Old Hand

    Points: 344

    When you set login authentication to Windows Only what happens to the SA account as it is(at least it appears to be) an SQL Server account. Is the SA account still accessible in-case you lose domain connectivity and need to access the server?

    Also what if your server is part of a workgroup instead of a domain. Can you still use the Windows Only authentication? i.e. same account setup between a web server and the SQL Server and utilize and NT account instead of a SQL Server account (login).

     

Viewing 15 posts - 1 through 15 (of 18 total)

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