Thought for the day

Shamelessly stolen from the Core Rulebook of the Elite: Dangerous RPG:

… when you’re out there in your ships, a canopy of stars above you, and not a living thing for a million miles all around, the light of a purple dwarf star glinting off the hull plates, and an unexplored galaxy stretching out in front of you … then you’ll know what it means to be alive.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email

Off-boarding Exchange Online users who have never had an on-prem mailbox

Wow. Sorry for the long title on this one folks, but allow me to continue.

As regular readers of my occasional tech-tips posts will know, our organisation exists in a hybrid relationship with Exchange Online. Some users are in the cloud, some are on-premises as we migrate them. Occasionally a user will need to be off-boarded from the cloud back to Exchange on-premises. This is easy enough if the user was migrate from Exchange on-premises in the first place, but what about the scenario where the user’s mailbox was created directly in the cloud? There’s no on-prem mailbox to go back to, so you might think it would be a painstaking process of PST exports, removal of licenses, being mail-enabled on-prem and then PST imports… Well, you can do it that way, or you can try the following:

  1. Log into Exchange Online PowerShell and run the following command to get the mailbox GUID:
    Get-Mailbox user@domain.com | Select ExchangeGuid
  2. Remove all the dashes from the GUID
  3. Generate a new value for the user’s legacyExchangeDN attribute. You can copy the value of an existing on-premises user, just change the CN= value at the end to the user’s sAMAccountName or name value, e.g.:
    /o=First Organization/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=User1

Once you have these values, you can run the following two PowerShell commands after importing the Active Directory module to set the values:

Set-ADUser user1 –Replace @{msExchMailboxGuid=[GUID]'d2cd81e6be4a46b48bc6655cfaad86d9'}

Set-ADUser user1 –Replace @{legacyExchangeDN="/o=First Organization/ou=Exchange Administrative Group (FYDIBOHF23SPDLT)/cn=Recipients/cn=User1"}

Obviously, you’ll need to substitute the values in the examples for msExchMailboxGuid and legacyExchangeDN for the real values you generate for your own users. Once the necessary syncs have taken place and the object is up to date, you will be able to create an off-boarding migration batch to move the cloud mailbox back on-premises.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email

Large DPM databases

Had an on-going issue recently where one of our DPM servers seems to crash over night. Eventually I found an error in the log stating that the DPM server had lost connectivity to the DPMDB database in the SQL instance. I thought this was a little odd because we have three DPM servers which all connect to instances on the same SQL server, and the other two servers had been fine.

A cursory check of the SQL server revealed nothing much, so I started looking at what DPM might have been doing. DPM has a number of housekeeping tasks that it runs to keep its database tables in order, and these run between midnight and 3am depending on the server load and how much each task has to do. It was between these times that we were seeing the crashes, so I started to check the jobs and the tables they look at.

It was quickly apparent that the table tbl_TE_TaskTrail was enormous, occupying nearly 16GB of the database. This seemed strange as one of those overnight processes is meant to clear out any records from this table that have a StoppedDateTime value older than 33 days ago, essentially meaning the table should only hold a little over a month’s worth of data. A quick check of the table running the SQL statement below showed we had a problem with this task:

SELECT TOP 100 * FROM dbo.tbl_TE_TaskTrail ORDER BY CreatedDateTime;

QueryForOldRecords-Cropped

If the clear-up jobs had been running successfully, we shouldn’t have records of jobs going back to 2015… So after discovering this, I ran the following to find out how many rows were older than the prescribed 33 days:

SELECT COUNT(*) FROM dbo.tbl_TE_TaskTrail WHERE StoppedDateTime < (GETDATE() - 33);

It returned a count of nearly 5 million rows… Hurp. No wonder the query was timing out!

So, how to clear this table down and allow DPM to continue on without impacting performance during the day or relying on the scheduled maintenance tasks, which demonstrably hadn’t been running correctly. Well, you can run the following SQL block against your DPMDB which will process the table in blocks of 50,000 at a time. It will delete rows associated with the tasks in the TaskTrail database first, before finally clearing the records from TaskTrail. Some of the tables might not have associated records in them, so don’t worry if some of the delete statements return results to the effect that they haven’t removed any rows, this is normal.

USE DPMDB_MY_DPM_DB -- Change this to the name of your DPM database
GO

DECLARE @GCTill DATETIME = GETUTCDATE() - 33
DECLARE @TempTable TABLE (TID GUID)
INSERT INTO @TempTable (TID) (SELECT TOP 50000 TaskID FROM dbo.tbl_TE_TaskTrail WHERE StoppedDateTime < @GCTill AND dbo.tbl_TE_TaskTrail.TaskID NOT IN (SELECT taskID FROM tbl_AM_AgentTask_Alerts) AND dbo.tbl_TE_TaskTrail.TaskId NOT IN (SELECT taskID FROM tbl_MM_MediaRequiredAlert))

DELETE FROM dbo.tbl_RM_RecoveryTrail_RecoverableObjects WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_AM_AgentDeploymentTrail WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_ARM_TaskTrail WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_CM_InquiryResult WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_MM_MediaRequiredAlert WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_MM_Task WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_MM_TaskTrail WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_PRM_CloudRecoveryPointTrail WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_PRM_ReferencedTaskTrail WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_RM_CandidateDatasetsForSCAssociation WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_RM_RecoveryTrail WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_RM_ReplicaTrail WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_RM_ShadowCopyTrail WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_TE_TaskError WHERE TaskID IN (SELECT TID FROM @TempTable)
DELETE FROM dbo.tbl_TE_TaskTrail WHERE TaskID IN (SELECT TID FROM @TempTable)

So, how long does this take to run? I find on my SQL server (which also hosts two other DPM instances) I was processing 1000 records a minute, so a 50,000 record block would take five minutes to process. If your server is beefier, you can increase the number in the INSERT INTO statement to process more records at a time.

Hope this helps everyone out there.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email

Exchange 2013 > Exchange 2007 mail relay issues

Had an interesting issue come to light in our Exchange environment recently. Before I go into it, I’ll give a bit of background on how we’re set up…

Most of our organisation has mailboxes in an Exchange 2007 environment. There are some users and shared mailboxes in Exchange 2013, and this environment is in a hybrid relationship with Exchange Online while we’re migrating to Office 365.

Mail from Exchange 2007 to Exchange 2013 and back is handled by internal transport services. Mail to and from Exchange Online is handled by a set of scoped connectors on the Exchange 2013 side of the organisation. All mail heading into or out of the organisation is relayed through a mail gateway which communicates with the transport services on Exchange 2013.

What started out as an investigation into why our text messaging system was not dealing with emails that should have been sent out as text messages led to the discovery that hundreds of emails had queued on the Exchange 2013 side of the organisation and were not being relayed to Exchange 2007.

The usual tricks of using PowerShell to force-resume the message queues or restarting the transport services did nothing, however we did notice some TLS errors in the event viewer stating that the Exchange 2013 servers could not create a TLS connection with the Exchange 2007 servers. On further investigation, we determined that this was due to a malformed exchange of trusted certificate authorities between the servers. Essentially, because the certificate chains were too long, they were being truncated by the protocol (this behaviour is by design, apparently) and the servers weren’t able to establish an authority to use for mutual trust.

The exact text of the error you’ll see in the log is:

451 4.4.0 Primary target IP address responded with: “421 4.4.2 Connection dropped due to SocketError.” Attempted to failover to alternate host, but that did not succeed. Either there are no alternate hosts, or delivery failed to all alternate hosts.

There are two main ways to fix this error if you encounter it:

  1. Delete some certificates from the computer account’s trusted root CA store. Be careful to only remove CA certificates that have expired or are otherwise invalid as deleting an incorrect certificate can cause serious issues with secure communications.
  2. Create the following registry key on the Exchange 2007 transport servers: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\SendTrustedIssuerList. This should be a DWORD with a value of 0. Once done, restart the Exchange Transport services.

