//
you're reading...
SQL Server

Changing the SQL Server Audit Level

I had recent requirement arise to have the auditing level change on about 200 of our remote database servers. We were already logging failed logons but the new requirement was to log both failed and successful. I new it was easily managed through Management Studio but that would be cumbersome . The alternative involves scripting a registry key change which would normally not be my first choice . However in this instance the choice was clear attach to 200 servers and manually change or automate the process. Being inherently lazy I chose automation. For the automated solution I had two options 1) T-SQL utilizing the undocumented xp_regwrite or 2) VBScript. Since this was going to be pushed out utilizing RemoteWare I opted for option 2. That way it could be run from the command line without logging into sql server.  As a bonus there is a bit of code to disable all but TCP\IP in the SQL Server Network protocol stack. This ended up being an additional requirement, something we normally do as part of our server builds but was missed on the golden image for these servers.

Script:

‘——————————————
‘Auth: L.D.Galler
‘Date: 20090508
‘Desc: Script to disable all but tcp\ip on
‘        a given sql server instance & Set Audit Level to ALL

‘Usage: SQLServerNetworkProtocolDefaults [-s server] [-r] [-i] [-l login] [-p password] [-w]

‘[-s server]     – server name
‘[-r]            – restart the server after disabling protocols
‘[-i]            – use integrated security
‘[-l login]      – login
‘[-p password]   – password
‘[-w seconds]    – how many seconds keep trying start the server

‘Notes:
‘You need to specify either [-i] or both [-l] and [-p].
‘If you specify both, [-i] will take over
‘-w defaults to 30 if not specified

‘Audit Level Registry Value
‘None         0×00000000 (0)
‘Success     0×00000001 (1)
‘Failure     0×00000002 (2)
‘All         0×00000003 (3)

‘CSCRIPT SQLServerNetworkProtocolDefaults.vbs
‘——————————————-
Option Explicit

Dim oArgs, sServer, bRestart, bIntegratedSec, sUserid, sPwd, iWait, iNum,sAuditLevel,bSuccess
sServer = “(local)”
bRestart = False
bIntegratedSec = True
sUserid = “”
sPwd = “”
iWait = 30
sAuditLevel = “3”
bSuccess=False

‘verify arguments
Dim bLS, bPS
bLS = False
bPS = False

iNum = 0
Set oArgs = WScript.Arguments
While iNum < oArgs.Count
Select Case LCase(oArgs(iNum))
Case “-s”:
iNum = iNum + 1
If iNum < oArgs.Count Then
sServer = oArgs(iNum)
Else
Call DisplayUsage
End If
Case “-r”:
bRestart = True
Case “-i”:
bIntegratedSec = True
Case “-l”:
iNum = iNum + 1
If iNum < oArgs.Count Then
sUserid = oArgs(iNum)
bLS = True
Else
Call DisplayUsage
End If
Case “-p”:
iNum = iNum + 1
If iNum < oArgs.Count Then
sPwd = oArgs(iNum)
bPS = True
Else
Call DisplayUsage
End If
Case “-w”:
iNum = iNum + 1
If iNum < oArgs.Count Then
iWait = CInt(oArgs(iNum))
Else
Call DisplayUsage
End If
Case Else
Call DisplayUsage
End Select
iNum = iNum + 1
WEnd

‘verify that [i] or both [-l] and [-p] were specified
If bIntegratedSec Or (bLS And bPS) Then
Call DisableAllButTCP (sServer, bRestart, bIntegratedSec, sUserid, sPwd, iWait)
Call SetLoginAuditLevel
Else
Call DisplayUsage
End If
‘ Tells the script to stop and exit.
‘——————————————————
Sub DisableAllButTCP(ServerName, Restart, Integrated, Login, Password, Wait)
‘create the instance of SQLServer object
Dim oServer
Set oServer = CreateObject(“SQLDMO.SQLServer”)

’set login information
If Integrated Then
oServer.LoginSecure = True
Else
oServer.Login = Login
oServer.Password = Password
End If

’set server name
oServer.Name = ServerName

‘connect to the server
oServer.Connect

‘get registry information for the server
Dim oRegistry
Set oRegistry = oServer.Registry

’set registry key to enable only TCP/IP
oRegistry.SuperSocketList = “tcp”

Set oRegistry = Nothing

‘restart the server
If Restart Then
’stop the server
WScript.Echo “Stopping the server”
On Error Resume Next
oServer.Shutdown(True)
On Error Goto 0
oServer.DisConnect

‘try to restart
WScript.Echo “Trying to start the server”
Dim iCount, bReady
iCount = 0
bReady = False
On Error Resume Next
‘try a few times
While (iCount < Wait) And Not bReady
oServer.Start False, ServerName
If Err <> 0 Then
‘delay 1s between retries
WScript.Sleep 1000
Else
bReady = True
End If
iCount = iCount + 1
Err = 0
WEnd
On Error Goto 0
If bReady Then
WScript.Echo “Server restarted successfully”
Else
WScript.Echo “Could not restart the server”
End If
End If

oServer.DisConnect

Set oServer = Nothing
End Sub
‘———————————————————
Sub DisplayUsage
WScript.Echo “Usage: SQLServerNetworkProtocolDefaults [-s server] [-r] [-i] [-l login] [-p password] [-w]”
WScript.Echo “”
WScript.Echo ”       [-s server]     – server name”
WScript.Echo ”       [-r]            – restart the server after disabling protocols”
WScript.Echo ”       [-i]            – use integrated security”
WScript.Echo ”       [-l login]      – login”
WScript.Echo ”       [-p password]   – password”
WScript.Echo ”       [-w seconds]    – how many seconds keep trying start the server”
WScript.Echo “”
WScript.Echo “You need to specify either [-i] or both [-l] and [-p]. If you specify both, [-i] will take over”
WScript.Echo “Server name defaults to “”(local)”” if not specified”
WScript.Echo “-w defaults to 30 if not specified”
WScript.Quit
End Sub
‘———————————————————
Sub SetLoginAuditLevel
Dim objShell, RegLocate, RegLocate1
Set objShell = WScript.CreateObject(“WScript.Shell”)
On Error Resume Next
RegLocate = “HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\MSSQLServer\AuditLevel”

objShell.RegWrite RegLocate,”3″,”REG_DWORD”

End Sub

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: