Dim GlobalErrorList: Set GlobalErrorList = New ArrayList
Function IsValidObject(ByVal oObject)
IsValidObject = False
If IsObject(oObject) Then
If Not oObject Is Nothing Then
IsValidObject = True
End If
End If
End Function
Function MomCreateObject(ByVal sProgramId)
Dim oError
Set oError = New Error
On Error Resume Next
Set MomCreateObject = CreateObject(sProgramId)
oError.Save
On Error GoTo 0
If oError.Number <> 0 Then ThrowScriptError "Unable to create automation object '" & sProgramId & "'", oError
End Function
Public Function GetSQLServiceName(sInstance)
If sInstance = SQL_DEFAULT Then
GetSQLServiceName = SQL_DEFAULT
Else
GetSQLServiceName = "MSSQL$" & sInstance
End If
End Function
'The function returns service or "Unknown" state
'Input:
' server - compute name
' service - system service name
'Output:
' service state or "Unknown" state
Function GetServiceState( sTargetComputer, sServiceName)
On Error Resume Next
Dim sNamespace, sQuery, oWMI, objClasses, sState
sNamespace = "winmgmts://" & sTargetComputer & "/root/cimv2"
sQuery = "SELECT State FROM Win32_Service where Name = """ & EscapeWQLString(sServiceName) & """"
Set oWMI = GetObject(sNamespace)
Set objClasses = oWMI.ExecQuery(sQuery)
if objClasses.Count >= 1 Then
sState = GetFirstItemFromWMIQuery(objClasses).Properties_.Item("State")
End If
If Err.number <> 0 Or objClasses.Count = 0 Then
sState = "Unknown"
End If
Err.Clear
GetServiceState = sState
End Function
'#Include File:SQL2014Constants.vbs
Public Sub Save()
m_lNumber = Err.Number
m_sSource = Err.Source
m_sDescription = Err.Description
m_sHelpContext = Err.HelpContext
m_sHelpFile = Err.HelpFile
End Sub
Public Sub Raise()
Err.Raise m_lNumber, m_sSource, m_sDescription, m_sHelpFile, m_sHelpContext
End Sub
Public Sub Clear()
m_lNumber = 0
m_sSource = ""
m_sDescription = ""
m_sHelpContext = ""
m_sHelpFile = ""
End Sub
Public Default Property Get Number()
Number = m_lNumber
End Property
Public Property Get Source()
Source = m_sSource
End Property
Public Property Get Description()
Description = m_sDescription
End Property
Public Property Get HelpContext()
HelpContext = m_sHelpContext
End Property
Public Property Get HelpFile()
HelpFile = m_sHelpFile
End Property
End Class
Class ArrayList
Private m_itemArray
Private Sub Class_Initialize()
Me.Clear
End Sub
Private Sub AddItemToArray(ByRef itemArray, ByVal item)
ReDim Preserve itemArray(UBound(itemArray) + 1)
itemArray(UBound(itemArray)) = item
End Sub
Public Sub Clear()
m_itemArray = Array()
End Sub
Public Sub Add(item)
AddItemToArray m_itemArray, item
End Sub
Public Sub RemoveAt(index)
If index < 0 Or index > UBound(m_itemArray) Then
Exit Sub
End If
Dim newArr: newArr = Array()
Dim i
For i = 0 To UBound(m_itemArray)
If i <> index Then
Call AddItemToArray(newArr, m_itemArray(i))
End If
Next
m_itemArray = newArr
End Sub
Public Property Get Count()
Count = UBound(m_itemArray) + 1
End Property
Public Property Get ItemsArray()
ItemsArray = m_itemArray
End Property
Public Property Get IsEmpty()
IsEmpty = UBound(m_itemArray) < 0
End Property
End Class
''''''''''''''''''''''''''''''''''''
''' ScriptLogger
''''''''''''''''''''''''''''''''''''
Class ScriptLogger
Dim sourceLogEvent
Private Sub Class_Initialize()
sourceLogEvent = "Management Group: " + ManagementGroupName + ". Script: " + WScript.ScriptName + ". Version: " + MANAGEMENT_PACK_VERSION
End Sub
Private Sub Class_Terminate()
End Sub
Public Property Get ErrorEventType
ErrorEventType = 1
End Property
Public Property Get WarningEventType
WarningEventType = 2
End Property
Public Property Get InfoEventType
InfoEventType = 4
End Property
Private Function LogEvent (message, eventType)
On Error Resume Next
Dim oAPI
Set oAPI = CreateObject("MOM.ScriptAPI")
Call oAPI.LogScriptEvent(sourceLogEvent, SCRIPT_EVENT_ID, eventType, message)
End Function
Public Function LogDebug(message)
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
LogEvent message, Me.InfoEventType
End If
End Function
Public Function LogError(message)
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
End If
LogEvent message, Me.ErrorEventType
End Function
Public Function LogWarning(message)
if DEBUG_MODE Then
WScript.StdOut.WriteLine message
End If
LogEvent message, Me.WarningEventType
End Function
Public Function LogFormattedError(customMessage)
If Err.number <> 0 Then
Me.LogError FormatErrorMessage(customMessage)
End If
End Function
Private Function ScriptInfo()
Dim commandLineInfo : commandLineInfo = WScript.ScriptFullName
Dim argument
For Each argument In WScript.Arguments
commandLineInfo = commandLineInfo & " """ & argument & """"
Next
ScriptInfo = commandLineInfo
End Function
End Class
Function FormatErrorMessage(customMessage)
FormatErrorMessage = customMessage
If Err.number <> 0 Then
Dim msg
msg = Replace(" Error Number: #P1# " & VbCrLf & " Description: #P2# ", "#P1#", CStr(Err.number) )
msg = Replace(msg, "#P2#", Err.Description )
If customMessage <> "" Then
msg = customMessage & VbCrLf & msg & VbCrLf
End If
FormatErrorMessage = msg
End If
End Function
Function ThrowScriptErrorNoAbort(ByVal sMessage, ByVal oErr)
On Error Resume Next
Dim errorText: errorText = sMessage & ": " & oErr.Description
GlobalErrorList.Add errorText
End Function
Function ThrowScriptError(Byval sMessage, ByVal oErr)
On Error Resume Next
ThrowScriptErrorNoAbort sMessage, oErr
GlobalErrorListToEventLog()
Quit()
End Function
Sub HandleError(customMessage)
Dim localLogger
If Err.number <> 0 Then
Set localLogger = new ScriptLogger
localLogger.LogFormattedError(customMessage)
Wscript.Quit 0
End If
End Sub
Function HandleErrorContinue(customMessage)
HandleErrorContinue = True
If Err.number <> 0 Then
HandleErrorContinue = False
GlobalErrorList.Add FormatErrorMessage(customMessage)
Err.Clear
End If
End Function
Function HandleSqlErrorContinue(adoConnection, customMessage)
HandleSqlErrorContinue = True
If Err.Number <> 0 Then
HandleSqlErrorContinue = False
Dim sqlErr
Dim e: Set e = new Error
e.Save
On Error Resume Next
If adoConnection.Errors.Count > 0 Then
Set sqlErr = adoConnection.Errors(0)
adoConnection.Errors.Clear
Call Err.Raise(sqlErr.Number, sqlErr.Source, sqlErr.Description)
Else
Call e.Raise()
End If
HandleErrorContinue customMessage
End If
End Function
Function GetGlobalErrorListEventString()
GetGlobalErrorListEventString = ""
If Not GlobalErrorList.IsEmpty Then
GetGlobalErrorListEventString = "The next errors occured:"& vbNewLine & Join(GlobalErrorList.ItemsArray, vbNewLine & vbNewLine)
End If
End Function
Function GlobalErrorListToEventLog()
On Error Resume Next
If Not GlobalErrorList.IsEmpty Then
Dim localLogger: Set localLogger = New ScriptLogger
localLogger.LogWarning GetGlobalErrorListEventString()
End If
End Function
Function Quit()
WScript.Quit()
End Function
'#Include File:WMI.vbs
Function EscapeWQLString (ByVal strValue)
On Error Resume Next
Err.Clear
EscapeWQLString = Replace(strValue, "'", "\'")
End Function
Function WMIGetProperty(oWmi, sPropName, nCIMType, ErrAction)
Dim sValue, oWmiProp
If Not IsValidObject(oWmi) Then
If (ErrAction And ErrAction_ThrowError) = ErrAction_ThrowError Then _
ThrowScriptErrorNoAbort "Accessing property on invalid WMI object.", Err
If (ErrAction And ErrAction_Abort) = ErrAction_Abort Then _
Quit()
WMIGetProperty = ""
Exit Function
End If
On Error Resume Next
Set oWmiProp = oWmi.Properties_.Item(sPropName)
If Err.Number <> 0 Then
If (ErrAction And ErrAction_ThrowError) = ErrAction_ThrowError Then _
ThrowScriptErrorNoAbort "An error occurred while accessing WMI property: '" & sPropName & "'.", Err
If (ErrAction And ErrAction_Abort) = ErrAction_Abort Then _
Quit()
End If
On Error GoTo 0
If IsValidObject(oWmiProp) Then
sValue = oWmiProp.Value
If IsNull(sValue) Then
'
' If value is null, return blank to avoid any issues
'
WMIGetProperty = ""
Else
Select Case (oWmiProp.CIMType)
Case wbemCimtypeString, wbemCimtypeSint16, wbemCimtypeSint32, wbemCimtypeReal32, wbemCimtypeReal64, wbemCimtypeSint8, wbemCimtypeUint8, wbemCimtypeUint16, wbemCimtypeUint32, wbemCimtypeSint64, wbemCimtypeUint64:
If Not oWmiProp.IsArray Then
WMIGetProperty = Trim(CStr(sValue))
Else
WMIGetProperty = Join(sValue, ", ")
End If
Case wbemCimtypeBoolean:
If sValue = 1 Or UCase(sValue) = "TRUE" Then
WMIGetProperty = "True"
Else
WMIGetProperty = "False"
End If
Case wbemCimtypeDatetime:
Dim sTmpStrDate
'
' First attempt to convert the whole wmi date string
'
sTmpStrDate = Mid(sValue, 5, 2) & "/" & _
Mid(sValue, 7, 2) & "/" & _
Left(sValue, 4) & " " & _
Mid (sValue, 9, 2) & ":" & _
Mid(sValue, 11, 2) & ":" & _
Mid(sValue, 13, 2)
If IsDate(sTmpStrDate) Then
WMIGetProperty = CDate(sTmpStrDate)
Else
'
' Second, attempt just to convert the YYYYMMDD
'
sTmpStrDate = Mid(sValue, 5, 2) & "/" & _
Mid(sValue, 7, 2) & "/" & _
Left(sValue, 4)
If IsDate(sTmpStrDate) Then
WMIGetProperty = CDate(sTmpStrDate)
Else
'
' Nothing works - return passed in string
'
WMIGetProperty = sValue
End If
End If
Case Else:
WMIGetProperty = ""
End Select
End If
Else
If (ErrAction And ErrAction_ThrowError) = ErrAction_ThrowError Then _
ThrowScriptErrorNoAbort "An error occurred while accessing WMI property: '" & sPropName & "'.", Err
If (ErrAction And ErrAction_Abort) = ErrAction_Abort Then _
Quit()
WMIGetProperty = ""
End If
If (ErrAction And ErrAction_Trace) = ErrAction_Trace Then _
WScript.Echo " + " & sPropName & " :: '" & WMIGetProperty & "'"
End Function
Function WMIExecQuery(ByVal sNamespace, ByVal sQuery)
'
' WMIExecQuery :: Executes the WMI query and returns the result set.
'
'
Dim oWMI, oQuery, nInstanceCount
Dim e
Set e = New Error
On Error Resume Next
Set oWMI = GetObject(sNamespace)
e.Save
On Error GoTo 0
If IsEmpty(oWMI) Then
ThrowScriptErrorNoAbort "Unable to open WMI Namespace '" & sNamespace & "'. Check to see if the WMI service is enabled and running, and ensure this WMI namespace exists.", e
ThrowEmptyDiscoveryData
End If
On Error Resume Next
Set oQuery = oWMI.ExecQuery(sQuery)
e.Save
On Error GoTo 0
If IsEmpty(oQuery) Or e.Number <> 0 Then
ThrowScriptError "The Query '" & sQuery & "' returned an invalid result set. Please check to see if this is a valid WMI Query.", e
End If
'Determine if we queried a valid WMI class - Count will return 0 or empty
On Error Resume Next
nInstanceCount = oQuery.Count
e.Save
On Error GoTo 0
If e.Number <> 0 Then
ThrowScriptError "The Query '" & sQuery & "' did not return any valid instances. Please check to see if this is a valid WMI Query.", e
End If
Set WMIExecQuery = oQuery
End Function
Function GetFirstItemFromWMIQuery(ByRef oQuery)
ON ERROR RESUME NEXT
Err.Clear
Dim oResult
Set oResult = oQuery.ItemIndex(0)
if Err.number <> 0 then
Err.Clear
Dim oObject
For Each oObject in oQuery
Set oResult = oObject
Exit For
Next
end if
Set GetFirstItemFromWMIQuery = oResult
End Function
'#Include File:ConnectionString.vbs
Function ArraysAreIntersected(ByRef arrA, ByRef arrB)
Dim itemA, itemB
ArraysAreIntersected = False
For Each itemA in arrA
For Each itemB in arrB
If itemA = itemB Then
ArraysAreIntersected = True
Exit Function
End If
Next
Next
End Function
Function ArrayContains(ByRef arr, ByRef searchItem)
ArrayContains = False
Dim item
For Each item In arr
If item = searchItem Then
ArrayContains = True
Exit Function
End If
Next
End Function
Function GetDriverVersionObj(oVersion)
Dim tpName, vVersion
tpName = TypeName(oVersion)
Select Case tpName
Case "DriverVersion"
Set vVersion = oVersion
Case "String"
Set vVersion = (new DriverVersion)(oVersion)
Case Else
Call Err.Raise(7100, "DriverVersion", "Parameter is not an instance of type DriverVersion.")
End Select
Set GetDriverVersionObj = vVersion
End Function
Class DriverVersion
Private m_major
Private m_minor
Private m_build
Private m_revision
Public Default Function Init(versionStr)
Parse(versionStr)
Set Init = Me
End Function
Private Sub Parse(vStr)
Dim parts, versionRegex, oRegex, matches
Set oRegex = New RegExp
oRegex.Pattern = "^(\d{1,8})(.\d{1,8}){0,3}$"
Set matches = oRegex.Execute(vStr)
If matches.Count = 0 Then
Call Err.Raise(7100, "DriverVersion", "Invalid version string.")
End If
parts = Split(vStr, ".")
m_major = CLng(parts(0))
m_minor = 0
m_build = 0
m_revision = 0
IF UBound(parts) >= 1 Then
m_minor = CLng(parts(1))
If UBound(parts) >= 2 Then
m_build = CLng(parts(2))
If UBound(parts) >= 3 Then
m_revision = CLng(parts(3))
End If
End If
End If
End Sub
Public Function CompareTo(v)
Dim d
d = m_major - v.Major
If d <> 0 Then
CompareTo = d
Exit Function
End If
d = m_minor - v.Minor
If d <> 0 Then
CompareTo = d
Exit Function
End If
d = m_build - v.Build
If d <> 0 Then
CompareTo = d
Exit Function
End If
d = m_revision - v.Revision
CompareTo = d
End Function
Public Function ToString()
ToString = "" & m_major & "." & m_minor & "." & m_build & "." & m_revision
End Function
Public Property Get Major()
Major = m_major
End Property
Public Property Get Minor()
Minor = m_minor
End Property
Public Property Get Build()
Build = m_build
End Property
Public Property Get Revision()
Revision = m_revision
End Property
End Class
Class VersionRange
Private m_startVersion
Private m_endVersion
Public Default Function Init(sStartVersion, sEndVersion)
Set m_startVersion = GetDriverVersionObj(sStartVersion)
Set m_endVersion = GetDriverVersionObj(sEndVersion)
Set Init = Me
End Function
Public Property Get StartVersion()
Set StartVersion = m_startVersion
End Property
Public Property Get EndVersion()
Set EndVersion = m_endVersion
End Property
End Class
Class DriverItem
Private m_name
Private m_nameVersion
Private m_driverVersion
Private m_parseObject
Public Default Function Init(sName, oNameVersion, oDriverVersion, ByRef oParseObject)
m_Name = sName
Set m_nameVersion = GetDriverVersionObj(oNameVersion)
Set m_driverVersion = GetDriverVersionObj(oDriverVersion)
Set m_parseObject = oParseObject
Set Init = Me
End Function
Public Property Get Name()
Name = m_name
End Property
Public Property Get NameVersion()
Set NameVersion = m_nameVersion
End Property
Public Property Get DriverVersion()
Set DriverVersion = m_driverVersion
End Property
Public Property Get ParseObject()
Set ParseObject = m_parseObject
End Property
End Class
Public Default Function Init(sNameRegex, sVersionRegex, sNameMinVersion, sNameMaxVersion, oVersionRangeArray)
m_computerId = "."
m_nameRegexStr = sNameRegex
Set m_nameRegex = New RegExp
m_nameRegex.Pattern = m_nameRegexStr
m_versionRegexStr = sVersionRegex
If Not IsNull(m_versionRegexStr) Then
If Len(m_versionRegexStr) > 0 Then
Set m_versionRegex = New RegExp
m_versionRegex.Pattern = m_versionRegexStr
m_versionRegex.Global = True
End If
End If
Set m_nameMinVersion = GetDriverVersionObj(sNameMinVersion)
Set m_nameMaxVersion = GetDriverVersionObj(sNameMaxVersion)
m_driverVersionArr = oVersionRangeArray
DriverCollection = Array()
IsNativeClient = False
Set Init = Me
End Function
Private Function CheckVersion(oVersion, vMinVersion, vMaxVersion)
Dim vVersion: Set vVersion = GetDriverVersionObj(oVersion)
CheckVersion = vVersion.CompareTo(vMinVersion) >= 0 And (vVersion.ToString() = MAX_DRIVER_VERSION_STR Or vMinVersion.CompareTo(vMaxVersion) = 0 Or vVersion.CompareTo(vMaxVersion) < 0)
End Function
Public Function MatchName(sDriverName)
Dim matches
Set matches = m_nameRegex.Execute(sDriverName)
If matches.Count > 0 Then
MatchName = True
Exit Function
End If
MatchName = False
End Function
Public Function GetNameVersion(sDriverName)
Dim sVersion, matches
If Not IsNull(m_versionRegexStr) Then
Set matches = m_versionRegex.Execute(sDriverName)
If matches.Count > 0 Then
sVersion = matches(0).Value
End If
End If
If Not IsNull(sVersion) Then
Set GetNameVersion = GetDriverVersionObj(sVersion)
Else
Set GetNameVersion = GetDriverVersionObj("0")
End If
End Function
Public Function CheckNameVersion(oVersion)
CheckNameVersion = CheckVersion(oVersion, m_nameMinVersion, m_nameMaxVersion)
End Function
Public Function CheckDriverVersion(oVersion)
Dim i: i = 0
CheckDriverVersion = False
Dim driverVersionItem
For Each driverVersionItem In m_driverVersionArr
If CheckVersion(oVersion, driverVersionItem.StartVersion, driverVersionItem.EndVersion) Then
CheckDriverVersion = True
Exit For
End If
Next
End Function
Public Function GetDriverVersion(sDriverName)
Dim computerId, driverPath, sDllVersion, objFSO, oRegistry
Dim HKEY_LOCAL_MACHINE: HKEY_LOCAL_MACHINE = &H80000002
Set oRegistry = GetObject("winmgmts:\\" & m_computerId & "\root\default:StdRegProv")
oRegistry.GetStringValue HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\" & sDriverName, "Driver", driverPath
Set objFSO = CreateObject("Scripting.FileSystemObject")
sDllVersion = objFSO.GetFileVersion(driverPath)
Set GetDriverVersion = (new DriverVersion)(sDllVersion)
End Function
Public Sub AddDriver(ByRef oDriver)
ReDim Preserve DriverCollection(UBound(DriverCollection) + 1)
Set DriverCollection(UBound(DriverCollection)) = oDriver
End Sub
Public Sub ResetState()
DriverCollection = Array()
End Sub
Public Property Get DriverNameRegex()
DriverNameRegex = m_nameRegexStr
End Property
Public Property Get NameMinVersion()
Set NameMinVersion= m_nameMinVersion
End Property
Public Property Get NameMaxVersion()
Set NameMaxVersion = m_nameMaxVersion
End Property
Public Property Get VersionRangeCollection()
VersionRangeCollection = m_driverVersionArr
End Property
Public Property Get CollectionIsEmpty()
CollectionIsEmpty = UBound(DriverCollection) < 0
End Property
End Class
Private Sub Class_Initialize()
Set m_selectorRules(0) = (new DriverSelectorRule)("^SQL\sServer\sNative\sClient\s\d{1,8}(\.\d{1,8})?$","\d{1,8}(\.\d{1,8})?$", "11.0", "11.0",_
Array(_
(new VersionRange)("2011.110.6020.0", MAX_DRIVER_VERSION_STR),_
(new VersionRange)("2011.110.5592.0", "2011.110.6000.0"),_
(new VersionRange)("2011.110.5347.0", "2011.110.5522.0")))
m_selectorRules(0).IsNativeClient = True
Set m_selectorRules(1) = (new DriverSelectorRule)("^ODBC\sDriver\s\d{1,8}(\.\d{1,8})?\sfor\sSQL\sServer$", "\d{1,8}(\.\d{1,8})?(?=\sfor\sSQL\sServer$)", "11.0", MAX_DRIVER_VERSION_STR,_
Array(_
(new VersionRange)("2014.120.4219.0", MAX_DRIVER_VERSION_STR),_
(new VersionRange)("2014.120.2546.0", "2014.120.4000.0")))
Set m_selectorRules(2) = (new DriverSelectorRule)("^SQL\sServer\sNative\sClient\s\d{1,8}(\.\d{1,8})?$", "\d{1,8}(\.\d{1,8})?$","0", MAX_DRIVER_VERSION_STR, Array((new VersionRange)("0", MAX_DRIVER_VERSION_STR)))
m_selectorRules(2).IsNativeClient = True
Set m_selectorRules(3) = (new DriverSelectorRule)("^ODBC\sDriver\s\d{1,8}(\.\d{1,8})?\sfor\sSQL\sServer$", "\d{1,8}(\.\d{1,8})?(?=\sfor\sSQL\sServer$)", "0", MAX_DRIVER_VERSION_STR, Array((new VersionRange)("0", MAX_DRIVER_VERSION_STR)))
Public Sub ProcessDrivers(enabledServerProtocols)
Dim ri, i, isSelected, currentSelect, oError
Set oError = New Error
Call ResetState()
On Error Resume Next
Dim ncNamespace: ncNamespace = GetNsNameWithHighestVersion()
Dim clientProtocols: clientProtocols = GetEnabledNativeClientProtocols(ncNamespace)
Dim ncProtocolsAreIntersected: ncProtocolsAreIntersected = ArraysAreIntersected(enabledServerProtocols, clientProtocols)
oError.Save
If oError.Number <> 0 Then
AddError(oError)
m_selectedDriverName = m_defaultDriverName
m_processed = true
Exit Sub
End If
On Error GoTo 0
Dim driver, selectorRule
For Each selectorRule In m_selectorRules
If Not selectorRule.CollectionIsEmpty Then
For Each driver In selectorRule.DriverCollection
If isSelected Then
If driver.NameVersion.CompareTo(currentSelect.NameVersion) >= 0 And driver.DriverVersion.CompareTo(currentSelect.DriverVersion) >= 0 Then
Set currentSelect = driver
End If
Else
Set currentSelect = driver
isSelected = True
End If
Next
End If
If isSelected Then
Exit For
End If
Next
If isSelected Then
m_selectedDriverName = currentSelect.Name
If currentSelect.ParseObject.IsNativeClient Then
oError.Clear
On Error Resume Next
Call GetNativeClientSettings(ncNamespace)
oError.Save
If oError.Number <> 0 Then
AddError(oError)
End If
m_ncli_tcpProtocolEnabled = ArrayContains(clientProtocols, "tcp")
On Error GoTo 0
End If
Else
m_selectedDriverName = m_defaultDriverName
End If
m_processed = True
End Sub
Private Sub ProcessOdbcDriver(driverName, protocolsAreIntersected)
Dim i, isInstalled, oRegistry, oNameVersion, oDriverVersion
On Error GoTo 0
Set oRegistry = GetObject("winmgmts:\\.\root\default:StdRegProv")
oRegistry.GetStringValue HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", driverName, isInstalled
i = 0
Dim selectorRule
For Each selectorRule In m_selectorRules
If(isInstalled <> "Installed") Then
Exit For
End If
If selectorRule.MatchName(driverName) Then
If (Not selectorRule.IsNativeClient) Or (selectorRule.IsNativeClient And protocolsAreIntersected) Then
Set oNameVersion = selectorRule.GetNameVersion(driverName)
If (selectorRule.CheckNameVersion(oNameVersion)) Then
Set oDriverVersion = selectorRule.GetDriverVersion(driverName)
If (selectorRule.CheckDriverVersion(oDriverVersion)) Then
selectorRule.AddDriver((new DriverItem)(driverName, oNameVersion, oDriverVersion, selectorRule))
Exit For
End If
End If
End If
End If
Next
End Sub
Private Sub ProcessSystemOdbcDrivers(protocolsAreIntersected)
Dim oRegistry, driverNames, paramValueTypes, i, oError
Set oError = New Error
Set oRegistry = GetObject("winmgmts:\\" & m_computerId & "\root\default:StdRegProv")
oRegistry.EnumValues HKEY_LOCAL_MACHINE, "SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers", driverNames, paramValueTypes
On Error Resume Next
Dim driverName
For Each driverName In driverNames
oError.Clear
Call ProcessOdbcDriver(driverName, protocolsAreIntersected)
oError.Save
If(oError.Number <> 0) Then
AddError(oError)
End If
Next
End Sub
Private Function GetNsNameWithHighestVersion()
Dim rootNs: rootNs = "winmgmts:{impersonationLevel=impersonate}!\\.\root\Microsoft\SqlServer"
Dim oWMI: Set oWMI = GetObject(rootNs)
Dim namespaces: Set namespaces = oWMI.ExecQuery("SELECT Name FROM __NAMESPACE WHERE Name LIKE 'ComputerManagement%'")
Dim currentName: currentName = ""
Dim objItem
For Each objItem In namespaces
If objItem.Name > currentName Then
currentName = objItem.Name
End If
Next
GetNsNameWithHighestVersion = rootNs & "\" & currentName
End Function
Private Sub GetNativeClientSettings(namespaceFullName)
Dim sNamespace, oWMI, objClasses, sState, sTargetComputer
Set oWMI = GetObject(namespaceFullName)
Set objClasses = oWMI.ExecQuery("SELECT FlagName, FlagValue FROM ClientSettingsGeneralFlag")
Dim objItem
For Each objItem in objClasses
Select Case objItem.FlagName
Case "Force protocol encryption"
m_ncli_ForceProtocolEncryption = objItem.FlagValue
Case "Trust Server Certificate"
m_ncli_TrustServerCertificate = objItem.FlagValue
End Select
Next
End Sub
Private Function GetEnabledNativeClientProtocols(namespaceFullName)
Dim oWMI: Set oWMI = GetObject(namespaceFullName)
Dim oQuery: Set oQuery = oWMI.ExecQuery("SELECT ProtocolName, ProtocolOrder FROM ClientNetworkProtocol")
If oQuery.Count > 0 Then
Dim protocolsArr: Set protocolsArr = New ArrayList
Dim protocolItem
For Each protocolItem In oQuery
If protocolItem.ProtocolOrder > 0 Then
protocolsArr.Add LCase(protocolItem.ProtocolName)
End If
Next
GetEnabledNativeClientProtocols = protocolsArr.ItemsArray
Else
GetEnabledNativeClientProtocols = Array()
End If
End Function
Private Sub AddError(oError)
Dim newSize
newSize = UBound(ErrorCollection) + 1
ReDim Preserve ErrorCollection(newSize)
ErrorCollection(newSize) = "[" & (oError.Number and 65535) & "][" & oError.Source & "] " & oError.Description
End Sub
Private Sub ResetState()
Dim pr
ErrorCollection = Array()
For Each pr In m_selectorRules
pr.ResetState()
Next
End Sub
Private Sub ThrowIfNotProcessed()
If Not m_processed Then
Call Err.Raise(SCRIPT_EVENT_ID, "", "Drivers are not processed. Call 'ProcessDrivers' first.")
End If
End Sub
Public Property Get HasErrors()
HasErrors = UBound(ErrorCollection) >= 0
End Property
Public Property Get DriverName()
ThrowIfNotProcessed
DriverName = m_selectedDriverName
End Property
Public Property Get UseFqdn()
ThrowIfNotProcessed
UseFqdn = m_ncli_ForceProtocolEncryption And Not m_ncli_TrustServerCertificate
End Property
Public Property Get ClientTcpProtocolEnabled()
ThrowIfNotProcessed
ClientTcpProtocolEnabled = m_ncli_tcpProtocolEnabled
End Property
End Class
Class UniqueCollection
Private m_dict
Public Default Function Init()
Set m_dict = CreateObject("Scripting.Dictionary")
Set Init = Me
End Function
Public Sub PutItem(item)
If Not m_dict.Exists(item) Then
m_dict.add item, ""
End If
End Sub
Public Function Exists(item)
Exists = m_dict.Exists(item)
End Function
Public Function GetItems()
GetItems = m_dict.Keys()
End Function
Public Sub Clear()
m_dict.RemoveAll()
End Sub
End Class
Class SqlServerTcpIpSettings
Private m_listenAllIPs
Private m_ipSettings
Public Default Function Init(listenAllIps, ipSettings)
m_listenAllIPs = listenAllIps
Set m_ipSettings = ipSettings
Set Init = Me
End Function
Public Property Get ListenAllIPs()
ListenAllIPs = m_listenAllIPs
End Property
Public Property Get IpSettings()
Set IpSettings = m_ipSettings
End Property
End Class
Function EscapeConnStringValue (ByVal strValue)
On Error Resume Next
Err.Clear
EscapeConnStringValue = "{" + Replace(strValue, "}", "}}") + "}"
End Function
Function EscapeWQLString (ByVal strValue)
ON ERROR RESUME NEXT
Err.Clear
EscapeWQLString = Replace(strValue, "'", "\'")
End Function
For i = 0 to Ubound(aReserved)
If vEncDec = "enc" Then
vString = Replace(vString,aReserved(i,0),aReserved(i,1))
End If
If vEncDec = "dec" Then
vString = Replace(vString,aReserved(i,1),aReserved(i,0))
End If
Next
furlEncode = vString
End Function
Function DelimitSqlIdentifier(identifier)
DelimitSqlIdentifier = "[" + Replace(identifier, "]", "]]") + "]"
End Function
Function SqlTcpPortIsEmpty(tcpPort)
SqlTcpPortIsEmpty = (tcpPort = "" Or tcpPort = "0")
End Function
Function GetDataSource(server, tcpPort)
Dim dataSource : dataSource = server
If Not SqlTcpPortIsEmpty(tcpPort) Then
Dim nameParts : nameParts = Split(dataSource, "\")
dataSource = nameParts(0) & "," & tcpPort
End If
GetDataSource = dataSource
End Function
Function GetConnectionString(driverName, dataSource, databaseName)
GetConnectionString = "Driver=" & EscapeConnStringValue(driverName) & ";Server=" & EscapeConnStringValue(dataSource) & ";Database=" & EscapeConnStringValue(databaseName) & ";Trusted_Connection=yes;"
End Function
Function GetEnabledSqlServerProtocols(namespaceName, computerName, instanceName)
Dim oWMI: Set oWMI = GetObject("winmgmts:\\" & computerName & "\root\Microsoft\SqlServer\" & namespaceName)
Dim oQuery: Set oQuery = oWMI.ExecQuery("SELECT ProtocolName, Enabled FROM ServerNetworkProtocol WHERE InstanceName = '"& EscapeWQLString(instanceName) &"'")
If oQuery.Count > 0 Then
Dim protocolsArr: Set protocolsArr = New ArrayList
Dim protocolItem
For Each protocolItem In oQuery
If protocolItem.Enabled Then
protocolsArr.Add LCase(protocolItem.ProtocolName)
End If
Next
GetEnabledSqlServerProtocols = protocolsArr.ItemsArray
Else
GetEnabledSqlServerProtocols = Array()
End If
End Function
Function GetSqlServerTcpIpSettings(instanceName, computerName)
ON ERROR RESUME NEXT
Dim oWMI, oQuery, tcpItem
Dim i, j
Dim uc: Set uc = (new UniqueCollection)()
Dim isListenAll: isListenAll = False
Dim ipSettings: Set ipSettings = CreateObject("Scripting.Dictionary")
Set oWMI = GetObject("winmgmts:\\" & computerName & "\root\Microsoft\SqlServer\"& SQL_WMI_NAMESPACE)
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND PropertyName = 'ListenOnAllIPs'")
If oQuery.Count >0 Then
Dim isListenAllObj: Set isListenAllObj = GetFirstItemFromWMIQuery(oQuery)
If isListenAllObj.PropertyNumVal = 1 Then
isListenAll = True
End If
End If
Dim tcpPorts, tcpPort, processedPort
If isListenAll Then
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = 'IPAll' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal <> ''")
If oQuery.Count > 0 Then
For Each tcpItem In oQuery
tcpPorts = Split(tcpItem.PropertyStrVal,",")
For Each tcpPort In tcpPorts
processedPort = Trim(tcpPort)
If Not SqlTcpPortIsEmpty(processedPort) Then
uc.PutItem(processedPort)
End If
Next
Next
ipSettings.add "IPAll", uc.GetItems()
End If
Else
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName <> '' AND PropertyName = 'Enabled' AND PropertyNumVal = 1")
Dim ipItem
If oQuery.Count > 0 Then
For Each ipItem In oQuery
Dim ipAddressName : ipAddressName = ipItem.IPAddressName
Dim oQuery2 : Set oQuery2 = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + EscapeWQLString(instanceName) + "' AND IPAddressName = '" + EscapeWQLString(ipAddressName) + "' AND PropertyName = 'IpAddress' AND PropertyStrVal != ''")
If oQuery2.Count > 0 Then
Dim ipAddress : ipAddress = GetFirstItemFromWMIQuery(oQuery2).PropertyStrVal
Dim oQuery3: Set oQuery3 = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = '"& EscapeWQLString(ipAddressName) &"' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal <> ''")
If oQuery3.Count > 0 Then
uc.Clear()
Dim portItem
For Each portItem In oQuery3
tcpPorts = Split(portItem.PropertyStrVal,",")
For Each tcpPort In tcpPorts
processedPort = Trim(tcpPort)
If Not SqlTcpPortIsEmpty(processedPort) Then
uc.PutItem(processedPort)
End If
Next
ipSettings.add ipAddress, uc.GetItems()
Next
End If
End If
Next
End If
End If
Set GetSqlServerTcpIpSettings = (new SqlServerTcpIpSettings)(isListenAll, ipSettings)
End Function
Function GetTcpPortsString(ByVal inputDataSource)
On Error Resume Next
Dim computerName, instanceName, pathArray
pathArray = Split(inputDataSource, "\")
computerName = pathArray(0)
instanceName = "MSSQLSERVER"
If (UBound(pathArray) > 0) Then
instanceName = pathArray(1)
End If
Dim tcpIpSettings: Set tcpIpSettings = GetSqlServerTcpIpSettings(instanceName, computerName)
Dim upc : Set upc = (new UniqueCollection)()
Dim ip, port
For Each ip In tcpIpSettings.IpSettings.Keys
For Each port In tcpIpSettings.IpSettings.Item(ip)
upc.PutItem port
Next
Next
GetTcpPortsString = Join(upc.GetItems(),", ")
End Function
Public Sub SqlTestDestination(dbConnection, serverName, instanceName, isADODB)
Dim destinationTestQuery
Dim queryServerName, queryInstanceName
On Error Goto 0
Dim serverNameWithoutDomain : serverNameWithoutDomain = serverName
destinationTestQuery = "select CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)) as ServerName, @@servicename as InstanceName"
if 0 = Err.number then
Dim queryResult
if isADODB then
Set queryResult = dbConnection.ExecuteQueryTE(destinationTestQuery)
else
Set queryResult = dbConnection.Execute(destinationTestQuery)
end if
if Not queryResult.EOF then
queryServerName = UCase(queryResult("ServerName").Value)
queryInstanceName = UCase(queryResult("InstanceName").Value)
Dim dotPosition : dotPosition = InStr(1, serverName, ".")
if Not IsNull(dotPosition) And (dotPosition > 0) then
serverNameWithoutDomain = Left(serverName, dotPosition - 1)
end if
if (UCase(serverNameWithoutDomain) = queryServerName) And (UCase(instanceName) = queryInstanceName) then
Exit Sub
end if
end if
dbConnection.Close()
Err.Raise 16389, "", "Connection target check failed: connected to " & serverNameWithoutDomain & "\" & instanceName & ", but got " & queryServerName & "\" & queryInstanceName & "."
end if
End Sub
Sub TryToConnectAndValidate(connectionObj, connectionString, timeout, machineName, instanceName, isADODB)
On Error GoTo 0
If isADODB Then
connectionObj.Open connectionString, "", timeout
Else
if (connectionObj.Mode <> 0) then
connectionObj.Close()
end if
connectionObj.ConnectionTimeout = timeout
connectionObj.Open connectionString
End If
SqlTestDestination connectionObj, machineName, instanceName, isADODB
End Sub
Function FormatConnectionErrorMessage(dataSource, lastError)
FormatConnectionErrorMessage = "Connection to data source '" & dataSource & "' failed: " & lastError.Description
End Function
Function SmartConnectWithoutSQLADODB(ByVal inputDataSource, ByVal tcpPort, ByVal machineName, ByVal instanceName, ByVal databaseName)
On Error Resume Next
Dim dbMasterConnection, dataSource, connectionString, errorMessage
Dim targetName : targetName = inputDataSource
Dim lastError : Set lastError = new Error
Dim errorMessageList : Set errorMessageList = New ArrayList
Dim ds: Set ds = New DriverSelector
Dim enabledServerProtocols: enabledServerProtocols = GetEnabledSqlServerProtocols(SQL_WMI_NAMESPACE, machineName, instanceName)
ds.ProcessDrivers(enabledServerProtocols)
Dim selectedDriverName: selectedDriverName = ds.DriverName
Dim useFqdn: useFqdn = ds.UseFqdn
Dim hasErrors: hasErrors = ds.HasErrors
Set dbMasterConnection = CreateObject("ADODB.Connection")
'Connect using Sql Browser
dataSource = GetDataSource(inputDataSource, "")
connectionString = GetConnectionString(selectedDriverName, dataSource, databaseName)
lastError.Clear
TryToConnectAndValidate dbMasterConnection, connectionString, 15, machineName, instanceName, False
lastError.Save
If lastError.Number = 0 Then
Set SmartConnectWithoutSQLADODB = dbMasterConnection
Exit Function
Else
errorMessageList.Add FormatConnectionErrorMessage(dataSource, lastError)
End If
'Try to connect through tcp/ip protocol only if it is enabled
If ArrayContains(enabledServerProtocols, "tcp") And ds.ClientTcpProtocolEnabled Then
Dim dsComputerName: dsComputerName = Split(inputDataSource, "\")(0)
Dim tcpIpSettings : Set tcpIpSettings = GetSqlServerTcpIpSettings(instanceName, dsComputerName)
If useFqdn Then
targetName = machineName
Else
targetName = dsComputerName
End if
Dim ip, port
'Use ports configured for all interfaces
If tcpIpSettings.ListenAllIPs Then
For Each port In tcpIpSettings.IpSettings.item("IPAll")
dataSource = GetDataSource(targetName, port)
connectionString = GetConnectionString(selectedDriverName, dataSource, databaseName)
lastError.Clear
TryToConnectAndValidate dbMasterConnection, connectionString, 10, machineName, instanceName, False
lastError.Save
If lastError.Number = 0 Then
Set SmartConnectWithoutSQLADODB = dbMasterConnection
Exit Function
Else
errorMessageList.Add FormatConnectionErrorMessage(dataSource, lastError)
End If
Next
Else
'Use one port from each interface
Dim upc : Set upc = (new UniqueCollection)()
For Each ip In tcpIpSettings.IpSettings.Keys
port = tcpIpSettings.IpSettings.Item(ip)(0)
If Not upc.Exists(port) Then
upc.PutItem port
dataSource = GetDataSource(targetName, port)
connectionString = GetConnectionString(selectedDriverName, dataSource, databaseName)
lastError.Clear
TryToConnectAndValidate dbMasterConnection, connectionString, 10, machineName, instanceName, False
lastError.Save
If lastError.Number = 0 Then
Set SmartConnectWithoutSQLADODB = dbMasterConnection
Exit Function
Else
errorMessageList.Add FormatConnectionErrorMessage(dataSource, lastError)
End If
End If
Next
'Go through all interfaces and ports
If Not useFqdn Then
For Each ip In tcpIpSettings.IpSettings.Keys
For Each port In tcpIpSettings.IpSettings.Item(ip)
dataSource = GetDataSource(ip, port)
connectionString = GetConnectionString(selectedDriverName, dataSource, databaseName)
lastError.Clear
TryToConnectAndValidate dbMasterConnection, connectionString, 10, machineName, instanceName, False
lastError.Save
If lastError.Number = 0 Then
Set SmartConnectWithoutSQLADODB = dbMasterConnection
Exit Function
Else
errorMessageList.Add FormatConnectionErrorMessage(dataSource, lastError)
End If
Next
Next
End If
End If
End If
GlobalErrorList.Add "Cannot connect to the target Sql Server instance. Connection log:" & vbNewLine & Join(errorMessageList.ItemsArray, vbNewLine)
Set SmartConnectWithoutSQLADODB = Nothing
End Function
Function SmartConnect(cnADOConnection, inputDataSource, tcpPort, machineName, instanceName, databaseName)
On Error Resume Next
Dim dataSource, connectionString, errorMessage
Dim targetName : targetName = serverName
Dim lastError : Set lastError = new Error
Dim errorMessageList : Set errorMessageList = New ArrayList
Dim ds: Set ds = New DriverSelector
Dim enabledServerProtocols: enabledServerProtocols = GetEnabledSqlServerProtocols(SQL_WMI_NAMESPACE, machineName, instanceName)
ds.ProcessDrivers(enabledServerProtocols)
Dim selectedDriverName: selectedDriverName = ds.DriverName
Dim useFqdn: useFqdn = ds.UseFqdn
Dim hasErrors: hasErrors = ds.HasErrors
'Connect using Sql Browser
dataSource = GetDataSource(inputDataSource, "")
connectionString = GetConnectionString(selectedDriverName, dataSource, databaseName)
lastError.Clear
TryToConnectAndValidate cnADOConnection, connectionString, 15, machineName, instanceName, True
lastError.Save
If lastError.Number = 0 Then
SmartConnect = True
Exit Function
Else
errorMessageList.Add FormatConnectionErrorMessage(dataSource, lastError)
End If
'Try to connect through tcp/ip protocol only if it is enabled
If ArrayContains(enabledServerProtocols, "tcp") And ds.ClientTcpProtocolEnabled Then
Dim dsComputerName: dsComputerName = Split(inputDataSource, "\")(0)
Dim tcpIpSettings : Set tcpIpSettings = GetSqlServerTcpIpSettings(instanceName, dsComputerName)
If useFqdn Then
targetName = machineName
Else
targetName = dsComputerName
End if
Dim ip, port
'Use ports configured for all interfaces
If tcpIpSettings.ListenAllIPs Then
For Each port In tcpIpSettings.IpSettings.item("IPAll")
dataSource = GetDataSource(targetName, port)
connectionString = GetConnectionString(selectedDriverName, dataSource, databaseName)
lastError.Clear
TryToConnectAndValidate cnADOConnection, connectionString, 10, machineName, instanceName, True
lastError.Save
If lastError.Number = 0 Then
SmartConnect = True
Exit Function
Else
errorMessageList.Add FormatConnectionErrorMessage(dataSource, lastError)
End If
Next
Else
'Use one port from each interface
Dim upc : Set upc = (new UniqueCollection)()
For Each ip In tcpIpSettings.IpSettings.Keys
port = tcpIpSettings.IpSettings.Item(ip)(0)
If Not upc.Exists(port) Then
upc.PutItem port
dataSource = GetDataSource(targetName, port)
connectionString = GetConnectionString(selectedDriverName, dataSource, databaseName)
lastError.Clear
TryToConnectAndValidate cnADOConnection, connectionString, 10, machineName, instanceName, True
lastError.Save
If lastError.Number = 0 Then
SmartConnect = True
Exit Function
Else
errorMessageList.Add FormatConnectionErrorMessage(dataSource, lastError)
End If
End If
Next
'Go through all interfaces and ports
If Not useFqdn Then
For Each ip In tcpIpSettings.IpSettings.Keys
For Each port In tcpIpSettings.IpSettings.Item(ip)
dataSource = GetDataSource(ip, port)
connectionString = GetConnectionString(selectedDriverName, dataSource, databaseName)
lastError.Clear
TryToConnectAndValidate cnADOConnection, connectionString, 10, machineName, instanceName, True
lastError.Save
If lastError.Number = 0 Then
SmartConnect = True
Exit Function
Else
errorMessageList.Add FormatConnectionErrorMessage(dataSource, lastError)
End If
Next
Next
End If
End If
End If
GlobalErrorList.Add "Cannot connect to the target Sql Server instance. Connection log:" & vbNewLine & Join(errorMessageList.ItemsArray, vbNewLine)
SmartConnect = False
End Function
'#Include File:SQLDatabaseDiscovery.vbs
'Copyright (c) Microsoft Corporation. All rights reserved.
' Parameters that should be passed to this script
' Parameters that should be passed to this script
' 0 MPElement ID ($MPElement$)
' 1 Target Id for ME this rule is running against ($Target/Id$)
' 2 Computer ID
' 3 Computer FQDN
' 4 SQL Connection String for the instance that the DBs are being discovered on
' 5 SQL Instance that this rule is being run for
' 6 Exlcuded database list (prefixed with Exclude:)
Dim oArgs
Set oArgs = WScript.Arguments
if oArgs.Count < 7 Or oArgs.Count > 8 Then
Wscript.Quit -1
End If
Dim SourceID, ManagedEntityId, TargetComputer, InstanceName, ConnectionString, TargetComputerID, ExcludeList, TcpPort
SourceId = oArgs(0)
ManagedEntityId = oArgs(1)
TargetComputerID = oArgs(2)
TargetComputer = oArgs(3)
ConnectionString = oArgs(4)
InstanceName = oArgs(5)
ExcludeList = Mid(oArgs(6), 9)
TcpPort = ""
If oArgs.Count = 8 Then
TcpPort = oArgs(7)
End If
Dim oAPI, oSQLDiscoveryData
Set oAPI = MOMCreateObject("MOM.ScriptAPI")
set oSQLDiscoveryData = oAPI.CreateDiscoveryData(0, SourceId, ManagedEntityId)
Dim errorCode: errorCode = 0
Dim state, serviceName
serviceName = GetSQLServiceName(InstanceName)
state = GetServiceState(TargetComputer, serviceName)
If (state <> "Running") And (state <> "Unknown") Then
oSQLDiscoveryData.IsSnapshot = False
Else
If DoDatabaseDiscovery(InstanceName, ConnectionString, oSQLDiscoveryData, TcpPort) >= 0 Then
StoreExcludeDatabaseListToRegistry InstanceName, ExcludeList
oAPI.LogScriptEvent "DatabaseDiscovery:" & InstanceName, SCRIPT_EVENT_ID, EVENT_TYPE_INFORMATION, "Database for SQL instance '" + InstanceName + "' discovers successfully."
Else
errorCode = -1
oAPI.LogScriptEvent "Management Group: " & ManagementGroupName & ". Script: " & WScript.ScriptName & ". Version: " & MANAGEMENT_PACK_VERSION & ". Instance: " & InstanceName, DISCOVERY_ERROR_EVENT_ID, EVENT_TYPE_ERROR, "Database discovery script '" & WScript.ScriptName & "' for instance '" & InstanceName & "' failed." & vbNewLine & GetGlobalErrorListEventString()
Call GlobalErrorList.Clear()
End If
End If
' This function should be used to escape Database Name parameter
Function EscapeDBName (ByVal strValue)
ON ERROR RESUME NEXT
Err.Clear
EscapeDBName = Replace(strValue, "]", "]]")
End Function
Sub WriteToEventLogAndExit(ByVal message)
oAPI.LogScriptEvent WScript.ScriptName, SCRIPT_EVENT_ID, EVENT_TYPE_ERROR, message
WScript.Quit()
End Sub
Function DoDatabaseDiscovery(ByVal sSqlInstance, ByVal sSQLConnectionString, ByVal oDisc, ByVal tcp)
If Len(ExcludeList) < 0 Then
WriteToEventLogAndExit("Database exclusion list invalid in DBDiscovery. Aborting discovery.")
End If
Dim e
Set e = New Error
Dim cnADOConnection
Set cnADOConnection = SmartConnectWithoutSQLADODB(sSQLConnectionString, tcp, TargetComputerID, sSqlInstance, "master")
if cnADOConnection Is Nothing Then
DoDatabaseDiscovery = SQL_DISCOVERY_CONNECT_FAILURE
Exit Function
End If
Dim cnADOConnectionGroup
On Error Goto 0
Dim oResults, gResults,pResult
e.Clear
On Error Resume Next
Set oResults = cnADOConnection.Execute("SELECT name, state_desc, resource_pool_id as resource_pool_id, HAS_DBACCESS(name) as has_access FROM sys.databases WHERE source_database_id IS NULL")
e.Save
On Error Goto 0
If e.Number <> 0 Then
DoDatabaseDiscovery = SQL_DISCOVERY_QUERY_FAILURE
ThrowScriptErrorNoAbort "Query execution failed", e
Exit Function
End If
On Error Resume Next
Set pResult = cnADOConnection.Execute("select Count(pool_id) from sys.dm_resource_governor_resource_pools")
e.Save
On Error Goto 0
If e.Number <> 0 Then
DoDatabaseDiscovery = SQL_DISCOVERY_QUERY_FAILURE
ThrowScriptErrorNoAbort "Query execution failed", e
Exit Function
End If
Dim iDBRow, iRow, sFileType
Dim iPos, iPos2, iTemp, bDBAuto, bLogAuto
Dim oDBResults, oDBInstance, sDBSize, sDBStatus
Dim sDBName, sDBState
Dim dDBSize, dLogSize
Dim sDBOwner
Dim oPool, oPoolRel, sPool, sPoolName
Dim NumberOfPool
NumberOfPool = pResult(0).Value
Do While Not oResults.EOF
sDBName = oResults(0)
sDBState = oResults(1)
sPool = oResults(2)
Dim iHasAccess: iHasAccess = cInt(oResults(3))
'sPoolName = oResults(3)
If Not(IsExcluded(SDBName)) Then
Set oDBInstance = oSQLDiscoveryData.CreateClassInstance(SQL_DATABASE_CLASS)
' Set basic DB properties
With oDBInstance
.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.ServerRole']/InstanceName$", sSqlInstance
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/DatabaseName$", sDBName
End With
' Only do further discovery for DB that is online - sp_helpdb wont return state for offline databases
' To do - may want to add owner, created, dbid and compat level to discovery, collation etc.
If sDBState = "ONLINE" Then
' If db is accessible use sp_helpdb
if (iHasAccess = 1) Then
On Error Resume Next
Set oDBResults = cnADOConnection.Execute("sp_helpdb [" & EscapeDBName(sDBName) & "]")
e.Save
On Error Goto 0
Select Case e.Number
Case 0
On Error Resume Next
sDBSize = Trim(oDBResults(1))
e.Save
On Error Goto 0
If e.Number = 0 Then
sDBSize = Trim(oDBResults(1))
sDBStatus = Split(oDBResults(5),",")
sDBOwner = oDBResults(2)
Set oDBResults = oDBResults.NextRecordset
bDBAuto = false
bLogAuto = false
dDBSize = 0
dLogSize = 0
Do While Not oDBResults.EOF
sFileType = oDBResults(7)
If LCase(sFileType) = "data only" Then
If (Trim(oDBResults(6)) <> "0%") And (Trim(oDBResults(6)) <> "0 KB") Then
bDBAuto = true
End If
End If
If LCase(sFileType) = "log only" Then
If (Trim(oDBResults(6)) <> "0%") And (Trim(oDBResults(6)) <> "0 KB") Then
bLogAuto = true
End If
End If
oDBResults.MoveNext
Loop
With oDBInstance
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/RecoveryModel$", Split(sDBStatus(3),"=")(1)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/Updateability$", Split(sDBStatus(1),"=")(1)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/UserAccess$", Split(sDBStatus(2),"=")(1)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/Collation$", Split(sDBStatus(5),"=")(1)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/DatabaseAutogrow$", CStr(bDBAuto)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/LogAutogrow$", CStr(bLogAuto)
If Not(IsNull(sDBOwner)) Then
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/Owner$", sDBOwner
End If
End With
End If
Case ERR_CANNOT_INSERT_NULL
' Throw error to event log in here
' Error text will be "The system stored procedure sp_helpdb, which is used to gather information about the databases, has returned an error that may indicate that it cannot determine the db owner for the database [" & strDBName & ']. Here are the details: sp_helpdb @dbName='" & strDBName & "' on SQL Server Instance: " & strSQLServerInstanceName & ". Error number: " & e.Number & ", Error Information: " & e.Description, _
Case Else
' Throw error to event log in here
' Error text will be "Could not execute sp_helpdb @dbname=N'" & Replace(strDBName, "'", "''") & "' on SQL Server Instance: " & strSQLServerInstanceName & ". Error number: " & e.Number & ", Error Information: " & e.Description, _
End Select
Set oDBResults = nothing
'use masterDb if target db is inaccessible
Else
Dim sDbQuery : sDbQuery = "SELECT d.name, d.collation_name, d.recovery_model_desc, d.user_access_desc, DATABASEPROPERTYEX(d.name,'Updateability')" & _
" ,(SELECT (CASE WHEN MAX(mf.growth) > 0 THEN 1 ELSE 0 END) FROM sys.master_files mf WHERE d.database_id = mf.database_id AND mf.type = 1) as LogGrowth" & _
" ,(SELECT (CASE WHEN MAX(mf.growth) > 0 THEN 1 ELSE 0 END) FROM sys.master_files mf WHERE d.database_id = mf.database_id AND mf.type = 0) as DbGrowth" & _
" ,ISNULL(SUSER_SNAME(d.owner_sid),'null') as owner_name" & _
" FROM sys.databases d WHERE d.source_database_id IS NULL And d.name = '" & Replace(sDBName, "'", "''") & "'"
On Error Resume Next
Set oDBResults = cnADOConnection.Execute(sDbQuery)
e.Save
On Error Goto 0
if e.Number=0 Then
On Error Resume Next
sDBSize = Trim(oDBResults(0))
e.Save
On Error Goto 0
If e.Number = 0 Then
With oDBInstance
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/RecoveryModel$", oDBResults(2)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/Updateability$", oDBResults(4)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/UserAccess$", oDBResults(3)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/Collation$", oDBResults(1)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/DatabaseAutogrow$", CStr(bDBAuto)
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/LogAutogrow$", CStr(bLogAuto)
If oDBResults(7)<>"null" Then
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.Database']/Owner$", oDBResults(7)
End If
End With
End If
Else
' Throw error to event log in here
' Error text will be "Could not execute query agains sys.databases @dbname=N'" & Replace(strDBName, "'", "''") & "' on SQL Server Instance: " & strSQLServerInstanceName & ". Error number: " & e.Number & ", Error Information: " & e.Description, _
End If
Set oDBResults = nothing
End If
End If
'create source
'
'We have to check if there is only one pool. Because Hekaton was implemented in Enterprise Edition.
'Enterprise Edition has two build-in resource pools they are internal and default and customer can add his own pools.
'
sPoolName = ""
If NumberOfPool > 1 Then
Set oPool = Nothing
If IsNull( sPool ) Then
On Error Resume Next
Set gResults = cnADOConnection.Execute("SET NOCOUNT ON " & vbCrLf & _
" USE " & DelimitSqlIdentifier(sDBName) & vbCrLf & _
" SELECT name FROM sys.filegroups WHERE type = 'FX'")
e.Save
If e.Number = 0 Then
If Not gResults.EOF Then
Set oPool = oSQLDiscoveryData.CreateClassInstance(SQL_DEFAULTPOOL_CLASS)
Set oPoolRel = oSQLDiscoveryData.CreateRelationshipInstance(SQL_DATABASE_REFERENCES_APPLICATION_POOL)
sPool = 2
End If
Else
'log error
End If
On Error Goto 0
Else
Set oPool = oSQLDiscoveryData.CreateClassInstance(SQL_DEDICATED_CLASS)
Set oPoolRel = oSQLDiscoveryData.CreateRelationshipInstance(SQL_DATABASE_REFERENCES_APPLICATION_POOL)
End If
'if there is no FX group when sPool will be null
If Not IsNull( sPool ) Then
'let's get a pool name.
Dim poolResult
On Error Resume Next
Set poolResult = cnADOConnection.Execute("select name,pool_id from sys.dm_resource_governor_resource_pools where pool_id = " & sPool)
e.Save
On Error Goto 0
If e.Number = 0 Then
If Not poolResult.EOF Then
sPoolName = poolResult(0).Value
With oPool
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/PoolID$", sPool
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.ResourcePool']/Name$", sPoolName
.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID
.AddProperty "$MPElement[Name='Microsoft.SQLServer.2014.ServerRole']/InstanceName$", sSqlInstance
.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", sPoolName
End With
'create relationship
oPoolRel.Source = oDBInstance
oPoolRel.Target = oPool
Call oSQLDiscoveryData.AddInstance(oPoolRel)
End If
Else
'log error
End If
End If
End If
' Add DB to the Instance List and move to next DB
call oSQLDiscoveryData.AddInstance(oDBInstance)
End If
oResults.MoveNext
Loop
Set oResults = nothing
cnADOConnection.Close
DoDatabaseDiscovery = SQL_DISCOVERY_SUCCESS
End Function
Function ConvertSizeStringToNumber(sSizeString)
' Remove the KB and return a valid double
sSizeString = Replace(sSizeString, " KB", "")
ConvertSizeStringToNumber = CDbl(sSizeString)
End Function
Function IsExcluded (sDatabase)
Dim aExcludes
Dim match
Dim i
match = false
If Trim(ExcludeList) = "*" Then
match = true
Else
aExcludes = Split(ExcludeList, ",")
For i = 0 To UBound(aExcludes)
If LCase(sDatabase) = LCase(Trim(aExcludes(i))) Then
match = true
End If
Next
End If
IsExcluded = match
End Function
Function StoreExcludeDatabaseListToRegistry(instanceName, ExcludeList)
Dim regKey
Dim oError
Set oError = New Error
On Error Resume Next
regKey = oAPI.GetScriptStateKeyPath("$Target/ManagementGroup/Id$")
regKey = "HKEY_LOCAL_MACHINE\" + regKey + "\" + instanceName + "\DatabaseExcludeList"
oError.Save
On Error Goto 0
If oError.Number <> 0 Then ThrowScriptError "Unable to get database exclude list registry key", oError
Dim wShell
Set wShell = MOMCreateObject("WScript.Shell")
On Error Resume Next
wShell.RegWrite regKey, ExcludeList
oError.Save
On Error Goto 0
If oError.Number <> 0 Then ThrowScriptError "Unable to save database exclude list to registry ", oError
End Function