Friday, July 2, 2021

Getting logs related to a specific user login in Ubuntu 20.04

I was having some trouble on Ubuntu 20.04 with figuring out why a Active Directory user couldn't log in to the server via SSH, and eventually came up with this, which was inspired by the link below it.

sudo journalctl | grep <<username>>



From here:

https://serverfault.com/questions/130482/how-to-check-sshd-log


All messages about sshd:

journalctl -t sshd

or

journalctl -u ssh 


Messages about sshd from the last boot:

journalctl -t sshd -b0


Messages about sshd from the last boot in the reverse order:

journalctl -t sshd -b0 -r

Monday, June 21, 2021

Linux Log Locations

Linux log locations

. . . because I never remember


/var/log/messages : General log messages
/var/log/boot : System boot log
/var/log/debug : Debugging log messages
/var/log/auth.log : User login and authentication logs
/var/log/daemon.log : Running services such as squid, ntpd and others log message to this file
/var/log/dmesg : Linux kernel ring buffer log
/var/log/dpkg.log : All binary package log includes package installation and other information
/var/log/faillog : User failed login log file
/var/log/kern.log : Kernel log file
/var/log/lpr.log : Printer log file
/var/log/mail.* : All mail server message log files
/var/log/mysql.* : MySQL server log file
/var/log/user.log : All userlevel logs
/var/log/xorg.0.log : X.org log file
/var/log/apache2/* : Apache web server log files directory
/var/log/lighttpd/* : Lighttpd web server log files directory
/var/log/fsck/* : fsck command log
/var/log/apport.log : Application crash report / log file


Copied from
https://ubuntuforums.org/showthread.php?t=1568706

Friday, May 14, 2021

The trust relationship between this workstation and the primary domain failed (in server core)

 I got the error 

The trust relationship between this workstation and the primary domain failed

in server core after restoring from a snapshot in VirtualBox.

On my remote management machine, I tried to connect to the machine using Computer Management, which threw this not-quite-helpful error message.

Computer X cannot be managed. Verify that the network path is correct, the computer is available on the network, and that the appropriate Windows Firewall rules are enable on the target computer.

To enable the appropriate Windows Firewall rules on the remote computer, open the Windows Firewall with Advanced Security snap-in, and enable the following Inbound rules:

COM+ Network Access (DCOM-in)
All rules in the Remote Event Log Management group

You can also enable these rules by using Group Policy settings for Windows Firewall with Advanced Security. For servers that are running the Server Core installation option, run the Netsh AdvFirewall command, or the Windows Powershell NetSecurity module.


As originally posted on this page, here's the steps to resolve.


1. Log into the server in question using the non domain admin account.

2. Open the Power Shell and run the command:

$credential = Get-Credential

3. Then run the command:

Reset-ComputerMachinePassword -Server ClosestDomainControllerNameHere


Thursday, February 18, 2021

Revised Script: Indexing and looking for a cursor in an update statement

I ran a QC script over the results of yesterday's cursor counts table, and realized I had made a logical error.


Here's the QC scripts:

select count(*) as counted, 'photos' as [Source] from dbo.photos
union all
select count(*) as counted, 'photos-null votes' as [Source] from dbo.photos where tally is null
union all
select count(*) as counted, 'votes' as [Source] from dbo.votes;

select top 10 * from dbo.photos;
select top 10 * from dbo.votes;
select sum(tally) as TotalVotesCounted from dbo.photos;
select top 10 * from dbo.votes as v left join dbo.photos as p on p.id = v.photoID where p.tally is null





And here's the revised creation script:

set nocount on;
use enron;
go

if object_id('dbo.photos') is null
create table photos (id INT identity(1,1) primary key, photo varbinary(max), tally INT);
else
truncate table dbo.photos;
go

if object_id('dbo.votes') is null
create table votes(id INT identity(1,1) primary key, userID INT, photoID INT);
else
truncate table dbo.votes;
go

DECLARE @counter INT;  
SET @counter = 1;  
WHILE @counter < 500
   BEGIN  
      insert into dbo.photos(photo) values(rand() * 1000)
      SET @counter = @counter + 1  
   END;  
GO  

DECLARE @counter INT, @userid int, @photoid int, @msg varchar(256);  
SET @counter = 1;  
WHILE @counter < 50000
	BEGIN  
		SELECT @userid = rand() * 100;
		select @photoid = rand() * 1000;
		if (@photoid between 1 and 499)
		insert into votes(userid, photoid) values (@userid, @photoid)
		SET @counter = @counter + 1;
	END;  
GO  


 



Wednesday, February 17, 2021

Indexing and looking for a cursor in an update statement

 From this page:


http://www.artfulsoftware.com/infotree/qrytip.php?id=531&m=0


I wanted to see if, in SQL Server, this same mySQL query 

  1. Could be reproduced
  2. Could be sped up with an index
  3. Would show up as a cursor in the estimated execution plan for the update statement 


1. Reproduction

So, yes, this is a fairly simple script to reproduce.



set nocount on;
use enron;
go

if object_id('dbo.photos') is null
create table photos (id INT identity(1,1) primary key, photo varbinary(max), tally INT);
else
truncate table dbo.photos;
go

if object_id('dbo.votes') is null
create table votes(id INT identity(1,1) primary key, userID INT, photoID INT);
else
truncate table dbo.votes;
go

DECLARE @counter INT;  
SET @counter = 1;  
WHILE @counter < 500
   BEGIN  
      insert into dbo.photos(photo) values(rand() * 1000)
      SET @counter = @counter + 1  
   END;  
GO  

DECLARE @counter INT, @userid int, @photoid int, @msg varchar(256);  
SET @counter = 1;  
WHILE @counter < 50000
	BEGIN  
		SELECT @userid = rand() * 100;
		select @photoid = rand() * 1000;
		if (@photoid between 0 and 500)
		insert into votes(userid, photoid) values (@userid, @photoid)
		SET @counter = @counter + 1;
	END;  
GO  


However, in my version, I loaded a larger data set into the tables, as I wanted to have enough data to get a measureable difference when applying the index in step two.

Note also, that in my version, the values are randomly generated, but controlled, so they can only be inserted if they match a photo that we expect to actually have.


2. Indexing


Yes, the statement can be sped up with an index.


Here's the properties of the script, before index application:




Here's after applying the index:




It's a third of the cost. Win!


3. Cursor


There are loops - which is expected - but SQL Server doesn't dissolve into an explicit cursor to handle the update.




Friday, February 5, 2021

Installing SQL Server Configuration Manager when missing for remote management

Following up from the previous post, installing Client Tools Connectivity from the SQL Server 2019 DVD/ISO installed the SQL Server Configuration Manager feature into Server Manager & Computer Management within my management VM.






Thursday, February 4, 2021

There is insufficient system memory in resource pool 'default' to run this query

So while running a really generic restore to the SQL Server cluster instance sql1:

USE [master]

RESTORE DATABASE [enron] 
FROM  DISK = N'\\ServerE\iso\enron.bak'
WITH  FILE = 1, 
MOVE N'enron_Data' TO N'C:\Data\enron_Data.mdf', 
MOVE N'enron_Log' TO N'C:\Data\enron_Log.ldf', 
NOUNLOAD,  REPLACE,  STATS = 5

GO


The error, while descriptive, was interesting because it was "terse" :)

Msg 3224, Level 16, State 2, Line 2

Error: 3224, Severity: 16, State: 2. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.

Msg 701, Level 17, State 123, Line 2

There is insufficient system memory in resource pool 'default' to run this query.


Completion time: 2021-02-04T03:18:26.1654581-08:00



Turns out, the RAM for the server was still set at it's installation default of 128 MB.




Once that was increased to 1024 MB, the problem magically went away. 





Tuesday, February 2, 2021

SSMS Error - An exception occurred while executing a Transact-SQL statement or batch.

I really don't like the errors like this. :(

I'm going to increase the RAM of the servers in the cluster and see if that helps.

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Error: 802, Severity: 16, State: 1. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped.
 (Microsoft SQL Server, Error: 802)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2000&EvtSrc=MSSQLServer&EvtID=802&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

Monday, February 1, 2021

Can't see SQL Server Configuration Manager in Computer Management

 So according to this doc on MS docs, I should be able to run configuration manager against a remote SQL server from a machine with a GUI.


https://docs.microsoft.com/en-us/sql/database-engine/install-windows/configure-sql-server-on-a-server-core-installation?view=sql-server-ver15


Except, as you can see from the screenshot, "SQL Server Configuration Manager" isn't anywhere in there.





The server is Windows Server Core 2019. MMC is a no-go.

So let's find the .msc, I thought, and so I pointed my File Explorer at c:\windows\system32. So I tried starting it manually, and got the error "Class not registered".



OK, this is Server Core, that's not really all that shocking.

I copied the .msc from my Server Core machine over to the GUI server I have SSMS installed on, and tried running it from there. This is the error I get.





So I'm going to have to figure out how to install the configuration manager without installing SQL Server itself.


Friday, January 22, 2021

pushing and popping in Windows Server Core

This is yet another "daggone it, how DID I forget that????"

I would like to share this URL:

https://superuser.com/questions/282963/browse-an-unc-path-using-windows-cmd-without-mapping-it-to-a-network-drive


Whose answer was:

If you use pushd and popd instead of cd you won't get that UNC error.

pushd <UNC path> will create a temporary virtual drive and get into it.
popd will delete the temporary drive and get you back to the path you were when you entered pushd.

Tuesday, January 19, 2021

New checklist for standing up servers in the VirtualBox UCS domain

New checklist for standing up servers in the VirtualBox UCS domain

It's dev! Don't disable your firewalls in production! Don't run with scissors, either, kids.


 1.

Set-DnsClientServerAddress -InterfaceAlias Ethernet -ServerAddresses 192.168.x.x

Rename-Computer "Servername" -restart

2.

Add-Computer –domainname "mydomain"  -restart

3.

sconfig, enable remote desktop

shut down

set 1st network adapter to bridged instead of NAT

enable bidrectional clipboard

enable 2nd network adapter

4.

log in via RDP and then

In Command Prompt as Adminsitrator

sc config vds start=auto

net start vds

In powershell as administrator

Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False

enable-psremoting

Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -Restart


Monday, January 18, 2021

MLK Day: Groovelectric

This seems like an appropriate time to post this:


MLK I have a dream - Groovelectric style


It's originally from

http://www.groovelectric.com


Friday, January 15, 2021

Install the PowerShell A/D module

 From this page:

How to install the PowerShell Active Directory module


Enable Active Directory module for Windows PowerShell in Remote Server Administration Tools > Role Administration Tools > AD DS and AD LDS Tools.


Thursday, January 14, 2021

How do I know if a Windows feature is installed?

 Honestly, every time previously I've tried filtering for Windows Features, I've used some form of variables and/or the PowerShell where-object, and today, I learned the get-windowsfeature cmdlet takes wildcards.


 get-windowsfeature "*fs*"






Wednesday, January 13, 2021

Set the friendly name of a physical disk

I need to set the FriendlyName of a PhysicalDisk in PowerShell, but the uniqueID isn't shown.

Use this:

Get-PhysicalDisk | Select-Object SerialNumber,UniqueID


Monday, January 11, 2021

Ubuntu: Errors when installing VirtualBox

I want to install VirtualBox on my Ubuntu 20.04 LTS host, so I run:


sudo apt install virtualbox virtualbox-ext-pack virtualbox-guest-additions-iso


which gives me THIS ugly mess


Setting up virtualbox-dkms (6.1.10-dfsg-1~ubuntu1.20.04.1) ...
Loading new virtualbox-6.1.10 DKMS files...
Building for 5.8.0-36-generic
Building initial module for 5.8.0-36-generic
Error! Bad return status for module build on kernel: 5.8.0-36-generic (x86_64)
Consult /var/lib/dkms/virtualbox/6.1.10/build/make.log for more information.
dpkg: error processing package virtualbox-dkms (--configure):
 installed virtualbox-dkms package post-installation script subprocess returned error exit status 10
dpkg: dependency problems prevent configuration of virtualbox:
 virtualbox depends on virtualbox-dkms (>= 6.1.10-dfsg-1~ubuntu1.20.04.1) | virtualbox-source (>= 6.1.10-dfsg-1~ubu
ntu1.20.04.1) | virtualbox-modules; however:
  Package virtualbox-dkms is not configured yet.
  Package virtualbox-source is not installed.
  Package virtualbox-modules is not installed.
    Package virtualbox-dkms which provides virtualbox-modules is not configured yet.
  
  
dpkg: error processing package virtualbox (--configure):  dependency problems - leaving unconfigured dpkg: dependency problems prevent configuration of virtualbox-ext-pack:  virtualbox-ext-pack depends on virtualbox (>= 6.1.10-dfsg-0~) | virtualbox-6.1; however:   Package virtualbox is not configured yet.   Package virtualbox-6.1 is not installed.  virtualbox-ext-pack depends on virtualbox (<< 6.1.10-dfsg-z) | virtualbox-6.1; however:   Package virtualbox is not configured yet.   Package virtualbox-6.1 is not installed.
dpkNo apport report written because the error message indicates its a followup error from a previous failure.                                                                                                              No app ort report written because the error message indicates its a followup error from a previous failure.                                                                                                     No apport repor t written because MaxReports is reached already                                                g: error processing package virtualbox-ext-pack (--configure):  dependency problems - leaving unconfigured dpkg: dependency problems prevent configuration of virtualbox-qt:  virtualbox-qt depends on virtualbox (= 6.1.10-dfsg-1~ubuntu1.20.04.1); however:   Package virtualbox is not configured yet.
dpkg: error processing package virtualbox-qt (--configure):  dependency problems - leaving unconfigured Processing triggers for desktop-file-utils (0.24-1ubuntu3) ... Processing triggers for mime-support (3.64ubuntu1) ... Processing triggers for hicolor-icon-theme (0.17-2) ... Processing triggers for gnome-menus (3.36.0-1ubuntu1) ... Processing triggers for libc-bin (2.31-0ubuntu9.1) ... Processing triggers for systemd (245.4-4ubuntu3.3) ... Processing triggers for man-db (2.9.1-1) ... Processing triggers for shared-mime-info (1.15-1) ... Errors were encountered while processing:  virtualbox-dkms  virtualbox  virtualbox-ext-pack  virtualbox-qt E: Sub-process /usr/bin/dpkg returned an error code (1)

So now, I'm going with Plan B.


  • sudo apt-get install software–properties–common
  • wget –q https://www.virtualbox.org/download/oracle_vbox_2016.asc -O- | sudo apt-key add -
  • wget -q https://www.virtualbox.org/download/oracle_vbox.asc -O- | sudo apt-key add -
  • sudo add-apt-repository "deb http://download.virtualbox.org/virtualbox/debian focal contrib"
  • sudo apt update
  • sudo apt install virtualbox-6.1




However, I *still* get the same error.


Loading new virtualbox-6.1.10 DKMS files...
Building for 5.8.0-36-generic
Building initial module for 5.8.0-36-generic
ERROR: Cannot create report: [Errno 17] File exists: '/var/crash/virtualbox-dkms.0.crash'
Error! Bad return status for module build on kernel: 5.8.0-36-generic (x86_64)
Consult /var/lib/dkms/virtualbox/6.1.10/build/make.log for more information.
dpkg: error processing package virtualbox-dkms (--configure):
 installed virtualbox-dkms package post-installation script subprocess returned error exit status 10
Setting up python2.7 (2.7.18-1~20.04) ...
Setting up libpython2-stdlib:amd64 (2.7.17-2ubuntu4) ...
Setting up python2 (2.7.17-2ubuntu4) ...
Setting up python-is-python2 (2.7.17-4) ...
Setting up virtualbox-6.1 (6.1.16-140961~Ubuntu~eoan) ...
Adding group `vboxusers' (GID 134) ...
Done.
Setting up virtualbox-ext-pack (6.1.10-1~ubuntu1.20.04.1) ...
removing old virtualbox extension packs
virtualbox-ext-pack: downloading: https://download.virtualbox.org/virtualbox/6.1.10/Oracle_VM_VirtualBox_Extension_
Pack-6.1.10.vbox-extpack
The file will be downloaded into /usr/share/virtualbox-ext-pack
License accepted.
0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Successfully installed "Oracle VM VirtualBox Extension Pack".
Processing triggers for mime-support (3.64ubuntu1) ...
Processing triggers for hicolor-icon-theme (0.17-2) ...
Processing triggers for gnome-menus (3.36.0-1ubuntu1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.1) ...
Processing triggers for systemd (245.4-4ubuntu3.3) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for shared-mime-info (1.15-1) ...
Processing triggers for desktop-file-utils (0.24-1ubuntu3) ...
Errors were encountered while processing:
 virtualbox-dkms
E: Sub-process /usr/bin/dpkg returned an error code (1)




The difference is, this time VirtualBox starts.


Interestingly, when I start VirtualBox, I am immediately chastised for having an out of date version of the extensions (!), and it walks me through downloading and applying the latest version.


Friday, January 8, 2021

Ubuntu: Install xrdp

 When I'm connecting from my Windows 10 laptop, I want to use mRemoteNG to keep my machines/connections/thoughts organized. I also run my VMs on an old laptop running Ubuntu, which means I'm using xrdp instead of VNC to connect.

Since I never remember the steps to reinstall xrdp, I'm sharing them here, so I remember next time:



sudo apt install xrdp
sudo systemctl status xrdp
sudo adduser xrdp ssl-cert
sudo systemctl restart xrdp
sudo systemctl status xrdp

Thursday, January 7, 2021

Windows 10: ESENT Error 642

I see a ton of this error in my PC Event Log:

 DllHost (7412,D,12) Microsoft.Windows.Search_cw5n1h2txyewy_NOEDP_LEGACY_IDB: The database format feature version 9080 (0x2378) could not be used due to the current database format 1568.20.0, controlled by the parameter 0x410022D8 (8920 | JET_efvAllowHigherPersistedFormat).



Solution:
You can rid yourself of the ESENT Microsoft.Windows.Search entries by going to control panel -> Indexing Options -> Advanced -> Delete and rebuild index.  

Found on:


Wednesday, January 6, 2021

mySQL: Create a temp table

 Quick reference: Create a temp table in mySQL



CREATE TABLE table_t1 (c1 int) ENGINE=MyISAM;

CREATE TABLE table_t2 (c1 int) ENGINE=MEMORY;

CREATE TEMPORARY TABLE table_t3 (c1 int) ENGINE=MyISAM;

CREATE TEMPORARY TABLE table_t4 (c1 int) ENGINE=MEMORY;


Related docs page:

8.4.4 Internal Temporary Table Use in MySQL



Tuesday, January 5, 2021

mySQL: How NOT to edit your mysql.cnf

 When I edited my mysql.cnf recently, I forgot the [mysqld] section header, which produced this error on startup:

brad@alienware:/etc/mysql/mysql.conf.d$ systemctl status mysql.service

● mysql.service - MySQL Community Server

     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

     Active: failed (Result: exit-code) since Sat 2020-12-19 19:46:23 EST; 2min 6s ago

    Process: 9336 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=1/FAILURE)


Dec 19 19:46:23 alienware systemd[1]: mysql.service: Scheduled restart job, restart counter is at 5.

Dec 19 19:46:23 alienware systemd[1]: Stopped MySQL Community Server.

Dec 19 19:46:23 alienware systemd[1]: mysql.service: Start request repeated too quickly.

Dec 19 19:46:23 alienware systemd[1]: mysql.service: Failed with result 'exit-code'.

Dec 19 19:46:23 alienware systemd[1]: Failed to start MySQL Community Server.


A quick check of the verbose help showed an immediately easy "well, duh" moment:

brad@alienware:/etc/mysql/mysql.conf.d$ mysqld --help --verbose

mysqld: [ERROR] Found option without preceding group in config file /etc/mysql/my.cnf at line 22.

mysqld: [ERROR] Fatal error in defaults handling. Program aborted!


Fixed that, and the server started. 

Monday, January 4, 2021

mySQL: Combine strings into a single list


In mySQL, how do I combine strings into a single list? That is, how do I pivot them from rows into a list of values?


First, let's take a look at the data we're dealing with. We have two tables, message and recipientinfo, and we want to show all messages as rows, and instead of having one row per TO recipient, we want to have one row per message, that holds all the people listed on the TO line for each message. To do this, in mySQL, we have the GROUP_CONCAT() function, and in SQL Server, in order to combine values, we have to STUFF() them.




Now, let's take a look at the statements using the Enron data set.


mySQL

SELECT 

    m.sender,

    date,

    GROUP_CONCAT(r.rvalue) as 'TO'

FROM recipientinfo as r

right JOIN message as m ON r.mid = m.mid 

where rtype = 'TO'

GROUP BY m.sender, date

LIMIT 100;



SQL Server

SELECT

m.sender as MsgSender,

m.[date] as MsgDate,

MsgToList = STUFF((

SELECT ',' + r.rvalue

FROM dbo.recipientinfo as r

WHERE r.mid = m.mid and rtype = 'TO'

FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

FROM dbo.[message] as m

Friday, January 1, 2021

Happy New Year - 2021

 Welcome to 2021!


I hope you have a great year!