10 Steps to Securing your SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Brian Knight » 10 Steps to Securing your SQL Server

18 posts, Page 1 of 2

1 2 »»

10 Steps to Securing your SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

Brian Knight

Posted Sunday, June 02, 2002 12:00 AM

SSCommitted

Group: Moderators
Last Login: Monday, August 13, 2012 1:06 PM
Points: 1,928, Visits: 224

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

Post #4569

Philip Kelley

Posted Monday, June 03, 2002 9:30 AM

SSChasing Mays

Group: General Forum Members
Last Login: Tuesday, September 11, 2012 1:10 PM
Points: 649, Visits: 201

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

Post #34825

Andy Warren

Posted Monday, June 03, 2002 10:16 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #34826

mjpins

Posted Monday, June 03, 2002 10:29 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2003 12:00 AM
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

Post #34827

Brian Knight

Posted Monday, June 03, 2002 10:34 AM

SSCommitted

Group: Moderators
Last Login: Monday, August 13, 2012 1:06 PM
Points: 1,928, Visits: 224

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

Brian Knight
Free SQL Server Training Webinars

Post #34828

danw

Posted Wednesday, June 05, 2002 3:50 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 27, 2012 10:43 PM
Points: 126, Visits: 56

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

Post #34829

K. Brian Kelley

Posted Thursday, June 06, 2002 7:19 AM

Keeper of the Duck

Group: Moderators
Last Login: Yesterday @ 1:55 PM
Points: 6,584, Visits: 1,789

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, 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 #34830

Jonr

Posted Thursday, April 03, 2003 12:42 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 13, 2012 9:34 AM
Points: 141, Visits: 61

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

Post #34831

BABs

Posted Thursday, April 03, 2003 8:47 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, June 04, 2007 7:45 AM
Points: 10, Visits: 1

Thanks Brian!

Bettyann Bowes

Post #34832

Tatsu

Posted Thursday, April 03, 2003 8:58 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 10:43 AM
Points: 287, Visits: 213

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

Post #34833

« Prev Topic | Next Topic »

18 posts, Page 1 of 2

1 2 »»

Permissions