Both options have advantages: Option 1 continues to allow mutual TLS between the servers, however has the risk that you might remove certificates that are needed for other services. Option 2 has the least risk but not exchanging a root CA list has implications for mutual TLS. This Microsoft article describes the issues… Quite why this issue chose to raise its head now when our Exchange co-existence environment has been configured for several years is anyone’s guess…

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email

Exchange Online migrations and calendar permissions

We’re in the middle of migrating our on-premises Exchange organisation to Exchange Online. You see some odd things when you’re running a hybrid Exchange environment, but one of the oddest I’ve seen so far is a user being denied permission to edit or remove items in their own calendar after being migrated. Trying in Outlook returned an error saying they didn’t have permission to send as the specified user (themselves…) and OWA looked like it had deleted the appointment until the view refreshed, after which it came back.

Turns out the problem is due to the LegacyExchangeDN attribute value on the user object changing when they migrate to Exchange Online. The original value of this attribute is meant to be added as an additional entry on the proxyAddresses attribute when migration completes, but it seems this doesn’t always happen, and this error is the result.

To fix it, you need to re-create the value of the LegacyExchangeDN attribute as it would have been prior to migration, and add it to proxyAddresses manually.

To do this, you’ll need to find another user that the user having issues sent an email to prior to being migrated. If that user then replies to the email, they will receive an NDR saying the message to “IMCEAEX-_O=…” failed. This IMCEAEX address can be converted into the missing X500 address by doing the following:

  • Delete the “IMCEAEX-” string from the start
  • Delete the “@domain.com” string from the end
  • Add “X500:” at the start

Replace special characters by using find and replace:

Old Value New Value
_ (underscore) /
+20 space
+26 &
+28 (
+29 )
+40 @
+2E .
+2C ,
+5F – (hyphen)

Once you have the fully reconstructed X500 address, add it into the proxyAddresses attribute for the user having the issues using ADSIEdit and wait for DirSync / ADFS to update the Cloud object for the user. After this, the issues should resolve themselves.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email

DPM, Previous Versions and Terminal Services

We use Data Protection Manager to allow end-users to perform self-service recovery on their own homes drives by using previous versions. This integrates with Active Directory to present the replica shares directly from the DPM server which performs the backup.

It seems that the functionality of this tab is disabled by default on remote desktop (RDP) servers, which means that users on such servers – most of ours as we operate an extensive remote desktop environment – didn’t have this functionality.

In order to enable this functionality you’ll need to create a DWORD registry key called “EnableDLSPreviousVersionsOnTS” in the following location:

HKLM\Software\Microsoft\Windows\CurrentVersion\Explorer

Once done, reboot the server at a suitable time and the functionality will be re-enabled for users on remote desktop servers.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email

Some things that have been annoying me

I’ve had a bee in my bonnet about a few things just lately. I’ve been meaning to find the time to write up about each once, but frankly I couldn’t be bothered. I still mostly can’t so here they all are on one post instead.

Real people don’t need encryption

This statement was uttered by the Home Secretary, Amber Rudd, after it became apparent that the attackers who were responsible for the deaths at the concert in Manchester had communicated using encrypted messaging apps. This statement is frankly offensive and Amber Rudd is a rotten, sickening cunt who should just fuck right off. The idea that we should all be forced to conduct our private business in the open to make it easier for the state to monitor everything we do and say is Orwellian and disgusting and the fact that she would try and leverage the tragedy in Manchester to her own political advantage shows just what a horrible, soulless creature she really is.

The idea that she and her followers propagate, that if you’re somehow against the idea of the state knowing everything about you you’re in league with or supporting the terrorists is offensive, disgusting and totally wrong. I don’t support terrorism but I also don’t support exposing my private business to anyone with a radio receiver or a copy of WireShark. The reason we are given for the security services wanting this extra level of insight and power is so that they can monitor everything for terrorist activity. Here’s an idea Rudd and the leadership of MI5/MI6 might like to consider: focus your resources better. If you need more people, ask for them and get them hired. Prioritise your workloads, focus on those targets and areas of the community where there is a proven track record of people strapping bombs to themselves and blowing themselves up in shopping centres and concerts. Don’t make suspects of all of us just because you can’t be arsed to do your jobs properly.

For those that say you need to be able to react quickly and that you can’t always wait for the courts to issue warrants and follow the existing processes, this is bullshit of the highest order. Any Minister of Secretary of State rank can sign a warrant and it can be done in minutes. If there’s a reasonable belief that you need a warrant to raid a house or seize some communications equipment, follow the emergency processes (citing national security allows the circumvention of a lot of normal processes and safeguards) and get your warrant that way. Then you only have treat those people whom you have a reasonable belief to be up to something as suspects, not every fucker in the country.

Oh, and journalists: do your fucking jobs. Stop peddling the state line about the use of encryption. I know most of you are technologically illiterate and don’t understand how it works, but that’s no reason to be fearful and spout the propaganda instead of the facts. Do some fucking research: it’s what you’re ostensibly paid for, isn’t it?

Still, on the flip-side it is interesting to think that if we did all live our lives in plain-text and let the state machine hoover up all that data into a great big database, the entirety of all our secret services and most of the police could be replaced with a few scheduled tasks and some SQL queries…

Islamic Terrorism

This doesn’t exist. There is no “Islamic terrorism”, there is only terrorism. Those sick individuals claiming to represent Muslims as they detonate themselves in crowded public places are no more representative of Islam than my dog is a representative of cats. Stop using the term: it serves only to widen the fractures already present in society and create fear and distrust where we should be working hard to bring communities and people closer together.

Basically: if you peddle the idea of “Islamic terrorism”, you’re a cunt. Simple.

Racism

First, lets start off by saying that “black” is not a race. It’s a colour. People who are black are human, not some separate species. The idea that we have to sub-divide and categorise everything is shit and, as noted in my little rant above, serves only to reinforce divides and fears between people. Lets start treating people as human instead of black or white or whatever ethnic background they happen to come from. We’ll never make the step to the next level as a species until we can accept the truth that something like skin colour is a load of old pointless bollocks that just be ignored.

Those people who spout bile, vitriol and hatred against groups in our society based purely on ethnicity should shut the fuck up and fuck right off. They’re a cancer growing in our species and we don’t need them: you know what happens to cancers and necrotic tissue in an organ, people? It gets cut out so healthy tissue can grow and cover the wound.

Religion

I am not a follower of any religion. I have supreme distaste for the fact they are unscientific, have no basis in fact and yet purport to tell people how they should live their lives, despite being demonstrably built on lies by people who used (and indeed still use) them to control others. Great works of evil have been done in the name of various religions across the centuries and I really wish people would stop putting their faith in imaginary sky wizards and instead focus on something more real. After all, we teach children from an early age that imaginary friends are a bad thing, yet billions of people around the globe believe their sky wizard is better than another sky wizard, despite neither sky wizard having any demonstrable shred of proof of existence.

That said, with the exception of a lot of the Eastern mystic religions which seem largely to be concerned with self-discovery and the personal spirit than any kind of sky wizard, most religions have a central teaching which can effectively be boiled down to the phrase: don’t be a dick.

What a shame it is then that a large number of religions’ most fervent followers spend most of their time being giant bell-ends.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email

Windows and long file names

Users can be a pain in the arse at times, especially when it comes to managing their file shares. We’ve had everything from users removing administrator groups and SYSTEM from the permissions list, stopping us from supporting them when they have issues and even backing up their files, to somehow creating file and folder structures with full paths longer than 260, which Windows (even in Windows 10 / Server 2016) doesn’t like.

So what do you do if you need to copy a set of folders and you know there are long file paths involved? Well, historically I used to adopt a hard line and tell users they should have created their data correctly, and that anything that couldn’t be read by the account doing the copy would be lost. More recently, however I’ve softened that line and to help with copying structures with long file paths, I’ve created this handy utility called LF Copy.

The ZIP contains a single executable and a supporting DLL. Simply extract and run. Choose source and destination folders and click “Go”:

LFCopy

Its then just a matter of sitting back and waiting. The “Status” tab will refresh with information about the current operation, while the “Errors” tab will provide details of any issues encountered.

I hope this will come in useful out there. Do let me know if you have any feedback, issues, bugs, suggestions etc…

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email

BizTalk 2016 Installer Crash

Had another weird one today while installing BizTalk Server 2016 on Server 2016 with SQL Server 2016. When applying the configuring to the server the installer crashed out while applying the BAM Portal settings. The event viewer had nothing useful to offer, only highlighting the particular DLL in question that had caused the error. The log file output to the AppData location had something useful though:

Cannot alter the role ‘NSSubscriberAdmin’, because it does not exist or you do not have permission.

Turns out that this issue was also present in BizTalk 2013… If you encounter this, you’ll need to log into SQL Server Management Studio, expand the BAMAlertsApplication database, expand Security to get the Roles node. Right-click this and choose “Add Role” and add the missing NSSubscriberAdmin role, owned by dbo. Once done, re-try applying the BizTalk server configuration and it should all go through fine.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email

SQL Server SSPI Errors

Wow. Its been a while since an update has appeared here, huh? Well, I had an interesting problem at work the other day and I thought I’d share the solution.

A new application is being installed and tested which uses an MSSQL database for its internal information management. Integrated security is used based on a per-user session using Kerberos tokens. This is done automatically by the application connecting to the database using functionality in SSPI (Security Support Provider Interface).

Well, long story short, it wasn’t working. The application spat out the following error each time anyone tried to start it:

Cannot generate SSPI context.

Additionally, various application-specific messages appeared.

As per the link above, most of the troubleshooting tips were based around making sure that the Active Directory user account running the service firstly had rights to log on as a service on machines where it was used, and that it had NT AUTHORITY\SELF entries on the ACL in ADSIEdit allowing readServicePrincipalName and writeServicePrincipalName. This was all correct in our situation, but still the error appeared, even after changing the user account on the service to a new one.

Trawling through the information logs on the SQL server side of things led to the following message being discovered:

The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/databases.andinet.local:COMMUNICATOR ] for the SQL Server service. Windows return code: 0x21c7, state: 15.

Interestingly this was reported as information message, not an error message. If it had been logged as an error it would have been spotted earlier in the troubleshooting process.

The key thing in this message is the return code. This particular return code indicates an index violation, that is to say the server was attempting to create a duplicate of something that should be unique. Error codes can be checked with this handy utility from Microsoft.

Jumping on a domain controller and executing the command setspn -l <server name> showed that the SQL instance already had an SPN assigned to it. Running the command produces output like this:

setspn-l-example

To remove the offending SPN entries, a different setspn command is needed: setspn -D <spn> <server> for example: setspn -D MSSQLSvc/databases.andinet.local:COMMUNICATOR databases

This should be run while the SQL instance itself and other all other SQL services running under the user account are shut down. The entire server should then be rebooted. Once the computer account has re-authenticated with the domain and the SQL instance has started up, it will re-register the SPN and the following message will be posted to the information log:

The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/databases.andinet.local:COMMUNICATOR ] for the SQL Server service.

Additionally, you can check what transport your connection to the SQL instance is using by executing the following SQL command. Note that results of this might vary depending on how you connect (e.g. via your application, through SSMS or via SQLCMD).

SELECT net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID;

Hope this helps. I’ve got a couple more tech-tips in the pipeline relating to Microsoft’s Data Protection Manager 2016 running on Server 2016, but we’ll save those for another day… Let me know if there’s anything specific you want me to cover in the comments and I’ll see if I can help.

Share and Enjoy:
  • Digg
  • StumbleUpon
  • Technorati
  • del.icio.us
  • Twitter
  • blogmarks
  • HackerNews
  • Tumblr
  • Posterous
  • email