PS script to get SQL version from BAK file

Quite unpleasant thing about MS SQL Server database backup files it that you can’t restore them on an older version of SQL Server (it seems that even you really want to create such BAK file it is not possible), moreover this is valid not only for major versions but also for things like R2, meaning you can’t restore BAK file created in SQL Server 2008 R2 on SQL Server 2008 Server (non-R2).

Just to save my time trying to restore BAK files against wrong versions of SQL Server I created this script which allows you to retrieve SQL version from BAK file headers and compare it with your server SQL version. Specify path to your BAK file and check the output – if BAK file SQL version is newer that your server version then BAK file can’t be restored on this server. Sample script output can be found below:

It tells you that backup was taken on SQL Server 2012 SP3, while you run SQL Server 2012 SP2. Once you install SP3 for SQL Server script output will change to this:

Once two numbers match you are ready for backup restore 🙂

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Unable to start SSRS sevice after upgrading SQL Server instance

I recently spent quite a few time trying to figure out why my SSRS service fails to start after performing upgrade from SQL Server 2012 to SQL Server 2016. Service was failing to start with quite generic error “System error 5 has occurred. Access is denied” which is a bit to broad, but after a while I looked into instance bin directory which contains log file where I seen some error indicating that configuration file is garbled somehow. At this point I revert to reinstall once again solution, and this time I noticed warning which I probably missed during initial instance upgrade attempt:

TITLE: Microsoft SQL Server 2016 Setup
——————————

The following error has occurred:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=13.0.1711.0&EvtType=0x35C10BED%25400xF538E98B

I proceed with upgrade I seen exactly the same problem – I can’t start SSRS service after upgrade completion. This warning essentially lead me to the thread on TechNet containing right solution. It seems that upgrade process somehow can’t handle SSL bindings for SSRS service or handles them differently (produces malformed XML configuration for them?) and what you need to do is remove SSL bindings for SSRS in Reporting Services Configuration Manager (see screenshot below).

Just to save some time which may be wasted on troubleshooting just remove your SSL bindings (I did it for Web Service URL & Web Portal URL) before starting upgade of your instance. You may add these bindings back after upgrade.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Installing Exchange 2016

I’ve finally found some time to install Exchange 2016 in my test environment. This thing has been on my to do list for a long time, and recently Nicolas Prigent wrote a blog post at StarWind blog on exactly this subject – Installing Exchange Server 2016 on Windows Server 2016, leaving me no excuses for not completing task from my to do list anymore 🙂

So essentially instructions provided in the abovementioned blog post were sufficient for me for getting Exchange Server 2016 up and running in my test environments. Installation process itself is a bit lengthy and before that you have to take care of prerequisites as well as prepare your AD DS schema. With these preliminary parts I had couple of issues which I will mention below.

First of all you have to have the following update for Windows Server 2016 – KB3206632 which is available for download in Microsoft Update Catalog. When I installed this update on my Hyper-V VM with Windows Server 2016 I had an issue on reboot stage – system sort of went into applying updates and restart but then stuck with empty blue background. After waiting for about 8 hours (I just left box running during the night) I just powered off VM and switched it on again – update was applied successfully after this.

Next part is AD DS schema modifications you need to make before installing Exchange by issuing the following commands one after another (strictly in order specified):

I run into issues with steps 1 and 2. Step 1 failed with on Extending Active Directory schema with the following error:

There was an error while running ‘ldifde.exe’ to import the schema file

‘C:\Windows\Temp\ExchangeSetup\Setup\Data\PostWindows2003_schema0.ldf’ The error code is: 8224.

To solve this I just attached installation ISO to one of my DCs and run it there – operation completed without errors.

Next issue happened on step 2:

An Active Directory 0x51 occurred when trying to check the suitability of server. Error: ‘ActiveDirectory response: The LDAP server is unavailable.’

In this case re-running the same command from DC didn’t helped and what actually helped is moving schema master role from one DC to another. If you are a bit rusty on how to do this – see this blog post.

So thanks to Nicolas’ post I now have Exchange 2016 in my test environment, and I already reconfigured my K2 farm (yes I run 4.7 + Feb 2017 CU) to enable Exchange Server integration:

Facebooktwittergoogle_plusredditpinterestlinkedinmail

How to force AD DS replication

Just a quick note on how you can force AD DS replication. You ca do this issuing the following command:

To decipher parameters: /A(ll partitions) P(ush) e(nterprise, cross sites) d(istinguished names). Essentially with this command you can do the same as Replmon used to do in Windows 2003 but in in one step. Don’t forget to replace DC_NAME with name of one of your domain controllers. Of course there are other methods to do that, including using Active Directory Sites and Services console (dssite.msc) like that (from/to selected DC):

Or like that:

When to use? When you made some changes in AD DS partitions and don’t want to wait or when you want to have a quick test of AD DS replication.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

How to get Exchange Web Services (EWS) URL – Exchange 2013

Just a short note on how to get Exchange Web Service (EWS) URL when “ask your Exchange administrator” is not an option 🙂

Method 1 – execute the following command in Exchange Management Shell:

Method 2 – Using “Test E-mail Auto Configuration” which available in Outlook client starting from Outlook 2007. Just right click on Outlook icon in tray holding Ctrl key and select “Test E-mail Auto Configuration” option, type in email address hosted on your Exchange server and Click “Test” – once you get output check “Availability Service URL” value – it contains you Exchange Server EWS URL.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

How to install and manage Nano Server

My 2nd article about new Windows Server 2016 installation option “Nano Server” is now available @StarWind Blog. In my previous article, I covered general concepts around Nano Server, in this one I talk about more practical aspects: installation and management. At the end of the day, you would agree that the best way to learn new technology it is try to use it – this way you will be exposed to its strengths and weaknesses directly, and can get real understanding of whether it works for you or not. Though at this point even Microsoft admits that despite all its greatness, at the moment, Nano Server has quite limited utility as it supports only a small subset of roles and features out of those which you can find in full GUI version of Windows Server.

Read more @StarWind Blog…

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Curious case of stubborn Workgroup flag

I’ve just recently got a support case from the client where no matter that we tried MSMQ won’t work in Directory Service Integration mode, resulting in the following warning from K2 blackpearl Setup Manager:

MSMQ component is a prerequisite for K2 and it seems that all you need to do covered in K2 documentation: Installation and Configuration Guide > Prepare > Supporting Components Configuration > Message Queuing (MSMQ). As an example of functionality which will be impacted by this issue I can mention task notification emails as those have to be queued in MSMQ before being picked up by the Eventbus.

Yet information there was not sufficient, and required me to do some prolonged troubleshooting and googling. But the thing is that MSFT documentation does not highlight root cause which I found either and only some old obscure blog post lead me in the right direction.

So according to K2 documentation we have at the moment we supposed to set permissions and instead of outlining what permissions to set and where, KB strangely starts from where to set them, omitting WHAT & WHY parts 🙂 Moreover it suggests to set permissions on what its called “root object” and according to KB documentation means MSMQ container itself:

But this is not possible at all – this Root Object does not exist before you installed MSMQ Directory Integration Service, and if was installed correctly there is no need for you to go and set any permissions. But if it was installed but not in Directory Integration mode it won’t have its properties exposed in AD:

For WHAT & WHY parts K2 documentation elegantly refers you to MSFT TechNet. There you have to navigate and do a bit of careful reading to find out that setting permissions in AD DS required before installing Directory Service Integration Features of Message Queuing (why not start with this in K2 documentation?).

MSFT KB says that special permissions have to be set IF you installing Directory Service Integration feature of Message Queuing on a domain controller (we can safely ignore this as we need to install feature on K2 server and we won’t be installing K2 on domain controller, except for “all-in-one” test box scenario). Next MSFT KB says that you have to grant the Network Service account the Create MSMQ Configuration Objects permission to the computer object in AD DS before installing the Directory Services Integration feature on a computer that is a domain controller. So all in all according to MSFT KB you only need to set permissions in case you installing MSMQ Directory integration on DC. So no need for extra permissions, right? There are some required permissions but normally they are in place by default.

To save you pain making sense of all this documentation you have to mess with permissions only when installing MSMQ on domain controller, period. But recently I had a support case where this just does not want to work and after unsuccessful troubleshooting attempts with client I revert to my test lab and run into the same issue there. The fact that I run into it only on one server tells me that it only happens if your computer ACL was somehow customized or locked down either intentionally or not. If that pesky Workgroup flag keeps reverting to 1 despite you installed Directory Service Integration Feature, please make sure that SELF identity has the following rights on your K2/MSMQ server in AD:

So in short K2 KB should be structured like that:

1. K2 requires MSMQ Server and MSMQ Directory Service Integration components to be installed. DS integration improves security as it enables publishing queue properties to AD, authentication and encryption of messages using certificates registered in the directory.
2. Before installing MSMQ on domain controller machine you may need to grant additional permissions as described in Microsoft documentation: https://technet.microsoft.com/en-us/library/cc730960(v=ws.11).aspx

NOTE: this is only applicable for “all-in-one” test servers where you may want K2 to coexist on the same machine with DC. This is not applicable for production deployments where K2 runs on the dedicated AD DS member server.

3. In case you doing normal installation on AD DS member server you usually do not have to grant any special permissions except for if you security ACL for K2 server is customized. If you installed MSMQ Directory Service integration on domain member server and Workgroup flag reverting to 1 all the time then check that SELF identity has Create MSMQ Configuration objects and Delete MSMQ Configuration objects rights granted over your K2 server computer object:

