Find SQL Server Instances Across Your Network Using Windows PowerShell


As more and more SQL Server instances get installed in my organization by different departments in both servers and desktops, I need to find out where they are as I’ll be responsible for managing and administering these instances. How do I list out all the SQL Server instances across my entire organization without even knowing where they are?


You may have probably heard about the term SQL Server sprawl – the uncontrolled and uncoordinated deployment of different SQL Server instances of different editions. It’s easy to track Enterprise and Standard Edition instances since we regularly do audits for licensing purposes. But, then, there are third-party and line-of-business applications that use SQL Server Express as their backend database that get installed by people from different departments of your organization. Who knows whether these SQL Server instances sit on their desktops or on servers allocated by the IT department? And the worst part is, we DBAs get charged for administering them.

You may have used tools like SQL Ping and a bunch of other stuff in the past, some of which are highlighted in this SQL Server Central article, to discover SQL Server instances in your network. The good news is, your nightmare is about to end. Since Windows PowerShell runs on top of the .NET Framework, it can leverage any .NET Framework-based assemblies available on the machine that it is running on top of (this includes having SQL Server 2005 Client Tools with Server Management Object (SMO) to administer SQL Server instances). For this particular case, you don’t even need to have SMO installed on your client machine to enumerate SQL Server instances across your organization as the .NET Framework already includes ADO.NET 2.0. ADO.NET contains the System.Data.Sql namespace which contains classes that support SQL Server-specific functionality. One of these classes is the SqlDataSourceEnumerator which can be used for enumerating all available instances of SQL Server within your local network, even if the SQL Server service is stopped. The SqlDataSourceEnumerator class has the Instance property that gets an instance of the SqlDataSourceEnumerator, which can then be used to retrieve information about available SQL Server instances. A method called GetDataSources retrieves a DataTable containing information about all visible SQL Server instances in the network from SQL Server 2000 onwards.

And this really highlights the power and simplicity of Windows PowerShell. This task can be done in a single line of code, using the information mentioned above.


