Category: SQL Server


Here are my recommendations to secure your computers and your domain:

Configuration\Windows Setting\Security Settings leaf.

Rename the Local Administrator Account: If the bad guy doesn’t know the name of your Administrator account, he’ll have a much harder time hacking it.

Disable the Guest Account: One of the worst things you can do is to enable this account. It grants a fair amount of access on a Windows computer and has no password. Enough said!

Disable LM and NTLM v1: The LM (LAN Manager) and NTLMv1 authentication protocols have vulnerabilities. Force the use of NTLMv2 and Kerberos. By default, most Windows systems will accept all four protocols. Unless you have really old, unpatched systems (that is, more than 10 years old), there’s rarely a reason to use the older protocols.

Disable LM hash storage: LM password hashes are easily convertible to their plaintext password equivalents. Don’t allow Windows to store them on disk, where a hacker hash dump tool would find them.

Minimum password length: Your minimum password size should be 12 characters or more. Don’t bellyache if you only have 8-character passwords (the most common size I see). Windows passwords aren’t even close to secure until they are 12 characters long — and really you want 15 characters to be truly secure. Fifteen is a magic number in the Windows authentication world. Get there, and it closes all sorts of backdoors. Anything else is accepting unnecessary risk.

Maximum password age: Most passwords should not be used longer than 90 days. But if you go to 15 characters (or longer), one year is actually acceptable. Multiple public and private studies have proven that passwords of 12 characters or longer are relatively secure against password cracking to about that length of time.

Event logs: Enable your event logs for success and failure. As I’ve covered in this column many times, the vast majority of computer crime victims might have noticed the crime had they had their logs on and been looking.

Disable anonymous SID enumeration: SIDs (Security Identifiers) are numbers assigned to each user, group, and other security subject in Windows or Active Directory. In early OS versions, non-authenticated users could query these numbers to identify important users (such as Administrators) and groups, a fact hackers loved to exploit.

Don’t let the anonymous account reside in the everyone group: Both of these settings, when set incorrectly, allow an anonymous (or null) hacker far more access on a system than should be given. These have been disabled by default since 2000, and you should make sure they stay that way.

Enable User Account Control: Lastly, since Windows Vista, UAC has been the No. 1 protection tool for people browsing the Web. I find that many clients turn it off due to old information about application compatibility problems. Most of those problems have gone away, and many of the remaining ones can be solved with Microsoft’s free application compatibility troubleshooting utility. If you disable UAC, you’re far closer to Windows NT security than you are a modern operating system.

Here’s the best part: Each of these settings is set correctly by default in Windows Vista/Server 2008 (and later). Most of my Windows security books were all about the settings I wanted you to more securely harden. These days, my best advice is don’t muck it up. When I see problems, it’s because people go out of their way to weaken them, and that’s never good.

Concretely:

  • Accounts: Rename administrator account — not highly effective but another security layer nonetheless (define a new name)
  • Accounts: Rename guest account (define a new name)
  • Interactive logon: Do not display last user name (set to “Enabled”)
  • Interactive logon: Do not require last user name (set to “Disabled”)
  • Interactive logon: Message text for users attempting to log on (define banner text for users to see – something along the lines of This is a private and monitored system…you abuse this system, you’re toast — just run it by your lawyer first)
  • Interactive logon: Message title for users attempting to log on — something along the lines of WARNING!!!
  • Network access: Do not allow enumeration of SAM accounts and shares (set to “Enabled”)
  • Network access: Let “Everyone” permissions apply to anonymous users (set to “Disabled”)
  • Network security: Do no store LAN Manager hash value on next password change (set to “Enabled”)
  • Microsoft Network client: send unencrypted password to third-party SMB servers (Set to “Disabled”)
  • Network security: LAN Manager authentication level (set to “Send NTLMv2 responses only. Refuse LM & NTLM”)
  • Shutdown: Allow system to be shut down without having to log on (set to “Disabled”)
  • Shutdown: Clear virtual memory pagefile (set to “Enabled”)
Advertisements

http://blogs.technet.com/b/askds/archive/2008/08/12/event-logging-policy-settings-in-windows-server-2008-and-vista.aspx

Using Powershell:

http://msexchange.me/2014/06/05/monitoring-event-id-thru-powershell/

http://community.spiceworks.com/topic/282720-powershell-event-log-monitor-email-alert-script-central-monitor

https://vijredblog.wordpress.com/2014/03/21/task-scheduler-event-log-trigger-include-event-data-in-mail/

Using SCOM:

http://jimmoldenhauer.blogspot.fr/2013/03/scom-2012-how-to-generate-alerts-from.html

http://scomandplus.blogspot.fr/2013/02/creating-rules-to-monitor-security-logs.html

http://thoughtsonopsmgr.blogspot.fr/2013/11/windows-event-log-monitoring-how-to-get.html

http://opsmgradmin.blogspot.fr/2011/05/scom-monitoring-windows-event-logs.html

 

 

 

 

Troubleshooting slow logons:

http://blogs.technet.com/b/askds/archive/2009/09/23/so-you-have-a-slow-logon-part-1.aspx

http://blogs.technet.com/b/askds/archive/2009/09/24/so-you-have-a-slow-logon-part-2.aspx

Logon process: http://fr.slideshare.net/ControlUp/understanding-troubleshooting-the-windows-logon-process

Tools for troubleshooting:

http://social.technet.microsoft.com/wiki/contents/articles/10128.tools-for-troubleshooting-slow-boots-and-slow-logons-sbsl.aspx

http://social.technet.microsoft.com/wiki/contents/articles/10123.troubleshooting-slow-operating-system-boot-times-and-slow-user-logons-sbsl.aspx

And powershell:

http://blogs.citrix.com/2015/08/05/troubleshooting-slow-logons-via-powershell/

Analyze GPOs load time: http://www.controlup.com/script-library/Analyze-GPO-Extensions-Load-Time/ee682d01-81c4-4495-85a7-4c03c88d7263/

 

How to use Xperf, Xbootmgr, Procmon, WPA?

xperf;xbootmgr;xperfview comes from Windows ADK (Windows performance toolkit sub part). Procmon is a sysinternal tool.

http://superuser.com/questions/594625/how-can-i-analyze-performance-issues-before-during-the-logon-process

http://blogs.technet.com/b/askpfeplat/archive/2012/06/09/slow-boot-slow-logon-sbsl-a-tool-called-xperf-and-links-you-need-to-read.aspx

http://social.technet.microsoft.com/wiki/contents/articles/10128.tools-for-troubleshooting-slow-boots-and-slow-logons-sbsl.aspx

Other interesting articles:

http://blogs.technet.com/b/askpfeplat/archive/2014/10/27/becoming-an-wpa-xpert-part-11-troubleshooting-long-group-policy-processing.aspx

https://www.autoitconsulting.com/site/performance/windows-performance-toolkit-simple-boot-logging/

https://randomascii.wordpress.com/2012/09/04/windows-slowdown-investigated-and-identified/

https://randomascii.wordpress.com/2013/04/20/xperf-basics-recording-a-trace-the-easy-way/

 

Windows Performance Analyzer (wpa.exe) youtube: https://www.youtube.com/watch?v=HGTlc_gWH_c

Xperf data collection tool: https://xperf123.codeplex.com/releases/view/66888

 

For boot tracing:

http://www.msfn.org/board/topic/140247-trace-windows-7-bootshutdownhibernatestandbyresume-issues/

xbootmgr -trace boot -traceFlags BASE+CSWITCH+POWER -resultPath C:\TEMP

with boot phases:
xbootmgr -trace boot -traceflags base+latency+dispatcher -stackwalk profile+cswitch+readythread 
       -notraceflagsinfilename -postbootdelay 120 -resultPath C:\TEMP
 

For shutdown tracing:

xbootmgr -trace shutdown -noPrepReboot -traceFlags BASE+CSWITCH+DRIVERS+POWER -resultPath C:\TEMP

For Standby+Resume:

xbootmgr -trace standby -traceFlags BASE+CSWITCH+DRIVERS+POWER -resultPath C:\TEMP

For Hibernate+Resume:

xbootmgr -trace hibernate -traceFlags BASE+CSWITCH+DRIVERS+POWER -resultPath C:\TEMP

replace C:\TEMP with any temp directory on your machine as necessary to store the output files

Analyses of the boot trace:

Boot_MainPathBoot.png

To start create a summary xml file, run this command (replace the name with the name of your etl file)

xperf /tti -i boot_BASE+CSWITCH+POWER_1.etl -o summary_boot.xml -a boot

Analyses of the shutdown trace:

The shutdown is divided into this 3 parts:

Shutdown_picture.png

To generate an XML summary of shutdown, use the -a shutdown action with Xperf:

