Ce type de module permet de modifier l'état d'une base de données Microsoft SQL Server 2016. Il analyse la configuration en fonction de la base de données à modifier et l'état à définir.
var DriverVersion = function (versionStr) {
arguments.callee.setAlias('DriverVersion.constructor');
var versionRegex = /^(\d{1,8})(.\d{1,8}){0,3}$/g;
if (!versionRegex.exec(versionStr)) {
throw new Exception("Invalid version string.");
}
var parts = versionStr.split(".");
this.major = parseInt(parts[0]);
this.minor = 0;
this.build = 0;
this.revision = 0;
if (parts.length > 1) {
this.minor = parseInt(parts[1]);
if (parts.length > 2) {
this.build = parseInt(parts[2]);
if (parts.length > 3) {
this.revision = parseInt(parts[3]);
}
}
}
this.compareTo = function (v) {
arguments.callee.setAlias('DriverVersion.compareTo');
if (!(v instanceof DriverVersion)) {
throw new Exception("Parameter is not an instance of type DriverVersion.");
}
var d = this.major - v.major;
if (d !== 0) {
return d;
}
d = this.minor - v.minor;
if (d !== 0) {
return d;
}
d = this.build - v.build;
if (d !== 0) {
return d;
}
d = this.revision - v.revision;
return d;
};
};
function GetDriverVersionObj(oVersion) {
arguments.callee.setAlias('GetDriverVersionObj');
if (oVersion instanceof DriverVersion) {
return oVersion;
} else {
if ((typeof oVersion) === "string") {
return new DriverVersion(oVersion);
}
}
throw new Exception("Parameter is not an instance of type DriverVersion.");
}
var VersionRange = function (sStartVersion, sEndVersion) {
arguments.callee.setAlias('VersionRange.constructor');
this.startVersion = GetDriverVersionObj(sStartVersion);
this.endVersion = GetDriverVersionObj(sEndVersion);
};
function EnumerateRegistryKeyValues(hiveName, keyPath) {
arguments.callee.setAlias('EnumerateRegistryKeyValues');
var hiveId, ex;
switch (hiveName) {
case "HKEY_CLASSES_ROOT":
hiveId = 2147483648;
break;
case "HKEY_CURRENT_USER":
hiveId = 2147483649;
break;
case "HKEY_LOCAL_MACHINE":
hiveId = 2147483650;
break;
case "HKEY_USERS":
hiveId = 2147483651;
break;
case "HKEY_CURRENT_CONFIG":
hiveId = 2147483653;
break;
default:
throw new Exception("Registry hive name '" + hiveName + "' is invalid");
}
try {
var providerName = "StdRegProv";
var services = GetObject("winmgmts:{impersonationLevel=impersonate}!\\\\.\\root\\default");
var registry = services.Get(providerName);
var enumFuncName = "EnumValues";
var inParams = registry.Methods_(enumFuncName).InParameters.SpawnInstance_();
inParams.hDefKey = hiveId;
inParams.sSubKeyName = keyPath;
var outParams = registry.ExecMethod_(enumFuncName, inParams);
var valueNames = outParams.sNames.toArray();
var valueTypes = outParams.Types.toArray();
var DriverSelectorRule = function (sNameRegex, sNameVersionRegex, sNameMinVersion, sNameMaxVersion, oVersionRangeArray) {
arguments.callee.setAlias('DriverSelectorRule.constructor');
var _computerId = ".";
this.nameRegex = sNameRegex;
this.nameVersionRegex = sNameVersionRegex;
this.nameMinVersion = GetDriverVersionObj(sNameMinVersion);
this.nameMaxVersion = GetDriverVersionObj(sNameMaxVersion);
var _versionRangeArray = oVersionRangeArray;
this.driverCollection = new Array();
this.isNativeClient = false;
var _nameRegexObj = new RegExp(sNameRegex);
var _nameVersionRegexObj = null;
if (sNameVersionRegex) {
if (sNameVersionRegex.length > 0) {
_nameVersionRegexObj = new RegExp(sNameVersionRegex);
}
}
var checkVersion = function (oVersion, vMinVersion, vMaxVersion) {
arguments.callee.setAlias('DriverSelectorRule.checkVersion');
var vVersion = GetDriverVersionObj(oVersion);
return vVersion.compareTo(vMinVersion) >= 0 && (vVersion.toString() === MAX_DRIVER_VERSION_STR || vMinVersion.compareTo(vMaxVersion) === 0 || vVersion.compareTo(vMaxVersion) < 0);
};
this.checkDriverVersion = function (oVersion) {
arguments.callee.setAlias('DriverSelectorRule.checkDriverVersion');
var result = false;
for (var i = 0; i < _versionRangeArray.length; i++) {
if (checkVersion(oVersion, _versionRangeArray[i].startVersion, _versionRangeArray[i].endVersion)) {
result = true;
break;
}
}
return result;
};
this.matchName = function (sName) {
arguments.callee.setAlias('DriverSelectorRule.matchName');
if (_nameRegexObj.exec(sName)) {
return true;
}
return false;
};
this.getNameVersion = function (sName) {
arguments.callee.setAlias('DriverSelectorRule.getNameVersion');
if (!_nameVersionRegexObj) {
return new DriverVersion("0");
}
var matches = _nameVersionRegexObj.exec(sName);
if (matches) {
return new DriverVersion(matches[0]);
}
throw new Exception("Driver name or version detection expression is invalid");
};
this.getDriverVersion = function (sName) {
arguments.callee.setAlias('DriverSelectorRule.getDriverVersion');
var registry = new Registry();
var driverPath = registry.Read("HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBCINST.INI\\" + sName + "\\Driver");
try {
var objFso = new ActiveXObject("Scripting.FileSystemObject");
var sDllVersion = objFso.GetFileVersion(driverPath);
} catch (ex) {
throw new Exception("Cannot obtain driver version using path '" + driverPath + "'");
}
return new DriverVersion(sDllVersion);
};
this.addDriver = function (oDriver) {
arguments.callee.setAlias('DriverSelectorRule.addDriver');
if (!(oDriver instanceof DriverItem)) {
throw new Exception("Parameter is not an instance of type DriverItem.");
}
this.driverCollection.push(oDriver);
};
this.resetState = function () {
this.driverCollection = new Array();
};
};
var DriverSelector = function () {
arguments.callee.setAlias('DriverSelector.constructor');
// DEPRECATED
var _selectorRules = DriverSelector.createSelectorRules();
var _selectedDriverName = null;
// DEPRECATED
var _computerId = ".";
var _defaultDriverName = "SQL Server";
// DEPRECATED
var _ncli_ForceProtocolEncryption = false;
var _ncli_TrustServerCertificate = false;
var _ncli_tcpProtocolEnabled = true;
var _ncli_smProtocolEnabled = true;
var _processed = false;
var _errorCollection = [];
// TODO: 2
function getNsNameWithHighestVersion(computerName) {
arguments.callee.setAlias('DriverSelector.getNsNameWithHighestVersion');
var rootNs = "root\\Microsoft\\SqlServer";
var oWMI = new WMIProvider(rootNs);
var namespaces = oWMI.ExecQuery("SELECT Name FROM __NAMESPACE WHERE Name LIKE 'ComputerManagement%'");
if (namespaces.Count > 0) {
var e = new Enumerator(namespaces);
var currentName = "";
for (; !e.atEnd(); e.moveNext()) {
var nsName = e.item().Name;
currentName = nsName > currentName ? nsName : currentName;
}
} else {
throw new Exception("No namespace 'ComputerManagement*' was found");
}
return rootNs + "\\" + currentName;
}
// TODO: 1
function getNativeClientOdbcSettings(namespaceFullName, params) {
arguments.callee.setAlias('DriverSelector.getNativeClientOdbcSettings');
// ComputerName must be '.' or not provided
var wmi = new WMIProvider(namespaceFullName);
var props = null;
// Query can fail, because native client not supported
try {
props = wmi.ExecQuery("SELECT FlagName, FlagValue FROM ClientSettingsGeneralFlag");
} catch (e) {
// Do nothing, its ok, just skip this settings
return;
}
if (props.Count > 0) {
var e = new Enumerator(props);
for (; !e.atEnd(); e.moveNext()) {
var prop = e.item();
switch (prop.FlagName) {
case "Force protocol encryption":
// _ncli_ForceProtocolEncryption = prop.FlagValue;
params.isForceProtocolEncryption = prop.FlagValue;
break;
case "Trust Server Certificate":
// _ncli_TrustServerCertificate = prop.FlagValue;
params.isTrustServerCertificate = prop.FlagValue;
break;
}
}
}
}
/*
* @namespace - full name
*/
function getClientProtocolsFromWMI(namespace) {
var protocols = [];
// create wmi provider for local machine
var wmi = new WMIProvider(namespace);
var result = null;
try {
result = wmi.ExecQuery('SELECT ProtocolName, ProtocolOrder FROM ClientNetworkProtocol');
} catch (e) {
// do nothing
}
// extract protocols from query
if (result && result.Count > 0) {
for (var e = new Enumerator(result); !e.atEnd(); e.moveNext()) {
if (e.item().ProtocolOrder > 0) {
protocols.push(e.item().ProtocolName.toLowerCase());
}
}
}
return protocols;
}
/*
* Look if driver is Native client
* @namespace - full name
*/
// rename getClientProtocols
function getClientProtocolsFromRegistry(rules) {
// TODO: read
return null;
}
// TODO: 3
function getEnabledNativeClientProtocols(namespace) {
arguments.callee.setAlias('getEnabledNativeClientProtocols');
// TODO: WMI will be replaced by reading from registry
// var oQuery = null;
// var protocols = [];
// try read from WMI
// can fail because namespace remains empty for sql cluster with empty
// node
// computerName for WMIProvider must be '.' or not provided, read
// locally
// var oWMI = new WMIProvider(namespace);
// try {
// oQuery = oWMI.ExecQuery("SELECT ProtocolName, ProtocolOrder FROM ClientNetworkProtocol");
// } catch (e) {
// // do nothing
// }
// TODO: check oQuery and try read protocols from registry
// if (oQuery && oQuery.Count > 0) {
// for (var e = new Enumerator(oQuery) ; !e.atEnd() ; e.moveNext()) {
// if (e.item().ProtocolOrder > 0) {
// protocols.push(e.item().ProtocolName.toLowerCase());
// }
// }
// }
// we try to use WMI as less as possible, read from registry first
return getClientProtocolsFromRegistry(namespace) || getClientProtocolsFromWMI(namespace);
}
function resetState() {
_errorCollection = new Array();
for (var i = 0; i < _selectorRules.length; i++) {
_selectorRules[i].resetState();
}
}
function addError(oErr) {
var errStr = typeof (oErr.toString) === 'undefined' ? "[" + (oErr.number & 0xFFFF) + "] " + oErr.message : oErr.toString();
_errorCollection.push(errStr);
}
function selectFreshDriver(rules) {
var selected = null;
var rule = null;
var driver = null;
for (var r = 0; r < rules.length; ++r) {
rule = rules[r];
for (var d = 0; d < rules[r].driverCollection.length; ++d) {
driver = rule.driverCollection[d];
// select first if not selected
if (selected === null) {
selected = driver;
} else {
// select fresh, compare driver with selected by name and version
if (driver.nameVersion.compareTo(selected.nameVersion) >= 0 &&
driver.driverVersion.compareTo(selected.driverVersion) >= 0) {
selected = driver;
}
}
}
// all rules have order by priority, if rule contains drivers, then
// selected with high priority
if (selected) break;
}
return selected;
}
// Select most appropriate driver from filtered installed system (ODBC) drivers.
// select all available drivers
// Filter by driver name and version
// Intersected: check client and server have shared protocols
// tcp and np (Named Pipes) can work by network, sm can work only locally
// driver priorities: sm, tcp; np not used
//
// For native client need common client and server protocols
//
// Select latest namespace with highest version
// Select client protocols
// Select server protocols (enabled)
// Intersect client and server protocols
// Select odbc drivers related to inresected protocols
// We have 3 possible drivers:
// - ODBC (all protocols enabled)
// - Native Client (tcp, sm, np protocols)
// only native client can choose protocols through registry settings
// - Microsoft SQL Server (all protocols enabled)
this.processDrivers = function (serverProtocols) {
arguments.callee.setAlias('DriverSelector.processDrivers');
// all rules ordered by priority
DriverSelector.createSelectorRules = function () {
var rules = [];
var pr = new DriverSelectorRule("^SQL\\sServer\\sNative\\sClient\\s\\d{1,8}(\\.\\d{1,8})?$",
"\\d{1,8}(\\.\\d{1,8})?$", "11.0", "11.0",
[
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")
]);
pr.isNativeClient = true;
rules.push(pr);
rules.push(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,
[
new VersionRange("2014.120.4219.0", MAX_DRIVER_VERSION_STR),
new VersionRange("2014.120.2546.0", "2014.120.4000.0")
]));
// Get available drivers in the system and fill rules drivers
// TODO: rename method
DriverSelector.processSystemOdbcDrivers = function (rules, isProtocolsIntersected) {
arguments.callee.setAlias('DriverSelector.processSystemOdbcDrivers');
var registry = new Registry();
var keys = EnumerateRegistryKeyValues("HKEY_LOCAL_MACHINE", "SOFTWARE\\ODBC\\ODBCINST.INI\\ODBC Drivers");
var drivers = keys.ValueNames;
var values = keys.ValueTypes;
for (var i = 0; i < drivers.length; ++i) {
try {
if (values[i] !== RegistryValueTypes.REG_SZ) {
continue;
}
var isInstalled = registry.Read("HKEY_LOCAL_MACHINE\\SOFTWARE\\ODBC\\ODBCINST.INI\\ODBC Drivers" + "\\" + drivers[i]);
if (isInstalled !== "Installed") {
continue;
}
var rule = null;
for (var j = 0; j < rules.length; ++j) {
rule = rules[j];
if (rule.matchName(drivers[i])) {
// TODO: extract filtering
if (!rule.isNativeClient || (rule.isNativeClient && isProtocolsIntersected)) {
var nameVersion = rule.getNameVersion(drivers[i]);
if (rule.checkNameVersion(nameVersion)) {
var driverVersion = rule.getDriverVersion(drivers[i]);
if (rule.checkDriverVersion(driverVersion)) {
var driverItem = new DriverItem(drivers[i], nameVersion, driverVersion, rule);
rule.addDriver(driverItem);
}
}
}
}
}
} catch (ex) {
addError(ex);
}
}
};
var UniqueCollection = function () {
var _dict = new ActiveXObject("Scripting.Dictionary");
this.put = function (item) {
if (!_dict.Exists(item)) {
_dict.add(item, '');
}
};
this.exists = function (item) {
return _dict.Exists(item);
};
this.get = function () {
return (new VBArray(_dict.Keys())).toArray();
};
this.clear = function () {
_dict.RemoveAll();
};
};
var UtcDateOperations = function () {
this.iso8601UtcStringToDate = function (sDate) {
var matches = /^(\d{4})-(\d\d)-(\d\d)(?:T|\s)(\d\d):(\d\d):(\d\d)Z$/g.exec(sDate);
if (!matches) {
throw new Exception("Input date string is in invalid format");
}
var year = parseInt(matches[1], 10);
var month = parseInt(matches[2], 10) - 1;
var day = parseInt(matches[3], 10);
var hour = parseInt(matches[4], 10);
var minute = parseInt(matches[5], 10);
var second = parseInt(matches[6], 10);
return new Date(Date.UTC(year, month, day, hour, minute, second));
};
this.checkSecondsElapsed = function (sDate, nSeconds) {
var utcNow = new Date();
var utcDate = this.iso8601UtcStringToDate(sDate);
return (((utcNow - utcDate) / 1000) > nSeconds);
};
};
var Common = function () {
arguments.callee.setAlias('Common.constructor');
this.DEBUG = false;
this.ManagementGroupName = "$Target/ManagementGroup/Name$";
this.ManagementGroupID = "$Target/ManagementGroup/Id$";
this.SQL_SERVICE_DEFAULT_NAME = "MSSQLSERVER";
this.EscapeWQLString = function (wqlString) {
return wqlString.replace(/\\/g, "\\\\").replace(/'/g, "\\'");
};
this.ParseInputBool = function (inputVal, allowEmpty, errorMessage) {
if (inputVal === null || inputVal === "") {
if (allowEmpty) {
return "true";
} else {
throw new Exception(errorMessage);
}
}
var supportedValues = ["true", "false", "t", "f", ".t.", ".f.", "1", "0"];
var replacements = ["true", "false", "true", "false", "true", "false", "true", "false"];
var processed = ("" + inputVal).trim().toLowerCase();
var pos = supportedValues.indexOf(processed);
if (pos < 0) {
throw new Exception(errorMessage);
}
return replacements[pos];
};
this.GetEnabledSqlServerProtocols = function (namespaceName, computerName, instanceName) {
arguments.callee.setAlias('GetEnabledSqlServerProtocols');
// TODO [dem]: check computerName must be short version
var oWMI = new WMIProvider("root\\Microsoft\\SqlServer\\" + namespaceName, computerName);
var oQuery = oWMI.ExecQuery("SELECT ProtocolName, Enabled FROM ServerNetworkProtocol WHERE InstanceName = '" + this.EscapeWQLString(instanceName) + "'");
var protocolsArr = [];
if (oQuery.Count > 0) {
for (var e = new Enumerator(oQuery); !e.atEnd(); e.moveNext()) {
if (e.item().Enabled) {
protocolsArr.push(e.item().ProtocolName.toLowerCase());
}
}
}
return protocolsArr;
};
this.GetSqlServerTcpIpSettings = function (instanceName, computerName) {
arguments.callee.setAlias('GetSqlServerTcpIpSettings');
var listenAllIps = false;
var e, e2, matches;
var uc = new UniqueCollection();
var ipSettings = new ActiveXObject("Scripting.Dictionary");
var portRegex = /(?:,|^)\s*([0-9]{1,4}|[1-5][0-9]{4}|6[0-4][0-9]{3}|65[0-4][0-9]{2}|655[0-2][0-9]|6553[0-5])\s*(?=,|$)/g;
var oWMI = new WMIProvider("root\\Microsoft\\SqlServer\\ComputerManagement13", computerName);
var oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND PropertyName = 'ListenOnAllIPs'");
if (oQuery.Count > 0) {
e = new Enumerator(oQuery);
e.moveFirst();
var isListenAll = e.item();
if (isListenAll.PropertyNumVal === 1) {
listenAllIps = true;
}
}
if (listenAllIps) {
oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND IPAddressName = 'IPAll' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal != ''");
if (oQuery.Count > 0) {
for (e = new Enumerator(oQuery); !e.atEnd(); e.moveNext()) {
while (matches = portRegex.exec(e.item().PropertyStrVal)) {
if (!this.SqlTcpPortIsEmpty(matches[1])) {
uc.put(matches[1]);
}
}
}
ipSettings.add("IPAll", uc.get());
}
} else {
oQuery = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND IPAddressName != '' AND PropertyName = 'Enabled' AND PropertyNumVal = 1");
if (oQuery.Count > 0) {
for (e = new Enumerator(oQuery); !e.atEnd(); e.moveNext()) {
var ipAddress = '';
var ipAddressName = e.item().IPAddressName;
var oQuery2 = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND IPAddressName = '" + this.EscapeWQLString(ipAddressName) + "' AND PropertyName = 'IpAddress' AND PropertyStrVal != ''");
if (oQuery2.Count > 0) {
e2 = new Enumerator(oQuery2);
e2.moveFirst();
ipAddress = e2.item().PropertyStrVal;
} else {
continue;
}
oQuery2 = oWMI.ExecQuery("SELECT * FROM ServerNetworkProtocolProperty WHERE ProtocolName = 'Tcp' AND InstanceName = '" + this.EscapeWQLString(instanceName) + "' AND IPAddressName = '" + this.EscapeWQLString(ipAddressName) + "' AND (PropertyName = 'TcpPort' OR PropertyName = 'TcpDynamicPorts') AND PropertyStrVal != ''");
uc.clear();
if (oQuery2.Count > 0) {
for (e2 = new Enumerator(oQuery2); !e2.atEnd(); e2.moveNext()) {
while (matches = portRegex.exec(e2.item().PropertyStrVal)) {
if (!this.SqlTcpPortIsEmpty(matches[1])) {
uc.put(matches[1]);
}
}
}
try {
ipSettings.add(ipAddress, uc.get());
} catch (ex) {
}
}
}
}
}
return { listenAllIPs: listenAllIps, ipSettings: ipSettings };
};
this.GetSqlServiceName = function (instanceName) {
arguments.callee.setAlias('GetServiceName');
this.EscapeCacheValueName = function (name) {
return name.replace(/_/g, "__");
};
this.GetSqlServerHostName = function (strDNSComputerName, instanceName, namespace) {
arguments.callee.setAlias('GetSqlServerHostName');
var serviceName = this.GetSqlServiceName(instanceName);
var escapedServiceName = this.EscapeWQLString(serviceName);
var wmiProvider = new WMIProvider("ROOT\\Microsoft\\SqlServer\\" + namespace, strDNSComputerName);
var isClustered = this.GetWMISingleValue(wmiProvider, "SELECT PropertyNumValue FROM SqlServiceAdvancedProperty WHERE PropertyName = 'Clustered' AND SqlServiceType = 1 AND ServiceName = '" + escapedServiceName + "'", "PropertyNumValue");
var hostName = null;
if (isClustered === 0) {
hostName = this.GetWMISingleValue(wmiProvider, "SELECT HostName FROM SqlService WHERE SQLServiceType = 1 AND ServiceName = '" + escapedServiceName + "'", "HostName");
} else {
hostName = this.GetWMISingleValue(wmiProvider, "SELECT PropertyStrValue FROM SqlServiceAdvancedProperty WHERE PropertyName = 'VSNAME' AND SqlServiceType = 1 AND ServiceName = '" + escapedServiceName + "'", "PropertyStrValue");
}
this.GetLocalHostName = function () {
arguments.callee.setAlias('GetLocalHostName');
var wshShell = new ActiveXObject("WScript.Shell");
return wshShell.ExpandEnvironmentStrings("%COMPUTERNAME%");
};
this.CheckConnectionTarget = function (dbConnection, hostName, instanceName) {
arguments.callee.setAlias('CheckConnectionTarget');
var destinationTestQuery = "select CAST(SERVERPROPERTY('MachineName') AS nvarchar(128)) as ServerName, @@servicename as InstanceName";
try {
var queryResult = dbConnection.ExecuteQuery(destinationTestQuery);
queryResult.MoveFirst();
if (!queryResult.EOF) {
var queryServerName = queryResult("ServerName").Value.toUpperCase();
var queryInstanceName = queryResult("InstanceName").Value.toUpperCase();
var connectionString = this.GetConnectionString(driverName, dataSource, databaseName);
var dbMasterConnection = new ADODB(connectionString, provider, timeout);
this.CheckConnectionTarget(dbMasterConnection, hostName, instanceName);
return dbMasterConnection;
};
this.SmartConnect = function (serverName, databaseName, tcpPort, machineName, instanceName) {
arguments.callee.setAlias('SmartConnect');
var dataSource;
var driverName;
var timeout;
var targetName = serverName;
var lastError = null;
var errorMessageColl = [];
var hostValueName = this.EscapeCacheValueName(machineName);
var instanceValueName = this.EscapeCacheValueName(instanceName);
var cacheExpirationTime = Number.MAX_VALUE;
var netBiosHostNameData = this.GetSqlServerHostNameEx(machineName, instanceName, "ComputerManagement13");
var netBiosHostName = netBiosHostNameData.hostName;
var dnsHostName = machineName.split(".")[0];
var enabledServerProtocols = this.GetEnabledSqlServerProtocols("ComputerManagement13", machineName, instanceName);
var ds = new DriverSelector();
ds.processDrivers(enabledServerProtocols);
var hasErrors = ds.hasErrors();
var useFqdn = ds.getUseFqdn();
driverName = ds.getSelectedDriverName();
var connStr = serverName;
var result;
// Sql Server Shared Memory protocol require usage of host's NetBios name.
// Shared Memory usually the first in the driver's priority list.
// Rebuild data source string in the case of standalone Sql Server instance, NetBios host name differs
// from DNS host name and enabled Shared Memory on Client and Server
if (netBiosHostName !== dnsHostName && !netBiosHostNameData.isClustered && enabledServerProtocols.indexOf("sm") >= 0 && ds.getClientSharedMemoryProtocolEnabled) {
var localHostName = this.GetLocalHostName();
if (netBiosHostName === localHostName) {
connStr = "lpc:" + this.BuildDataSourceFromParts(netBiosHostName, instanceName, null);
}
}
//try to use SQL server browser
try {
dataSource = this.GetDataSource(connStr, "");
result = this.GetAndCheckConnection(driverName, dataSource, databaseName, "", 15, netBiosHostName, instanceName);
this.PutStringValueToCache(paramsPath, "DriverName", driverName);
this.PutStringValueToCache(paramsPath, "TargetDataSource", dataSource);
this.PutStringValueToCache(paramsPath, "ConnectionTimeout", 15);
return result;
} catch (ex) {
lastError = ex;
errorMessageColl.push("Connection to data source '" + dataSource + "' failed: " + ex.message);
}
if (enabledServerProtocols.indexOf("tcp") >= 0 && ds.getClientTcpProtocolEnabled()) {
var pathArray = serverName.split("\\");
var dsComputerName = pathArray[0];
targetName = useFqdn ? machineName : dsComputerName;
var tcpIpSettings = this.GetSqlServerTcpIpSettings(instanceName, dsComputerName);
if (tcpIpSettings.listenAllIPs) {
for (var j = 0; j < tcpIpSettings.ipSettings.item("IPAll").length; j++) {
try {
dataSource = this.GetDataSource(targetName, tcpIpSettings.ipSettings.item("IPAll")[j]);
result = this.GetAndCheckConnection(driverName, dataSource, databaseName, "", 10, netBiosHostName, instanceName);
this.PutStringValueToCache(paramsPath, "DriverName", driverName);
this.PutStringValueToCache(paramsPath, "TargetDataSource", dataSource);
this.PutStringValueToCache(paramsPath, "ConnectionTimeout", 10);
return result;
} catch (ex) {
lastError = ex;
errorMessageColl.push("Connection to data source '" + dataSource + "' failed: " + ex.message);
}
}
} else {
var ipArr = (new VBArray(tcpIpSettings.ipSettings.Keys())).toArray();
var upc = new UniqueCollection();
for (var i = 0; i < ipArr.length; i++) {
if (!upc.exists(tcpIpSettings.ipSettings.item(ipArr[i])[0])) {
upc.put(tcpIpSettings.ipSettings.item(ipArr[i])[0]);
try {
dataSource = this.GetDataSource(targetName, tcpIpSettings.ipSettings.item(ipArr[i])[0]);
result = this.GetAndCheckConnection(driverName, dataSource, databaseName, "", 10, netBiosHostName, instanceName);
this.PutStringValueToCache(paramsPath, "DriverName", driverName);
this.PutStringValueToCache(paramsPath, "TargetDataSource", dataSource);
this.PutStringValueToCache(paramsPath, "ConnectionTimeout", 15);
return reslut;
} catch (ex) {
lastError = ex;
errorMessageColl.push("Connection to data source '" + dataSource + "' failed: " + ex.message);
}
}
}
if (!useFqdn) {
for (var i1 = 0; i1 < ipArr.length; i1++) {
var portArr = tcpIpSettings.ipSettings.item(ipArr[i1]);
for (var j1 = 0; j1 < portArr.length; j1++) {
try {
dataSource = this.GetDataSource(ipArr[i1], tcpIpSettings.ipSettings.item(ipArr[i1])[j1]);
result = this.GetAndCheckConnection(driverName, dataSource, databaseName, "", 10, netBiosHostName, instanceName);
this.PutStringValueToCache(paramsPath, "DriverName", driverName);
this.PutStringValueToCache(paramsPath, "TargetDataSource", dataSource);
this.PutStringValueToCache(paramsPath, "ConnectionTimeout", 10);
return result;
} catch (ex) {
lastError = ex;
errorMessageColl.push("Connection to data source '" + dataSource + "' failed: " + ex.message);
}
}
}
}
}
}
throw new Exception("Cannot connect to the target Sql Server instance. Connection log:\n" + errorMessageColl.join("\n"), lastError);
};
};
var Exception = function (message, innerException) {
arguments.callee.setAlias('Exception.constructor');
this.message = message;
this.innerException = innerException;
var parts = [];
var collectArguments = function (caller) {
parts.push('(');
for (var i = 0; i < caller.length; i++) {
if (typeof (caller[i]) !== 'undefined') {
parts.push(caller[i] === null ? 'null' : (typeof (caller[i].toString) === 'undefined' ? 'Object' : caller[i].toString()));
parts.push(',');
}
}
parts.pop();
parts.push(')');
parts.push(',\n');
};
var collectCallStack = function (caller) {
arguments.callee.setAlias('Exception.collectCallStack');
parts.push(caller.callee.getAlias ? caller.callee.getAlias() : 'anonymous');
collectArguments(caller);
var nextCaller = caller.caller;
if (nextCaller) {
collectCallStack(nextCaller);
}
};
collectCallStack(arguments);
this.callStack = parts.join('');
this.toString = function () {
arguments.callee.setAlias('Exception.toStringFull');
var toStringParts = [];
toStringParts.push(this.message);
toStringParts.push('\n');
if (this.innerException) {
if (this.innerException.number || this.innerException.description) {
toStringParts.push('Inner exception: \n');
if (this.innerException.number) {
toStringParts.push("Error Number : ");
toStringParts.push(this.innerException.number);
toStringParts.push('\n');
toStringParts.push("Error Code : ");
toStringParts.push(this.innerException.number & 0xFFFF);
toStringParts.push('\n');
toStringParts.push("Win32 Facility : ");
toStringParts.push((this.innerException.number >> 16 & 0x1FFF));
toStringParts.push('\n');
}
if (this.innerException.description) {
toStringParts.push("Error Description : ");
toStringParts.push(this.innerException.description);
toStringParts.push('\n');
}
}
}
toStringParts.push('Call stack:');
toStringParts.push(this.callStack);
return toStringParts.join('');
};
};
var OpsMgrAPI = function () {
arguments.callee.setAlias('MOMAPI.constructor');
var scriptAPI;
try {
scriptAPI = new ActiveXObject('MOM.ScriptAPI');
} catch (e) {
throw new Exception("Application cannot create MOM.ScriptAPI ActiveX object.", e);
}
this.ScriptAPI = scriptAPI;
this.ErrorReturnEmptyDiscovery = function (sourceId, managedEntityId) {
var discoveryData = this.ScriptAPI.CreateDiscoveryData(0, sourceId, managedEntityId);
discoveryData.IsSnapshot = false;
this.ScriptAPI.Return(discoveryData);
};
};
var Logger = function () {
arguments.callee.setAlias('Logger.constructor');
this.common = new Common();
this.messageType = {
error: 1,
warning: 2,
information: 4
};
var ADODB = function (connectionString, provider, connectionTimeout) {
arguments.callee.setAlias('ADODB.constructor');
if (!connectionString) {
throw new Exception("Connection string cannot be null or empty string");
}
this.Close = function () {
arguments.callee.setAlias('ADODB.Close');
try {
if (connection && connection.State != 0) {
connection.Close();
}
}
catch (e) {
throw new Exception("Can't close connection.", e);
}
};
};
var Registry = function () {
arguments.callee.setAlias('Registry.constructor');
var registry;
try {
registry = new ActiveXObject("WScript.Shell");
} catch (e) {
throw new Exception("Can't create ActiveXObject 'WScript.Shell'", e);
}
this.Write = function (key, value, type) {
arguments.callee.setAlias('Registry.Write');
try {
if (type) {
registry.RegWrite(key, value, type);
} else {
registry.RegWrite(key, value);
}
} catch (e) {
throw new Exception("Can't write value '" + value.toString() + "' by registry key '" + key.toString() + "'.", e);
}
};
this.Read = function (key) {
arguments.callee.setAlias('Registry.Read');
var value;
try {
value = registry.RegRead(key);
} catch (e) {
throw new Exception("Can't read value from registry key '" + key.toString() + "'.", e);
}
return value;
};
this.IsKeyExists = function (key) {
arguments.callee.setAlias('Registry.IsKeyExists');
var result;
try {
registry.RegRead(key);
result = true;
} catch (e) {
result = false;
}
return result;
};
this.Delete = function (key) {
arguments.callee.setAlias('Registry.Delete');
var value;
try {
value = registry.RegDelete(key);
} catch (e) {
throw new Exception("Can't delete registry key '" + key.toString() + "'.", e);
}
return value;
};
};
var WMIProvider = function (wmiNamespace, computerName) {
arguments.callee.setAlias('WMIProvider.constructor');
this.wmiNamespace = wmiNamespace;
this.computerName = computerName || ".";
var sWbemLocator;
try {
sWbemLocator = new ActiveXObject("WbemScripting.SWbemLocator");
} catch (e) {
throw new Exception("Cannot create 'WbemScripting.SWbemLocator' object.", e);
}
this.SWbemLocator = sWbemLocator;
var wmiService;
try {
wmiService = this.SWbemLocator.ConnectServer(this.computerName, this.wmiNamespace);
} catch (e) {
throw new Exception("Cannot connect to WMI of computer '" + this.computerName + "' by namespace '" + this.wmiNamespace + "'.", e);
}
this.WMIService = wmiService;
this.ExecQuery = function (query) {
arguments.callee.setAlias('WMIProvider.ExecQuery');
var result = null;
try {
result = this.WMIService.ExecQuery(query, "WQL", 0x0);
} catch (e) {
throw new Exception("Cannot execute WMI query '" + query + "'.", e);
}
return result;
};
};
var DBHelper = function () {
arguments.callee.setAlias('DBHelper.constructor');
this.EVENT_ID = 4211;
this.DatabaseState = {
ONLINE: 0,
RESTORING: 1,
RECOVERING: 2,
RECOVERY_PENDING: 3,
SUSPECT: 4,
EMERGENCY: 5,
OFFLINE: 6
};
this.common = new Common();
this.logger = new Logger();
this.IsRunningInstanceSQLServer = function (sqlInstanceName, computerName) {
arguments.callee.setAlias('IsRunningInstanceSQLServer');
var isRunning = false;
try {
var wmiProvider = new WMIProvider("root\\Microsoft\\SqlServer\\ComputerManagement13", computerName);
var sqlService = wmiProvider.ExecQuery("select state from SqlService WHERE (ServiceName = '" + this.common.EscapeWQLString(sqlInstanceName) + "' OR ServiceName = 'MSSQL$" + this.common.EscapeWQLString(sqlInstanceName) + "') and SQLServiceType ='1'");
if (sqlService.Count > 0) {
var e = new Enumerator(sqlService);
e.moveFirst();
var sqlServiceObject = e.item();
this.GetDatabaseExcludeList = function (instanceName) {
arguments.callee.setAlias('GetDatabaseExcludeList');
var databaseExcludeList = "";
try {
var opsMgrAPI = new OpsMgrAPI();
var registryKey = opsMgrAPI.ScriptAPI.GetScriptStateKeyPath(this.common.ManagementGroupID);
this.logger.LogDebug(this.EVENT_ID, "ScriptStateKeyPath: " + registryKey);
var registry = new Registry();
var databaseExcludeListKey = "HKEY_LOCAL_MACHINE\\" + registryKey + "\\" + instanceName + "\\DatabaseExcludeList";
if (registry.IsKeyExists(databaseExcludeListKey)) {
databaseExcludeList = registry.Read(databaseExcludeListKey);
databaseExcludeList = databaseExcludeList.replace(/(^\s+)|(\s+$)/g, "");
if (databaseExcludeList !== '*') {
var excludeDatabases = databaseExcludeList.split(",");
for (var i = 0; i < excludeDatabases.length; i++) {
excludeDatabases[i] = excludeDatabases[i].replace(/(^\s+)|(\s+$)/g, "");
}
databaseExcludeList = excludeDatabases.join(",");
}
}
} catch (e) {
throw new Exception("Can't read database exclude list from registry.", e);
}
return databaseExcludeList;
};
// Returns -1: If DB is not in AlwaysOn
// Returns 0: If DB is in AlwaysOn and replica allow connections is NO
// Returns 1: If DB is in AlwaysOn and replica allow connections is YES
this.AlwaysOnReplicaAllowConnections = function (aDbConnection, aDatabaseID) {
var query = " SELECT columns.id, " +
" CASE WHEN OBJECT_ID('sys.availability_replicas') IS NOT NULL THEN 1 ELSE 0 END AS HasAlwaysOn " +
" FROM master.sys.syscolumns columns where name = 'replica_id' and id = OBJECT_ID('sys.databases')";
var result = aDbConnection.ExecuteQuery(query);
if (!result.EOF) {
result.MoveFirst();
if (result("HasAlwaysOn").Value === 1) {
query = "SELECT d.name, d.database_id, drs.is_primary_replica AS db_is_primary_replica " +
", CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica " +
", CASE WHEN drs.is_primary_replica = 1 THEN ar.primary_role_allow_connections ELSE ar.secondary_role_allow_connections END AS role_allow_connections " +
", CASE WHEN drs.is_suspended = 0 THEN -1 ELSE suspend_reason END AS db_suspended_state " +
" FROM sys.databases as d " +
" JOIN sys.dm_hadr_database_replica_states drs ON drs.database_id = d.database_id " +
" JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id WHERE drs.is_local = 1 AND d.database_id = ?";
result = aDbConnection.ExecuteQueryWithParams(query, aDatabaseID);
if (!result.EOF) {
if (result("is_replica").Value === 1) {
if (result("role_allow_connections").Value <= 1) {
return 0;
} else {
if ((result("db_suspended_state").Value > 0) &&
(!result("db_is_primary_replica").Value || (result("db_is_primary_replica").Value && result("db_suspended_state").Value !== 5))) {
return 0;
}
return 1;
}
}
}
}
}
return -1;
};
};
var ServicesHelper = function () {
arguments.callee.setAlias('ServicesHelper.constructor');
this.EVENT_ID = 4211;
this.IsServiceUnavailable = "ServiceUnavailable";
this.logger = new Logger();
this.common = new Common();
this.GetServiceUnavailableRegistryKey = function (serviceName) {
arguments.callee.setAlias('GetRegistryKeyForService');
var opsMgrAPI = new OpsMgrAPI();
var registryKey = opsMgrAPI.ScriptAPI.GetScriptStateKeyPath(this.common.ManagementGroupID);
var key = "HKEY_LOCAL_MACHINE\\" + registryKey + "\\" + serviceName + "\\";
return key;
};
this.ReturnServiceStateToOpsMgr = function (serviceState) {
var opsMgrAPI = new OpsMgrAPI();
this.logger.LogDebug(this.EVENT_ID, "Return to OpsMgr state of service = " + serviceState);
var propertyBag = opsMgrAPI.ScriptAPI.CreatePropertyBag();
propertyBag.AddValue("ServiceState", serviceState);
opsMgrAPI.ScriptAPI.AddItem(propertyBag);
opsMgrAPI.ScriptAPI.ReturnItems();
};
};
//#Include File:SetSQL2016DBState.js
var SetSQL2016DBState = function () {
arguments.callee.setAlias('SetSQL2016DBState.constructor');
this.common = new Common();
this.ADOConnectionTimeout = 30;
this.SCRIPT_CONNECT_FAILURE_MSG = "Could not connect to SQL";
this.EscapeDBName = function (strValue) {
return strValue.replace(/]/g, "]]");
};
this.PrintError = function (error) {
WScript.Echo("============================= BEGIN SCRIPT ERROR =================================\n");
WScript.Echo("Script Name : " + WScript.ScriptName);
var currErr = null;
if ((error instanceof Exception) && error.innerException != null) {
currErr = error.innerException;
}
else if (error instanceof Error) {
currErr = error;
}
if (currErr != null) {
if (typeof currErr.name !== 'undefined') {
WScript.Echo("Error Type : " + currErr.name);
}
WScript.Echo("Error Number : " + currErr.number);
WScript.Echo("Error Code : " + (currErr.number & 0xFFFF).toString());
WScript.Echo("Win32 Facility : " + (currErr.number >> 16 & 0x1FFF).toString());
if (typeof currErr.source !== 'undefined') {
WScript.Echo("Error Source : " + currErr.source);
}
WScript.Echo("Error Description : " + currErr.description + "\n");
}
else {
WScript.Echo(error.toString());
}
WScript.Echo("============================== END SCRIPT ERROR ==================================\n");
};
this.FailScript = function (error, sMessage) {
var opsmgrAPI = new ActiveXObject("MOM.ScriptAPI");
opsmgrAPI.LogScriptEvent("SetSQL2016DBState.js", 4211, 1, sMessage + ". " + error.toString());
};
this.Launch = function (args) {
if (args.Length !== 6) {
WScript.Quit();
}
var connectionString = args(0);
var tcpPort = args(1);
var databaseName = args(2);
var databaseState = args(3);
var targetComputer = args(4);
var instanceName = args(5);
Home
FRA 
Show References: