Add node fails with Attempted to read or write protected memory error

Recently I had worked on an issue where the end user was trying to add a third Node to an existing 2 node cluster. Soon after the setup is intiated, it would fail with the following error message (snippet from the Summary.txt).

Exception type: System.AccessViolationException
    Message: 
        Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
    Data: 
      DisableWatson = true
    Stack: 
        at Microsoft.SqlServer.Chainer.Infrastructure.MsiNativeMethods.MsiOpenPackageEx(String szPackagePath, UInt32 dwOptions, UInt32& hProduct)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.SetCommonProperties()
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsiCore(String commandLine)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsiWithRetry(String commandline)
        at Microsoft.SqlServer.Configuration.MsiExtension.InstallPackage.RunMsi(IEnumerable`1 commandLineProps)
        at Microsoft.SqlServer.Configuration.SetupExtension.MSIInstallerEngine.InstallPackage(PackageId pkg, InstallAction pkgAction)
        at Microsoft.SqlServer.Configuration.MsiExtension.PackageInstallAction.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun)

“Attempted to read or write protected memory. This is often an indication that other memory is corrupt.” Scary little error message! Since this is a setup related issue, the best place to start troubleshooting it is from the Setup Logs.  This article has more details on how to read SQL Server Setup logs.

Detail.txt pointed in the right direction as seen below.

Opening existing patch 'c:\Windows\Installer\97363c.msp'.
Couldn't find local patch 'c:\Windows\Installer\97363c.msp'. Looking for it at its source.
Resolving Patch source.
SOURCEMGMT: Looking for sourcelist for product {D7C6A337-F6BB-46CB-AE32-204DD6A8825D}
SOURCEMGMT: Trying source c:\7cb5fee2d4775f9d53c7f95659\x64\setup\.
Note: 1: 2203 2: c:\7cb5fee2d4775f9d53c7f95659\x64\setup\sql_ssms.msp 3: -2147287037 
SOURCEMGMT: Source is invalid due to missing/inaccessible package.
Unable to create a temp copy of patch 'sql_ssms.msp'.
Searching provided command line patches for patch code {D7C6A337-F6BB-46CB-AE32-204DD6A8825D}
Could not find source for missing patch {D7C6A337-F6BB-46CB-AE32-204DD6A8825D} -- orphaning this patch
SequencePatches starts. Product code: {72AB7E6F-BC24-481E-8C45-1AB

Now it was very clear that the issue is the classic missing MSI/MSP file issue. It was just matter of copying the right files to C:\Windows\Installer as explained here and here.

Once the missing files were replaced, the setup completed without any further errors.

Jobs fail with “Cannot execute as the database principal because the principal “dbo” does not exist this type of principal cannot be impersonated or you do not have permission” error

Recently I worked on an issue in which the jobs which were running fine started failing all of the sudden. The job history had the following error message.

Cannot execute as the database principal because the principal "dbo" does not exist this type of principal cannot be impersonated or you do not have permission

But the principal “dbo” exists, it can be impersonated and I have permission because I am the sysadmin! Now what changed?

Checked the job properties and “sa” was the job owner. This job was last modified a few years ago. The next step is to find out the properties of the database against which the job is executing the queries.

When I right clicked on the database and selected properties, got the following error.

Property Owner is not available for Database ‘[DatabaseName]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

This error is generated when the database owner’s login does not exist in the active directory and I had blogged about it here.

When I executed sp_helpdb, the output looked like the one below.

sp_helpdb_invalidowner

As evident from the output, the database owner’s login was not present in the Active Directory and it was reported as ~~unknown~~. Now that we know the problem, the resolution is pretty simple. Just executed sp_changedbowner or ALTER AUTHORIZATION command against the databases in question. The job started running successfully.

The next time you create a database please take care to change the database owner to a different login. Else you leave the organization and someone else needs to cleanup your name from all the databases that you had created!

Database mirroring cannot be enabled because the database is not in full recovery mode on both partners

Recently I had worked on a mirroring issue. While initializing mirroring, an error message as shown in the below screenshot is encountered.

mirror2

Here’s how this issue was fixed

  • Check if the recovery model of the database on the Prinicipal Server was set to FULL. <– Yes, it was.
  • Take the full database backup of the database on the Prinicpal server
  • Take the transaction log backup of the database on the Principal Server
  • Restore the full database backup on the mirrored instance WITH NORECOVERY
  • Restore the transaction log backup on the mirrored instance WITH NORECOVERY

Configure Mirroring again.

SQL Server Install | Use Role Management Tool to install Microsoft .Net framework 3.5 SP1

I was trying to setup SQL Server 2008 on my lab machine running on Windows Server 2008 R2. When the setup was installing the prerequisites, the following error message popped up.

.net2

This is a very simple error message, which a DBA would come across often. However the next screen which popped up after this message was interesting.

.net1

Since Windows Server 2008 R2 has the .Net framework 3.5 already available as a feature, all we need to do is just enable that feature. There is no need to download the installer and install it separately. As the error message states, just enable it using the “Role Management Tool”. Role Management tool can be found under Server Manager –> Features –> Add features as seen in the following screenshot.

.net3

.net4

That’s all. Now the .Net framework 3.5.1 is installed and ready to use! After this the SQL Server setup completed successfully.

Service does not start | TDSSNIClient initialization failed with error 0×80092004, status code 0×80

Yesterday on my local instance, SQL Server service failed to start. The SQL Server error log had the following entries in it.

2012-12-31 12:31:26.58 Server      Error: 17190, Severity: 16, State: 1.
2012-12-31 12:31:26.58 Server      Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893788.
2012-12-31 12:31:26.58 Server      Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
2012-12-31 12:31:26.58 spid7s      Informational: No full-text supported languages found.
2012-12-31 12:31:26.58 Server      Error: 17182, Severity: 16, State: 1.
2012-12-31 12:31:26.58 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property.

2012-12-31 12:31:26.58 Server      Error: 17182, Severity: 16, State: 1.
2012-12-31 12:31:26.58 Server      TDSSNIClient initialization failed with error 0x80092004, status code 0x1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2012-12-31 12:31:26.58 Server      Error: 17826, Severity: 18, State: 3.
2012-12-31 12:31:26.58 Server      Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2012-12-31 12:31:26.58 Server      Error: 17120, Severity: 16, State: 1.
2012-12-31 12:31:26.58 Server      SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Before getting too much into the error details, I started to analyse what had changed since the last service/server restart. Here is what I had done the day before, to troubleshoot some other issue.

  • Removed the Server from the domain and made it part of a workgroup
  • After completing the desired tasks, I had made the server part of the domain again.

That’s all. The SQL Server service was configured to start using a domain account. 

When I changed the SQL Server service account from the domain account to a local (built-in) account, the service started normally.

Configuration Manager

However, if I changed the service to start using a domain account the same error message would re-appear.

Cannot find object

The following portion of the error message caught my attention.

TDSSNIClient initialization failed with error 0x80092004, status code 0x80. Reason: Unable to initialize SSL support. Cannot find object or property

SQL Server service is trying to intialize SSL support but it cannot find the requisite information. Like  I mentioned earlier, the only thing that had changed on this instance was that the server was re-added to the domain. Did the permissions of the SQL Server service account have changed? No. I verified that the permissions were intact. Did something go wrong with the local profile of the Service account? Here is what I saw under the User Profiles (Computer Properties –> Advanced –> User Profiles)

 

The SQL Server service account had *two* profiles on this computer. One had the status of “Backup” whereas the other one had the status of “Temporary”. This was unusual because for any user who logs on to a computer, one local profile is created. The profile related files are created under C:\Users directory (on Windows 2003 server and below it is C:\Documents and Settings). In this case there was only one folder created but under User Profiles it was showing two. Since this seems to be an issue, I deleted the profile which had the status of “Backup”. After this In logged onto the system using the SQL Server service account to ensure that the there are no visible errors during the profile creation.

After this the SQL Server service started normally! Perfect way to end the year 2012 and I am welcoming the year 2013 with this blog post!

Reading list for the week – 24/10/11

I am starting off this week’s Reading List with an article related to Hotfix Service Model. This article has good information on how Microsoft SQL Server team uses the Incremental Model to deliver hotfixes to SQL Server.

A new hotfix was recently released to fix the issue of series of messages while restarting SQL Server 2005 through SSMS. This issue happens when SQL Server 2005 and Biz Talk Server 2006 are installed on the same server. Read more about this here.

Next is an interesting article by Kalen Delaney (b | t) on The Pros and Cons of Parameter Sniffing.

Are you using Database Mirroring and the transaction log is growing huge during Index maintenance? If yes, Kimberly L. Tripp (b | t) and Paul Randal (b | t) discuss about a different approach to index maintenance on mirrored database in this article.

That’s all for this week. Happy learning!

Reading list for the week – 17/10/11

In this week’s edition, I have got quite a few good links lined up.

The big new first. SQL Server code-named “Denali” gets an Official Name. In the recently concluded PASS Summit 2011 it was announced that the next version of SQL Server will be called SQL Server 2012. Read more about this in this article.

Another big announcement that was made last week is that Microsoft SQL Server ODBC Driver  for Linux will be available along with SQL Server 2012! This is for sure a big step in the right direction.

We know that the Analysis Service cannot be added as a cluster instance. Amit Banerjee (b | t) explains how to add an Analysis Service as a failover cluster instance using the command line setup. This article is a very interesting read.

On clicking Fragmentation tab under the properties of an index in SSMS, the response time used to be very slow. This was because SSMS used to check the fragmentation of all the objects referenced by sys.dm_db_index_physical_stats DMV instead of the selected index. This hotfix resolves that bug.

Tibor Karaszi (b | t) in his article Who owns your jobs talks about the relationship between Active Directory users/groups and SQL Server Agent Job ownership.

Happy learning!