Randy Treit (MSFT) on 10 seconds

Background

What is 10 seconds to you?

In a blog posting MSFT’s Randy Treit talks about the constraints Microsoft’s places on itself to quickly identify, classify, and get in the way of targeted virus penetration.

 

Windows Defender Antivirus cloud protection service: Advanced real-time defense against never-before-seen malware

Link

For cybercriminals, speed is the name of the game. It takes newly released malware an average of just four hours to achieve its goal—steal financial information, extort money, or cause widespread damage. In a recent report, the Federal Trade Commission (FTC) said that cybercriminals will use hacked or stolen information within nine minutes of posting in underground forums. Stopping new malware in real-time is more critical than ever.

Approximately 96% of all malware files detected and blocked by Windows Defender Antivirus (Windows Defender AV) are observed only once on a single computer, demonstrating the polymorphic and targeted nature of modern attacks, and the fragmented state of the threat landscape. Hence, blocking malware at first sight is a critical protection capability.

To fight the speed, scale, and complexity of threats, we work to continually enhance Windows Defender AV and other security features built into Windows 10. In our white paper “The evolution of malware prevention” we discussed our advanced, predictive approach to protecting customers from threats that they face today, as well as those that will emerge in the future.

This blog continues that discussion and provides the first detailed account of one way we improve our capability to stop never-before-seen malware with new enhancements to the Windows Defender Antivirus cloud protection service.

In Windows 10 Creators Update, the Windows Defender AV client uploads suspicious files to the cloud protection service for rapid analysis. Our ability to make a swift assessment of new and unknown files allows us to protect customers from malware the first time we see it.

We have built these enhancements on the next-gen security technologies enabling Windows Defender AV to automatically block most new, never-before-seen threats at first sight using the following methods:

  • Lightweight client-based machine learning models, blocking new and unknown malware
  • Local behavioral analysis, stopping file-based and file-less attacks
  • High-precision antivirus, detecting common malware through generic and heuristic techniques

In relatively rare cases, when Windows Defender AV needs additional intelligence to verify the intent of a suspicious file, it sends metadata to the cloud protection service, which can determine whether the file is safe or malicious within milliseconds using the following techniques:

  • Precise cloud-based machine learning models that can make an accurate assessment based on signals from the client
  • Microsoft Intelligent Security Graph that monitors threat data from a vast network of sensors

In rarer cases still, when Windows Defender AV cloud protection service is unable to reach a conclusive verdict based on metadata, it can request the potential malware sample for further inspection.

In Windows 10 Creators Update, the Windows Defender AV client uploads suspicious files to the cloud protection service for rapid analysis. While waiting for a verdict, the Windows Defender AV client maintains a lock on the dubious files, preventing possible malicious behavior. The Windows Defender AV client then takes action based on the verdict. For example, if the cloud protection service determines the file as malicious, it blocks the file from running, providing instant protection.

Windows Defender Antivirus instant protection from the cloud

Instant protection at work: A few seconds can make a lot of difference in protection

In a recent real-life example, a Windows 10 Home customer was tricked into downloading a new variant of the Ransom:Win32/Spora family of ransomware.

The malware was disguised as a font file with the name “Chrome font.exe”. It was hosted on an online learning website that had been compromised by an attacker, who attempted to trick people into downloading the malware using a social engineering tactic described by Proofpoint in this blog. In this scheme targeting Chrome users, legitimate websites were compromised to open a pop-up window indicating “The ‘HoeflerText’ font wasn’t found”, requiring a supposed update to fix. The customer clicked the “Update” button in the pop-up window, which downloaded the Spora ransomware variant.

The customer’s Windows Defender AV client routinely scanned the file using on-box rules and definitions. Since it had not encountered the file before, Windows Defender AV did not detect it as malicious; however, it recognized the file’s suspicious characteristics, so it temporarily prevented the file from running. The client sent a query to the Windows Defender AV cloud protection service, which used machine-learning-powered cloud rules to confirm that the file was likely malware needing further investigation.

Within 312 milliseconds, the cloud protection service returned an initial assessment. It then instructed the client to send a sample and to continue locking the file until a more definite verdict was given.

In about two seconds, the client finished uploading the sample. By default, it’s set to wait for up to 10 seconds to hear back from the cloud protection service before letting such suspicious files run.

As soon as the sample was uploaded, a backend file-processing system analyzed the sample. A multi-class machine learning classifier determined there was more than a 95% chance that the file was malicious. The cloud protection service created a signature, which it sent back to client. All of this happened in just five seconds.

One second later, the Windows Defender AV client applied the cloud signature and quarantined the malware. It reported the results back to the cloud service; from that point on, this file was automatically blocked, protecting all Windows PC customers.

From the time Windows Defender AV uploaded the sample, the cloud protection service returned the malware signature in just five seconds, as shown by these actual timestamps:

2017-04-20 03:53:21 – Cloud protection service received query from Windows Defender AV client

2017-04-20 03:53:21 – Cloud protection service assessed it hadn’t seen the file and that is was suspicious, so it requested a sample and to keep locking the file

2017-04-20 03:53:23 – Sample finished uploading

2017-04-20 03:53:28 – Cloud protection service determined file as malware, generated signature, and sent that back to client

2017-04-20 03:53:29 – Windows Defender AV client notified that it successfully detected and removed the malware

Stay protected with Windows 10 Creators Update

Our many years of in-depth research into malware, cyberattacks, and cybercriminal operations give us insight into how threats continue to evolve and attempt to slip past security solutions. Guided by expert threat researchers, we use data science, machine learning, automation, and behavioral analysis to improve our detection solutions continuously.

In Windows 10 Creators Update, we rolled out important updates to Windows Defender Antivirus, which uses cloud protection service that delivers real-time protection against threats. With these enhancements, we show our commitment to providing unparalleled real-time defense against modern attacks.

Our ability to make a swift assessment of new and unknown files allows us to protect even would-be patient zero against attacks. More importantly, we use this intelligence to protect the rest of our customers, who may encounter these malware in subsequent attacks or similar threats in other cybercriminal campaigns.

Cloud-based protection is enabled in Windows Defender AV by default. To check that it’s running, launch the Windows Defender Security Center. Go to Settings > Virus & threat protection settings, and make sure that Cloud-based protection and Automatic sample submission are both turned On.

In enterprise environments, cloud protection service can be managed using Group Policy or via the Windows Defender Security Center app.

When enabled, Windows Defender AV locks a suspicious file for 10 seconds by default, while it queries the Windows Defender AV cloud protection service. Administrators can configure Windows Defender AV to extend the timeout period up to one minute to give the cloud service time to perform even more analysis and apply additional techniques to detect new malware.

As the threat landscape continues to move towards more sophisticated attacks and malware campaigns that can achieve their goals in hours instead of days, it is critical to be able to respond to new attacks in real-time. With Windows 10 Creators Update and the investments we’ve made in cloud protection service, we’re able to detect brand new threat families within seconds, protect “patient zero”, and disrupt new malware campaigns before they start.

Randy Treit

Senior Program Manager, Windows Defender Engineering

SqlServer – Tempdb – Error – “Insufficient space in tempdb to hold row versions”

Background

Here reviewing failed jobs and tried to re-run one of them, but still No Go!

TroubleShooting

Error

Image

Textual

Insufficient space in tempdb to hold row versions.
Need to shrink the version store to free up some space in tempdb.
Transaction (id=17270854 xsn=2470235 spid=79 elapsed_time=3101) has been marked as victim and it will be rolled back if it accesses the version store.
If the problem persists, the likely cause is improperly sized tempdb or long running transactions.
Please refer to BOL on how to configure tempdb for versioning.

 

Query

Top Version Store Occupants

Let us see what Objects are in the Version Store.

Code



set nocount off;
go

declare @tblHOBT TABLE
(
	  [id]			int not null identity(1,1)
	, [dbid]		int not null
	, [database]	sysname not null
	, [schema]		sysname not null
	, [object]		sysname not null
	, [objectType] sysname not null
	, [index]		sysname null
	, [hobtID]		sysname not null

)

insert into @tblHOBT
(
	  [dbid]
	, [database]
	, [schema]
	, [object]
	, [objectType]
	, [index]
	, [hobtID]
)
EXECUTE master.sys.sp_MSforeachdb 
	'
		if databasepropertyex(''?'', ''Collation'') is not null
		begin

			USE [?]; 
			select distinct
				      db_id()
					, ''?''
					, tblSS.[name]
					, tblSO.[name]
					, tblSO.[type_desc]
					, tblSI.[name]
					, tblSP.[hobt_id]

			from   sys.objects tblSO

			inner join sys.schemas tblSS

				on tblSO.schema_id = tblSS.schema_id

			inner join sys.indexes tblSI

				on tblSO.object_id = tblSI.object_id

			inner join sys.partitions tblSP

				on  tblSI.object_id = tblSP.object_id
				and tblSI.index_id = tblSP.index_id

			-- On User Table
			where tblSO.[type] = ''U'' 


		end

	'

select 
		[rowNumber]
			= ROW_NUMBER() 
			OVER
				(
					ORDER BY 
						  [database] ASC
						, [schema]   ASC
						, [object]   ASC
						, [index]    ASC
				) 

		, [database] 
		, [schema]   
		, [object] 
		, [objectType] 		  
		, [index]   
		, [count]
			= count(*)
		, [aggregatedRecordLengthInBytes]
			= sum
			(
				aggregated_record_length_in_bytes
			)
		, [aggregatedRecordLengthInKB]
			= sum
			(
				aggregated_record_length_in_bytes
			)
			/ (1024)

from   @tblHOBT tblHOBT

inner join sys.dm_tran_top_version_generators AS tblSTTVG

		on tblHOBT.[dbid] = tblSTTVG.database_id
		and tblHOBT.[hobtID] = tblSTTVG.rowset_id

group by
		  [database] 
		, [schema]   
		, [object]   
		, [objectType] 
		, [index]   

order by 
		  [database] asc
		, [schema]   asc
		, [object]   asc
		, [index]    asc


Output

 

Monitor TempDB Disk Space Used

Monitor Tempdb Disk Space Used By Type ( sys.dm_db_file_space_usage )

Outline

Let us query sys.dm_db_file_space_usage to divvy up tempdb storage allocation by type.

Code

use [tempdb]
go

select 
		  getdate() AS runtime

		, [userObjectKB]
			= SUM (user_object_reserved_page_count)*8

		, [userObjectMB]
			= (SUM (user_object_reserved_page_count)*8) / (1024)

		, [internalObjectKB]
			= SUM (internal_object_reserved_page_count)*8

		, [internalObjectMB]
			= SUM (internal_object_reserved_page_count)*8 / 1024

		, [internalObjectGB]
			= SUM (internal_object_reserved_page_count)*8 / (1024 * 1024)

		, [versionStoreKB]
			= SUM (version_store_reserved_page_count)*8

		, [versionStoreKB]
			= (SUM (version_store_reserved_page_count)*8) / ( 1024)

		, [mixedextentKB]
			= SUM (mixed_extent_page_count)*8

		, [mixedextentMB]
			= SUM (mixed_extent_page_count)*8 / 1024

		, [freeSpaceKB]
			= SUM (unallocated_extent_page_count)*8

		, [freeSpaceMB]
			= SUM (unallocated_extent_page_count)*8
				/ ( 1024)

		, [freeSpaceGB]
			= SUM (unallocated_extent_page_count)*8
				/ ( 1024 * 1024)

FROM [tempdb].sys.dm_db_file_space_usage



Output

Explanation
  1. User Object is 1 MB
  2. Internal Object 166 GB
  3. mixed extent is 7 MB
  4. free space is 18 GB

 

 

Monitor Disk Space Used By Object ( tempdb.sys.system_internals_allocation_units )

Outline

Let us query tempdb.sys.system_internals_allocation_units and tempdb.sys.system_internals_partitions to gather storage allocation by object.

Code

select 
		  [object]
			= quoteName(object_schema_name(tblSIP.object_id))
				+ '.'
				+ quoteName(object_name(tblSIP.object_id))

		, tblSO.[type_desc]

		, [totalPages] 
			= sum(tblSIAU.total_pages)

		, [totalKB] 
			= sum(tblSIAU.total_pages * 8)

from   tempdb.sys.system_internals_allocation_units tblSIAU

JOIN   tempdb.sys.system_internals_partitions tblSIP

		ON tblSIAU.container_id = tblSIP.partition_id

inner join sys.objects tblSO
	on tblSIP.object_id = tblSO.object_id

where  tblSO.[type] not in 
		(
			'S'
		)

group by
		  tblSIP.[object_id]
		, tblSO.[type_desc]

order by
		sum(tblSIAU.total_pages) desc


Output

Explanation
  1. Space usage for individual objects is very miniscule

Summary

An alarm, through logging in the error log, is raised due to inability to grow the our “Version Store“.

We checked sys.dm_tran_top_version_generators to see which objects are currently hogging the Version Store, we identified hangFire.server and msdb SQL Server Agent Job related tables.

We don’t really appear to have high uptake.

We went and looked at [tempdb].sys.dm_db_file_space_usage.  And, the biggest occupant seems to be internal objects.

Both tempdb.sys.system_internals_allocation_units and tempdb.sys.system_internals_partitions have very low numbers.

Conclusion

Our problem is not Version Store nor actual tables (system, user, temp ).

But, internal objects …

sys.dm_db_file_space_usage (Transact-SQL)
Link

Let’s Encrypt – Zero SSL Online Wizard

 

Background

In this exercise we will use ZeroSSL Online Wizard to process a new Let’s Encrypt SSL Certificate.

 

Glossary

Name Definition Other Name Link
Certificate Signing Request In Public Key Infrastructure (PKI) systems, a Certificate Signing Request (also CSR or certification request) is a message sent from an applicant to a Certificate Authority in order to apply for a digital identity certificate. It usually contains the public key for which the certificate should be issued, identifying information (such as a domain name) and integrity protection (e.g., a digital signature).  Link
CSR
 Domain Validated Certificate  A domain-validated certificate (DV) is an X.509 digital certificate typically used for Transport Layer Security (TLS) where the identity of the applicant has been validated by proving some control over a DNS domain.

The sole criterion for a domain-validated certificate is proof of control over a domain. Typically control over a domain is determined using one of the following:

a) Response to email sent to the email contact in the domain’s whois details
b) Response to email sent to a well-known administrative contact in the domain, e.g. (admin@, postmaster@, etc.)
c) Publishing a DNS TXT record
d) Publishing a nonce provided by an automated certificate issuing system

 Link
 Intermediate Certificate Intermediate certificates are used as a stand-in for our root certificate. We use intermediate certificates as a proxy because we must keep our root certificate behind numerous layers of security, ensuring its keys are absolutely inaccessible.

However, because the root certificate itself signed the intermediate certificate, the intermediate certificate can be used to sign the SSLs our customers install and maintain the “Chain of Trust.”

Installing Intermediate Certificates
After your SSL certificate is issued, you will receive an email with a link to download your signed certificate and our intermediate certificates.

How you install the certificates depends on the server software you use. In most cases, you can download and install an intermediate certificate bundle. However, for some server types you must download and install the two intermediate certificates individually. Please refer to the Install SSL certificates for the specific process you should follow.

 Link

 

 

Let’s Encrypt – Client Option

From the list of Client Options for Let’s Encrypt, we have ZeroSSL.

 

ZeroSSL Windows

ZeroSSL has two options for utilizing ZeroSSL on Windows.

One option is through scripting and the other is thru a browser based wizard.

Because of reasons that we will have to cover in another post, our only option based on our targeted OS,  MS Windows 2003, is the Wizard option.

 

Processing

Outline

  1. Using IIS Manager, Request Certificate
  2. Using IIS Manager, Configure virtual folder
    • .well_known\acme-challenger
      • Mime Type ( extension-less files )
  3. Access ZeroSSL’s Website
    • Access Wizard
    • Submit Request
      • Paste generated CSR unto right side of request
      • Receive Domain Certificate
      • Press OK
    • Verification Process
      • Select Verification process ( HTTP or DNS )
      • Process Verification
    • Receive Certificates
      • Machine Certificate
      • Certificate Authority Certificate
  4. Using IIS, Accept Certificate
  5. Using IIS, Review Accepted Certificate

 

Request Certificate

Hopefully, you have already installed IIS on your targeted machine.

Steps

  1. Launch IIS Manager
  2. Access Website
  3. Access the “Directory Security” tab
    • Click the “Server Certificate” button
  4. The Wizard starts
    • The “Welcome to the Web Server Certificate Wizard” window appears
      • Click the Next button
    • The “IIS Certificate Wizard – Server Certificate” window appears
      • Choose the “Create a new certificate” option button
    • The “IIS Certificate Wizard – Delayed Or Immediate Request” window appears
    • The “IIS Certificate Wizard – Name and Security Settings” window appears
      • Change Certificate Name from “Default” to friendly,  pertinent name that will make it easy to associate and identify later
      • Change Bit Length from 1024 to 4096
    • IIS Certificate Wizard – Organization Information
      • Entered “Organization” Information
      • Entered “Organization Unit” Information
    • IIS Certificate Wizard – Geographical Information
      • Choose Country
      • Entered State
      • Entered City
    • IIS Certificate Wizard – Certificate Request File Name
      • Enter a filename to save the “Certificate Request” file under
    • IIS Certificate Wizard – Request File Summary
      • Review Request Summary

Image

Window – Default Web Site Properties // Tab – Directory Security

Welcome to the Web Server Certificate Wizard

IIS Certificate Wizard – Server Certificate

 

IIS Certificate Wizard – Delayed Or Immediate Request

IIS Certificate Wizard – Name and Security Settings

IIS Certificate Wizard – Name and Security Settings – Initial

 

IIS Certificate Wizard – Name and Security Settings – After

IIS Certificate Wizard – Organization Information

 

IIS Certificate Wizard – Geographical Information

 

IIS Certificate Wizard – Certificate Request File Name

 

IIS Certificate Wizard – Request File Summary

IIS Certificate Wizard – Completing the Web Server Certificate Wizard

Configure .well-known\acme-challenge

 

Steps

  1. Using Windows Explorer or Command Shell, create new folder under the root folder
    • Example
      • c:\inetpub\wwwroot\.wellknown\acme-challenge
  2. Register new mime-type for extension-less files
  3. Validate extension-less files are handled
    • Temporarily enable directory browsing
    • Create extension-less files under .wellknown\acme-challenge
    • Using web browser access folder and access extension-less files

 

Images

acme-challenge Properties

 

acme-challenge Properties – Mime Types – Adding Extension-less file

 

acme-challenge Properties – Mime Types

Validate Extension less file are handled

Access ZeroSSL Website

https://zerossl.com/free-ssl/#crt

Details

Outline

On the Details Tab

  • Enter fields
    • Email (optional)
      • Email to correspond and inform of pending expiration
    • Paste your Let’s Encrypt key
      • If you already have a Let’s Encrypt Key, please paste it
    • Domains ( Only if you have no CSR)
    • Paste your CSR or leave it blank to generate
      • We have a CSR we generated using IIS Manager
    • Verification
      • Verification Choices
        • HTTP Verification
        • DNS Verification
      • We chose HTTP
    • Accept ZeroSSL TOS
    • Accept Let’s Encrypt SA (PDF)
  • We pasted the generated CSR
  • And, clicked on the Next button
  • Account Key
    • The system stays busy for a while, as the Account Key is generated
    • Once generate the Account key is placed in the Account Key text box
  • Click the next button

Image

ZeroSSL : Free SSL – Home Page

 

ZeroSSL : Free SSL – Free SSL Certificate Wizard

 

Details

ZeroSSL : Free SSL – Free SSL Certificate Wizard – Details

 

 

ZeroSSL : Free SSL – Free SSL Certificate Wizard – Details  – CSR Pasted

CSR Pasted

Here we paste the “Certificate Request” ( CSR ) we generated earlier.

 

ZeroSSL : Free SSL – Free SSL Certificate Wizard – Generate Account Key

 

ZeroSSL : Free SSL – Free SSL Certificate Wizard – Account Key Generated

 

Verification

Verification  – Guidance

On the Verification Tab, for each Domain that we submitted on the Details tab, we are given guidance per:

  • Domain Name
  • Filename
  • File Content

Screen Shot

Verification – Initial

Verification  – Implementation

On the Verification Tab, for each Domain that we submitted on the Details tab, we are given guidance per:

  • Access WebSite root folder
    • Usually C:\inetpub\wwwroot
  • Create sub-folder .well-known \ acme-challenge
  • For each domain
    • Create file
    • Add file contents

Verification – Created File

Verification – File Contents

Verification – Link Clicked

 

Certificate

Outline

On the Certificate Tab

  • Information
    • Certificates good for 90 days
    • Keep the following keys for when you renew
      • Let’s Encrypt Key
        • Certificate Authority Key
      • CSR
        • Host specific
  • Download
    • Two keys are availed as text
      • Host Assigned Cert
      • Issuer Cert
    • Depending on your targeted purpose, you have choices
      • IIS
        • For IIS, you can download the entire block inclusive of being and end marker and save as one file

ScreenShot

Your Certificate is Ready

Certificate Text

 

Receive Certificate

In this section, we use IIS Manager to receive the Certificate.

