SQL Server: security and accounts

Been reading about

  • Managed Service Accounts
  • Virtual Accounts
  • Domain Service-only users with non-expiring accounts

… and impersonation seems to be the way to get to use those accounts… but I’m not quite getting it right now. And of course stupidly I blew my credits on my MSDN Azure account so I can’t have a play.

Security: OWASP and O-Saft

OSaft17

The goal: to display security vulnerabilities in the environment of the target machine:

OSaft16

Documentation.

Main site here.  Note that it is part of OWASP

OSaft01

OSaft02

Installation

OSaft09

Dependencies

A Perl distribution

Given the choice, maybe pick Strawberry Perl rather than e.g. ActivePerl, as the former seems to make it much easier to run OpenSsl, and it’s designed for Windows.. whatever that means in practice.

stPerl01

stPerl02

stPerl04

stPerl06

Test the basics

stPerl07

stPerl08

stPerl09

stPerl10

perl \perl_tests\hello_world.pl

stPerl11

Now over to the O-Saft section

Go to the https://www.owasp.org/index.php/O-Saft I referenced earlier and download as per the screenshot above.

Now that comes down as a file like this..

osafts01

So drill in and keep unzipping until you get to this level below o-saft.tar:

OSaft10

, and then move all that to a folder osaft, and delete the file I’ve highlighted below:

OSaft11

Then go to an administrator prompt and type

OSaft12

So now we start to get stuff. The red flags are the combinations of yes (i.e. I support on my machine ), and especially low or medium. Can we find any bad ones? Why yes…

OSaft13

Rather than plough through the file, much better to get PowerShell to parse it (yes Perl did occur to me, but you can have too many script languages). Our first test could just be for the yes/weak combinations. For that, let’s:

  • get the O-Saft output into a text file (as I don’t think I can get the perl directly into a variable)
  • move that text file into a variable
  • walk the variable, outputting (case-insensitive) [yes(tab)weak] matches

Which gives this (on my Windows 10 laptop – the first command takes about 15 seconds):

perl o-saft.pl +check localhost > .\osaft.txt
$warnings = Get-Content -Path .\osaft.txt
$warnings  | Where-Object {$_ -match 'yes[\t]weak'}

OSaft14

Are there any other bad combinations?

$warnings | Where-Object {$_ -match 'yes[\t]weak' -or $_ -match 'yes[\t]medium' -or $_ -match 'yes[\t]medium'}

OSaft15

No. However there is clearly repetition, so let’s reduce to unique combinations (and I am not bringing out under which particular protocols we are weak – for now we have to scan the file for the detail (osaft.txt in this example)).

$warnings | Where-Object {$_ -match 'yes[\t]weak' -or $_ -match 'yes[\t]medium' -or $_ -match 'yes[\t]low'} | select -Unique

OSaft16

And finally, this person deserves a call-out for their useful summary of Regex in PowerShell: http://ss64.com/ps/syntax-regex.html

fin

 Well, of that section. And then there’s OpenSsl:

OpenSsl01

Can’t find the config file, certainly not in that location. OK – set the system envvar to hold it:
OpenSsl02

OpenSsl03

OpenSsl04

Now we can do other stuff. This looks handy around OpenSsl examples.

PowerShell: basic Desired State Configuration (DSC)

This shows a basic config…

, which I have adapted for my Windows 10 box. Note ref Kerberos etc objections, you will also need to look at this.

Some history from the PowerShell session lest I forget:

Get-DscResource service -Syntax
Get-Item -Path WSMan:\localhost\Client\TrustedHosts
$cred = Get-Credential
Set-Item -Path WSMan:\localhost\Client\TrustedHosts -Value ‘DEN-I7’
$sess = New-PSSession -ComputerName DEN-I7
$sess = New-PSSession -ComputerName DEN-I7 -Credential $cred
C:\temp\BasicInstall.ps1

, where last line is this:

dsc01

Configuration BasicDscConfig
{
Node “DEN-I7” {
WindowsFeature NetFramework35Core {
Name = “NET-Framework-Core”
Ensure = “Present”
}

WindowsFeature NetFramework45Core {
Name = “NET-Framework-45-Core”
Ensure = “Present”
}

WindowsFeature ASP {
Ensure = “Present”
Name = “Web-Asp-Net45”
}
}
}

BasicDscConfig -InstanceName “DEN-I7”
Start-DscConfiguration -Path .\BasicDscConfig -Wait -Verbose -Force

#http://blogs.technet.com/b/privatecloud/archive/2013/08/30/introducing-powershell-desired-state-configuration-dsc.aspx

dsc02

Following a reboot after unsetting .Net 3.5 and 4.6 (sic), I tried again, and this time go this:

dsc03

So because this is a client-type machine, it won’t accept these particular Features from DSC.

Let’s see what happens if I more modestly just try to create a folder (I’ll delete the MOF files first)

Configuration BasicDscConfig
{
Node “DEN-I7” {
<# WindowsFeature NetFramework35Core { Name = “NET-Framework-Core” Ensure = “Present” } WindowsFeature NetFramework45Core { Name = “NET-Framework-45-Core” Ensure = “Present” } WindowsFeature ASP { Ensure = “Present” Name = “Web-Asp-Net45” } #>
File MyRandomDir {
DestinationPath = “c:\temp3\andMore”
Type = “Directory”
Recurse = $false
}
}
}

BasicDscConfig -InstanceName “DEN-I7”
Start-DscConfiguration -Path .\BasicDscConfig -Wait -Verbose -Force

#http://blogs.technet.com/b/privatecloud/archive/2013/08/30/introducing-powershell-desired-state-configuration-dsc.aspx
#https://technet.microsoft.com/en-us/library/dn282129.aspx

1

dsc04

2

dsc05

3

dsc06

4