NOTE: (1) My tests show me that it is sufficient to apply these permissions to “This objects only”, but you should understand that enabling disabling Directory Service Integration feature requires restart of the machine, so it seems it is all about rights at the point of installation and first reboot after it to create MSMQ objects correctly – I noticed that if I delete MSMQ objects, revoke rights and even reboot the machine Workgroup flag keep staying set to 0, but reinstalling MSMQ feature reveals this problem again. Granting rights sometimes work on the fly without reinstalling MSMQ – just restart the service.

(2) These advanced permissions may become messy way to quickly as you inherit them from your domain and then each time you click on Add button separate ACL entry is being created for your computer object, so watch out for explicit Deny settings overriding your Allow grants.

(3) As usual monitor Administrative Events view in Event Viewer and watch out for MSMQ related errors – it should be able to tell you what’s wrong.

4. When installing K2 on standalone servers (not joined to domain) or in cases when you unable to make Directory Service Integration work you can consider using workgroup mode and private queues. Though if latter is the case it is much better investigate and resolve your Directory Service Integration issue.

Apart from possibility of better structured related information in K2 documentation I’m wondering why MSFT does not have full set of permissions required for Directory Service Integration mode published somewhere? It seems this issue with lacking rights for SELF identity does not happen as by default in Windows Server 2012 R2 domain on newly provisioned server its ACL entry looks as follows:

Hope this article will save someone some time which may be wasted on troubleshooting this.

UPDATE: And as for original support case, there issue persisted even after we granted all required rights to SELF object. At this point we identified that MSMQ logs the following error with code 0xc00e050f, which indicated that MsmqServices object was missing in client’s AD DS domain Configuration partition (though to find this out it was necessary to read some forum t thread created 13 years ago and relevant for Windows Server 2003 🙂 ). Here is the steps how to verify its presence and correct this if necessary:

1.Run ADSIEdit

2. Expand the the configuration container, then expand Services. Check whether MsmqServices object and the regular MSMQ object are there

3. If the MsmqServices object is missing, right-click on Services and select “New Object”. For the object type, select mSMQEnterpriseSettings, for the object name use MsmqServices

4. Save the changes and close ADSIEdit, and force AD DS replication to avoid situation when machine where MSMQ is being installed queries some other DC than the one where you made this modification

5. Retry the MSMQ installation, it should now succeed.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

How to: prevent password expiration domain wide

First of all what this post suggest is absolute no go for production environments, but at the same absolute must have for some training/test environments where you want to use the same static passwords all the time without pesky password expiration/change requests. This is one of the first things I do in my test environments (OK, maybe not first, but usually I do this right after first password change prompt 🙂 ). So to get rid of this in test environment just create new GPO and link it to domain (also not the best practice for production/real world, but were fitting for test lab use case), and set settings as depicted below:

That’s it – execute gpupdate and revert to your default password which won’t expire anymore with these group policies in place.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Restore missing K2 Server Console Mode Shortcut

Recently I’ve seen failed K2 blackpearl installation which went seemingly well but logged an error on Configuration Analysis stage for  K2 blackpearl Server Running task. The warning message was quite helpful listing all possible reasons which may cause issues for K2 service startup: disabled service or service account, wrong credentials, missing logo as a service rights for K2 service accounts or conflicting ports configuration (required ports are busy by something else). And we all know that we have detailed setup logs to check what went wrong during installation, right?

Nonetheless assuming you focused on inability to start K2 service, then as it fails even before initializing itself and its logging which would be the case in scenario with aforementioned warning in K2 blackpearl Configuration Analysis (Windows Services snap-in will show you “Service on local computer has been started then stopped” message), your first action should be attempt to run it in console mode and see what exactly is wrong/what it complains about 🙂

Unfortunately in my specific case Setup Manager also failed to create K2 Server Console Mode Shortcut, which should give you a hint that this is likely credentials issue. In case K2 Server Console Mode Shortcut is missing it can be created manually using the following command:

Refer to related section of K2 help for details. Simply by looking at the command above there are not a lot of things which can go wrong here – missing service executable file (definitely should lead to more warnings on the final stages of setup) or wrong credentials (those in theory can be locked during final stages of setup at the stage when it tried to register service/create shortcut). Anyhow if you run into something like this just restore console mode shortcut to investigate your problem with service startup further.

Facebooktwittergoogle_plusredditpinterestlinkedinmail

Windows Server 2016 Nano Server – Just enough OS model

I’ve recently spent some time exploring Windows Nano Server installation option and wrote detailed blog post for StarWind blog entitled  “Windows Server 2016 Nano Server – Just enough OS model” you can read it here. Article covers Nano Server basic concepts and compares this installation type with conventional Full Server and Server Core installation options – if you find this topic interesting please read on @ StarWinds Blog.

Facebooktwittergoogle_plusredditpinterestlinkedinmail