Steps

  1. Launch IIS Manager
  2. Access Website
  3. Access the “Directory Security” tab
    • Click the “Server Certificate” button
  4. The Wizard starts
    • The “Welcome to the Web Server Certificate Wizard” window appears
      • Click the Next button
    • The “IIS Certificate Wizard – Pending Certificate” window appears
      • Choose the “Process Pending Request and install the certificate” option button
    • The “IIS Certificate Wizard – Process a pending Request” window appears
      • A lone text box asking for the certificate filename
        • The filename being asked for is the one generated by our Certificate Authority ( CA )
            • Enter or paste the file name
            • Or click on the browse button to navigate the File System ad select the file
    • The “IIS Certificate Wizard – Process a Pending Request – SSL Port” window appears
      • Accept or Change the SSL/HTTPS Port Number
    • The “IIS Certificate Wizard – Process a Pending Request – Certificate Summary” window appears
        • Review the Certificate Summary
          • Issued to :-
            • Internet :- FQDN
            • Intranet :- Computer Name
          • Issued By :-
            • Let’s Encrypt Authority X3
          • Expiration Date :-
            • For “Let’s Encrypt Authority X3”, 3 months from Issue Date
          • Intended Purpose :-
            • Server Authentication
            • Client Authentication
          • Friendly Name
            • Friendly Name
      • The “IIS Certificate Wizard – Process a Pending Request – Completing the Web Server Certificate Wizard” window appears

Image

Window – Default Web Site Properties // Tab – Directory Security

Welcome to the Web Server Certificate Wizard

IIS Certificate Wizard – Pending Certificate Request

certReceived_PendingCertificateRequest_20170720_1148PM

 

IIS Certificate Wizard – Process a Pending Request

certReceived_ProcessAPendingRequest_20170720_1149PM

IIS Certificate Wizard – Process a Pending Request – Browse

certReceived_PendingCertificateRequest_Open_20170720_1150PM

IIS Certificate Wizard – Process a Pending Request – File Selected

certReceived_ProcessAPendingRequest_20170720_1150PM (Brushedup)

 

IIS Certificate Wizard – Process a Pending Request – SSL Port

 

certReceived_PendingCertificateRequest_SSLPort_20170720_1151PM

 

IIS Certificate Wizard – Process a Pending Request – Certificate Summary

certReceived_PendingCertificateRequest_CertificateSummary_20170720_1152PM (BrushedUp)

 

IIS Certificate Wizard – Process a Pending Request – Completing the Web Server Certificate Wizard

certReceived_PendingCertificateRequest_Completing_20170720_1152PM

 

IIS Certificate Wizard – Process a Pending Request – Completed Web Server Certificate Wizard

certReceived_PendingCertificateRequest_Completed_20170720_1153PM

 

Review Certificate

In this section, we use IIS Manager to review the Certificate.

Steps

  1. Launch IIS Manager
  2. Access Web Site
  3. Access the “Directory Security” tab
    • Click the “View Certificate” button
  4. The “Certificate” window appears
    • Window – Certificate // Tab –  General
      • Issued To
      • Issued By
        • Let’s Encrypt Authority X3
      • Valid from
        • Valid from Begin to End Date
        • In our case 7/20/2017 thru 10/18/2017
    • Window – Certificate // Tab –  Details
      • Issuer
          • Let’s Encrypt Authority X3
      • Valid from
      • Valid To
      • Subject
        • Common Name
      • Public Key
        • Length
        • Integration Guide
          Link

          • Let’s Encrypt accepts RSA keys from 2048 to 4096 bits in length
        • In our case 4096
    • Window – Certificate // Tab –  Certification Path
      • Certificate Path
        • Issuer
        • Issued To
      • Certificate Status :-
        • This certificate is OK

Certificate – View – General

certView_General_20170720_1143PM (Brushedup)

 

Certificate – View – Details

certView_Details_20170720_1154PM - (BrushedUp)

 

Certificate – View – Certificate Path

certView_CertificatePath_20170720_1154PM (BrushedUp)

 

References

  1. GoDaddy
    • IIS 8/Windows Server 2012: Generate CSRs (Certificate Signing Requests)
      Link
  2. Certificate Requests
    • Specifications
      • Bit Length
        • Integration Guide
          Link
        • Is it possible?
          Link

SQL Server Agent Roles

Background

We are evaluating using BMC’s Control-M, our corporate IT Job Management tool.

And, wanted to see what we will have to do to get it to work against SQL Server Instances.

 

SQL Server Agent Roles

In SQL Server, jobs are managed through SQL Server Agent.

PreDefined Roles

SQL Server Agent has predefined security roles.

SQL Server Management Studio ( SSMS )

Jobs are saved in the system database, msdb.

To view the roles, please do the following:

  1. Launch SQL Server Management Studio (SSMS)
    • Connect to the SQL Server Instance
    • Choose System Databases
    • From the list of System Databases, choose msdb
    • Within the msdb database, transverse to Security \ Roles \ Database Roles
    • The SQL Server Agent roles are noted having names that start with SQLAgent

       

 

Permission Set

Let us dig deeper into these roles and see what they afford us, where they are different, and what is the minimum we can get away with.

Concentric

The roles are listed in increasing order of privileges assigned.

More precisely as Microsoft would say it, they are concentric.

Looked up the term concentric and here is how it is defined:

They are of or denoting circles, arcs, or other shapes that share the same center, the larger often completely surrounding the smaller.

And, so we can see that MSFT’s documentation is very useful, and here it is in verbatim:

Link
The SQL Server Agent database role permissions are concentric in relation to one another — more privileged roles inherit the permissions of less privileged roles on SQL Server Agent objects (including alerts, operators, jobs, schedules, and proxies). 

Tabulated

Role Definition Details
SQLAgentUserRole Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own.  a) Have permission on owned jobs
SQLAgentReaderRole SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. a) List all jobs – their properties, schedules, and executionhistory
SQLAgentOperatorRole SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole and SQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the server. a) Manage ( enable or disable jobs, edit job steps )

b) They can execute, stop, or start jobs

c) Delete job execution history

 

Which Role?

For system jobs we do not want an external job manager as SQL Server Agent is able to do so sufficiently.

We only want an external job manager for specific jobs.

Let us review the predefined system roles and judge their appropriateness for what we have in mind:

  1. SQLAgentOperatorRole
    • Affords all roles to all jobs
    • Too much for us
  2. SQLAgentReaderRole
    • (+)
      • Able to create and and manage own job
      • Read privileges on all jobs; their steps, schedule, and run history
    • (-)
      • Job Management does not need to view job data nor review job run history
        • A bit much for our targeted need
  3. SQLAgentUserRole
    • Requirements
      • (+)
        • Create own jobs
        • Run owned jobs
          • Existing jobs ownership can be re-assigned
      • (-)
        • Job has be owned
          • We have to review what is the ramification of changing job ownership for each specific job

 

Follow Up

Our follow-up task is to review the impact of changing job ownership for specific jobs.

 

References

  1. Microsoft
    • SQL Server Agent Fixed Database Roles
      Link
    • Implement SQL Server Agent Security
      Link

 

Webprofusion Ltd – Certify The Web – Day 1

Background

Security is being in the news a lot lately.

In this post, we will talk about using SSL, specifically reaping SSL certificates from LetsEncrypt.Org via “WebProfusion Ltd – Certify the Web“.

LetsEncrypt.Org

Client Options

Here are the Client Options available for Windows

Link

WebProfusion Ltd – Certify GUI –
(.Net, WinForms )

In this post, we will go with WebProfusion Ltd – Certify the Web.

 

Requirement

Outline

  1. Network
    • DNS
  2. Website Availability
    • Website Availability Test
  3.  Software
    • Microsoft .Net v4.5
  4. Microsoft IIS
    • Bindings

Network

DNS

DNS Requirement

From a networking standpoint, the LetsEncrypt validation servers have to able to connect to the originating computer.

That rules out the following:

  1. Servers that are not reachable over the Internet
    • Servers that only have local IP Addresses

 

DNS Server Names

Here are a couple of popular DNS Servers:

Vendor Link DNS-1 DNS-2
Verisign  Link  64.6.64.6  64.6.65.6
Google  Link  8.8.8.8  8.8.4.4
OpenDNS  Link  208.67.222.222  208.67.220.220

 

DNS Validation
nslookup

On MS Windows, we can use nslookup to validate.

Syntax

Here is the syntax


nslookup [FQDN] [dns-server]

Sample – DNS – Google ( 8.8.4.4 & 8.8.8.8 )
Code

nslookup web.labDomain.org 8.8.8.8 

Output

