April 19, 2011 at 1:40 pm
garshin_alexander (4/19/2011)
powershell script works fine after upgrade to version 2.0sqlcmd -L list only brodcasting servers ,while this solution simply try to access every station that is up
and running
Woohoo!:-D
There is an exception to every rule, except this one...
April 19, 2011 at 1:45 pm
K. Brian Kelley (4/19/2011)
jeff.mason (4/19/2011)
Of course, all of the solutions listed assume that you have a domain admin-level account that can go out and do this searching. If you work in a locked down environment like I do, where we even have to fight for local admin on the database servers we administer, so segmented and controlled is security, none of these help. In such an environment, there unfortunately is not (at least that I have found) a practical way to locate every SQL Server on site.Not exactly. In this kind of environment the DBAs shouldn't be running the discovery. One of your security folks should be. In our case we had a process run on a schedule as a domain admin level account. It put its results in a folder accessible by the DBAs.
The problem we have is that 1) we are the ones who need the info, and they don't know what they are seeking and 2) getting the AD team to do this sort of thing is like pulling teeth because they get nervous when you need a domain admin level access (very few things are that high). And when they do try it, they don't know SQL and we run into problems. Any way you go about it, this is a tough thing to do in a huge multi-domain, locked down environment.
April 19, 2011 at 2:20 pm
jeff.mason (4/19/2011)
The problem we have is that 1) we are the ones who need the info, and they don't know what they are seeking and 2) getting the AD team to do this sort of thing is like pulling teeth because they get nervous when you need a domain admin level access (very few things are that high). And when they do try it, they don't know SQL and we run into problems. Any way you go about it, this is a tough thing to do in a huge multi-domain, locked down environment.
Then have your security team use port scanning with tools like Nessus + nmap or Qualys. If it's not listening on the network, it's harder to compromise. If it is listening on the network, those tools can fingerprint SQL Server.
Basically what you're saying here is it's not a technical issue. It's an organizational/political issue. I've seen military grade environments where these scans are permitted to occur because the folks involved have talked and know what needs to be done. Unfortunately, if folks aren't talking and understanding, no technical solution is going to solve the problem. So you're right that no tool will work, because a tool isn't going to overcome communications/turf/lack of knowledge issues.
K. Brian Kelley
@kbriankelley
April 20, 2011 at 7:30 am
HI,
Very interesting script, I am testing it in our test environment, however when running the last script within SSMS I get the following error:
Msg 14234, Level 16, State 1, Procedure sp_verify_subsystem, Line 21
The specified '@subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems).
Am I doing something wrong?
Thanks in advance for any assistance.
April 20, 2011 at 7:49 am
t.holmes (4/20/2011)
HI,Very interesting script, I am testing it in our test environment, however when running the last script within SSMS I get the following error:
Msg 14234, Level 16, State 1, Procedure sp_verify_subsystem, Line 21
The specified '@subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems).
Am I doing something wrong?
Thanks in advance for any assistance.
When it creates the job step it's looking for "PowerShell" as the type. If it's not an option, then try it on a SQL2008 box.
There is an exception to every rule, except this one...
April 28, 2011 at 12:21 pm
I do not get all my SQL Servers when I run this command. For example, it returns the DEV-Arms server... but not the Arms server. Any information would be greatly apprecitated.
April 28, 2011 at 12:22 pm
I was restricted to to one subnet also. Did you write a PowerShell script to get around this?
May 5, 2011 at 3:20 pm
The article is great and everything ran the first time without any problems. However, I only received a list of 5 computers back. We have hundreds of sql servers on several domains. Is there something I may need to "tweek" to get it to cross domains. Is it possible that I have both the crossing domain and subnet issue?
Thanks
May 6, 2011 at 9:18 am
What is a minuim security needed to list all the servers on domain ?
LAN admins don't want to shedule execution of this script with domain admin account.
May 9, 2011 at 7:05 am
garshin_alexander (5/6/2011)
What is a minuim security needed to list all the servers on domain ?LAN admins don't want to shedule execution of this script with domain admin account.
A security ID that can read a list of services in all computers/servers, I'm fairly certain has be domain admin account.
There is an exception to every rule, except this one...
May 9, 2011 at 7:06 am
lonny.lappin (5/5/2011)
The article is great and everything ran the first time without any problems. However, I only received a list of 5 computers back. We have hundreds of sql servers on several domains. Is there something I may need to "tweek" to get it to cross domains. Is it possible that I have both the crossing domain and subnet issue?Thanks
I am currently trying to resolve this issue, just a fyi.
There is an exception to every rule, except this one...
May 9, 2011 at 12:53 pm
Ignacio A. Salom Rangel (4/19/2011)
Maybe you can add it on your next script 😉
Just an update, I have rewritten the script to find all sql servers on a particular subnet that can be updated to a different subnet. But, it's one at a time :pinch:. Change the subnet numbers (198.168.1) to match your environment.
replace:
if ($_ -match "\\\\(\S+)") {$computers += $matches[1]}
}
$computers |? {(get-service -computername $_ |% {$_.name}) -match "sql"}
with:
1..254 | where {test-connection 198.168.1.$_ -count 1 -ea silentlycontinue} | ? {try{(get-service -ComputerName 198.168.1.$_ -ErrorAction `
"silentlycontinue" | %{$_.name}) -match "sql"}catch{}} | %{[system.net.dns]::gethostbyaddress("198.168.1.$_").hostname}
Will continue to work on scanning a larger subnet range when I can.
There is an exception to every rule, except this one...
May 10, 2011 at 7:45 am
Ok, here is a PS script that is a bit more friendly for multiple subnets. The only downside here is that you will need to know (or get a list from the network admins) all subnets.
$destserver = "destserver"
$destDB = "SQL_Names"
$destTable = "FoundSQLServers"
###########################################################################################
function Set-FoundSqlServers
{
param($ComputerName)
$Query = "INSERT INTO $destTable (SQLServerName) VALUES ('$($ComputerName)')"
$conn = new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};Database={1};Integrated Security=True;" -f $destserver,$destDB
$conn.ConnectionString = $ConnectionString
$conn.Open()
$cmd = new-object system.Data.SqlClient.SqlCommand($Query,$conn)
[void]$cmd.ExecuteNonQuery()
$conn.Close()
}
###########################################################################################
function Find-Computers
{
$subnets = "198.168.4.","198.168.5.","198.168.6."
foreach($subnet in $subnets)
{
1..254 | where {test-connection $subnet$_ -count 1 -ea silentlycontinue} | ? {try{(get-service -ComputerName $subnet$_ -ErrorAction `
"silentlycontinue" | %{$_.name}) -match "sql"}catch{}} | %{[system.net.dns]::gethostbyaddress("$subnet$_").hostname}
}
}
############################################################################################
Find-Computers | foreach-object {Set-FoundSqlServers $_}
Please let me know how this works out for you.:satisfied:
There is an exception to every rule, except this one...
July 25, 2013 at 1:06 am
Ignacio A. Salom Rangel (4/19/2011)
I like the script. The one thing I don't like is that you also get computers that have services like the "SQL Server VSS Writer".
You will just need to modify the search string.
I hava a similar powershell script, and my search is in a function
function isSqlInstalled ([String] $svrAdd) {
IF (@(get-service -cn $svrAdd | ? {$_.name -like "MSSQL*"}).Count -gt 0) {
return $True
}
return $false
}
July 25, 2013 at 6:40 am
I have a similar powrshell script that I run from cmd line on an adhoc basis.
I get round the subnet issue like this
Top of script
param (
[switch]$help,
[string]$filename = {},# Name of the SQL Server host.
[string]$instanceName = {},# Name of the SQL Server instance.
[string]$S = {},# Subnet range
[string]$H = {},# Host range
[string]$I = {},# Host range
[String]$F=0, # scan from. default scan full range
[String]$t=255 # scan to.
)
Here is the bulk of the code - I have a set of library functions that get included at the top.
Hopefully its clear what it is doing
function list ([String] $IP) {
#Write-host "Scanning $ip" -foreground "GREEN"
if (Test-Connection $IP -Quiet -Count 1){
#$OS = whatOS $IP
$serverInfo = getServerInfo $IP
if ($serverInfo -ne "Unknown Server"){
if (isSqlInstalled $IP){
$Hostname = getHostName $IP
$instances = getSQLInfo $IP
foreach ($ins in $instances){
if ($ins.port -gt 0){
$port=$ins.port
}else{
$port=1433
}
$svrConn =getConnection "$Hostname\$($ins.name),$port" "master"
try {
$svrConn.open()
#write-host "connected to $Hostname\$($ins.name),$port" -foreground "blue"
"$Hostname\$($ins.name),$port¬$($serverInfo.Processors)¬$($serverInfo.LogicalProcessors)" | out-file "connected.txt" -append
$svrConn.close()
}catch{
#write-host "Conn failed to $Hostname\$($ins.name),$port" -foreground "red"
"$Hostname\$($ins.name),$port" | out-file "failed.txt" -append
}
}
}
}
}
}
function scanTxt ([string] $file){
foreach ($ip IN get-content($file)) {
#scan $ip
list $ip
}
}
function scanHosts ([string] $range="172.23.32") {
#1..255 | %{
$F..$T | %{
$IP=$range+".$_"
#scan $ip
list $ip
}
}
function scanSubnet ([string] $range="172.23"){
#0..255 | %{
$F..$T | %{
$ip=$range+".$_"
scanHosts $ip}
}
#main code
write-host "Currently Only listing"
if ( $help ) {
write-host "Usage:"
write-host "host scan -H xxx.xx.x"
write-host "subnet scan -S xxx.xx"
write-host "IP scan -I xxx.xx.xx.xx"
write-host "range from scan -F x"
write-host "range to scan -T x"
exit 0
}
if ( $filename.Length -gt 0 ) {
scanTxt $filename
}
#subnet range i.e 172.23.x
if ( $S.Length -gt 0 ) {
scanSubnet $S
}
#host scan i.e 172.23.1.x
if ( $H.Length -gt 0 ) {
scanHosts $H
}
if ( $I.Length -gt 0 ) {
#scan $I
list $I
}
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply