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
'#Include File:SQL2012Constants.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
Function ThrowScriptErrorNoAbort(ByVal sMessage, ByVal oErr)
On Error Resume Next
Dim oAPITemp
Set oAPITemp = MOMCreateObject("MOM.ScriptAPI")
oAPITemp.LogScriptEvent WScript.ScriptName, 4001, 1, sMessage & ". " & oErr.Description
End Function
Function ThrowScriptError(Byval sMessage, ByVal oErr)
On Error Resume Next
ThrowScriptErrorNoAbort sMessage, oErr
Quit()
End Function
Sub HandleError(customMessage)
Dim localLogger
If Not (Err.number = 0) Then
Set localLogger = new ScriptLogger
localLogger.LogFormattedError(customMessage)
Wscript.Quit 0
End If
End Sub
Function HandleErrorContinue(customMessage)
Dim localLogger
HandleErrorContinue = False
If Not (Err.number = 0) Then
Set localLogger = new ScriptLogger
localLogger.LogFormattedError(customMessage)
Err.Clear
HandleErrorContinue = True
End If
End Function
'#Include File:ConnectionString.vbs
Function BuildConnectionString(strServer, strDatabase)
ON ERROR RESUME NEXT
Err.Clear
Dim dataSource
dataSource = BuildServerName(strServer, "")
BuildConnectionString = "Data Source=" & EscapeConnStringValue(dataSource) & ";Initial Catalog=" & EscapeConnStringValue(strDatabase) & ";Integrated Security=SSPI"
End Function
Function BuildConnectionStringWithPort(ByVal strServer, ByVal strDatabase, ByVal tcpPort)
ON ERROR RESUME NEXT
Err.Clear
Dim dataSource
dataSource = strServer
If ((tcpPort <> "0") And (tcpPort <> "")) Then
dataSource = dataSource & "," & tcpPort
End If
BuildConnectionStringWithPort = "Data Source=" & EscapeConnStringValue(dataSource) & ";Initial Catalog=" & EscapeConnStringValue(strDatabase) & ";Integrated Security=SSPI"
End Function
' This function should be used to escape Connection String keywords.
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
Function GetTcpPort (ByVal strServer)
ON ERROR RESUME NEXT
Err.Clear
Dim tcpPort
tcpPort = ""
Call BuildServerName(strServer, tcpPort)
GetTcpPort = tcpPort
End Function
Function BuildServerName(ByVal strServer, ByRef tcp)
ON ERROR RESUME NEXT
Err.Clear
Dim pathArray, instanceName, computerName, ip, serverName
Dim oWMI, oQuery
ip= ""
pathArray = Split(strServer, "\")
computerName = pathArray(0)
instanceName = "MSSQLSERVER"
if (pathArray.Count > 1) Then
instanceName = pathArray(1)
End If
serverName = strServer
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 isListenAll
Set isListenAll = oQuery.ItemIndex(0)
If(isListenAll.PropertyNumVal = 1) 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
tcp = oQuery.ItemIndex(0).PropertyStrVal
If ((tcp <> "0") And (tcp <> "")) Then
serverName = serverName & "," & tcp
Else tcp = ""
End If
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")
If (oQuery.Count > 0) Then
Dim ipAddressName
ipAddressName = oQuery.ItemIndex(0).IPAddressName
Set oQuery = 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 (oQuery.Count > 0) Then
tcp = oQuery.ItemIndex(0).PropertyStrVal
End If
Set oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '"& EscapeWQLString(instanceName) &"' AND IPAddressName = '"& EscapeWQLString(ipAddressName) &"' AND PropertyName = 'IpAddress' AND PropertyStrVal <> ''")
If (oQuery.Count > 0) Then
ip = oQuery.ItemIndex(0).PropertyStrVal
End If
If ip <> "" Then
serverName = ip
End If
If ((tcp <> "0") And (tcp <> "")) Then
serverName = servername & "," & tcp
Else tcp = ""
End If
End If
End If
End If
On Error Goto 0
BuildServerName = serverName
End Function'#Include File:SQLADODB.vbs
''''''''''''''''''''''''''''''''''''
''' Class ADODB
''''''''''''''''''''''''''''''''''''
Class ADODB
Dim ADOConnection
Private Sub Class_Initialize()
On Error Resume Next
Set ADOConnection = CreateObject("ADODB.Connection")
HandleError("Cannot create object 'ADODB.Connection' (ADODB.Class_Initialize).")
End Sub
Public Function Open(connectionString, provider, connectionTimeout)
On Error Resume Next
Open = false
if connectionString = "" Then
Err.Raise SCRIPT_EVENT_ID, "ADODB.Open()", "Argument 'connectionString' cannot be empty." , "", 0
End If
ADOConnection.ConnectionString = connectionString
if provider = "" Then
ADOConnection.Provider = "sqloledb"
Else
ADOConnection.Provider = provider
End If
if connectionTimeout <= 0 Then
ADOConnection.ConnectionTimeout = 30
Else
ADOConnection.ConnectionTimeout = connectionTimeout
End If
HandleError("Cannot initialize ADODB connection (ADODB.Open).")
ADOConnection.Open()
if Err.number = 0 Then
Open = True
End If
End Function
Public Function ExecuteQuery(query)
On Error Resume Next
Set ExecuteQuery = ADOConnection.Execute(query)
End Function
Public Function Close()
On Error Resume Next
if Not IsNull(ADOConnection) Then
ADOConnection.Close()
HandleError("Cannot close ADODB connection (ADODB.Close).")
End If
End Function
Public Function HandleOpenConnectionErrorContinue(database, serverName, sqlInstanceName)
HandleOpenConnectionErrorContinue = true
if Err.number <> 0 Then
HandleOpenConnectionErrorContinue = false
Dim oError : Set oError = new Error
oError.Save()
Dim instanceIsRunning : instanceIsRunning = IsServiceRunning(sqlInstanceName)
On Error Resume Next
oError.Raise()
if ((Err.number and 65535) = 16389 or (Err.number and 65535) = 3661) and instanceIsRunning Then
Logger.LogError("Cannot login to database [" & serverName & "][" & sqlInstanceName & ":" & database & "] ")
Err.Clear
ElseIf (instanceIsRunning) Then
Logger.LogFormattedError("Cannot open ADODB connection. (Connection string: '" & ADOConnection.ConnectionString & "'.)")
Err.Clear
Else
Err.Clear
End If
On Error Goto 0
End If
End Function
Public Function HandleExecutionQueryErrorContinue(query, serverName, sqlInstanceName)
HandleExecutionQueryErrorContinue = true
if Err.number <> 0 Then
HandleExecutionQueryErrorContinue = false
Dim oError : Set oError = new Error
oError.Save()
Dim instanceIsRunning : instanceIsRunning = IsServiceRunning(sqlInstanceName)
On Error Resume Next
error.Raise()
if ((Err.number and 65535) = 16389 or (Err.number and 65535) = 3661) and instanceIsRunning Then
Logger.LogError("Cannot login to database [" & serverName & "][" & sqlInstanceName & ":" & ADOConnection.DefaultDatabase & "] ")
Err.Clear
ElseIf (instanceIsRunning) Then
Logger.LogFormattedError("Cannot execute query: '" & query & "'.")
Err.Clear
Else
Err.Clear
End If
On Error Goto 0
End If
End Function
Public Function IsServiceRunning(sInstance)
Dim sServiceName : sServiceName = GetSQLServiceName(sInstance)
Dim oService, sObjectString
sObjectString = "winmgmts:\\.\root\cimv2"
On Error Resume Next
Err.Clear
'We want to do our own error handling here. No WMIGetObject().
Set oService = GetObject(sObjectString & ":Win32_Service.Name='" & EscapeWQLString(sServiceName) & "'")
If Err.Number <> 0 Then
IsServiceRunning = false
Else
If oService.State = "Running" Then
IsServiceRunning = true
Else
IsServiceRunning = false
End If
End If
Set oService = Nothing
On Error GoTo 0
End Function
End Class
'#Include File:DiscoverSQL2012DBPolicy.vbs
Set oAPI = MOMCreateObject("MOM.ScriptAPI")
Set oSQLDiscoveryData = oAPI.CreateDiscoveryData(0, SourceId, ManagedEntityId)
resultPolicyList = "Discovered next objects: " & vbCrLf
'If DiscoverPolicies(InstanceName, ConnectionString, oSQLDiscoveryData) >= 0 Then
' oAPI.LogScriptEvent "DatabaseUserPolicyDiscovery:" & InstanceName, SCRIPT_EVENT_ID, EVENT_TYPE_INFORMATION, "Database User Policies for SQL instance '" + InstanceName + "' were discovered successfully."
'Else
' oAPI.LogScriptEvent "DatabaseUserPolicyDiscovery:" & InstanceName, SCRIPT_EVENT_ID, EVENT_TYPE_ERROR, "Database User Policies for SQL instance '" + InstanceName + "' discovery failed."
' oSQLDiscoveryData.IsSnapshot = False
'End If
If DiscoverPolicies(InstanceName, ConnectionString, oSQLDiscoveryData, TcpPort) < 0 Then
oAPI.LogScriptEvent "DatabaseUserPolicyDiscovery:" & InstanceName, SCRIPT_EVENT_ID, EVENT_TYPE_ERROR, "Database User Policies for SQL instance '" + InstanceName + "' discovery failed."
oSQLDiscoveryData.IsSnapshot = False
End If
Function DiscoverPolicies(ByVal sSqlInstance, ByVal sSQLConnectionString, ByVal oDisc, ByVal sTcpPort)
Dim e
Set e = New Error
Dim dbMsdbConnection : Set dbMsdbConnection = new ADODB
Dim strProv : strProv = BuildConnectionStringWithPort(sSQLConnectionString, "msdb", sTcpPort)
e.Clear
On Error Resume Next
Dim res : res = dbMsdbConnection.Open(strProv, "sqloledb", 30)
e.Save
'get fresh tcp port and try to connect again
if 0 <> Err.number then
e.Clear
Err.Clear
strProv = BuildConnectionString(sSQLConnectionString, "msdb")
res = dbMsdbConnection.Open(strProv, "sqloledb", 30)
e.Save
if 0 <> Err.number then
'Error event in here
'g_oSQL.CreateConnectionFailureAlert sInstance, Err.number, Err.Description
DiscoverPolicies = SQL_DISCOVERY_CONNECT_FAILURE
Exit Function
end if
end if
On Error Goto 0
Dim oResults
e.Clear
On Error Resume Next
Dim query : query = " SELECT p.policy_id, " & vbCrLf & _
" p.name AS policy_name, " & vbCrLf & _
" p.[description], " & vbCrLf & _
" p.help_text, " & vbCrLf & _
" p.help_link, " & vbCrLf & _
" p.is_enabled, " & vbCrLf & _
" c.name AS condition_name, " & vbCrLf & _
" c.facet, " & vbCrLf & _
" pc.name AS category_name, " & vbCrLf & _
" cf.obj_name " & vbCrLf & _
" FROM syspolicy_policies p " & vbCrLf & _
" JOIN syspolicy_conditions c ON c.condition_id = p.condition_id " & vbCrLf & _
" JOIN syspolicy_object_sets os ON os.object_set_id = p.object_set_id " & vbCrLf & _
" JOIN syspolicy_target_sets ts ON ts.object_set_id = os.object_set_id " & vbCrLf & _
" JOIN syspolicy_target_set_levels tsl ON tsl.target_set_id = ts.target_set_id " & vbCrLf & _
" LEFT OUTER JOIN syspolicy_policy_categories pc ON pc.policy_category_id = p.policy_category_id " & vbCrLf & _
" LEFT OUTER JOIN syspolicy_conditions cf ON cf.condition_id = tsl.condition_id " & vbCrLf & _
" WHERE c.facet = 'Database' " & vbCrLf & _
" AND p.is_system = 0 " '& vbCrLf & _ Selects user policies only
'" AND p.is_enabled = 1 " 'Selects enabled policies only
Set oResults = dbMsdbConnection.ExecuteQuery(query)
e.Save
On Error Goto 0
If e.Number <> 0 Then
DiscoverPolicies = SQL_DISCOVERY_QUERY_FAILURE
Exit Function
End If
Do While Not oResults.EOF
Dim policyID : policyID = oResults("policy_id").Value
Dim policyName : policyName = oResults("policy_name").Value
Dim policyDescription : policyDescription = oResults("description").Value
Dim policyCategory : policyCategory = oResults("category_name").Value
Dim policyCondition : policyCondition = oResults("condition_name").Value
Dim policyFacet : policyFacet = oResults("facet").Value
Dim policyHelpLink : policyhelpLink = oResults("help_link").Value
Dim conditionObjectName : conditionObjectName = oResults("obj_name").Value
Dim policyIsEnabled : policyIsEnabled = oResults("is_enabled").Value
Dim isPolicyCritical
Dim containsErrorString
If Not (IsNull(policyCategory) Or IsEmpty(policyCategory)) Then
containsErrorString = InStr(LCase(policyCategory), "error")
Else
containsErrorString = False
policyCategory = SQL_USERPOLICY_DEFAULT_CATEGORY_NAME
End If
isPolicyCritical = Not IsNull(containsErrorString) And containsErrorString > 0
If IsNull(conditionObjectName) Or Len(conditionObjectName) = 0 Then
conditionObjectName = "%"
End If
e.Clear
On Error Resume Next
' query for the list of databases which are not database snapshots
query = " SELECT name FROM sys.databases " & vbCrLf & _
" WHERE source_database_id IS NULL " & vbCrLf & _
" AND name like '" & conditionObjectName & "' " & vbCrLf & _
" AND name not in ('master', 'model', 'msdb', 'tempdb', 'distribution')" 'Excluding system tables
Dim dbResults : Set dbResults = dbMsdbConnection.ExecuteQuery(query)
e.Save
On Error Goto 0
If e.Number <> 0 Then
DiscoverPolicies = SQL_DISCOVERY_QUERY_FAILURE
Exit Function
End If
Do While Not dbResults.EOF
Dim databaseName : databaseName = dbResults("name").Value
' Generate Discovery data
Dim oDBInstance
If isPolicyCritical Then
Set oDBInstance = oSQLDiscoveryData.CreateClassInstance(SQL_DATABASE_ERROR_USER_POLICY_CLASS)
Else
Set oDBInstance = oSQLDiscoveryData.CreateClassInstance(SQL_DATABASE_WARNING_USER_POLICY_CLASS)
End If
If Not IsNull(oDBInstance) Then
With oDBInstance
.AddProperty "$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", TargetComputerID
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.ServerRole']/InstanceName$", sSqlInstance
.AddProperty "$MPElement[Name='SQL!Microsoft.SQLServer.Database']/DatabaseName$", databaseName
.AddProperty "$MPElement[Name='System!System.Entity']/DisplayName$", policyName
resultPolicyList = resultPolicyList & databaseName & "." & policyName & vbCrLf
' Add DB to the Instance List and move to next DB
call oSQLDiscoveryData.AddInstance(oDBInstance)
End If
dbResults.MoveNext
Loop
Set dbResults = nothing
oResults.MoveNext
Loop
Set oResults = nothing
dbMsdbConnection.Close
DiscoverPolicies = SQL_DISCOVERY_SUCCESS
Home
ITA 
Show References: