Tag Archives: WMI

CScript and RunAsAdmin

Posted on by

I had written a script a while back which would set the TCP/IP port for a SQL Server instance. Before you start throwing brick bats at me…. Powershell was not in existence in those days and yes…. doing the same tasks in Powershell is much easier! Phew… Now let me get back to my story!

One of my colleagues told me that the script was failing due with the following error message:

SQL_PortChange.vbs(52, 1) Microsoft VBScript runtime error
: Object required: ‘objOutParams‘

Now the above error is not the most intuitive of error messages considering the fact the object is being assigned a value in my VBscript. With a little bit of troubleshooting, we figured out that the RunAs Administrator (it can really catch you off-guard) option was not used to launch the command prompt.

So when running such VBscripts, do not forget to use RunAs Administrator option!

Now let us look under the hood a bit! I naturally was curious as to why the access denied message was not being thrown back to the user. I used Process Monitor to trace the registry activity of cscript.exe and wmiprvse.exe which actually works in the background to perform the tasks mentioned in the VBscript. I found that there were Access Denied messages in the Process Monitor trace but they were not being bubbled up to the user (see screenshot below)!

image

As you can see above, the access denied was on the SQL Server TCP/IP registry key and the WBEM keys. Since the registry key could not be read, the object was not created. And which is why we got the weird error listed above.

I thought this would be a good issue to blog on in case some one else ran into a similar issue!

How to find out BINN folder path using WMI

Posted on by

A reply to a Tweet on #sqlhelp prompted me to look this up. The question was on finding out the SQL Server BINN folder path. This can be done using WMI in the following manner for SQL Server 2008:


strComputer = "."

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10")

Set colItems = objWMIService.ExecQuery( _

"SELECT * FROM SqlService WHERE SQLServiceType = 1 and ServiceName = 'MSSQLSERVER'",,48)

For Each objItem in colItems

Wscript.Echo "-----------------------------------"

Wscript.Echo "SqlService instance"

Wscript.Echo "-----------------------------------"

Wscript.Echo "BinaryPath: " & MID(objItem.BinaryPath,1,InStr(objItem.BinaryPath,"sqlservr.exe")-1)

Next

The SQL Server 2008/R2 WMI namespace has visibility for SQL Server 2005 also. So the above snippet of code can be used to retrieve the BINN path (PathName property contains the fully qualified path to the service binary file that implements the service) for the SQL instance. You can change the SQLServiceType to a different value to get the Binary Path folder for other services as well. The value is the above code is set to 1 for the Database Engine. The ServiceName parameter can be used to filter down the results.

For a named instance, you would need to change the service name to MSSQL$INSTA if your instance is called INSTA.

The same is possible through the much talked about Powershell as well.

Other ways to do this would be to use the xp_instance_regread XSP which is not recommended as it is an undocumented command. An example is show here.

Thanks to @afernandez for pointing out another way through a CLR TVF.

[Blog Reference]: Listing SQL instances in your environment

Posted on by

I have had multiple questions on how to enumerate SQL instances in your complete environment to run maintenance operations from a centralized environment. There are multiple tools which implement one of the methods or a variation of the methods described in the blog post below.

I keep referring people to this blog post. So, I thought I would put a referral of the same on my blog so that I can find it easily the next time.

Jonathan Sayce [Blog] has shown in his blog post what are the different methods to achieve this.

http://sqlblogcasts.com/blogs/jonsayce/archive/2008/02/10/programatically-listing-sql-servers.aspx

How to change the TCP/IP port for a SQL Server instance

Posted on by

I wrote this script sometime back. I thought it would be a good idea to share this script. The background for writing this script was there were a bunch of SQL Server installations in the environment with the same instance names but on different boxes. The installations were pushed through programmatically and company policy was set in place which needed the TCP/IP port to be changed to a different port other than the default port i.e. 1433. The DBA obviously wouldn’t be too happy sitting and using SQL Server Configuration Manager on multiple servers doing the same monotonous task. The ask was to write a script which could be pushed to all the servers so that the port on which the SQL instance was listening on was modified programmatically.

This script changes the PORT number for a named instance of SQL Server 2008 called KATMAI to 1434. The namespace used here are:

SQL Server 2005: \root\Microsoft\SqlServer\ComputerManagement

SQL Server 2008: \root\Microsoft\SqlServer\ComputerManagement10

So if you wanted to use the script for SQL Server 2005, you would need to change the namespace to the one for SQL Server 2005. The other variables that come into play for this script are the Instance Name and the Port number:

‘Specify the instance name if any. Default is MSSQLSERVER.
‘Assumption is that we are running this for the default instance.
‘For a named instance, only provide the instance name. Eg: For a named instance LABDC\INST1, the parameter below would be strInstance = "INST1"
‘For a cluster, we need to run this script on the active node.

strInstance = "KATMAI"

‘Specify the new PORT

strPort = "1434"

So, you would have to modify the above variables for the correct Instance Name and Port number.

The script then makes use of the ServerNetworkProtocolProperty class methods to set the TCP port value.

If you need to extend this script, you can do so by creating an Array for the list of Servers in your environment and running the entire code in the script in another WHILE loop.

Another customized implementation could be if you want to change the port number for multiple instances with incremental values of Port Numbers. This can be done by querying the same namespace for getting the SQL instances on the box using SqlService. A sample WMI script is provided in the blog post below.

HOW TO DETECT SQL SERVER INSTANCES / FEATURES INSTALLED ON A MACHINE

Once you have the Instance Names, then you can again create a loop with the Port Number variable being incremented and execute the function to set the TCP/IP Port number.

Download the script from here

Reference:

How to- Configure a Server to Listen on a Specific TCP Port

Happy Automation!! 🙂

Retrieving TCP/IP Properties using WMI

Posted on by

This can be done using the following script 

******* START OF SCRIPT ******** 

strComputer = "." 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement") 

Set colItems = objWMIService.ExecQuery( _ 

"SELECT * FROM ServerNetworkProtocolProperty where InstanceName='MSSQLSERVER' and ProtocolName = 'Tcp'",,48) 

For Each objItem in colItems 

Wscript.Echo "-----------------------------------" 

Wscript.Echo "ServerNetworkProtocolProperty instance" 

Wscript.Echo "-----------------------------------" 

Wscript.Echo "InstanceName: " & objItem.InstanceName 

Wscript.Echo "IPAddressName: " & objItem.IPAddressName 

Wscript.Echo "PropertyName: " & objItem.PropertyName 

Wscript.Echo "PropertyNumVal: " & objItem.PropertyNumVal 

Wscript.Echo "PropertyStrVal: " & objItem.PropertyStrVal 

Wscript.Echo "PropertyType: " & objItem.PropertyType 

Wscript.Echo "PropertyValType: " & objItem.PropertyValType 

Wscript.Echo "ProtocolName: " & objItem.ProtocolName 

Next

******* END OF SCRIPT ******** 

This uses the class ServerNetworkProtocolProperty. Similarly, you use the above script and modify it to obtain information for the other server network protocols.