Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


10 Steps to Securing your SQL Server


10 Steps to Securing your SQL Server

Author
Message
Brian Knight
Brian Knight
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: Moderators
Points: 1955 Visits: 235
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bknight/10securingyoursqlserver.asp

Brian Knight
Free SQL Server Training Webinars
Philip Kelley
Philip Kelley
Say Hey Kid
Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)Say Hey Kid (681 reputation)

Group: General Forum Members
Points: 681 Visits: 232
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
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7231 Visits: 2679
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/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
mjpins
mjpins
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 1
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
Brian Knight
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: Moderators
Points: 1955 Visits: 235
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! Smile 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

Brian Knight
Free SQL Server Training Webinars
danw
danw
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 59
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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (6.8K reputation)

Group: Moderators
Points: 6816 Visits: 1917
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
Jonr
SSC-Enthusiastic
SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)SSC-Enthusiastic (148 reputation)

Group: General Forum Members
Points: 148 Visits: 65
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
BABs
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 1
Thanks Brian!

Bettyann Bowes



Tatsu
Tatsu
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 307
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

Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
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