you're reading...
SQL Server

Disabling SQL Server Network Protocols


Posted in SQL Server on May 8, 2009 by ldgaller

I had a 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. Enjoy, however be sure and test thuroughly in your environment. LDG


‘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

‘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”

‘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)
                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
                Call DisplayUsage
            End If
        Case “-p”:
            iNum = iNum + 1
            If iNum < oArgs.Count Then
                sPwd = oArgs(iNum)
                bPS = True
                Call DisplayUsage
            End If
        Case “-w”:
            iNum = iNum + 1
            If iNum < oArgs.Count Then
                iWait = CInt(oArgs(iNum))
                Call DisplayUsage
            End If
        Case Else
            Call DisplayUsage
    End Select
    iNum = iNum + 1

‘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
    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
        oServer.Login = Login
        oServer.Password = Password
    End If

    ’set server name
    oServer.Name = ServerName

    ‘connect to the server

    ‘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
        On Error Goto 0

        ‘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
                bReady = True
            End If
            iCount = iCount + 1
            Err = 0
        On Error Goto 0
        If bReady Then
            WScript.Echo “Server restarted successfully”
            WScript.Echo “Could not restart the server”
        End If
    End If


    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”
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


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.


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: