SQL Server – Error – “Service Broker needs to access the master key in the database. Error code:32.”

Background

Reviewing SQL Server Instance for performance issue and noted the error message listed below :-

Error

Error Image

errorLog.20181211.1150AM

Error Text


Error: 28054, Severity: 11, State: 1

Service Broker needs to access the master key in the database 'IdfMC'.
Error code:32.
The master key has to exist and the service master key encryption is required.

Troubleshooting

Metadata

sys.databases

SQL

Sample
use [IdfMC]
go

select
           [database]
            = tblSD.[name]

         , tblSD.is_master_key_encrypted_by_server 

         , [currentDB]
            = case
                when (tblSD.[name] = db_name()) then 1
                else 0
              end

from   sys.databases tblSD

Output

sys.databases.before.20181211.1156AM

Output
  1. Table :- sys.databases
    • is_master_key_encrypted_by_server
      • 0 ( Master Key not encrypted )

sys.symmetric_keys

SQL

Sample
use [IdfMC]
go

select *

from sys.symmetric_keys

Output

sys.sysmmetric_keys.20181211.1157AM

Explanation
  1. Table :- sys.symmetric_keys
    • Empty

Remediation

Create Master Key

Outline

  1. Change Contextual Database
    • use [database]
  2. Issue “Create Master Key

SQL

Sample

use [IdfMC]
go

IF NOT EXISTS
(
    SELECT *
    FROM sys.symmetric_keys
    WHERE symmetric_key_id = 101
)
BEGIN

  PRINT 'Creating Database Master Key'

  CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Spuvephu3h'

END

Validate

Metadata

sys.databases

SQL

Sample
use [IdfMC]
go

select
           [database]
            = tblSD.[name]

         , tblSD.is_master_key_encrypted_by_server 

         , [currentDB]
            = case
                when (tblSD.[name] = db_name()) then 1
                else 0
              end

from   sys.databases tblSD

Output

sys.databases.before.20181211.1206AM

Output
  1. Table :- sys.databases
    • is_master_key_encrypted_by_server
      • 1 ( Master Key encrypted )

sys.symmetric_keys

SQL

Sample
use [IdfMC]
go

select *

from sys.symmetric_keys

Output

sys.sysmmetric_keys.20181211.1246PM

Explanation
  1. Table :- sys.symmetric_keys
    • Records
      • Name :- ##MS_DatabaseMasterKey##
      • Principal_id :- 1
      • symmetric_key_id :- 101
      • key_length :- 256
      • key_algoritm :- A3
      • algorithm_desc :- AES_256

 

Visual Studio – Xamarin – Traffic Light

Background

Here we go with our first small CSC 101 application in Xamarin.

Outline

  1. addControls
    • instantiate Button Object
    • If in manual mode
      • Add event handler for Button Click
    • Instantiate StackLayout
      • Add Button
  2. OnButtonClick
    • handle buttonclick event
      • call triggerNextState
  3. triggerNextState
    • Call Device.BeginInvokeOnMainThread
      • Have next method be processed on main thread
  4. reflectState
    • Update Button Text
    • Base next procession based on whether manual or automated
      • Enable Button
      • Update Button Background Color
  5. initializeData
    • Instantiate each trafficLightState
    • Add trafficLightState object to collection list
  6. initializeTimer
    • Instantiate timer
    • instantiate Time Event Handler
  7. Process Timer Event
    • call triggerNextState

Code

Add Controls


 void addControls()
{

	btnTL = new Button
	{

		  Text = btnLTText + " " + strMode
		, HorizontalOptions = LayoutOptions.StartAndExpand

	};

	if (bManualOrAutomated == false)
	{
		btnTL.Clicked += OnButtonClicked_btnTL;

	}

	// Assemble the page.
	this.Content = new StackLayout
	{

		Children =
		{
			new StackLayout
			{

				  // Stack Orientation
				  Orientation = StackOrientation.Horizontal

				  // Add Children
				, Children =
				{
					btnTL
				}

			 } // Stack Layout
		} // Children

	}; // Assemble the page.

} // addControls() 

OnButtonClicked_btnTL


void OnButtonClicked_btnTL(object sender, EventArgs args)
{

	Button button = (Button)sender;

	if (button == btnTL)
	{

		triggerNextState();

	}

}

triggerNextState


void triggerNextState()
{

	//prepare for next transition
	iTrafficLightStateCurrent = iTrafficLightStateCurrent + 1;

	try
	{
		Device.BeginInvokeOnMainThread
			(
				() =>
				{
					reflectState();
				}
			);
	}
	catch (Exception)
	{

	}

}

reflectState


void reflectState()
{

	try
	{

		//reset display
		//btnTL.Text = btnLTText;
		btnTL.Text = btnLTText + " " + strMode;

		if (dtSignalTime != null)
		{
			btnTL.Text += " ("
							+ dtSignalTime.ToLongTimeString().TrimStart()
							+ " )"
							;
		}

		btnTL.IsEnabled = (bManualOrAutomated == false);

		//reset marker if at end of line
		if (
				(iTrafficLightStateCurrent > iTrafficLightStatesCountZeroBased)
		   )
		{
			iTrafficLightStateCurrent = 0;
		}

		// get object at current node
		objTLSCurrent = objTLS[iTrafficLightStateCurrent];

		// if valid object
		if (objTLSCurrent != null)
		{

			//set background color
			btnTL.BackgroundColor = objTLSCurrent.color;

		}

	}
	catch (Exception ex)
	{

		//show exception
		btnTL.Text = ex.Message;

		//reset counter
		iTrafficLightStateCurrent = 0;

	}
}

initializeData


void initializeData()
{

	tlStateRed = new trafficLightState();
	tlStateRed.color = Color.Red;
	objTLS.Add(tlStateRed);

	tlStateGreen = new trafficLightState();
	tlStateGreen.color = Color.Green;
	objTLS.Add(tlStateGreen);

	tlStateYellow = new trafficLightState();
	tlStateYellow.color = Color.Yellow;
	objTLS.Add(tlStateYellow);

	// Get Number of Elements
	iTrafficLightStatesCount = objTLS.Count;

	// Get Number of Elements ( 0 Based )
	iTrafficLightStatesCountZeroBased = objTLS.Count - 1;

	// Set Current at 0
	iTrafficLightStateCurrent = 0;

}

initializeTimer


void initializeTimer(Boolean bTimer)
{

	if (bTimer)
	{

		// Create a timer with a two second interval.
		objTimer = new System.Timers.Timer(lSecondsInThousands);

		// Hook up the Elapsed event for the timer.
		objTimer.Elapsed += OnTimedEvent;

	}
	else
	{

		if (objTimer != null)
		{

			//unsubscribe
			objTimer.Elapsed -= OnTimedEvent;

			objTimer = null;

		}

	}

	if (objTimer != null)
	{

		objTimer.AutoReset = bTimer;

		objTimer.Enabled = bTimer;

	}

}

OnTimedEvent


private void OnTimedEvent(Object source, ElapsedEventArgs e)
{

    dtSignalTime = e.SignalTime;

    triggerNextState();

}

Source Code Control

GitHub

VS Studio Xamarin Traffic Light
Link

References

  1. Microsoft
    • Microsoft | Docs
      • Docs / .NET / .NET API Browser / Xamarin.Forms / Device / Methods / BeginInvokeOnMainThread
        Link

 

Innocence Down!

 

Videos

  1. Lesandro Guzman-Feliz, Junior
    • How a Gang Hunted and Killed a 15 Year Old in the Bronx | NYT – Visual Investigations
      • Profile
        • The murder of 15-year-old Lesandro Guzman-Feliz, known as “Junior,” drew outrage from across the city. Our investigation retraces his last steps — and reveals how a recent wave of violence in the Bronx took the life of a boy just two blocks from his home.
      •  Videos
        • Video #1
          Channel :- The New York Times
          Published On :- 2018-Sept-10th
          Link
    • The Junior Files
      • The Junior Files, Part 2: How the NYPD tracked the 14 suspects
        • Profile :- In the weeks before the savage slaying of “Junior” Guzman-Feliz, his mother saw other teens being rushed to the hospital where she works. They were getting stabbed near a street close to home. In Part 3 of #TheJuniorFiles, Mary Murphy talks to the mom who tried to heed the warning signs.
          Published On :- 2018-Sept-20th
          Channel :- PIX 11 News
          Link
      • The Junior Files, Part 3: Bronx teen’s mom had bad feeling about Adams Place
        • Profile :- In the weeks before the savage slaying of “Junior” Guzman-Feliz, his mother saw other teens being rushed to the hospital where she works. They were getting stabbed near a street close to home. In Part 3 of #TheJuniorFiles, Mary Murphy talks to the mom who tried to heed the warning signs.
          Published On :- 2018-Sept-20th
          Channel :- PIX 11 News
          Link
      • The Junior Files, Part 4: Police say Junior made one call before deadly gang chase
        • Profile :- That`s the word from a law enforcement source on Junior`s murder case, who told PIX11, “There`s no text on Junior`s phone records to come to Adams Place. Junior made a call.”
          Published On :- 2018-Sept-25th
          Channel :- PIX 11 News
          Link
      • The Junior Files, Part 5: `His Last Words Were `Water, Water”
        • Profile :- Three young women who live across the street from the bodega sidewalk where Lesandro “Junior” Guzman-Feliz was fatally stabbed by a mob on June 20 told PIX11 they ran to the store and then St. Barnabas Hospital after the vicious attack.
          Published On :- 2018-Sept-26th
          Channel :- PIX 11 News
          Link
  2. Shots Fired
    • Videos
      • Video #1
        Published On :- 2018-Nov-28th
        Channel :- Is this one new?
        Link
    • Participants
      • Smith
        • Quincy Smith
        • Gwendolyn Smith
      • Neighbors
        • Jon Jay Tompkins
    • Stories
      • CBS
        • CBS Evening News
          • Officer speaks about shooting after attempted killer sentenced
            Link
      • Fox News
        • Officer recalls near-fatal shooting captured by body cam in doc: ‘I did think I was going to die’
          Link

SQL Server – Error – “Full-Text Search is not installed, or a full-text component cannot be loaded”

Background

While trying to deploy an Application that I am working on ran into an expected error.

Error

Error Image

FullTextSearchIsNotInstalled.20181209.0457PM

Error Message


Msg 7609, Level 17, State 5, Line 31513
Full-Text Search is not installed, or a full-text component cannot be loaded.

Troubleshooting

Transact SQL

FULLTEXTSERVICEPROPERTY

Code


SELECT
	[IsFullTextInstalled]
	  = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

Output

metadata.FULLTEXTSERVICEPROPERTY.No.20181209.0511PM

Explanation

Full-text is not installed.

Remediation

Install and configure Full Text.

Outline

  1. Install
    • Install Fulltext
  2. Restart MS SQL Server Engine
  3. Validate FullText Installation

 

Install

Install Fulltext

Code

Syntax

sudo yum install -y {package}

Sample

sudo yum install -y mssql-server-fts

Output
Output – 01

yum.install.mssql-server-fts.20181209.0528PM.PNG

Output – 02

yum.install.mssql-server-fts.20181209.0537PM

Restart SQL Server Engine Services

systemctl

Syntax


sudo systemctl restart {service}

Sample

sudo systemctl restart mssql-server

FULLTEXTSERVICEPROPERTY

Code


SELECT
	[IsFullTextInstalled]
	  = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

Output

metadata.FULLTEXTSERVICEPROPERTY.Yes.20181209.0545PM

Explanation

Full-text is installed.

 

References

  1. Docs \ SQL \ SQL Server on Linux
    • Install SQL Server Full-Text Search on Linux
      Link

Dos Batch File – Special Characters

Background

If you end up having special characters as in the case of nice passwords and also needing to use delayed expansions in your batch file, you might have to escape those passwords.

Batch File

Deploy


@echo off

if not exist log mkdir log

SETLOCAL DISABLEDELAYEDEXPANSION

set "_errorlevelSaved="

REM Batch files - number of command line arguments ( nimrodm )
rem https://stackoverflow.com/questions/1291941/batch-files-number-of-command-line-arguments

set argC=0

for %%x in (%*) do Set /A argC+=1

echo Argument Count is %argC%

set "_app=sqlcmd.exe"

set _dbHost=%1

IF NOT "%2"=="" set "_dbUsername=%2"

IF NOT "%3"=="" set "_dbPassword=%3"

set "_appOption=-S %_dbHost% -b "

IF NOT "%_dbUsername%"=="" set "_appOption=%_appOption% -U%_dbUsername% "

IF NOT "%_dbPassword%"=="" set _appOption=%_appOption% -P"%_dbPassword%"

set "_filename=modules.txt"

echo appOption is %_appOption%

rem goto skipped

SETLOCAL ENABLEDELAYEDEXPANSION

set _fileCount=0

for /f "tokens=1,2 delims=," %%a in ('type %_filename%') do (

	set /A _fileCount+=1

	set _folder=%%a

	set _file=%%b

	set "_fileFull=!_folder!\!_file!"

	echo Processsing file !_fileCount!  - !_fileFull!

	%_app% %_appOption% -i !_fileFull!	

	set "_errorlevelSaved=%errorlevel%"

	rem echo _errorlevelSaved is %_errorlevelSaved%

	IF NOT _errorlevelSaved NEQ 0 GOTO exception

)

:skipped

goto completed

:exception

echo "Error Level %_errorlevelSaved%"

:completed

SETLOCAL DISABLEDELAYEDEXPANSION

endlocal

Invoke

Outline

  1. Server Name :- 118.90.10.1
  2. User :- dadeniji
  3. Password :- paul.winters

Actual Invocation


deploy 118.90.10.1 john paul.winters

Output

Output – Textual

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'dadeniji'..

Output – Image

sqlcmd.specialCharacters.inbatchfile.(dadeniji)20181209.0700AM

 

Workaround

In our case we have special characters in our password and we are getting failed logins, and so we have to escape each special character.

Outline

In this sample we escaped the period in our password.

  1. The actual password is paul.winters.
  2. The only character that needs to be escaped is the period.
  3. And, so we ended up with paul^.winters.

 

Actual Invocation


deploy 118.90.10.1 john paul^.winters

Output

sqlcmd.specialCharacters.inbatchfile.(john.smith)20181209.0707AM

Dedicated

Dedicating to Rob van der Woude’s.

Rob van der Woude’s Scripting Pages
Escape Characters
Link

.Net Frameworks – Installed Products

Background

Wanted to get a list of Microsoft .Net Frameworks installed on a machine.

Windows Management Interface ( WMI )

Introduction

In this post, we will use Windows Management Interface, WMI.

 

Powershell

Outline

  1. Get-WmiObject
    • Class :- Win32_Product
    • Filter :- Microsoft .Net Framework
    • Columns :-
      • Name
      • Version

Code


[char] $CHAR_WILDCARD="*"

$computer=$env:computername

$filter="Microsoft .Net Framework"

$filterWildcard= $filter + $CHAR_WILDCARD

Get-WmiObject -Class Win32_Product -Computer $computer | `

	Select @{"Name"="Name"; "Expression"={($_.Name.trim())}}, Version | `

	Where-Object -FilterScript {$_.Name -like $filterWildcard} | `

	Sort-Object Name, Version -Descending

Images

getVersionNumber_self_20181207_1209PM

Powershell

Outline

  1. Get-WmiObject
    • Class :- Win32_Product
    • Filter :- Microsoft .Net Framework
    • Columns :-
      • Name
      • Version

Code


set "_wildcard=%%"

wmic product where "Name like 'Microsoft .Net %_wildcard%' " get Name, Version

Images

MSWindowsBatch.20181207.1229PM

Visual Studio – Error – “Could not load file or assembly ‘netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51’ or one of its dependencies.”

Background

Upon preparing Visual Studio to be able to develop mobile applications courtesy of Xamarin, ran into a blocking issue right away.

Set the Stage

Setting the stage for our error is easy  and it goes :-

  1. Launch Visual Studio
  2. Elected to design new Project
  3. Chose Project based on Mobile / Xamarin
  4. The system prepares the baseline project
  5. Attempted to build the project

Error

Error Image

CouldNotloadfileorassemblynetstandard.20181207.0340AM.PNG

Error Text


Could not load file or assembly 'netstandard, Version=2.0.0.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies.

Troubleshooting

Microsoft .Net Version

Review the version of Microsoft .Net that is installed.

Windows Explorer

Launched Windows Explorer and entered %windir%\Windows\Microsoft.NET\Framework64 as targeted folder.

Image

WindowsExplorer.20181207.0401AM

Explanation

We have a Version 4 version of Microsoft .Net installed; The baseline version # is v4.0.30319.

Please keep in mind that patches to his baseline might be installed, as well.

 

Registered Files

Registered File – netstandard.dll

Accessed %windir%\Windows\Microsoft.NET\Framework64  to determine if the referenced Assembly, netstandard.dll is present.

Confirmed it is not.

Remediation

Microsoft .Net Version

Goal

Installing .net framework 4.7.1 and above will help!

Artifacts

Availability

Please access https://dotnet.microsoft.com/download ( Link ) and see if there are newer versions of .Net than the one you have installed.

As of this morning, 2018-12-07, here is what is available.

Image

DotNetArtifacts.20181207.0826AM

Explanation

The latest .Net Framework, not the .Net Core, is 4.7.2.

 

Product :- v4.7.2

Image

DotNetArtifacts.4.7.2.20181207.0832AM.PNG

Explanation
  1. .Net Framework 4.7.2
    • Products
      • Build apps – Dev pack
      • Run apps – Runtime

We chose the Developer pack ( offline installer ).

Installer

Install

Images
License Terms

licenseTerms.20181207.0417AM.PNG

Setup Successful

setupSuccessfully.20181207.0426.PNG

 

Restarted Machine

Restarted Machine

Validation

Assemblies

Assembly – netstandard.dll

Windows Explorer

Accessed Windows Explorer and targeted C:\Windows\Microsoft.NET\Framework64\v4.0.30319

Steps

Took the following steps :-

  1. Folder View
    • Columns
      • Added Date Created
    • Sort/Order
      • Ordering by Date Created
Image

WindowsExplorer.20181207.0843AM.PNG

Explanation
  1. netstandard.dll now included

 

Visual Studio

Project Build

Launched Visual Studio, loaded our mobile project, and it compiled successful with a couple of non-blocking warnings.

Image

newProject.errorList.noerrors.20181207.0516AM.PNG

Explanation

  1. As we do not have a Mac attached expected this warning.

 

Referenced Work

  1. xamarin
    • xamarin/Xamarin.Forms
      • Xamarin.Forms.Build.Tasks.GetTasksAbi build error #2667
        Link
    • forums.xamarin.com
  2. David Kean
    • Twitter ( @davkean )
      • Folks this is a bug in the Xamarin task – it’s making the assumption netstandard is in the GAC.
        Link