Sample – Verisign ( 64.6.64.6 & 64.6.65.6 )
Code

nslookup web.labDomain.org 64.6.64.6 

Output

 

Website Availability

Website Availability Test

Here are some availability tools:

  1. Uptrends

 

Uptrends.com

Go to https://www.uptrends.com/tools/uptime.

Intentionally entered an invalid URL, in this case upTimeTest.cnn.com

Uptrends.com – Sample

uptimeTest.cnn.com

We entered a FQDN that we know is not available.

hyattHouse.com

We entered hyattHouse.com and we are able to successfully validate.

 

Software

Microsoft .Net Framework v4.5

Although the software can be installed without first installing .Net v4.5, it can not be used.

If one tries to do so, the user is prompted to install .Net 4.5.

BTW, .Net v4.5 has its own requirement in terms of minimal OS.  And, those are:

  1. Windows 2003
    • .Net v4.5 can not be installed on MS Windows 2003
  2. Windows 7
  3. Windows 2012

 

Microsoft IIS

IIS – Site Bindings

Internet Information Server ( IIS )

Site Bindings

We can use IIS Manager and access the Site Bindings

Site Bindings – Original

 

Site Bindings – Add Binding

Click on the “Add..” button.

Add each hostname or alias that you will like to generate certificate for.

Please add only http entries.

The https will be added for you.

 

Site Bindings – After adding
  
Explanation

In the screen above, we have added the hostname that we will like exposed.

 

Download

Downloaded “Certify The Web” from the Vendor’s website.

As of 2017-July-15th, the current version is V2.0.7-beta4.

Installation

ScreenShots

License Agreement

Image

 

Select Destination Location

Image

Explanation

  1. 9 MB

 

Select Start Menu Folder

Image

 

Ready to Install

Image

Installing ….

Image

Complete the Wizard

Image

 

Usage

Launch “Certify the web“.

Initial Screen

Empty Canvas

New Certificate

Click the “New Certificate” button.

Managed Sites – New Certificate – Options

Image

Explanation

  1. Select IIS Site
    • Chose the IIS Site
  2. Name
    • The Name is only figurative
  3. Primary Domain Name
    • Please choose the Domain Name
    • If none shown, please visit the TroubleShooting section
  4. Alternative Domain Subject Name
    • All of the hostnames registered in the Site Bindings are listed

 

Managed Sites – New Certificate – Advanced

 

 

Explanation

  1. Auto create/update IIS bindings ( use SNI )
    • Chose to use SNI
      • Please read more about SNI ( Server Name Indication )
      • As always Wikipedia is a good source and here is the Link

 

Once you are comfortable with your choices, please click the Save button.

 

Request Certificate

Here are the steps for actually requesting a certificate.

Saved Certificate Request

Here is the screen once a Certificate is Requested.

Image

 

Certificate Received and Installed

Image

Explanation

  1. In the image above, our request has been validated, a certificate has been issued, and installed on our machine.

 

Troubleshooting

Primary Domain Name

Primary Domain Name – Empty

In the example that follows we just installed the Application and we are trying to add a “New Certificate”.

New Certificate

Error – “A primary domain must be selected”

Explanation:

  1. The error message states “A Primary Domain” must be selected
    • The reason is because we have not selected “Primary Domain Name

 

Remediate:

To fix, please …

  1. Launch IIS Manager
  2. Access Site
  3. Under Sites, select the Web Site
  4. In the Action Panel
    • Under Edit Site, Choose Bindings…
  5. In the “Site Bindings” window
    • Review listed Site Bindings
    • If not listed, click the “Add” button
      • The “Site Binding” window appears
        • In the Host name text box, add the host’s “Fully Qualified Domain Name

 

Summary

If you are running at a minimum MS Windows 7 ( desktop)  or 2012 ( server ), you should consider “Certify The Web“.

There is a lot more as this is only Day ONE.

 

References

  1. Certify The Web
    • Home Page
      Link
    • Docs
      Link
    • Getting Started
      Link
    • Issues
      • Issues – does not give list of possible domains #83
        Link
  2. Server Name Indication
  3. Browser – SSL
    • Google Chrome
      • Akemi Iwaya
        • Akemi Iwaya – How Do You View SSL Certificate Details in Google Chrome?
          Link
  4. DNS Servers – Public
    • Lifewire
      • LifeWire – Free & Public DNS Servers
        Link