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

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

SSIS SQL Server provider logging (Can’t log to sysdtslog90 table)

I recently migrated from SQL Server 2005 to SQL Server 2008 for our Data Warehouse and analytical reporting (SSIS, SSAS). In SQL Server 2005 I had created several template SSIS projects and a general infrastructure for auditing and error handling. Everything ported fine except for some reason I was not capturing the SSIS logging to the normal dbo.sysdtslog90 table, which is created by default in the database specified in the connection object parameter when you turn the SQL Server logging provider on. I quickly found out that contrary to popular belief SQL Server 2008 (SSIS) does not log to dbo.sysdtslog90 but instead logs to the msdb.dbo.sysssislog table.

Search All Databases for a Table

I just had a user send me an application error stating that a table the process was trying to create already existed. The user running the process did not know which of the five application databases the process was referring to and not wanting to run a query against all five databases I created the following simple stored procedure that searches all the databases on a server for a given table name. It has been tested on SQL 2k5 & SQL 2k8.

 

Create PROCEDURE [Util].usp_SearchAllDBforTable
    @tablename sysname
AS
declare @cmd1 varchar(500)
set @cmd1 = ‘USE ?
SELECT [TABLE_CATALOG]
      ,[TABLE_SCHEMA]
      ,[TABLE_NAME]
      ,[TABLE_TYPE]
  FROM [INFORMATION_SCHEMA].[TABLES]
  WHERE [TABLE_NAME] = ”’ + @tablename + ””
exec sp_MSforeachdb  @command1=@cmd1
GO

SSIS: Using IF Then Else logic in Derived Columns

A lot of people tend to use a custom script in order to accomplish If Then Else logic simply because the interface for the derived column data object does not have an IF\IIF operator.. In order to place an IF statement for the derived column you would use the following syntax:

({Boolean Expression}?{True Part}:{False Part})

So if I were checking against a ProductType column to determine whether I should use WholesalePrice or RetailPrice You could simply write up something like the following.

([ProductType ]==99?[WholesalePrice ]:[RetailPrice ])

In order to use multiple entries you would just need to expand the syntax. For example:

([ProductType ]==99?[x]:([ProductType ]==1?[y]:[z]))

A benefit to using the derived column data object instead of a custom script task is that the process is so much faster. In my experience with large ETL sets of data substituting the derived column for the custom script task cuts the processing for that event by 50%.

SQL Server service account locked

I received the following message in the SQL Server error log after an Active Directory change inadvertently locked one of our sql service accounts.

“The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0×2098. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.”

The SPN is essentially a mapping between a principal name and the Windows account that started the server instance service. This is needed because the client will use the server’s hostname and the TCP/IP port to which it connects to compose an SPN. If the SPN mapping has not been performed, then the Windows security layer will be unable to determine the account associated with the SPN and Kerberos authentication will not be used. In an attempt to facilitate this, the SQL Server 2005 instance will automatically try to register the SPN with the AD at startup if TCP/IP is enabled. This message results from the fact that only a domain administrator or a Local System account has the authority to register an SPN. Therefore, under a normal account, SQL Server will be unable to register the SPN for the instance. This should normally not prevent the services from starting but for us it acted as a cryptic indicator that there was an issue with AD. Had we not discovered that the account was locked we would have had all sorts of issues once the cached credentials for the service account had expired.

Property IsLocked is not available for Login

One of my users phoned stating that their application sql logon was unable to connect. I checked the error logs and indeed saw that the logon attempts where failing. I then went to check the account settings in management studio and received the follow error when right clicking on the logon properties:

TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
——————————
Property IsLocked is not available for Login ‘[xxxx]‘. This property may
not exist for this object, or may not be retrievable due to
insufficient access rights. (Microsoft.SqlServer.Smo)

Apparently the account had been disabled or was in some kind of funky state. About to drop and re-create the logon I decided to try and re-enable the account. Thankfully that resolved the issue and I did not have to drop and re-create the logon. I am still trying to determine root cause but if you encounter the same issue then run the following where “thelogon” is the logon in question and “thepassword” is the password for the account:

ALTER LOGIN [thelogon] WITH PASSWORD=N’thepassword’, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 
GO  
ALTER LOGIN [thelogon] ENABLE

Optimizing Integration Services Lookups

Be selective about the lookup columns

