June 2, 2002 at 12:00 am
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
June 3, 2002 at 9:30 am
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
June 3, 2002 at 10:16 am
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
June 3, 2002 at 10:29 am
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
June 3, 2002 at 10:34 am
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
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars
June 5, 2002 at 3:50 pm
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
June 6, 2002 at 7:19 am
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
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 3, 2003 at 12:42 am
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
April 3, 2003 at 8:47 am
Thanks Brian!
Bettyann Bowes
April 3, 2003 at 8:58 am
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.
[font="Tahoma"]Bryant E. Byrd, BSSE MCDBA MCAD[/font]
Business Intelligence Administrator
MSBI Administration Blog
April 3, 2003 at 9:11 am
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
April 14, 2003 at 1:59 pm
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
May 27, 2005 at 3:01 am
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.
May 27, 2005 at 5:39 am
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).
May 27, 2005 at 10:13 am
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