SQL Server Discovery Tools and Scripts
Each time I start a new contract, the first thing I do is search for all SQL Servers across any network/subnet to which I have access. While the documentation I get is usually paltry or non-existent. When it does exist, it's usually only for the production SQL Servers. Sometimes, most production SQL Servers aren't even documented; one of my clients only had 20 documented servers, and I found over 60 (including SQL Express instances, of course.)
So here I am again, starting a new contract and I'm out looking for SQL Discovery Tools. Ultimately, here are the 5 I ended up finding and using, in no particular order:
Idera SQL Discovery is a really great tool within the Idera SQL Toolbox. That one's good and , which has been in beta for years.
A new tool I discovered this time around is the Microsoft Assessment and Planning Toolkit. Free (as in beer), of course.
Next is a quick lil script that grabs all SQL Servers in Active Directory that contain the word "SQL"
import-module activedirectory
get-adcomputer -filter "Name -like '*SQL*'" |select Name
Next is a more thorough script written by Colin Smith which grabs a list of servers within a text file and probes their services. I created the list with the script above, though I replaced "*SQL*" with "*Server*"
#####################################################################################
## Author: Colin Smith
## Script: Get_Intstance_names.ps1
## Purpose: This script will read in a file of hotnames that has been
## Provided of servers with SQL Server running on them. This
## script will then look at the services on that host to find
## the instance name if the instance is named. If the instance
## is a default instance the script will also report that.
#####################################################################################
$Servers = get-content "C:\servers.txt"
echo "Server, Instance" >> "C:\sqltab.txt"
foreach ($server in $servers)
{
$instances = Get-WmiObject -ComputerName $server win32_service | where {$_.name -like "MSSQL*"}
if (!$?)
{
echo "Failure to connect on $server" >> "C:\failures.txt"
echo "Failure to connect on $server"
}
else
{
$instancenames = @()
foreach ($name in $instances)
{
if (($name.name -eq "MSSQLSERVER") -or ($name.name -like "MSSQL$*"))
{
$instancenames += $name.name
}
}
foreach ($iname in $instancenames)
{
echo "$server, $iname" >> "C:\sqltab.txt"
echo "$server, $iname"
}
}
}
Next up, I'd like to investigate the SQL Server Active Directory Helper service and see what that's all about.. then get all these SQL Servers registered.
Patterns and Practices: A Guide to Claims-Based Identity and Access Control – Free ebook.
While I usually like conversation-style technical books (think: Manning's In Action series and many Wrox books), Microsoft's Patterns and Practices series is one of my favorite even though they feel so formal. Recently, my buddy Buck Woody posted a link to the freely available Patterns and Practices book for A Guide to Claims-Based Identity and Access Control, Second Edition.
Courtesy of Avkash Chauhan's Blog
It's the same book that can be purchased at Amazon.com for 23 dollars (make you holler.)
PowerShell Workaround: “Replicate Directory Changes” Permissions in AD Required for SharePoint 2010 Profile Syncs
According to the SharePoint 2010 Communities FAQ, Microsoft is burdening SharePoint 2010 Administrators with new requirements to obtain Active Directory accounts with "Replicate Directory Changes" permissions because...
In order to interrogate AD about “what has changed since time xyz”, we need the replicate-directory-changes permissions on partitions being synchronized, for example the domain partition being synchronized.These permissions are needed in particular to be able to read data within the deleted objects container of the partition. Standard users do not have permissions to read the content of this container, and we cannot simply grant rights over that container to the synchronization account.
While this may be the cleanest way to go about things, it's an unfortunate requirement for SharePoint admins within organizations which understandably frown upon making such concessions for service accounts, especially when the change wasn't required for SharePoint 2007 farms.
Microsoft's recommended architecture is to to use the published information from a centralized farm but some SharePoint administrators are on their own and do not have that luxury. I encountered this issue in a lab environment and was able to circumvent it by writing a quick and dirty script to add new users from AD and sync all SharePoint users with AD. This script only addresses one site collection and does not drill down to its subsites. Run it as often as you would run a regular sync.
# SharePoint <-> AD Group Sync
# netnerds.net
#
# What it does:
# Uses LDAP to get a list of users from a specific group in AD (in this case the "All Finance Users" group)
# Enables "Allow Unsafe Updates" for the given Site Collection then returns it to its previous state.
# Adds AD Users (but ignores nested AD groups) to the specified SharePoint Group
# Syncs all Site Users with Active Directory. This includes name changes, etc.
# 1,2,3 Go
$ldappath = "LDAP://CN=All Finance Users,OU=Finance,DC=icanhas,DC=net"
$spgroupname = "Icanhas Intranet Members"
$sitename= "http://sharepoint"
$domain = $env:USERDOMAIN
$adgroup = [ADSI]($ldappath)
$spsite = SPSite($sitename)
$rootweb = $spsite.rootweb
$spgroup = $rootweb.Groups[$spgroupname]
$allowUnsafeUpdates = $spsite.AllowUnsafeUpdates
$spsite.allowUnsafeUpdates = 1
foreach ($memberDN in $adgroup.member) {
$member = [ADSI]("LDAP://$memberDN") | where {$_.properties.objectcategory -match "CN=Person"}
if ($member){
$username = $domain + "\" + $member.sAMAccountName
$spsiteuser = $rootweb.EnsureUser($username)
$spUser = $rootweb.AllUsers[$spsiteuser]
$spgroup.AddUser($spsiteuser)
}
}
$spsite.allowUnsafeUpdates = $allowUnsafeUpdates
$rootweb.dispose()
$spsite.dispose()
Get-SPUser –Web $sitename| Set-SPUser –SyncFromAD
Note the AD Sync on the last line. Surprisingly enough, PowerShell's SyncFromAD works without explicitly allowing Replicate Directory Changes. To test this, first I tried a domain account with regular privileges then I went a step further and denied Replicate Directory Changes for that account and it still worked.
You may want to make additional changes such as: deleting users that no longer exist in AD, and replicating all changes to subsites.
Ahh, just good enough 
Find LDAP DN of Users and Groups using the Command Line
I always forget this command, so here's a handy reference (for moi):
| Find LDAP Path of... | Command |
| OU | dsquery OU –name "OU name" |
| Group | dsquery group –name "Group name" |
| Username | dsquery user –name username |
| Computer | dsquery computer -name computername |
Fun!
FIX: Windows 7 Login Error "The trust relationship between this workstation and the primary domain failed."
Recently, my workstation was suddenly unable to logon to my Windows 2008 domain. After entering my domain username and password at startup, I was presented with the error: "The trust relationship between this workstation and the primary domain failed."
Ahh, I've experienced something similar before and I knew I'd have to rejoin the domain. I hoped and prayed that my user profile wouldn't be recreated and fortunately, I found a solution that not only worked, but my profile stayed the exact same:
My Computer -> Properties -> Advanced System Settings -> Computer Name -> Network ID... -> This is part of a business network... -> My Company uses a network with a domain -> Next -> Enter your domain username and password -> "An Account for this computer ("COMPUTERNAME") has been found on the domain "DOMAIN." Would you like to use this? Yes -> Add the following domain user account (the one you usually logon with) -> Administrator (if that's how you roll) -> Finish -> Restart.
For those of you looking to resolve your trust issues, I hope this works for you as seamlessly as it did for me.
Securing Apache using mod_ssl, OpenSSL and Microsoft Certificate Authority (CA)
Recently, I used my Windows-based domain's Enterprise Root Certification Authority to secure my subversion repository that is hosted on an Apache-based server. The process was rather straight-forward and relatively fast -- especially because I skipped over all of the file transfers and just used vi/notepad to copy/paste all the key info. The first step in this process is to generate a server key on the Linux machine:
ariel:~ # openssl genrsa -des3 -out ariel.corp.netnerds.net.key 1024
Generating RSA private key, 1024 bit long modulus
............++++++
................................................................................
...................................++++++
e is 65537 (0x10001)
Enter pass phrase for ariel.corp.netnerds.net.key: **********
Verifying - Enter pass phrase for ariel.corp.netnerds.net.key: **********Next, I used the key to create a certificate signing request
ariel:~ # openssl req -new -key ariel.corp.netnerds.net.key -out ariel.corp.netnerds.net.csr
Enter pass phrase for ariel.key: **********
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:US
State or Province Name (full name) [Some-State]:LA
Locality Name (eg, city) []:Kaplan
Organization Name (eg, company) [Internet Widgits Pty Ltd]:netnerds
Organizational Unit Name (eg, section) []:IT
Common Name (eg, YOUR name) []:ariel.corp.netnerds.net
Email Address []:postmaster@netnerds.net
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:Next, I concatenated the contents of ariel.corp.netnerds.net.csr and copied that into my clipboard. The request looked something like this:
-----BEGIN CERTIFICATE REQUEST-----
wCvPKErAn5QBKFwlT5RCcOjeSZhAOx3UNe+Ispk874rvvwL6YIApAsMujrUlDNVo
......
vwL6
-----END CERTIFICATE REQUEST-----I then opened up my domain's CA @ http://windowsCA/certsrv and went to
- Request a certificate
Or, submit an advanced certificate request. - Submit a certificate request by using a base-64-encoded CMC or PKCS #10 file, or submit a renewal request by using a base-64-encoded PKCS #7 file.
- Saved Request:
-----BEGIN CERTIFICATE REQUEST-----
wCvPKErAn5QBKFwlT5RCcOjeSZhAOx3UNe+Ispk874rvvwL6YIApAsMujrUlDNVo
......
vwL6
-----END CERTIFICATE REQUEST-----Certificate Template: Web Server
Note: Be sure to decline when prompted by the browser to install the certificate locally.
I then opened the file in notepad, and copied the contents back into Linux as temp.key. In order to avoid having to type the passphrase in each time Apache is restarted, I decoded the key and moved that to the Apache directory.
openssl rsa -in temp.key -out ariel.corp.netnerds.net-decoded.keyNext, I copied the files into the appropriate directories in /etc/apache/ssl* and modified my /etc/apache2/vhosts.d/vhost-ssl.conf and added the appropriate file locations:
SSLCertificateFile /etc/apache2/ssl.crt/ariel.corp.netnerds.net.crt
SSLCertificateKeyFile /etc/apache2/ssl.key/ariel.corp.netnerds.net-decoded.keyFinally, I restarted the apache service and then partied to Wayne Toups.
Windows 7: Disable Unnecessary Services on a Domain Workstation
I finally took the plunge and installed Windows 7 on my Dell Netbook (Inspiron mini iM10-008B). I originally bought the Dell to make it into a Hackbook but after I realized how time-intensive the initial setup may be, I looked to installing Win 7 for instant newness gratification.
Initially, I was going to install the Enterprise version of Win 7 like I had done with Vista, but I decided on Ultimate since Ultimate is essentially the "Enterprise version for consumers" and I didn't have to have a local Key server around to validate my install.
The Netbook I purchased came with only 1GB RAM so it's especially important that I keep it running as efficiently as possible. I performed the basic tweak of setting Win 7 for Best Performance and then began checking out which services I could disable. I found Black Viper's Service Configurations page and used that as a guide to modifying my own services. His recommendations appeared more geared towards machines that are not on a domain, so my setup is a bit different from his. I have a Windows 2008-based domain and, while I know it offers a lot of Vista/7 specific features, I don't have time to explore them right all so my setup is geared towards a basic domain membership.
Ultimately, I was able to reduce initial memory usage to 480 MB. That's a lot, but better than the ~600MB or so used by default. There are 144 services that I reviewed, including the AVG firewall service. So without further ado...
Securing Subversion with Windows 2008 Kerberos-Based SSO and Linux-Based Apache
Some things just belong on Linux. Like Subversion and Apache, for instance. I've seen the ghetto workarounds for Windows-based Apache installs and no thanks -- I'd much rather waste my time on ghetto SharePoint workarounds.
But I sure do like the way Windows-based web servers such as IIS seamlessly and securely authenticate users across a domain. I wanted Apache to do the same and, after a week of trying various methods of authentication, I found the easiest, most efficient way is to use SSL, Kerberos, and Likewise.
I start this project, as I do all of my Linux projects, by using a fresh install of SuSE Linux Enterprise Server (SLES 11). During the initial install, I made sure to use a local passwd file for authentication. Likewise takes care of all the advanced authentication methods after the install is complete. When using Likewise, do not attempt to use YaST to configure authentication or you'll run into a variety of pam and krb5 key issues.
Here are the following steps and tutorials I used to accomplish my goal of SSO
- Install and configure Likewise Open.
- Joining a domain is as easy as /opt/likewise/bin/domainjoin-cli join corp.netnerds.net Administrator, even when authenticating against Windows 2008 Active Directory.
- Setup Apache to support SSL
- Setup Apache to support Kerberos-based SSO
- My ktpass, for example, looks like this:
ktpass /out http.ktb /princ HTTP/ariel.corp.netnerds.net@CORP.NETNERDS.NET /pass SkiAlta2009 /mapuser corp\linuxweb
- My ktpass, for example, looks like this:
- Install the One-Click Installer that comes with OpenSuSE by default, but not SLES 11.
- yast -i yast2-metapackage-handler
- Add the subversion packages to the local repository.
- OCICLI http://software.opensuse.org/ymp/Subversion/SLE_11/subversion.ymp
- Go into YaST and install the necessary subversion packages.
- Follow the OpenSuSE tutorial for Setting Up a Subversion Server Using Aapache 2
- Throw a party! Just turn up Pandora's Cajun station and DANCE.
I recommend using your domain's own Certificate Authority to generate the SSL cert that Apache will use. That way, users won't be prompted to accept an untrusted self-signed SSL certificate.
Have fun!
Apache: Pre-compiled mod_auth_pam for SLES 11
This was ridiculous. Since apache-devel isn't available in SLES (and I do understand why, but give me the option at least!), I had to sync up one of my SLES machines to an OpenSuSE repository and get all of my necessary packages required to compile mod_auth_pam. I was required to downgrade quite a few packages, but whatever works, eh?
For those of you running SLES and are trying to get mod_auth_pam to work, you can grab a precompiled copy of mod_auth_pam.tgz.
This works on the standard Apache2 install that comes with SLES 11. I presume it also works on OpenSuSE 11 as well. Just download the tgz, extract the contents, and run ./install. Then you can load up Novell's step-by-step tutorial on how to get this to work. Take note at the instructions to manually change a few files because that still needs to be done.
For my future reference, here are the Apache directives I ended up using:
AuthPAM_Enabled On
AuthPAM_FallThrough Off
AuthBasicAuthoritative Off
AuthGROUP_Enabled Off
AuthUserFile /dev/null
AuthType Basic
AuthName ADDomain
require valid-userWhile this works, it isn't seamless like way mod_auth_ntlm_winbind, but it works over SSL, unlike mod_auth_ntlm_winbind.
Enable Windows NTLM Pass-through Authentication in Linux-based Apache
Thank Science for SuSE Linux Enterprise 11; it's made this process relatively easy. SLES 10 SP2 was giving me a headache because of some Windows 2008 based Active Directory authentication issues but upgrading SLES 11 took care of all that.
First thing is first, setup samba to authenticate to AD. Next, install the OpenSuSE mod_auth_ntlm_winbind RPM.
rpm --install http://download.opensuse.org/distribution/11.0/repo/oss/suse/i586/apache2-mod_auth_ntlm_winbind-0.0.0.lorikeet_svn_682-135.1.i586.rpm
The wiki for this Apache 2 module can be found here. Next, we're going to instasll pam_smb, set the proper permissions on winbindd_privileged, add the module to apache and restart the web service.
yast -i pam_smb
setfacl -m u:wwwrun:rx /var/lib/samba/winbindd_privileged
a2enmod auth_ntlm_winbind
rcapache2 restartFinally, add something to the effect of this to your Apache config file:
<directory " /srv/www/htdocs">
AuthName "NTLM Authentication"
NTLMAuth on
NTLMAuthHelper "/usr/bin/ntlm_auth --helper-protocol=squid-2.5-ntlmssp"
NTLMBasicAuthoritative on
AuthType NTLM
require valid-user
</directory>Restart the service and you should be authenticating automatically. Don't forget to add the website to your browser's Intranet zone if needed.
Also, I read that, unfortunately, auth_ntlm_winbind, doesn't work over SSL but I'm going to try it anyway. In the event that it doesn't, I'll be exploring Kerberos authentication within Apache.
About
-
We are IT consultants
who grew up living and loving life
in Cajun Country.
Authors
Categories
- Oracle
- OS X & iDevices
- PowerShell
- Security
- SharePoint
- SQL Server
- VBScript
- Virtualization
- Windows
- Wordpress
