Fixing DistributedCOM launch issues

Recently, when checking some event logs on a clustered SQL environment, I encountered the following error in the System event log:

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID
{806835AE-FD04-4870-A1E8-D65535358293}
and APPID
{EE4171E6-C37E-4D04-AF4C-8617BC7D4914}
to the user DOMAIN\user SID (<SID>) from address LocalHost (Using LRPC) running in the application container Unavailable SID (Unavailable). This security permission can be modified using the Component Services administrative tool.

2017-05-18 17_58_02-mRemoteNG - mgRemoteNG.xml - SQL PRD 1

This happened twice … at the beginning of every minute.

Unaware what the Application ID listed was, I was able to retrieve the list of DCOM applications with the following PowerShell script:

$strComputer = “.”

$colItems = get-wmiobject -class “Win32_DCOMApplication” -namespace “root\CIMV2” -computername $strComputer

foreach ($objItem in $colItems) {
write-host “Application ID: ” $objItem.AppID
write-host “Caption: ” $objItem.Caption
write-host “Description: ” $objItem.Description
write-host “Installation Date: ” $objItem.InstallDate
write-host “Name: ” $objItem.Name
write-host “Status: ” $objItem.Status
write-host
}

With this, it was easy to track the application ID listed back to Microsoft SQL Server Integration Services. The user mentioned in the error was also a user only used for the SQL Server Agent jobs.

This led me to a SQL Server Job which ran every minute which uses SSIS in some of it’s steps. Strangely, the SQL Server job did not fail and kept working as expected.

After some Googling, I managed to fix the issue as so:

  • Start “Component Services”
  • Choose Computers
  • Choose “My Computer”
  • Choose “DCOM Config”
  • Choose the service matching the APPID. In this case Microsoft SQL Server Integration Services 12.0
    2017-05-18 18_05_32-mRemoteNG - mgRemoteNG.xml - SQL PRD 1
  • Click the Security tab
  • Go to Launch and Activation Permissions, Customize, Edit and add the account mentioned. Check the check boxes as below:
    2017-05-18 18_07_13-mRemoteNG - mgRemoteNG.xml - SQL PRD 1
  • Don’t forget to restart the application service (here: Microsoft SQL Server Integration Services 12.0) in order to be sure that the service applied the changes.

Once done, the errors stopped occurring and the event log was kept nice and tidy.

Hope this helps at least someone.

Cheers,
Pieter

Advertisements

SQL Server Agent 2016 does not start with Shared Memory disabled

If you need to install BizTalk Server 2016, there is a big chance you will also install SQL Server 2016. Not only does SQL Server 2016 support the AlwaysOn capabilities that BizTalk Server 2016 might need, it comes with a much-welcomed upgrade to the support life-cycle. At the moment of writing, the latest service pack of SQL Server 2016 is SP1, which offers support until Jul 13 2021.

Installing BizTalk Server properly will require you to make some changes to the SQL Server Configuration as well. Specifically, you might want/need to disable the Shared Memory Protocol.

Shared Memory Protocol

The SQL Server Shared Memory Protocol per the MSDN documentation around SQL protocols:

Shared memory is the simplest protocol to use and has no configurable settings. Because clients using the shared memory protocol can only connect to a SQL Server instance running on the same computer, it is not useful for most database activity. Use the shared memory protocol for troubleshooting when you suspect the other protocols are configured incorrectly.

Basically, in the case your application is not hosted on the same server as your SQL Database Engine, it makes zero sense to enable the protocol. Therefore, it is considered good practice to disable the protocol and only enable the Named Pipes and TCP/IP protocol:

2017-03-29 14_53_26-mRemoteNG - mgRemoteNG.xml - SQL PROD

The above screenshot gives an overview of how the protocol configuration at server side should look like. At Codit, we disable the protocol at server side, since it might pose somewhat of a security risk.

SQL Server Agent not running

While the above has been a good practice for many years and many different BizTalk versions and never gave us any issues, for SQL Server 2016 this meant that the SQL Server Agent (windows service) was stopping unexpectedly. Something that came to my attention while trying to configure the BizTalk SQL Jobs.

The below is a screenshot of the event log and the error logged:

2017-04-12 16_30_41-Clipboard

The error:

SQLServerAgent could not be started (reason: Unable to connect to server ‘SOMESERVER’; SQLServerAgent cannot start).

(Re)starting the SQL Agent service works fine, but after about a minute, we get the same issue and the service crashes.

After some google-Fu, I finally managed to find the culprit due to a message in the SQL Server log:

2017-03-27 13:33:18 – ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]

And with some further research, it seemed the ODBC version that ships with SQL Server 2016, which is ODBC v13.0, contains a bug that will prevent the SQL Server Agent from running with Shared Memory disabled!

ODBC Drivers

To check the version of the ODBC drivers on your machine, there is actually a page on Microsoft Docs: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/check-the-odbc-sql-server-driver-version-windows

Mine said:

2017-04-12 16_47_22-Clipboard

Note: 2015.130.x = v13.0

We now need to upgrade ODBC to version v13.1. You can download ODBC v13.1 from the Microsoft website: https://www.microsoft.com/en-us/download/details.aspx?id=53339

After upgrading, the Drivers tab mentions v2015.131.x = v13.1

2017-04-13 09_09_25-Clipboard

And, as expected (and hoped), the SQL Server Agent service now start successfully and keeps running:

2017-04-13 09_10_18-Clipboard

Let me know if this helped you or if you have any questions.

Cheers!
Pieter

Check the recovery model of (BizTalk) databases

Today I was doing a BizTalk assessment of a newly installed BizTalk Server and needed to verify the recovery model of the BizTalk databases, installed on the SQL instance.

Below is a very simple SQL script you can use to do this:

SELECT 
  Name, 
  DATABASEPROPERTYEX(Name, 'RECOVERY') AS [Recovery Model]
FROM 
  master.dbo.sysdatabases

recoverymodels

FYI: This is a screenshot from a SQL Server 2014 (SP1/CU4) machine.