PowerShell and SQL Server

To be able to use the “sqlps” PowerShell Module you first need to install it from: Link

  • SQLSysClrTypes.msi – CLR Types for SQL Server
  • SharedManagementObjects.msi – Shared Management Objects
  • PowerShellTools.msi – PowerShell Extension for SQL Server

For information on how using this cmdlets, look it up in the ISE or here.

Using .net api:

# SQL-Server settings
$Database = "Database" # Database name
$Server = "SERVER\SQLEXPRESS"; # SQL-Server Instanz
 
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT [Report],[Filiale],[E-Mail] FROM [dbo].[verteiler]"; # The query

## Example Database Layout
## "Report","Filiale","E-Mail"
## "012","12","xyz@irgendwo.de"
## "033","33","abc@web.de"
## "112","112","caz@aol.com"
 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
$objTable = $DataSet.Tables[0]

PowerShell send E-Mail

Without smtp Server Authentication (with static IP or internal Mail Server)
Note: Most public mail servers only accept a message for there users and don’t forward it to other servers.

$DNSHostName = (Get-WmiObject win32_computersystem).DNSHostName;
$DNSDomainName = (Get-WmiObject win32_computersystem).Domain;

$From = "PowerShellMonitoring@$DNSHostName+'.'+$DNSDomainName";
$To = "edv@$DNSHostName+'.'+$DNSDomainName";
$Subject = "Failure $(@($isOnline).Count) Hosts down";
$SMTPServer = @(Resolve-DnsName -Name $DNSDomainName -Type MX | Select-Object -First 1).NameExchange;
$Port = 465

$Body = "MESSAGE BODY";
Send-MailMessage -From $From -Subject $Subject -To $To -Body $Body -SmtpServer $SMTPServer -UseSsl -Port $Port;

With authentication (dynamic ip or no internal Mail Server)
Note: Some public mail servers only accept the message if the “From-Address” is a valid DNS name (even @example.com)…

$DNSHostName = (Get-WmiObject win32_computersystem).DNSHostName;
$DNSDomainName = (Get-WmiObject win32_computersystem).Domain;

$From = "PowerShellMonitoring@$DNSHostName+'.'+$DNSDomainName";
$To = "edv@$DNSHostName+'.'+$DNSDomainName";
$Subject = "Failure $(@($isOnline).Count) Hosts down";
$SMTPServer = @(Resolve-DnsName -Name $DNSDomainName -Type MX | Select-Object -First 1).NameExchange;
$Port = 25

$Credential = New-Object System.Management.Automation.PSCredential("USERNAME", $(ConvertTo-SecureString "PASSWORD" -AsPlainText -Force))
$Body = "MESSAGE BODY";
Send-MailMessage -From $From -Subject $Subject -To $To -Body $Body -SmtpServer $SMTPServer -UseSsl -Credential $Credential -Port $Port;

With Outlook Api (so the message shows up in sent items):

# Folders
$FolderReports = "D:\Reports"; # Where your reports are

# SQL-Server settings
$Database = "Database" # Database name
$Server = "SERVER\SQLEXPRESS"; # SQL-Server Instanz

# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT [Report],[Filiale],[E-Mail] FROM [dbo].[verteiler]"; # Your distributiontable

# The mail
$MailSubject = "Enter subjectline here"
$MailBody = '
Sehr geehrter Kunde,
.......
......
.....
' # Can be plain text or html

# To use this script you must have a database that looks like this:
## Database Layout
## "Report","Filiale","E-Mail"
## "012","12","xyz@irgendwo.de"
## "033","33","abc@web.de"
## "112","112","caz@aol.com"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
$objTable = $DataSet.Tables[0]

# Send the E-Mail using Outlook
$ol = New-Object -comObject Outlook.Application
$objTable | ForEach-Object {
    $mail = $ol.CreateItem(0)
    $Mail.To = $_."E-Mail"
    $Mail.BCC = $mailBCC
    $Mail.Subject = $MailSubject
    if ($MailBody -like '*<HTML>*</HTML>*') {
        $Mail.HTMLBody = $MailBody
    } else {
        $Mail.Body = $MailBody
    }
    $Mail.Attachments.Add("$FolderReports\$($_."Report").pdf")
    $Mail.Send()
}

Status Monitoring

Here is a code sniped to monitor your servers. In the first example the result is simply written into a CSV-File, but in the second a e-mail is sent.

Import-Csv -Path ".\IP-List.txt" -Header ComputerName | Test-NetConnection | Select-Object ComputerName,PingSucceeded | Export-Csv -Path ".\Summary.csv" -Delimiter ";" -NoTypeInformation
$isOnline = Import-Csv -Path ".\IP-List.txt" -Header ComputerName | Test-NetConnection | Select-Object ComputerName,PingSucceeded | Where-Object -Property PingSucceeded -EQ -Value $True

$DNSHostName = (Get-WmiObject win32_computersystem).DNSHostName;
$DNSDomainName = (Get-WmiObject win32_computersystem).Domain;

$From = "PowerShellMonitoring@$DNSHostName+'.'+$DNSDomainName";
$To = "edv@$DNSHostName+'.'+$DNSDomainName";
$Subject = "Failure $(@($isOnline).Count) Hosts down";

$Body = $($isOnline | Format-Table | Out-String);
Send-MailMessage -From $From -Subject $Subject -To $To -Body $Body;

VPN Connection and Device Authentication

Automatically establish a VPN connection if a specific application is launched. So your users cannot forget to launch it first and will not call you therefor 😉

Add-VpnConnection -Name VPN -ServerAddress myid.myfritz.net -AuthenticationMethod MSChapv2 -DnsSuffix fritz.box -EncryptionLevel Optional -IdleDisconnectSeconds 0 -TunnelType Pptp -UseWinlogonCredential
Add-VpnConnectionTriggerApplication -ApplicationID %windir%\system32\mstsc.exe -ConnectionName VPN -Force
Add-VpnConnectionTriggerTrustedNetwork -ConnectionName VPN -DnsSuffix fritz.box -Force

The Example uses pptp seriously you should not use pptp today.
Encryption should be set to Required as a bare minimum, Maximum is recommended.
The username and password used for authentication are those of the actually logged on user “UseWinlogonCredential”.

If you try to implement a Machine based authentication instead of a user one, you have to use IkeV2 (TunnelType) with MachineCertificate (AuthenticationMethod) and also the option “AllUserConnection” should be checked. May you also want to add “LogonUI.exe” as a trigger application, so your logon scripts can run (if the user has internet connection at this point, many network cards need some time to wake up, keep this in mind)

SMB: Short file name creation should be disabled

If the Best Practive Analyzer is showing this error, you can disable the 8dot3 Names by running the following commands

Enter-PSSession <<ServerName>>
fsutil behavior set disable8dot3 1

The number one stands for one of the following options:
0 – Enable 8dot3 name creation on all volumes on the system
1 – Disable 8dot3 name creation on all volumes on the system
2 – Set 8dot3 name creation on a per volume basis
3 – Disable 8dot3 name creation on all volumes except the system volume

https://technet.microsoft.com/en-us/library/ff633453(WS.10).aspx

Unsolicited Remote Assistance

Yes it is possible to make the Remote Assistance somewhat usable.

Upside:
– It’s free
– I recommend setting it up as a backup (If e.g. TeamViewer servers are down again).
Downside:
– UAC Prompts are not visible to you
– Supporter needs to be local Administrator
– Only Local and Routed Networks (e.g. no NAT)

First you need to make a new Domain Local Group named “Remotesupport” and add all your Supporters (the Globlal Group of there teams) to it.
Make a new Policy (on DC) and name it “Unsolicited Remote Assistance”.
Go to the Directory “Computer Configuration\Policies\Administrative Templates\System\Remote Assistance”.
Enable the Policy “Configure Solicited Remote Assistance” with default settings.
Enable the Policy “Configure Offer Remote Assistance”, click on “Show…” and enter “\Remotesupport”.
The last step you have to go is linking it under your Domain (or OU) it should apply to.

