//
you're reading...
SQL Server

Disabling SQL Server Network Protocols

 

I had recent requirement to disable all but TCP\IP in the SQL Server Network protocol stack on about 200 of our remote database servers. This is something we normally do as part of our server builds but was missed on the golden image for these servers. As this was going to be deployed via RemoteWare I opted to create and execute a VBScript via command line rather then a T-SQL utilizing the undocumented xp_regwrite.  As a bonus there is a bit of code to change the server audit level which ended up being part of an overall security configuration adjustment..

 

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         0x00000000 (0)
‘Success     0x00000001 (1)
‘Failure     0x00000002 (2)
‘All         0x00000003 (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: