10 Steps to Securing your SQL Server

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

  • 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

  • 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



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


    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


    Set oServer = Nothing



    Wscript.Echo "Total Servers Checked: " & oTotalSvr

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

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


    Set oApp = Nothing


  • 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



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



  • 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



    K. Brian Kelley

  • 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


  • Thanks Brian!

    Bettyann Bowes

  • Updated link to the Retina Sapphire utility:


    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.


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

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


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

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



  • 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


    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.

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

  • 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