Allow in your Windows Firewall:
– TCP 135
– %systemroot%\PCHEALTH\HELPCTR\Binaries\helpsvc.exe
– %systemroot%\system32\Raserver.exe
– %systemroot%\system32\sessmgr.exe
You can add this to the “Unsolicited Remote Assistance” policy if you use the Windows Firewall (“Computer Configuration\Policies\Windows Settings\Security Settings\Windows Firewall with Advanced Security\Inbound Rules”)

Now all your remote support team has to do is opening “msra.exe /offerra” and entering the Client IP or Hostname.

If you really depend on being able to see the UAC prompt you can lower your device security to the bare minimum by disabling the Secure Desktop:
Seriously don’t do it. That allows Malware running with user Privileges to log your keystrokes.
I warned you.
Ok, I think you really want to do it, so I won’t stop you from enabling the Policy (“Computer Configuration\Policies\Windows Settings\Security Settings\Local Policies\Security Options\User Account Control: Allow UIAccess applications to prompt for elevation without using the secure desktop”)
My recommendation: Put this setting in a separate policy and enable it as needed (e.g. your primary remote assistance application fails). Normally applying a policy can take up to 15 Minutes. Just run “winrs -r:HOSTNAME gpupdate /force” as Administrator to force apply them immediately.

Documentation of a given infrastructure

Getting the same information from every pc especially in a very complex and grown environment can be a tedious work. So why don’t just call a script to help out 😉

# Network Drives
Get-WmiObject -Class Win32_MappedLogicalDisk | Select-Object Name,ProviderName

# Powershell Version
$PSVersionTable.PSVersion

# Get all Printer
Get-WMIObject -Class Win32_Printer | Select-Object -Property Name,PortName,Default | Sort-Object Name |Sort-Object Default -Descending | Out-String

# Get Office
Get-WmiObject -Class Win32_Product -Filter "name like '%office%'" | Select-Object -Property Vendor,Name,Version | Out-String

# Get all Applications
Get-WmiObject -Class Win32_Product | Select-Object -Property Vendor,Name,Version | Out-String

# Local Drives (with ntfs):
Get-WmiObject win32_volume -Filter "filesystem like 'ntfs'" | Sort-Object Name | Select-Object Name,Label | Out-String

# Find all local PST-Files
# Get-ChildItem -Path C:\ -Filter *.pst -Recurse -ErrorAction SilentlyContinue
Get-WmiObject win32_volume -Filter "filesystem like 'ntfs' and DriveLetter like '%'" | foreach-object {Get-ChildItem -Path $_.name -Filter *.pst -Recurse -ErrorAction SilentlyContinue | Select-Object FullName | Out-String}

# Processor and Windows Architecture
Get-WmiObject Win32_processor | Select-Object Name,Caption,AddressWidth,DataWidth | Out-String
Get-WmiObject Win32_OperatingSystem | Select-Object OSArchitecture | Out-String

# Get Bios Information
Get-WmiObject win32_bios | Select-Object SerialNumber,Manufacturer,BiosVersion,ReleaseDate,SMBIOSBIOSVersion,SMBIOSMajorVersion,SMBIOSMinorVersion | Format-Table | Out-String
Get-WmiObject Win32_ComputerSystem | Select-Object Manufacturer,Model,NumberOfProcessors,NumberOfLogicalProcessors | Out-String

# Environment Variables
Get-ChildItem Env: | Out-String

# # Get EventLog
# Get-EventLog -LogName *

Apache2 optimizations

To gain a faster page loading, you can enable the client side caching. That means that the browser of the connecting clients is storing the contents of your page until it expires.
Therefore you have to add the following under your virtual host entry (right before </VirtualHost>)

<IfModule mod_expires.c>
        ExpiresActive On
        ExpiresDefault "access plus 10 seconds"
        ExpiresByType text/html "access plus 60 seconds"
        ExpiresByType image/gif "access plus 120 minutes"
        ExpiresByType image/jpeg "access plus 120 minutes"
        ExpiresByType image/png "access plus 120 minutes"
        ExpiresByType text/css "access plus 60 minutes"
        ExpiresByType text/javascript "access plus 60 minutes"
        ExpiresByType application/javascript "access plus 60 minutes"
        ExpiresByType application/x-javascript "access plus 60 minutes"
        ExpiresByType text/xml "access plus 60 minutes"
    </IfModule>

After that run the following as root:

cd /etc/apache2/mods-enabled
ln -s ../mods-available/expires.load expires.load
ln -s ../mods-available/headers.load headers.load
service apache2 restart

Also if you want to redirect all http traffic to https you should use HTTP Response code 301 instead of 302. This is something that is nearly everywhere you look for http to https redirects missing (“R=301”).
To accomplish this you simply have to replace “<VirtualHost *>” at the beginning of your website configuration file (replace server names 😉 ) with:

<VirtualHost example.org:80>
        ServerName www.example.org
        # Redirect http://(www.)example.org/* to https://www.example.org/*
        RewriteEngine On
        RewriteCond %{HTTP_HOST}   ^(?:.*)example\.org$ [NC]
        RewriteCond %{SERVER_PORT}   !^443$
        RewriteRule  (.*)  https://www.example.org$1   [R=301,L]
</VirtualHost>
<VirtualHost example.org:443>

Also you should use the ServerName attribute and avoid using “<VirtualHost *>” for convenience and later usage.

Configuring https is as simple. First you have to get your certificate use startssl or lets encrypt.
After you have managed to get your certificate for (www.example.org; don’t miss typing the www subdomain 😉 ) place the files in the following directory:
The Private key: /etc/ssl/private/example.org.key
The Certificate File: /etc/ssl/certs/example.org.crt
The Intermediate Certificates File (e.g. lets encrypt or sub.class1.server.ca.pem): /etc/ssl/certs/letsencryptauthorityx1.pem or /etc/ssl/certs/sub.class1.server.ca.pem

After the files are there you have to add some text to your VirtualHost configuration section

<VirtualHost example.org:443>
        ServerAdmin webmaster@example.org
        ServerName www.example.org

        SSLEngine on
        SSLCertificateFile /etc/ssl/certs/example.org.crt
        SSLCertificateKeyFile /etc/ssl/private/example.org.key
        SSLCertificateChainFile /etc/ssl/certs/sub.class1.server.ca.pem
        SSLProtocol ALL -SSLv2 -SSLv3
        SSLHonorCipherOrder on
        # SSLCipherSuite ALL:!ADH:!RC4:+HIGH:!MEDIUM:!LOW:!SSLv2:!SSLv3!EXPORT
        SSLCipherSuite "EECDH+ECDSA+AESGCM EECDH+aRSA+AESGCM EECDH+ECDSA+SHA384 EECDH+ECDSA+SHA256"
        # Replace Certificate Hashes below
        Header always add Public-Key-Pins "pin-sha256=\"Vjs8r4z+80wjNcr1YKepWQboSIRi63WsWXhIMN+eWys=\"; pin-sha256=\"YLh1dUR9y6Kja30RrAn7JKnbQG/uEtLMkBgFF2Fuihg=\"; max-age=2592000; includeSubdomains"
        Header always add Strict-Transport-Security "max-age=15768000"
        Header always add Content-Security-Policy "default-src 'self';frame-ancestors 'self';style-src 'self' 'unsafe-inline';script-src 'self' 'unsafe-inline' 'unsafe-eval';font-src 'self' data:;img-src 'self' data:"
        Header always add X-Content-Type-Options "nosniff"
        Header always add X-Frame-Options "sameorigin"
        Header always add X-XSS-Protection "1;mode=block"

As soon as WordPress stops using inline scripts, inline styles, fonts as “data:” urls and also images as “data:” urls, the line:

Header always add Content-Security-Policy "default-src 'self';frame-ancestors 'self';style-src 'self' 'unsafe-inline';script-src 'self' 'unsafe-inline' 'unsafe-eval';font-src 'self' data:;img-src 'self' data:"

can be changed to the more secure

Header always add Content-Security-Policy "default-src 'self';frame-ancestors 'self'"

WordPress