Command : [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

Example :

PS C:\Users\vkarthikeyana> [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

ServerName        InstanceName         IsClustered              Version
———-             ————               ———–                 ——-
IN001VS0098      CONFIGMGRSEC           No                     11.0.6020.0
IN001VM0009     CITRIX_METAFRAME     No                     10.0.2531.0
IN001VS0012      CITRIX_METAFRAME     No                      10.0.2531.0
IN001VS0025      CITRIX_METAFRAME     No                      10.0.2531.0
IN001VM0023     AUTODESKVAULT         No                       12.0.2000.8
IN001VS0032       INSQL139                     No                       10.50.4000.0
IN001VS0023      CITRIX_METAFRAME      No                     10.0.2531.0





I have copied the above data for document purpose.


Source url:


Another tool :

which does the same job and in this site lot of free tools available.




Blank default gateway may occur after configuring Static IP address

Error: Blank default gateway may occur after configuring Static IP address or if you configure IP address in your NIC by next restart of VM or the guest OS the gateway will disappear.

Cause:  When removing a network driver using a vendor-provided uninstallation program or through Device Manager, previously entered static IP address information may not be removed correctly from the registry. After reinstalling the network driver and reentering the same static IP address, the presence of previous registry information prevents the newly entered address information from being saved correctly.

For VM if you upgrade VMtools or any hardware version this error will occur to some of the VM.


Open a command prompt under the administrator context and run the following command TWICE, using the desired static address information. For example:

netsh interface ipv4 set address “Local Area Connection” static

Note: This first command may return an error “Object already exists”
netsh interface ipv4 set address “Local Area Connection” static


Note: This command should succeed with no errors

To Find iDRAC IP Address on ESX Host

Scenario : To identify iDRAC IP address of ESXi host through cli. This would be useful when you forget the IP Address of idrac.



There is no VMware command to find the IP address of iDRAC. But we can get the information from local CIM providers through ESXi shell query.


# enum_instances OMC_IPMIIPProtocolEndpoint root/cimv2 | grep IPv4Address

IPv4Address =




dbo.VPX_HOST_VM_CONFIG_OPTION & ‘VIM_VCDB’ because the ‘PRIMARY’ filegroup is full

Vcenter service stops frequently or Unable to login through VIClient.

Event-ID: 1105

Could not allocate space for object ‘dbo.VPX_HOST_VM_CONFIG_OPTION’.’PK_VPX_HOST_VM_CONFIG_OPTION’ in database ‘VIM_VCDB’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

RootCause: This error due to limitation size in log file growth in SQL and we need to clear unwanted files in the log file to reduce the size.


1.Download and install the Microsft SQL Server Management Studio Express.

2. Connect to ServernameSQLEXP_VIM and login with Windows Authentication.








3. Expand databases > Expand VIM_VCDB > Expand Tables > Open table dbo.VPX_PARAMETER:








4. Modify event.maxAge to a be 30 > Modify event.maxAgeEnabled to value of true








5. Modify task.maxAge to be 30 > Modify task.maxAgeEnabled to a value of true















6. Then run the built in stored procedure: from the VCDB > Programmability > Stored Procedures

Right Click dbo.cleanup_events_tasks_proc and click “Execute Stored Procedure”








7. This will purge the data from tables: vpx_event, vpx_event_arg and vpx_task based on the date specified for maxAge.










8. When this has sucessfully completed, close the SQL Management Studio down > Then start the VMware Virtual Centre Server service

9. If you are getting error on the Step 7 (“Right Click dbo.cleanup_events_tasks_proc and click “Execute Stored Procedure””)








10. Increase the log file size as mentioned in the below screen shot

a. Right click on the VIM_VCDB and go to properties











b. Select the log “File type” option as highlighted






c. Extend the log file growth as mentioned







d. Now the solved Output of the Step7. “”Right Click dbo.cleanup_events_tasks_proc and click “Execute Stored Procedure””









Ref article:






Event log contents by email on an event log trigger

Scenario: You need to get an alert email for specific events occurred on the critical / production server.


Step 1:

Identify the event which you need to trigger






Step 2: Go to Task scheduler and select the Event viewer tasks & create a new task.







Step 3:  Name the task and select “Run whether user logged on or not” & “Run with highest privilges”








Step 4: Go to triggers and select the option begin the task “On an Event” & select the option as per screenshot







Go to actions and need to perform two actions

  1. Query a script which will create a copy of the required event in text and make an action in the scheduler.

Script to run : save the below as Query.cmd

del %temp%\Logonfailure.txt

wevtutil query-events Security /rd:true /format:text /q:”Event[System[(EventID=4625)]]” > %temp%\Logonfailure.txt








2. Create another action for sending email with the required recipient and attachment of the query log








Output will be









The following are the steps involved for installing squid proxy as a transparent mode and various troubleshooting which I undergone during the installation.












The following are the prerequisite in RHEL 6.7 before installing squid are

  1. Disable selinux by #vim /etc/selinux/config
  2. Enable IPforward=1 in /etc/sysctl.conf
  3. Enable EPEL repo

a. #cd /root/



b. #rpm -Uvh epel-release-6.8.noarch.rpm

c. Create squid repo by the following syntax

d. #cd /etc/yum.repos.d

e. #vi  SQUID.repo

   Enter or insert the following text in the squid.repo file


name=Squid repo for CentOS Linux – $basearch

#IL mirror





4. Do #yum update

 Installation of SQUID

#yum install perl-Crypt-OpenSSL-X509 (We need to install this application successful else https site will not work).

#yum install -y squid

Install squid helper from the url:

# wget

# yum install -y squid-helpers-3.5.19-1.el6.x86_64.rpm

Now we initialize SQUID ssl_db directory, in the following syntax

#/usr/lib/squid/ssl_crtd -c -s /var/lib/ssl_db

Assign ownership for squid

#chown -R squid.squid /var/lib/ssl_db

Edit squid.conf file in /etc/squid/squid.conf and the configuration

Define your local source network by

acl localnet src

Enable and define the ports in squid.conf file

acl SSL_ports port 443

acl Safe_ports port 80 # http

acl Safe_ports port 21 # ftp

acl Safe_ports port 443 # https

acl Safe_ports port 70 # gopher

acl Safe_ports port 210 # wais

acl Safe_ports port 1025-65535 # unregistered ports

acl Safe_ports port 280 # http-mgmt

acl Safe_ports port 488 # gss-http

acl Safe_ports port 591 # filemaker

acl Safe_ports port 777 # multiling http


http_access deny !Safe_ports

http_access deny CONNECT !SSL_ports

http_access allow all

http_port 3130

http_port 3128 intercept

https_port 3129 intercept ssl-bump generate-host-certificates=on

dynamic_cert_mem_cache_size=4MB cert=/etc/squid/ssl_cert/myca.pem key=/etc


#always_direct allow all

ssl_bump server-first all

#sslproxy_cert_error deny all

#sslproxy_flags DONT_VERIFY_PEER

sslcrtd_program /usr/lib/squid/ssl_crtd -s /var/lib/ssl_db -M 4MB

sslcrtd_children 8 startup=1 idle=1

coredump_dir /var/spool/squid

# Add any of your own refresh_pattern entries above these.

refresh_pattern ^ftp: 1440 20% 10080

refresh_pattern ^gopher: 1440 0% 1440

refresh_pattern -i (/cgi-bin/|\?) 0 0% 0

refresh_pattern . 0 20% 4320

Generate Certificate for SQUID using OpenSSL

go to SQUID directory, create the certificate folder and generate the keys:

#mkdir /etc/squid/ssl_cert

#chown -R squid.squid /etc/squid/ssl_cert

#cd /etc/squid/ssl_cert

Generate ssl certificate by the following syntax

#openssl req -new -newkey rsa:1024 -days 1365 -nodes -x509 -keyout myca.pem -out myca.pem

For Windows clients the key :

#openssl x509 -in myca.pem -outform DER -out myca.der

Now enable squid on all run levels and start the squid service

#chkconfig squid on

#/etc/init.d/squid start

Redirect / Accept HTTP and HTTPS traffic from router/Firewall to Proxy

#iptables -t nat -A PREROUTING -p tcp -s –dport 80 -j DNAT –to

#iptables -t nat -A PREROUTING -p tcp -s –dport 443 -j DNAT –to

Save the IPtables configuration

#/etc/ini.d/iptables save

On Windows Client Configuration

We need to copy the /etc/squid/ssl_cert/myca.der file to our

Windows clients

For Internet Explorer :

Tools -> Internet Options -> Content -> Certificates Click on

Import , select myca.der file , make sure that you import to

Root Trusted Certificates

For Mozilla Firefox :

Tools-> Options-> Advanced -> Certificates – > View Certificates


(x) Trust this CA to identify websites

(x) Trust this CA to identify email users

(x) Trust this CA to identify software developers

Click OK you are done.

NOTE : If the main certificate expires for SQUID , and you generate

a new one , don’t forget to delete the old certificates ,

/var/lib/ssl_db/certs also empty the file /var/lib/ssl_db/index.txt, and set the number inside the file /var/lib/ssl_db/size to 0

In our infrastructure the bind service not required for name resolution.


To my experience squid with transparent proxy for http and https is successful with the squid version 3.5.19 and the lower version of squid seems to be some bugs.

Below are the bugs which we come across with the older versions of squid in the /var/log/squid/access.log

1301567341.317 23434 TCP_MISS/200 957 POST http://by2msg3010710.by2.gateway.edg…y/gateway.dll? – DIRECT/ text/html
1301567341.896 531 TCP_MISS/200 1056 POST http://by2msg3010710.by2.gateway.edg…y/gateway.dll? – DIRECT/ text/html
1301567344.042 770 TCP_MISS/200 1117 POST – DIRECT/ application/x-javascript
1301567347.991 414 TCP_MISS/200 316 POST – DIRECT/ text/plain
1301567351.115 494 TCP_MISS/200 316 POST – DIRECT/ text/plain
1301567352.986 412 TCP_MISS/200 316 POST – DIRECT/ text/plain
1301567354.288 555 TCP_MISS/200 6079 GET – DIRECT/ text/html
1301567354.516 37 TCP_MISS/302 683 GET – DIRECT/ text/html
1301567354.773 254 TCP_MISS/204 367 GET – DIRECT/ text/html
1301567354.842 161 TCP_MISS/302 856 GET – DIRECT/ text/html
1301567355.165 320 TCP_MISS/204 413 GET – DIRECT/ text/html
1301567355.234 456 TCP_MISS/204 322 GET – DIRECT/ text/html