YouTube – Upload Audio Files – Using Microsoft Movie Maker (v2012)

Background

It seems easy enough.  There are some audio files that I will like to upload to YouTube.

But, Youtube is kicking back that it does not accept Audio Files.

 

Error Message

Image

TheFileYouUploadedContainsOnlyAudioAndNoVideo

 

Textual

The file you uploaded contains only audio and no video. Learn how to easily convert your audio track to a video file type.

The hyperlink directed us to:

Convert audio and image files into a format that will work on YouTube

Microsoft Movie Maker – v2012

Movie Maker is bundled as part of Microsoft Live Essential.

Depending on my OS, I will need a different version of Live Essential.

OS Matrix and Software Version

http://windows.microsoft.com/en-us/windows-live/essentials

OS  Product Version Availability
 Windows 8.1, Windows 8, and Windows 7 Windows Movie Maker 2012 ( Windows Essential 2012 ) Available
 Windows Vista Windows Movie Maker 6.0 for Windows Vista (  Windows Essential 2011 ) Bundled in Vista
  Windows Vista  Windows Movie Maker 2.6 for Windows Vista Available
 Windows XP  Windows Movie Maker 2.1 Bundled as part of MS Windows XP SP2 and SP3

 

 

Download Location

Thankfully, On a blog post, Microsoft’s Aviraj Ajgekar provided a link for Windows Essential 2012.  The link is http://g.live.com/1rewlive5-all/en/wlsetup-all.exe

 

Installed Application

Downloaded and installed Application.

 

Usage

Here are quick steps on how to create a video file out of an audio file.

  1. Avail Audio File
    • For us, we downloaded Ray Stedman’s Audio file on Prayer
  2. Select a background image
    • Choose Ray’s picture from Googling his name
  3. Launch Windows Movie Maker v2012
    • Choose to create a new project ( File / New Project )
    • From the menu access the “Add videos and photos” icon ( AddVidoesAndPhotos )
      • From the File menu, navigate to the folder where our photos were placed
    • From the menu access the “Add Music” icon ( AddMusic )
      • From the File menu, navigate to the Folder where our audio ( mp3 ) files were placed
    • Get duration of the Audio
      • Double click on the Audio
      • Context sensitive information on the selected media is shown at the top of the Application
      • Here is what ours look like ( when we selected the green audio strip )
        GetDurationOfAudio
      • Note the Start and End Point; in our case 0s and 1776 s
    • Set the duration of the Picture element to be same as the Audio
      • Double-click on the Picture Element
      • Again, the top panel reflects specificalities of the chosen element
        pictureDurationInitial
      • Match the duration
      • Here is what our screen looks like, post matchpictureDurationSet
  4. The window changes to reflect the fact that we have extended the duration of the pictures
    PostPictureSizeChange
  5. Save the movie via the “File” \ “Save Movie” menu option
    SaveMovie_v2
    Of course, we chose “YouTube”

Images

A little out of place, but here are the pictures we took.

Picture Added

AddedPicture

Audio  Added

AddedPictureAndLaterAudio

The difference between this image and the one preceding it is the AudioAdded

 

Upload

We now have a good, well package mp4 file that YouTube will allow us to upload as a movie.

Result

Here are Ray Stedman’s Audio artifacts that we are able to package as video (mp4) files.

Media Audio Video
Ray C. Stedman – The Cirmcumcised Life Audio Video
Ray C. Stedman  – How Prayer Works Audio Video
Ray C. Stedman –  The Furnace and the Lamp Audio Video

 

 

 

Google Chrome and Adobe Flash

Background

We all know that having multiple windows and tabs opened in Chrome, can beat up your machine.

So we struggle with one of the pointers to Attention Deficit Disorder (ADD).

Living with it

So we live with it, until we click on a new link, and everything drags down for what seems like moments.

And, we are left wondering what is it this time.

 

Disable Flash

To disable Flash, please do the following:

  1. In the address box, enter “chrome://plugins/”
  2. Seek out “Adobe Flash Player”
  3. Within “Adobe Flash Player” group box, click the “Disable” link

 

Current

Plugins-Current

 

Post Changes

Plugins

 

Is Flash Installed and Enabled?

Here are some web sites that we can use to determine if Flash is installed and enabled for your current browser…

  1. What Is
    https://www.whatismybrowser.com/detect/is-flash-installed

 

When Flash is Enabled

FlashInstalled

 

When Flash is Disabled

FlashDisabled

Metering

Let us measure the impact of having Flash enabled.

I will use the web page that got me thinking…

Start / Stop / Enable / Disable Terminal services from command line
http://www.windows-commandline.com/start-terminal-services-command-line/

 

Instrumentation

We will simply use the Task Manager that comes with Google.

To access the Task Manager:

  1. Click on the menu – menu
  2. And click on “More tools” \ “Task Manager”:

 

LaunchTaskManagerClipped

 

With Flash Enabled

Single Page Using Flash

TaskManager-Plugin-Shockwave-NoBroker

 

Quick Explanation:

  1. When flash is enabled, we have a Task by the name – Plugin: Shockwave Flash
  2. The plugin is using 76 MB

 

 

Multiple Pages Using Flash

TaskManager-Plugin-Shockwave

 

Quick Explanation:

  1. When multiple pages are using flash, we have two plugins ( Plugin Broker : Shockwave Flash and Plugin: Shockwave Flash )
  2. The Plugin Broker uses 14 MB and the actual plugin is at 71 MB

 

With Flash Disabled

TaskManager-Plugin-ShockwaveNo

 

Quick Explanation:

  1. No more task listed for the Flash Plugin

Summary

I think with the advent of HTML5, I for one can jettison Flash.

Love my YouTube Videoes, thankfully most of them have been converted from FLASH to HTML5…What an herculean effort that must have been.

As Flash Videos are often autoplay, one can feel a bit overwhelmed with all the side shows.

It is not just the memory consumed, but also the CPU and Network trffic that is negotiated.

Later

I think anyone who edits via WordPress will agree that the WordPress editor is itself a healthy eater of resources; especially memory.

References

  1. Adobe Flash Player plug-in
    https://support.google.com/chrome/answer/108086?hl=en
  2. Reduce Chrome Memory
    https://support.google.com/chrome/answer/6152583?hl=en

Microsoft – Task Manager – lsass.exe – Consistent I/O Read of 3

Forward

It is a MS Windows 2003 box and noticed a very busy hard drive.

 

What is pegging the Hard Drive?

Task Manager

Let us launch Task Manager and include “Process ID”, “I/O reads” and “I/O Writes” in the list of columns we are interested in trending.

Here is a capture we took at 08:08 PM

TaskManager0808PM

 

Another screen capture at 08:08 PM

TaskManager0808PMv2

 

Explanation

Here is IO reads at the beginning and end of our time slot.

csrss.exe is growing the biggest.

  1. svchost.exe
    • 4397 – 4248 = 149
  2. lsass.exe
    • 3154 – 2194 = 960
  3. csrss.exe
    • 2975 – 1378 = 1597

 

Dig More into lsass.exe

SysInternals – Process Explorer

Image

image

 

Explanation

  1. Path
    • We are tracking the right lsass.exe; as it is the one in C:\Windows\System32
    • One never knows if a virus is bearing same name
  2. The Parent Process’s name is winlogon.exe
    • That is lsass.exe is started whenever a user logs on

 

Performance

performance

 

Explanation

  1. We confirmed the Read and Write Deltas of 3

 

Services

Services

 

Explanation:

The services that are reliant on our process are:

  1. NetLogon
  2. PolicyAgent
  3. ProtectedStorage
  4. Security Accounts Manager

All of them are vital security related services.

 

TCP/IP

TCPIP

 

Explanation:

  1. TCP ports:
    • 1205
  2. UCP Ports
    • IP interfaces :- All – Ports – 4500,500
    • IP Interfaces :- Localhost [127.0.0.1] only :- Ports – 1026

 

Security

Security

 

 

Explanation:

  1. Review privileges. i.e. SeImpersonatePrivilege, SeManagerVolumePrivilege

 

Services – Terminal Services

Our read and write delta is at 3.  From Google the culprit is usually “Terminal Services”.

If you can live without “Remote Desktop” experience, disable “Terminal Services”

Control Panel – Applet – Services

Permanent Solution

Current Setting

Review Services applet and pay attention to “Terminal Services”

TerminalServicesManual

Explanation:
  1. Even though at Manual, something is triggering its initiation.
  2. As we noted that it is started

 

Disable Service

Let us go in and disable the Service

TerminalServicesDisabledUnableToStop

 

Explanation
  1. We were able to disable the Service
  2. But, from the GUI, we are unable to stop currently running Service \ Process
  3. To effect the change we have to restart the box

 

Session Solution

Let us stop any current process

Identify Process ID

Textual


   tasklist /svc | findstr /C:TermService


Image tasklist

Abruptly Stop Process

Be very careful with this step and only take if you must!

Map Process to Services

Command


   tasklist /svc | findstr /C:TermService

Image

mapExecutableToService

 

If Lone Service Using Process, kill Process

If Terminal Services is the only process being hosted by our container ( svchost.exe ), then it is safe to kill it.

 


   taskkill /F /FI "SERVICES eq TermService"

 

killServiceProcess

Other Checks & Changes

  1. Turn off CDROM Autorun
  2. Disable un-needed services and change services of other services from Automatic to Manual
  3. Turn off visual effects
  4. Review Auto Start up programs
  5. Temporarily enable OpenFiles and review constantly “opened files
  6. Review “Automatic Updates” settings

 

CDROM

The registry fix:

  • Click Start, and then click Run.
  • Type regedit, and then click OK.
  • Locate and then click the following registry subkey:
  • HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\CDRom
  • If the value for Autorun is 1, right-click Autorun, and then click Modify. In the Value data box, type 0, and then click OK.

 

Services – Microsoft OS

  • Consider disabling the following services
    • Terminal Services ( unless you require Remote Desktop access )
    • Web Element Manager ( Provides access to extensible Web user interface elements for a remotely managed server. If this service is stopped, it will restart automatically. If this service is disabled, the Remote Administration Tools Web user interface for server administration will not function properly )
    • Indexing Service
  • Consider changing the “Status” on the following services to manual
    • Print Spooler
    • Computer Browser

Performance Options

Change “System Properties – Performance Options – Visual Effects” from having “Let Windows choose what’s best for my computer” to “Adjust for performance“.

Let Windows Choose

Original

Adjust for best performance

Revised

 

Auto Start Applications

Use mconfig.exe or another tool to review applications that are auto-starting.

Startup-cftmon

 

OPENFILES

Syntax


openfiles /Query

Output

OpenedFiles

Wordpres

Have to dedicate to Google and WordPress.

The post was originally started on April 28th, 2014:

Revision History

RevisionHistory

Exactly, 1 year and 4 months later, I can change it’s status from Private to Published:

Original

 

References

LSASS.EXE

 

Services – Terminal Services ( TermServices)

 

CD AUTORUN

 

OpenFiles

SQL Server – Data Encryption – Compare Bulk Data Retrieval

Background

As a follow up to a foundational post on data encryption, let us compare the cost of reading and persisting the read data.

DDL

We will create Views and Stored Procedures.

DDL – Retrieve Data – Declarative



if object_id('[dataPlain].[vw_accountHolder]') is null
begin

	exec('create view [dataPlain].[vw_accountHolder] as select 1/0 as [shell]' )

end
go


alter view [dataEncrypted].[vw_accountHolder]
as

alter view [dataPlain].[vw_accountHolder]
as

	SELECT
		  [id]
		, tblAH.[entityID]
		, tblAH.[firstname]
		, tblAH.[lastname]

		, tblAH.[addedBy]   
		, tblAH.[addedOn]   

	from    [dataPlain].[accountHolder] tblAH

go

DDL – Retrieve Plain Data – Procedural


if object_id('[dataPlain].[usp_accountHolder_Retrieve]') is null
begin

	exec('create procedure  [dataPlain].[usp_accountHolder_Retrieve] as select 1/0 as [shell]' )

end
go


alter procedure [dataPlain].[usp_accountHolder_Retrieve]
as

	set nocount on;

	SELECT
		  [id]
		, tblAH.[entityID]
		, tblAH.[firstname]
		, tblAH.[lastname]

		, tblAH.[addedBy]   
		, tblAH.[addedOn]   

	from    [dataPlain].[accountHolder] tblAH

go

DDL – Retrieve Encrypted Data – Declarative



if object_id('[dataEncrypted].[vw_accountHolder]') is null
begin

	exec('create view [dataEncrypted].[vw_accountHolder] as select 1/0 as [shell]' )

end
go


alter view [dataEncrypted].[vw_accountHolder]
as

   SELECT
	  [id]
	, cast
		(
		    DecryptbyKeyAutoCert
		    (
			  cert_id('certPIA')
			, null
			, tblAH.[entityID]
			, 1
			, CONVERT(varbinary(8000), tblAH.[id])
		    )
			as varchar(80)
		) as [entityID]

	, cast
		(
		    DecryptbyKeyAutoCert
		    (
			  cert_id('certPIA')
			, null
			, tblAH.[firstname]
		        , 1
			, CONVERT(varbinary(8000), tblAH.[id])
		    )
			as nvarchar(80)
		) as [firstname]


	, cast
	       (
		   DecryptbyKeyAutoCert
		   (
			  cert_id('certPIA')
			, null
			, tblAH.[lastname]
			, 1
			, CONVERT(varbinary(8000), tblAH.[id])
		  )
			as nvarchar(80)
		) as [lastname]

	, tblAH.[addedBy]   
	, tblAH.[addedOn]   

  from    [dataEncrypted].[accountHolder] tblAH


go

 

DDL – Retrieve Encrypted Data – Procedural

 




if object_id('[dataEncrypted].[usp_accountHolder_Retrieve]') is null
begin

	exec('create procedure  [dataEncrypted].[usp_accountHolder_Retrieve] as select 1/0 as [shell]' )

end
go


alter procedure [dataEncrypted].[usp_accountHolder_Retrieve]
as

   set nocount on;

   SELECT
	  [id]
	, cast
		(
		   DecryptbyKeyAutoCert
		   (
		      cert_id('certPIA')
		    , null
		    , tblAH.[entityID]
		    , 1
		    , CONVERT(varbinary(8000), tblAH.[id])
		   )
		   as varchar(80)
		) as [entityID]

	, cast
	   (
		DecryptbyKeyAutoCert
		(
	  	    cert_id('certPIA')
		  , null
		  , tblAH.[firstname]
		  , 1
		  , CONVERT(varbinary(8000), tblAH.[id])
		)
		  as nvarchar(80)
	   ) as [firstname]


	, cast
		(
	  	  DecryptbyKeyAutoCert
		  (
		     cert_id('certPIA')
	 	  , null
		  , tblAH.[lastname]
		  , 1
		  , CONVERT(varbinary(8000), tblAH.[id])
		 )
		  as nvarchar(80)
	     ) as [lastname]

	, tblAH.[addedBy]   
	, tblAH.[addedOn]   


  from    [dataEncrypted].[accountHolder] tblAH

go

Performance Measurement

Read data

Simple read data and display result on Screen/Grid.

Notice this is quite in-efficient and we are only attempting to do so with the fore-thought that our data set is only a thousand records.

Code


set nocount on;
go

set statistics io on
go


print '*****************************************************************************************'
print 'Plain data via Declarative Code'
print '*****************************************************************************************'
select *
from   [dataPlain].[vw_accountHolder]

print ''

print '*****************************************************************************************'
print 'Plain data via Procedure Code'
print '*****************************************************************************************'
exec [dataPlain].[usp_accountHolder_Retrieve]

print ''

print '*****************************************************************************************'
print 'Encrypted data via Declarative Code'
print '*****************************************************************************************'

select *
from   [dataEncrypted].[vw_accountHolder]

print ''

print '*****************************************************************************************'
print 'Encrypted data via Procedural Code'
print '*****************************************************************************************'
exec [dataEncrypted].[usp_accountHolder_Retrieve]


Query Plan

displayData

 

Interpretation

  • When we read plain data view direct database table query and through Stored Procedure, our Query Cost is 16%
  • When we read encrypted data via view and through Stored Procedure our Query Cost is 34%
  • We see that encrypted query is twice as expensive
  • Also, queries targeting Encrypted  Objects bears “Restricted Text” for Stored Procedures

 

IO

StatisticsIO

Interpretation

  • When we read plain data view direct database table query and through Stored Procedure, our Statistics IO is 19
  • When we read encrypted data via view and through Stored Procedure our Statistics IO is 43
  • We see that encrypted query is twice as expensive in terms of IO

 

 

 

Read & Capture Data into a table

Code



set nocount on;
go

set statistics io on
go


declare @accountHolder TABLE
	(
		  [id]		bigint not null --identity(1,1)
		, [entityID]    varchar(60) not null  
		, [firstname]   nvarchar(60) not null
		, [lastname]    nvarchar(60) not null

		, [addedBy]     nvarchar(60) not null

		, [addedOn]     datetime not null

	)

print '*****************************************************************************************'
print 'Plain data via Declarative Code'
print '*****************************************************************************************'
insert into  @accountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
select *
from   [dataPlain].[vw_accountHolder]

print ''

print '*****************************************************************************************'
print 'Plain data via Procedure Code'
print '*****************************************************************************************'
insert into  @accountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
exec [dataPlain].[usp_accountHolder_Retrieve]

print ''

insert into  @accountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
select *
from   [dataEncrypted].[vw_accountHolder]

print ''

print '*****************************************************************************************'
print 'Encrypted data via Procedural Code'
print '*****************************************************************************************'

insert into  @accountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
exec [dataEncrypted].[usp_accountHolder_Retrieve]

 

Query Plan

QueryPlan

 

Interpretation

  • When we consumed plain and encrypted data via views our Query Cost is at 41% and 44% respectively
  • When we consumed same data via Stored Procedures our Query Cost is 2% for plain data and 8% for encrypted data
  • It seems that the SQL Engine is not properly computing the costs of accessing encrypted data

 

Statistics IO

StatisticsIO

Interpretation

  • Plain data
    • Consuming plain data read from the view resulted in about 19 logical reads
    • Same data when captured from a Stored Procedure costed same 19, but there is an added on 1013 for our targeted table or 2150 from a work table
  • Encrypted data
    • When reading encrypted data from a view, we expended 43 logical reads
    • It costed an additional 1013 for our destination table, and 2140 for a worktable

 

More on Insert Into / Exec Stored Procedure

Let us dig more into Insert Into/Exec SP.

This time rather than to place data into temporary or Table Variable, we will place data into an actual table.

Also, no encrypted data, just plain data!

Code




use [dbLAB]
go

set nocount on;
set statistics io on
go


if object_id('zzzAccountHolder') is null
begin

	create table zzzAccountHolder
	(
		  [id]		bigint not null --identity(1,1)
		, [entityID]    varchar(60) not null  
		, [firstname]   nvarchar(60) not null
		, [lastname]    nvarchar(60) not null

		, [addedBy]     nvarchar(60) not null

		, [addedOn]     datetime not null

	)

end

print '*****************************************************************************************'
print 'Plain data via Declarative Code'
print '*****************************************************************************************'
insert into  zzzAccountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
select *
from   [dataPlain].[vw_accountHolder]


truncate table zzzAccountHolder

print ''
print '********************************************************************************************************************************************************************************'
print ''


print '*****************************************************************************************'
print 'Plain data via Procedural Code'
print '*****************************************************************************************'
insert into  zzzAccountHolder
(
	  [id]	
	, [entityID]
	, [firstname] 
	, [lastname]  
	, [addedBy]   
	, [addedOn]   
)
exec [dataPlain].[usp_accountHolder_Retrieve]


Query Plan

QueryCost

Explanation

  1. View
    • Querying and Consuming the view comes in at 88%
  2. Stored Procedure
    • Querying and Consuming the SP is a two step process
      • In the SP we query the data
      • In the Insert Statement, the yielding of the SP data is indicated as a “Parameter Table Scan

Statistics I/O

IOStats

The blindside is the Worktable I/O

WorkTable

The most expensive IO task.

Summary

Interesting results.

Expected the more expensive reads when querying encrypted data.

But, did not expect that consuming data from Stored Procedure will be far more expensive than reading same from a View.

 

Listening

Listening to Miranda…

Miranda Lambert – More like her

Miranda On Stage

SQL Server – Data Encryption – Foundational

Background

This is my first look at Data Encryption in MS SQL Server.

We will cover what is required and the general API sets.

In later posts, we will look at optimization, key management ( what needs to be secured), etc.

 

 

Key Management

Here the a couple of things that we need:

  1. Master Key
  2. Certificate
  3. Symmetric key

Master Key Creation



set nocount on;

if db_id('dbLAB') is null
begin

	exec('create database [dbLAB];')

end
go

use [dbLAB]
go

/*

	CREATE MASTER KEY (Transact-SQL)
	https://msdn.microsoft.com/en-us/library/ms174382.aspx


	BACKUP MASTER KEY (Transact-SQL)
	https://msdn.microsoft.com/en-us/library/ms174387.aspx

*/


declare @symmetrickeyIDMaster int

declare @keyAlgorithm			 varchar(30)
declare @keyLength				 smallint
declare @AlgorithmDescription    varchar(30)
declare @backupMasterKey	     bit = 0

declare @iFileExistsBackup INT

/*
	Symmetric Key - Master - 101
*/
set @symmetrickeyIDMaster = 101
set @backupMasterKey = 1

set @keyAlgorithm = null
set @keyLength = null
set @AlgorithmDescription = null


/*
	Get Info for Master Key
	by querying the sys.symmetric_keys catalog view.

	-- select * from sys.symmetric_keys tblSK
*/
SELECT 
		  @keyAlgorithm = tblSK.key_algorithm
		, @keyLength = tblSK.key_length
		, @AlgorithmDescription = tblSK.algorithm_desc

FROM   sys.symmetric_keys tblSK

WHERE  tblSK.symmetric_key_id = @symmetrickeyIDMaster


/*
	Create master key if it does not exist!
*/
if (@keyAlgorithm is null)
begin

	print 'Master key does not exist ...'
	
		CREATE MASTER KEY ENCRYPTION BY 
		PASSWORD = 'n8ceyAP6UHevudrawrucRus7';

	print 'Master key created ...'

end
else
begin

	print 'Master Key exists!'

end

if (@backupMasterKey = 1)
begin

	print 'Backup master key ....'

	/*

		Msg 15313, Level 16, State 1, Line 111
		The key is not encrypted using the specified decryptor.

	*/

	OPEN MASTER KEY DECRYPTION BY PASSWORD = 'n8ceyAP6UHevudrawrucRus7'


		exec master.dbo.xp_fileexist 
					  @filename = 'd:\SQLServerMasterKey4DB__dbLAB.mssqlMaskeyKey'
					, @iFileExistsBackup = @iFileExistsBackup output


		if (@iFileExistsBackup != 1)
		begin

			BACKUP MASTER KEY TO FILE = 'd:\SQLServerMasterKey4DB__dbLAB.mssqlMaskeyKey'
				ENCRYPTION BY PASSWORD = 'th8y7cuKuN5rathESt5JAcha';
		
			print 'Master key backed up'

		end
		else
		begin

			print 'Backup file already exist.  Skipping backup!'

		end


	CLOSE MASTER KEY;


end
go


Certificates



use [dbLAB]
go

/*
	CREATE Certificate
	https://msdn.microsoft.com/en-us/library/ms187798.aspx

*/


declare @certificateID int
declare @certificate   varchar(60) = 'certPIA'

set @certificateID = null

-- select * from sys.certificates dmvSC
select @certificateID = dmvSC.certificate_id
from   sys.certificates dmvSC
where  dmvSC.name = @certificate

if (@certificateID is null)
begin

	print 'Creating Certificate ...'

	CREATE CERTIFICATE [certPIA]
		AUTHORIZATION dbo
		WITH SUBJECT = 'certPIA'
			;
	
	print 'Created Certificate'


end
go


Create Symmetric Key


use [dbLAB]
go

/*
	CREATE SYMMETRIC KEY
	https://msdn.microsoft.com/en-us/library/ms188357.aspx

	DROP SYMMETRIC KEY
	https://msdn.microsoft.com/en-us/library/ms182698.aspx

*/


declare @symmetricKeyID		    int
declare @symmetricKeyLength	    int

declare @symmetricKey		    varchar(60) = 'symmetricKeyPIA'
declare @symmetricKeyidentityValue  varchar(60) = 'symmetricKeyPIA'

declare @symmetricKeyAlgorithm	    varchar(255) = null
declare @symmetricKeyAlgorithmDesc  varchar(255) = null

set @symmetricKeyID = null
set @symmetricKeyLength = null
set @symmetricKeyAlgorithm = null
set @symmetricKeyAlgorithmDesc = null


--select * from sys.symmetric_keys
select 
		  @symmetricKeyID = dmvSK.symmetric_key_id
		, @symmetricKeyLength = dmvSK.key_length
		, @symmetricKeyAlgorithm = dmvSK.key_algorithm
		, @symmetricKeyAlgorithmDesc = dmvSK.algorithm_desc
from   sys.symmetric_keys dmvSK
where  dmvSK.name = @symmetricKey

if  (@symmetricKeyID is null)
begin

    print 'Creating Symmetric Key ...'

   /*
    http://benjii.me/2010/05/how-to-use-sql-server-encryption-with-symmetric-keys/
    The IDENTITY_VALUE parameter is used to generate the guid for the key 
    and the KEY_SOURCE is used to generate the actual key. 
    This allows you to run the above code on any server as many times as you like, 
    with the same values, to generate the exact same key.
  */
  CREATE SYMMETRIC KEY [SymmetricKeyPIA]
	AUTHORIZATION [dbo]
	WITH 
		  ALGORITHM = AES_256
		, IDENTITY_VALUE = 'SymmetricKeyPIA'
		, KEY_SOURCE = 'SymmetricKeyPIA'

	ENCRYPTION BY CERTIFICATE [certPIA]
	;
	
	print 'Created Symmetric Key'


end

go


Targeted Objects

Tables

[dataPlain].[accountHolder]

 


set noexec off
go


if schema_id('dataPlain') is not null
begin

	set noexec on

end
go


create schema [dataPlain]
	authorization [dbo]
	;

go


/*

	drop table [dataPlain].[accountHolder];
	drop table [encryptionData].[accountHolder];

*/
if object_id('[dataPlain].[accountHolder]') is null
begin

	create table [dataPlain].[accountHolder]
	(
		  [id]		bigint not null identity(1,1)
		, [entityID]    varchar(60) not null  
		, [firstname]   nvarchar(60) not null
		, [lastname]    nvarchar(60) not null

		, [addedBy]     nvarchar(60) not null
							constraint  [dataPlain.defaultAddedBy]
								default SYSTEM_USER

		, [addedOn]     datetime not null
							constraint  [dataPlain.defaultAddedOn]
								default getdate()
							
  	    , constraint [dataPlain.PK_ACCOUNTHOLDER] primary key
			(
				[entityID]
			)


	)

end
go


 

[dataEncrypted].[accountHolder]

 



set noexec off
go

if schema_id('dataEncrypted') is not null
begin

	set noexec on

end
go


create schema [dataEncrypted]
	authorization [dbo]
	;

go

set noexec off
go




/*

	drop table [dataEncrypted].[accountHolder];

*/
if object_id('[dataEncrypted].[accountHolder]') is null
begin

 create table [dataEncrypted].[accountHolder]
 (

      [id]  bigint not null identity(1,1)
    , [entityID]    varbinary(300) not null  
    , [firstname]   varbinary(300) not null
    , [lastname]    varbinary(300) not null

    , [addedBy]	    nvarchar(60) not null
			constraint  [dataEncrypted.defaultAddedBy]
				default SYSTEM_USER

   , [addedOn]	   datetime not null
			 constraint  [dataEncrypted.defaultAddedOn]
			      default getdate()
							
   , constraint [dataEncrypted.PK_ACCOUNTHOLDER] primary key
		(
  		  [entityID]
		)
  )

end
go

 

Sample Data

SQL Data Generator

TemplateDefinition

Generated Data


INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('191-14-338', 'Damaris', 'Boyle');
INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('188-92-921', 'Pearl', 'Frye');
INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('328-41-549', 'Devan', 'Everett');
INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('812-31-537', 'Elliot', 'Nunez');
INSERT INTO [dataPlain].[accountHolder] ('entityID', 'firstname', 'lastname') VALUES ('602-56-283', 'Angel', 'Petersen');

 

Revised

Removed single quotes from column names


INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('191-14-338', 'Damaris', 'Boyle');
INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('188-92-921', 'Pearl', 'Frye');
INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('328-41-549', 'Devan', 'Everett');
INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('812-31-537', 'Elliot', 'Nunez');
INSERT INTO [dataPlain].[accountHolder] (entityID, [firstname], [lastname]) VALUES ('602-56-283', 'Angel', 'Petersen');


 

Encrypt data

 


set nocount on;

truncate table [dataEncrypted].[accountHolder]
go

/*
	https://msdn.microsoft.com/en-us/library/ms174361.aspx
	Using the SQL Server encryption functions together with the ANSI_PADDING OFF setting,
	could cause data loss because of implicit conversions. 
	For more information about ANSI_PADDING, see SET ANSI_PADDING (Transact-SQL).
*/
set ansi_padding on;

-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY [SymmetricKeyPIA]
   DECRYPTION BY CERTIFICATE  [certPIA];

set identity_insert [dataEncrypted].[accountHolder] on;

declare @keyGUID uniqueIdentifier = key_guid('SymmetricKeyPIA')

insert into [dataEncrypted].[accountHolder]
(
	  [id]
    , [entityID] 
	, [firstname]
	, [lastname]
)
select 
		    tblAH.[id]

		  , ENCRYPTBYKEY(
						  @keyGUID -- Is the GUID of the key to be used to encrypt the cleartext
						, CONVERT(varbinary(8000), tblAH.[entityID]) -- value
						, 1 -- add_authenticator
						, CONVERT(varbinary(8000), tblAH.[id]) -- authenticator is the ID Column
					 ) as [entityID]


		 , ENCRYPTBYKEY(
						  key_guid('SymmetricKeyPIA')
						, CONVERT(varbinary(8000), tblAH.[firstname])
						, 1
						, CONVERT(varbinary(8000), tblAH.[id])
  					   ) as [firstname]


		 , ENCRYPTBYKEY(
						  key_guid('SymmetricKeyPIA')
						, CONVERT(varbinary(8000), tblAH.[lastname])
						, 1
						, CONVERT(varbinary(8000), tblAH.[id])
  					   ) as [lastname]

from    [dataPlain].[accountHolder] tblAH

CLOSE SYMMETRIC KEY [SymmetricKeyPIA];


set identity_insert [dataEncrypted].[accountHolder] off;

 

 

Query Data

Query – Plain data


select top 5 
		  [id]
		, [entityID]
		, [firstname]
		, [lastname]
from   [dataPlain].[accountHolder]
order by id

 

queryDataPlain

 

Query – Encrypted data

Simple Mind

queryDataSimpleMind

Procedural



-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY [SymmetricKeyPIA]
   DECRYPTION BY CERTIFICATE  [certPIA];


select 
     tblAH.id
   , cast(
	    DECRYPTBYKEY
               (
		   tblAH.[entityID]
		 , 1
		 , CONVERT(varbinary(8000), tblAH.[id])
	       )
		as varchar(255)
	) as [entityID]



  , cast
	(
 	   DECRYPTBYKEY(
	 	           tblAH.[firstname]
			 , 1
			 , CONVERT(varbinary(8000), tblAH.[id])
			)
			as nvarchar(255)
	) as [firstname]


  , cast
	(
	  DECRYPTBYKEY(
	                  tblAH.[lastname]
			, 1
			, CONVERT(varbinary(8000), tblAH.[id])
	 	      )
		as nvarchar(255)
	) as [lastname]


from    [dataEncrypted].[accountHolder] tblAH

order by tblAH.[id] asc

CLOSE SYMMETRIC KEY [SymmetricKeyPIA];


Declarative


SELECT top 5
	  [id]
	, cast
		(
		   DecryptbyKeyAutoCert
			(
			      cert_id('certPIA')
			    , null
			    , tblAH.[entityID]
			    , 1
			    , CONVERT(varbinary(8000), tblAH.[id])
			)
			as varchar(80)
		) as [entityID]

	, cast
		(
		  DecryptbyKeyAutoCert
			(
			     cert_id('certPIA')
		  	   , null
			   , tblAH.[firstname]
			   , 1
			   , CONVERT(varbinary(8000), tblAH.[id])
			)
			as varchar(80)
		) as [firstnameIncorrectCastedToVarchar]

	, cast
	     (
		DecryptbyKeyAutoCert
		   (
		      cert_id('certPIA')
		    , null
		    , tblAH.[firstname]
		    , 1
		    , CONVERT(varbinary(8000), tblAH.[id])
		   )
			as nvarchar(80)
	     ) as [firstname]


	, cast
	    (
		DecryptbyKeyAutoCert
			(
		              cert_id('certPIA')
			    , null
			    , tblAH.[lastname]
			    , 1
			    , CONVERT(varbinary(8000), tblAH.[id])
			)
			  as nvarchar(80)
	  ) as [lastname]


from    [dataEncrypted].[accountHolder] tblAH

order by [id]
go

 

Comparative Analysis

Storage Requirements

Plain Data

plainData

Encrypted Data

encryptedData

Query Plan

encrypted

When dealing with encrypted data, prepare to see “Restricted Text”.

QueryCost-Restricted

Cleanup


/*
	Drop Table if it exists
*/
if object_id('[dataEncrypted].[accountHolder]') is not null
begin

	print 'Drop table - [dataEncrypted].[accountHolder]..'
	drop table [dataEncrypted].[accountHolder]

end
go

/*
	Symmetric Keys
*/
--select * from   sys.symmetric_keys dmvSK
if key_guid('SymmetricKeyPIA') is not null
begin

	print 'Drop SYMMETRIC KEY - SymmetricKeyPIA ..'

	DROP SYMMETRIC KEY [SymmetricKeyPIA]

end
go


/*
	Certificates
*/
--select * from  sys.certificates
if cert_id('certPIA') is not null
begin

	print 'Drop Certificate - certPIA ..'

	DROP CERTIFICATE [certPIA]

end
go


/*
	DROP MASTER KEY
*/

if exists
	(

		SELECT *
		FROM   sys.symmetric_keys tblSK
		WHERE  tblSK.symmetric_key_id = 101

	)
begin

	print 'Drop Master Key'

	DROP MASTER KEY;

end


 

Tecnical Summary

There we have it.

Authenticator

To thwart whole value substitution of encrypted data, we are using an authenticator.  In this case for the ease of simplicity the identity id of the row.

Please keep in mind that this value can not change through the life of our encrypted record.

 

Original Data Type

As encrypted data is saved as binary, one needs to keep a note of the original data types, once data is decrypted via DecryptbyKey or DecryptbyKeyAutoCert, one needs to cast/convert back to the original datatype.

SQL Server 2005 Install failed on MS Windows 7

Background

Unfortunately, failed while trying to install Microsoft SQL Server v2005 on a MS Windows 7.

Warning

Got the warning pasted below, but chose to ignore it.

Textual

To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 from the command prompt and include SKUUPGRADE=1 parameter

Image

SKUUpgrade

Diagnosis

Reviewed “C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap” and found the error message posted below.

Please keep in mind that 90 is version specific. 80 is SQL Server 2000, 90 is SQL Server 2005, 100 is SQL Server 2008, 110 is SQL Server 2012, 120 is SQL Server 2014.

 

Error Message


Microsoft SQL Server 2005 9.00.5000.00 
============================== 
OS Version      : Professional Service Pack 1 (Build 7601) 
Time            : Thu Aug 27 10:28:19 2015 
 
OAKWSHP001 : Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features.  Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.
OAKWSHP001 : Failed to find the ASP.Net Version Registration with Microsoft Internet Information Services (IIS).
OAKWSHP001 : To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
OAKWSHP001 : There was an unexpected failure during the setup wizard. You may review the setup logs and/or click the help button for more information. 


Resolution

Add Internet Information Server

Accessed Control Panel \ Programs \ “Programs and Features” \ “Turn Windows Features On and Off”:

AddIIS

Add Client Components

The error message says that we can not initiate setup via Control Panel, but through command line.

And, so here we go


set appPath=D:\Downloads\Microsoft\SQLServer\v2005\x64\CD1
Start /wait %appPath%\setup.exe SKUUPGRADE=1  UPGRADE=SQL_Engine INSTANCENAME=V2005 ADDLOCAL=Client_Components

Steps

Feature Selection

Please be sure to indicate that you want “Client Components“.

FeatureSelection

 

Setup Progress

SetupProgress

 

Program Compatibility

Choose “Run Program” button…

ProgramCompatibilityAssistant

 

Completing Microsoft SQL Server 2005 Setup

CompletingSetup

 

 

Summary

Again, not paying attention to Warnings, “you must run SQL Server 2005 from the command prompt“, cost me time.

In retrospect, we chose a full install without paying attention that we did not have IIS installed.  In SQL Server v2005, Reporting Services (RS) needs IIS.

Because of the failure that we experienced while trying to install RS, Client Components was not installed.

Later install of Client Components can not be initiated via Control Panel, but through Command Line.

 

Quotes

Roger Staubach

There are no traffic jams along the extra mile

It’s okay to have personal ambitions, but you have to take someone with you

More

Auditing Home Network using MS Windows Based Tools

Prelude

Here I am having gone crazy troubleshooting my home network.  And, so let us write down the little bit I found out.

 

Tools

Address Resolution Protocol ( ARP )

Get all entries in the ARP Pool

Syntax:


arp -a

Output:

arp-allinterface

 

In ARP Pool, get entries for Specific Interface

As we are only interested in the local network, let us get a bit more specific and restrict our search to only our intranet IP Address.

Syntax:


  arp -a -N [IP-Address]

Sample:


  arp -a -N 10.0.4.100

Output:

arp-interface-intranet

 

In ARP Pool, using Specific Interface, perform network sweep

Let us do a network sweep; by adding -v

Syntax:


  arp -a -v -N [interface]

Sample:


  arp -a -v -N 10.0.4.100

Output:

arp-interface-intranet-networksweep

 

Explanation:

  1. When no host
    • Physical Address :- 00-00-00-00-00-00
    • Type – invalid
  2. When internal host
    • Physical Address :- MAC Address
    • Type :- dynamic
  3. When Internet host
    • Physical Address :- MAC Address
    • Type :- Static
  4. Network broadcast
    • Physical Address :- MAC Address – ff-ff-ff-ff-ff-ff
    • Type :- Static

 

Ping -a

Syntax:


  ping -a [IP-Address]

Sample -1 :


  arp -a -N 10.0.4.6

Output:

ping-a-6

 

 

Sample -2 :


  arp -a -N 10.0.4.7

Output:

ping-a-7

 

Sample -3:


  arp -a -N 10.0.4.94

Output:

ping-a-94

Explanation:

So here I am going crazy.  How come I can’t figure out the hostname bearing 10.0.4.94

 

Angry IP Scanner

Downloaded Angry IP Scanner and Java’s JRE 

Ran it and got this back.

AngryIPScanner

So again, though we received back a ping’s response for 10.0.4.94, no corresponding hostname.

 

MAC Address Lookup

Took to the internet to find out who is the manufacturer for the MAC Address corresponding to the IP Address 10.0.4.94

Web Sites:

 

CheckMacAddress

So entered our MAC Address, but got back a vendor that I am still not that familiar with; specifically Azurewave Technologies, Inc., TAIWAN

Lookup

 

Wireless Access Point

Connected to our WAP and using the DHCP Client Table, here is what we received back:

DCHPClientTable

Finally, we have a match for IPAddress 10.0.4.94 /MAC Address 6C…..

Our match is the ChromeCast device that my brother brought us to be able to view youtube videos on the TV.

 

Summary

No country for old men!