xperf /tti -i shutdown_BASE+CSWITCH+DRIVERS+POWER_1.etl -o summary_shutdown.xml -a shutdown

 

 
Finding remote session connected to your computer?
who is running a (hidden) remote PowerShell on your machine? Here’s a simple one-liner:
Get-WSManInstance -ConnectionURI (‘http://{0}:5985/wsman’ -f $env:computername) -ResourceURI shell -Enumerate
It will return anyone connecting via port 5985 to your machine. However, if you’re not running in a domain environment,
you first have to enable non-Kerberos connections
(remember that without Kerberos, you no longer know for sure that the target computer really is the computer it pretends
to be):
Set-Item WSMan:\localhost\Client\TrustedHosts * -Force

wusa <update>.msu /quiet /norestart /log

example: wusa d:\hotfixes\Windows8.1-KB29456426.msu /quiet /norestart

You can use the Windows Management Instrumentation Command-line (WMIC) to view the installed updates on your computer:

wmic qfe list

Caption CSName Description FixComments HotFixID InstallDate InstalledBy InstalledOn Name ServicePackInEffect Status

Else If the WMIC output is difficult to read, you can use Systeminfo instead, as follows:

systeminfo | findstr /i /c:”KB29456426″

[18]: KB29456426

How to use WUSA with Powershell?

Get-Item .\* | %{Expand-ZipFile -FilePath $_.FullName -OutputPath d:\hotfixes}

Get-Item d:\hotfixes\* | foreach {WUSA “”$_.FullName /quiet /norestart””;while(get-process wusa){Write-Host “Installing $_.Name”}}

Get-HotFix | Where Description -match hotfix
(Get-HotFix | Where Description -match hotfix).count

Introduction: How to migrate a SQL cluster to a new SAN ?

Environment: Server 2008 r2 (failover cluster – aka MSCS) and SQL 2008 sp3

Reference about Failover clustering:

http://technet.microsoft.com/en-us/library/cc753362(v=ws.10).aspx

http://technet.microsoft.com/en-us/library/ff182338(v=ws.10).aspx

 

I selected for your this article  – how to move a SQL 2008 cluster to a new SAN:

https://gallery.technet.microsoft.com/SAN-Migration-step-for-SQL-d66dea19

 

Notes from the field:

http://blogs.technet.com/b/hugofe/archive/2012/07/04/windows-2008-2008-r2-san-migration.aspx

 

Note regarding the Quorum:

The Cluster service recognizes and identifies disks by their disk signatures (stored on the Quorum). Disk signatures are stored on the physical disk in the master boot record (MBR)

http://support.microsoft.com/kb/280425/en-us

Failover Clustering error codes: http://technet.microsoft.com/en-us/library/cc756229(v=WS.10).aspx

 

Note regarding Disk Signatures:

http://blogs.technet.com/b/markrussinovich/archive/2011/11/08/3463572.aspx
 
Disk Signatures
 
A disk signature is four-byte identifier offset 0x1B8 in a disk’s Master Boot Record, which is written to the first sector of a disk. This screenshot of a disk editor shows that the signature of my development system’s disk is 0xE9EB3AA5 (the value is stored in little-endian format, so the bytes are stored in reverse order).
 
Windows uses disk signatures internally to map objects like volumes to their underlying disks and starting with Windows Vista, Windows uses disk signatures in its Boot Configuration Database (BCD), which is where it stores the information the boot process uses to find boot files and settings.
Disk Signature Collisions
 
Windows requires the signatures to be unique, so when you attach a disk that has a signature equal to one already attached, Windows keeps the disk in “offline” mode and doesn’t read its partition table or mount its volumes. This screenshot shows how the Windows Disk Management administrative utility presents an offline disk that I caused when I attached the VHD Disk2vhd created for my development system to that system:
 
If you right-click on the disk, the utility offers an “Online” command that will cause Windows to analyze the disk’s partition table and mount its volumes:
 
When you chose the Online menu option, Windows will without warning generate a new random disk signature and assign it to the disk by writing it to the MBR. It will then be able to process the MBR and mount the volumes present, but when Windows updates the disk signature, the BCD entries become orphaned, linked with the previous disk signature, not the new one.
 
 
http://support.microsoft.com/kb/937251
 
The BIOS may or may not enumerate disks in a specific order. There is no direct relationship between the BIOS order, and the order in which Windows numbers the disks. During startup, Windows switches from using the BIOS INT13 support to native Windows drivers to access disks. Windows waits for several seconds for the system disk to enumerate through Plug and Play. When there is a match within the time-out period, normal startup will proceed. Otherwise, the system will trigger a bug check with Stop error code of 0x7B. Windows uses other mechanisms to differentiate disks, as Windows has no control over the disk-numbering process before startup. Windows has no information about any changes to hardware when the computer is turned off. Therefore, Windows initiates its own query for device enumeration.  
 
The disk numbers that are assigned by Windows after it switches to native Windows storage controller drivers during startup are dependent solely on the order in which the disks are enumerated and processed by Plug and Play. Windows will enumerate available fixed disks, followed by removable disks, assuming that the correct native Windows drivers are already present and installed on the system. Various uncontrollable timing factors may affect the enumeration order.
 

http://blogs.technet.com/b/askds/archive/2011/09/26/advanced-xml-filtering-in-the-windows-event-viewer.aspx

http://blog.oneboredadmin.com/2013/05/filtering-windows-event-log-using-xpath.html

 

 

 

 

Here are tips and tricks about AD/Azure RMS:

Azure RMS blog: http://blogs.msdn.com/b/rms/

RMS for externals: https://blogs.msdn.microsoft.com/rms/2013/11/18/rights-management-licensing-terms-for-orgs-and-isvs/

Azure RMS Documentation: http://blogs.technet.com/b/rms/archive/2016/03/31/announcement-azure-rms-documentation-library-update-for-march-2016.aspx

PowerShell for Azure RMS: https://technet.microsoft.com/en-us/library/jj585027.aspx

Comparing Azure RMS and AD RMS: https://technet.microsoft.com/en-us/library/jj739831.aspx

Whitepapers about RMS: http://www.microsoft.com/en-us/download/confirmation.aspx?id=40333

 

PREVIOUSLY:

How AD RMS works: http://technet.microsoft.com/en-us/library/how-adrms-works.aspx

AD RMS concepts: http://blogs.technet.com/b/rms/archive/2012/04/16/ad-rms-infrastructure-concepts-part-1.aspx

Hosting the AD RMS Servers in an Internal Network and Publishing Them to the Internet through a Reverse Proxy with an Application Layer Firewall: http://technet.microsoft.com/fr-fr/library/dd996632%28v=ws.10%29.aspx

Making AD RMS Available on the Internet: http://technet.microsoft.com/fr-fr/library/hh311037%28v=ws.10%29.aspx

AD RMS best practices: http://technet.microsoft.com/en-us/library/jj735304.aspx

Operations: http://technet.microsoft.com/fr-fr/library/dd772674%28v=ws.10%29.aspx

 

 

How to install the Windows failover clustering from the command line ?

First, you should make sure that the nodes, running Windows Server 2012 R2 that you are intending to add to the cluster are part of the same domain, and proceed to install the Failover-Cluster feature on them. This is very similar to conventional Cluster installs running on Windows Servers. To install the feature, you can use the Server Manager to complete the installation.

Server Manager can be used to install the Failover Clustering feature:
Introducing Server Manager in Windows Server 2012
http://blogs.technet.com/b/askcore/archive/2012/11/04/introducing-server-manager-in-windows-server-2012.aspx

We can alternatively use PowerShell (Admin) to install the Failover Clustering feature on the nodes.
Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools

An important point to note is that PowerShell Cmdlet ‘Add-WindowsFeature’ is being replaced by ‘Install-WindowsFeature’ in Windows Server 2012 R2. PowerShell does not install the management tools for the feature requested unless you specify ‘-IncludeManagementTools’ as part of your command.

BONUS READ:
The Cluster Command line tool (CLUSTER.EXE) has been deprecated; but, if you still want to install it, it is available under:
Remote Server Administration Tools –> Feature Administration Tools –> Failover Clustering Tools –> Failover Cluster Command Interface in the Server Manager

The PowerShell (Admin) equivalent to install it:
Install-WindowsFeature -Name RSAT-Clustering-CmdInterface

Now that we have Failover Clustering feature installed on our nodes. Ensure that all connected hardware to the nodes passes the Cluster Validation tests. Let us now go on to create our cluster. You cannot create an AD detached clustering from Cluster Administrator and the only way to create the AD-Detached Cluster is by using PowerShell.
New-Cluster MyCluster -Node My2012R2-N1,My2012R2-N2 -StaticAddress 192.168.1.15 -NoStorage -AdministrativeAccessPoint DNS

NOTE:
In my example above, I am using static IP Addresses, so one would need to be specified. If you are using DHCP for addresses, the switch “-StaticAddress 192.168.1.15 ” would be excluded from the command.

Once we have executed the command, we would have a new cluster created with the name “MyCluster” with two nodes “My2012R2-N1” and “My2012R2-N2”. When you look Active Directory, there will not be a computer object created for the Cluster “MyCluster”; however, you would see the record as the Access Point in DNS.