Most designers would use the obvious default behavior of the Lookup transform, selecting a table or view to look up in. However, choosing a table will be interpreted as “SELECT *”. This will cause the transformation to fetch un-needed data. That is a waste of effort, memory and resources. I recommend choosing “Use results of an SQL query” instead of naming a table, and in the query selecting only the columns that are used.

Enable memory restriction

Two things happen when memory restriction is enabled on a Lookup: First, the amount of memory that the Lookup is allowed to use is limited. A cache policy is used and new rows are added to the cache on demand. Second, the new rows are added to the cache individually. In other words, SSIS will query the relational database whenever data for a row cannot be located in the internal cache kept by SSIS. These are single-row queries, unlike the large set-based table queries that occur when memory restriction is not enabled. The benefit of enabling memory restriction is that large lookups can be performed which might not be possible otherwise. The cost is that single row queries are used, which collectively are usually slower than a single table query. However, I have found that this performance cost is not always as bad as one might expect, if appropriate indexes are present on the lookup table.

Conclusion

Most of the time you will get good performance from Lookup transforms with default settings. Sometimes, lookups can become significant in the performance of an SSIS package especially  if processing a large amount of source rows. I have given a few tips that I have found make the biggest difference in lookup performance. I hope you find them useful in your package designs.

• Be selective about the columns you request in a lookup
• Enable memory restriction if the lookup is using too much memory
• Be sure you have an appropriate index on the lookup table when using memory restriction
• Consider enabling memory restriction if the lookup refers to a small number of rows from the lookup table

Random Password Generator

We have an environment with over 300 sql server instances and every 90 days we need to cycle “sa”. To help in this endeavor I created a script to generate random passwords. The passwords can be any length you require (while @idx < n). I use this script to generate a random password, reset “sa” and then write the new password out to an encrypted column in SQL Server.

 

declare @idx as int
declare @randomPwd as nvarchar(64)
declare @rnd as float
select @idx = 0
select @randomPwd = N”
select @rnd = rand((@@CPU_BUSY % 100) + ((@@IDLE % 100) * 100) +
       (DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))
while @idx < 8 –64
begin
   select @randomPwd = @randomPwd + char((cast((@rnd * 83) as int) + 43))
   –PRINT @randomPwd –debug
   select @idx = @idx + 1
elect @rnd = rand()

end

Select

@randomPwd

Computed Columns and Divide by Zero Errors

Lets say you have a query, computed column etc. that has the following formula: SomeColumnA / SomeColumnB, where SomeColumnA and SomeColumnB are non-NULL numeric columns.  Clearly a problem can arise if there is a record in the database with SomeColumnB equal to 0.0, as that will result in a Divide by zero error.

Thankfully there is a rather simple solution:

use NULLIF to see if SomeColumnB was 0.0.  If so,  have it return NULL, and the division will result in NULL.  Then wrapped the whole thing in an ISNULL, so if the division resulted in NULL, the result was 0.0.

As an alternate solution use the case statement

Case

SomeColumnB = 0 then 0.0

ELSE SomeColumnA / SomeColumnB

END

 

 

SQL 2008 Install Blocked on Express Tools

 

While attempting to install SQL Server 2008 Developer Edition on my laptop I failed on the express tools validation. Forgotten that I had even installed the express edition I immediately uninstalled and rebooted my machine. To my dismay I was blocked at the exact same point, having successfully completely uninstalled the SQL server 2005 express edition from my laptop. Seeing that it was in fact gone I was a bit perplexed and thought possibly it was a bug in the installation process.  I began digging through the install logs confirming that setup thought I had the Express Tools installed as shown below

upon further investigation I found a reference to the registry key SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM

Slp: Initializing rule      : SQL Server 2005 Express tools
Slp: Rule applied features  : SSMS;Adv_SSMS
Slp: Rule is will be executed  : True
Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.Sql2005SsmsExpressFacet
Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine
Slp: Sco: Attempting to open registry subkey
Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM
Slp: Rule ‘Sql2005SsmsExpressFacet’ detection result: SQL 2005 Ssms EE installed=True
Slp: Evaluating rule        : Sql2005SsmsExpressFacet
Slp: Rule evaluation done   : Failed
Slp: Rule evaluation message: The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools.

Checking the registry I find that the key does indeed exist and in fact was added by the install of Redgate SQL Prompt 3.5 as shown below

I exported the offending registry key, deleted it, installed SQL server 2008 without an issue and then imported the sql promt key back into the registry.

Filed under: SQL Tools, SQL 2008