By default WordPress has a few external dependencies. For security reasons it’s a good idea to prevent external dependencies or completely remove them when possible. Also this prevents you from writing a whole lot of text into your privacy policy. And it’s always a good idea to remove unused things that can cause you a lot of trouble…

  1. Google Fonts
      Some fonts are fetched from Google. So Google can create a list of nearly all pages a user visited. To disable this, you have to install the Plug-in “Disable Google Fonts”
  2. Emojis
      The emojis are pictures (instead of characters) from s.w.org. So you may become liable if something goes wrong there. To disable this, you have to install the Plug-in “Disable Emojis”
  3. Registration
      Turn it off (until you really know what you do with all consequences). Settings => General
  4. Update Services
      Turn it off (until you really know what you do with all consequences). Settings => Writing
  5. E-Mail Publishing
      Turn it off (until you really know what you do with all consequences). Settings => Writing
  6. Comments
      Turn it off (until you really know what you do with all consequences). Settings => Discussion
      If you don’t want to disable it, it’s a good idea to enable manual approval.
      Enable leave “Name and E-Mail-Address” but note, you have to write about that in your privacy policy.
      Don’t enable notification of other weblogs, because that can lead to triggering DOS protections and so others may think you’re trying to attack them.
      Disable the “Show Avatar” option. The WordPress Avatars are also fetched from a external site. It may also be a legal issue in some countries, because you are liable for the pictures others provide.
  7. Plugins
      Don’t install and enable every possible plugin. Many have big security wholes that can lead to your site being used in the next fishing e-mail “You have won…” for hosting there viruses.
      Disable all default plugins, you don’t need them.
      Remember that if a plugin is communication with another server you would have to mention that in your privacy policy, so don’t install stuff where you cannot verify that it does not.
      Also plugins can prevent you from updating to a save WordPress version if a bug was found because the Plugin author has not yet released an update.
  8. Updates
      Regularly (at least once a month) log into your WordPress to look if there are any updates. If you have any updates, make an additional Backup and press start.
  9. Users and Permissions
      Don’t use your Administrative WordPress user to publish!!!!
      Don’t name your user Admin, Administrator or root!!!!
      If you cannot think about a good name, use an online user name generator.
      Use very long and Complex Passwords (at least for your admin user), you don’t have to remember it, if you use e. g. KeePass.
      If you have problems remembering your publishing users password just think about your favorite song and write the lyrics with random capitalization or replacements. That way its easy to remember for you but very hard to brute force (length) and impossible for dictionary attacks (randomizations you made). Remember longer is better than random:

    • “jsdga”: 0.002970344 seconds to crack using a desktop PC
    • “Hell0”: 0.229033208 seconds to crack using a desktop PC
    • “StarWars”: On the top 60 passwords list.
    • “JUST LETTERS”: 546 years to crack using a desktop PC (But it’s probably guessable 😉 )
      Note: Password attacks are normally not performed by desktop PCs instead they are done on rented or hacked high-end servers e. g. Amazon AWS so brute forcing them becomes a lot easier because that systems have a 24/7 uptime, high bandwidth and very fast CPUs (I know GPUs would be better but not many root server have good ones).
  10. Publishing
      Don’t set your website live (.htaccess user name “user” and password “123” is enough) until you have checked all law requirements. You may have to provide your full address and name, depending where you live. As long as your site requires a password to be accessed and even if it’s as simple as “user” and “123” it’s enough to be protected from sue (but you really should contact a lawyer before you believe me).
      On many places around the world there is something called “Notice and Take down” which means if you were notified about something wrong you have to correct that (like if someone posted harassment in your comments
  11. URL-Shortener
      Don’t use them. If you use a e. g. tinyurl.com and that domain get sold, hacked or the operators decide to redirect to a landing page with (e. g. pornographic ads) you may become liable for that.
      If you really want to use an URL shortener, than use your own. There are products like: Your Own URL Shortener
      But don’t use them without knowledge. They may have a “calling home” (or contacting other servers) function that you better disable in certain countries.
  12. FINALY
      Don’t use anything before you have RTFM (read the fucking manual).
      Don’t use anything before you trust it (e. g. for liability reasons).
      Think about what you’re about to do before you do it.