Tag Archives: services

SQL Server Cluster resource fails with “Data source name not found and no default driver specified” error

Last week I had worked on SQL Server 2008 R2 installation on a Windows Cluster. The installation would go fine until the installer tries to bring the SQL Server resource online. At that time it would fail with the following error message,

The cluster resource 'SQL Server' could not be brought online. Error: The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0x8007139F)

On clicking OK, the installation would not fail but the SQL Server resource would continue to remain in failed state. Looking at System/Application Event Logs wasn’t of much help. From the SQL Server error logs it was evident that the Service was getting started successfully but it was getting stopped after the Cluster Manager failed to bring the resource online after 10 attempts.

The cluster log revealed that the resource was failing to come online with the following error

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Since SQL Server service is working fine, the problem is for sure outside SQL Server. The resolutions mentioned here and here, did not help.

As we already know, the Windows Failover Cluster manager uses SQSRVRES.dll to connect and manage the SQL Server Cluster resource. Hence looked at the properties of SQSRVRES.dll to check if there was any problems with it. This DLL was just fine but its version was 11.0.2100. This is strange since the version of SQL Server that we were trying to install was SQL Server 2008 R2 (10.50.xxxx). On checking customer informed that he had tried installing SQL Server 2012 earlier on this server but it was uninstalled for some reasons.

This clearly seemed to be the root cause. Hence copied the SQSRVRES.dll from a server which already had SQL Server 2008 R2 installed and overwrote it on the server in question. After this the installation succeeded and the SQL Sever cluster resource also came online just fine.

SQL Server Agent failing to start with the error “StartServiceCtrlDispatcher failed (error 6)”

Recently I worked on an issue where SQL Server Agent was failing to start. Before this there was some problems with the disks on the server and the databases were restored from backups.

I started looking for errors in few of the logs

  • System Event Log – No errors
  • SQL Server Error Log- No errors

Looked for the SQL Server Agent logs in the directory where SQL Server Error Logs are located but could not find any. The attempts to start the service from services.msc and SQL Server Configuration Manager failed with a generic error message.

Hence I took the SQL Server Agent service’s binary path from the service properties.

BinaryPath

Then typed “…..Binn\SQLAGENT.EXE” -i MSSQLSERVER” the same command in the command prompt.

ErrorMessage_StartServiceCtrlDispatcher

Error message “StartServiceCtrlDispatcher failed (error 6).” doesn’t help! Hence I started sqlserveragent.exe with the “-c” parameter which indicates SQL Server Agent is running in console mode.

“…..Binn\SQLAGENT.EXE” -i MSSQLSERVR -c”

Now the details came out!

CannotFindPath

SQL Server Agent is trying to rename D:\Data3\SQLAGENT.OUT to D:\Data3\SQLAGENT.1
It is failing to start since the file doesn’t exist because the drive isn’t there!

Now time to change the SQL Server Agent Log path. Tried executing the following command but it failed

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT’

Msg 15281, Level 16, State 1, Procedure sp_set_sqlagent_properties, Line 0
SQL Server blocked access to procedure ‘dbo.sp_set_sqlagent_properties’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online.

The only option left was to modify the SQL Server Agent Error Log path in the Registry.
Navigated to the following registry key and modified it to point to the correct path.

RegistryPath

As expected now the SQL Server Agent started successfully.

Service does not start | TDSSNIClient initialization failed with error 0x80092004, status code 0x80

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!

How the Cannot generate SSPI context error was fixed

Last week on one of the production instances no one was able to connect to SQL Server using Windows Authentication. All the connections were failing with the following error.

Cannot generate SSPI context

Since the domain controller to which this server was connected is known to have connectivity issues, it was decided to restart the SQL Server instance so that. After stopping the SQL Server instance failed to get started. Here is the error message with which it was failing.

This KB article nicely explains many of the reasons why we would get “Cannot generate SSPI context” error of which an incorrect or non-existent SPN is one of the reasons.  As evident from the error message the service was not starting due to some issues with the SPN (Service Principal Name). Before I start writing about how this issue was fixed, let us try to get some information about SPN.

What is Service Principal Name (SPN)?

SPN is a unique identifier for each service that is running on servers. With the help of SPN the clients which try to connect to the service can easily identify it. SPN for each service is registered in the Active Directory. SPNs can be registered under a Computer account or as a user account in Active Directory. The SPN for a service is created in the following format.

<service name>/<FQDN of the server>:<port number>

MSSQL/servername.domain.com:1433

How is SPN created?

When a service starts, the service tries to create the SPN (if it does not exist already) under the credentials of the service start up account. If the service is configured to run under machine accounts (Local System, Network service), SPN is created under a Computer Account  in AD. If the service is configured to start using a domain user account, the SPN is created under the user account in Active Directory.

If the service is starting under a domain account, that account should have Domain Administrator privilege in the Active Directory. Else the creation of the SPN will fail when the service starts. It is not a good security practice grant service accounts with Domain Administrator privilege. SPN can be manually added using the setspn.exe utility.

What was the root cause of this error?

As I mentioned earlier, from the error message it was clear that the issue was a result of errors with the SPN. The output of the existing SPN listing for the SQL Server service account is as below.

The SPN for the service account was wrongly set as MSSQLSvc/<domain name> instead of MSSQLSvc/<computername.domainname>. Once this was confirmed, the old SPN entry was deleted by using the -D switch in setspn.exe and the correct SPN was created by using the following command.

setspn –A MSSQLSvc/<servername.domainname> accountname

After the correct SPN was created, SQL Server service started successfully.

SQL Server Reporting Server service does not start | Object reference not set to an instance of an object

The month of April has been too hectic for me. Earlier it was Incident Management at Office and Toddler Management at Home. This month People Management also got added to the list because of my role change. As a result even though I had quite a few items to blog about, I could not find time to do so. Now somehow I was able to squeeze in some time for this post.

On the 30th of March 2o11, one of the DBAs in other team reached out to me for an issue related to SQL Server Reporting Server 2008 R2 (64 bit). For some testing purpose, they had installed SQL Server Reporting Server 2005 on the same server and uninstalled it later. Ever since the Reporting Server service would not start and the following error message was logged in the Reporting Server log.

Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException:
The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException:
The report server cannot open a connection to the report server database.
A connection to the database is required for all requests and processing.
System.NullReferenceException: Object reference not set to an instance of an object.

From this message, it appeared to be an issue with the Privileges of the SSRS Service Account. It was not be. The Service Account had all the requisite privileges on the Reporting Server databases. The last line in the above error message caught my attention. It appeared that while starting the service, it found a NULL in an object which was supposed to have some value in it. Like I always, I rely on Process Monitor in a situation like this. Here is what I saw in Process Monitor.

Bingo! In the Registry under HKLMSYSTEMCurrentControlSetServicesMSRS 2008 Windows ServicePerformance, Counter Names & Counter Types keys were missing. On quickly checking the registry entries on a server running SQL Server Reporting Service this was confirmed.

This Server

Working Server

Now that I know the reason for failure, I exported MSRS 2008 Windows ServicePerformance on the working server to a .reg file. Then opened the .reg file in notepad and cleaned up everything except the Counter Names and Counter Types in it. Importing this .reg file on the Server having problems ensured that the requisite keys were present in the registry. The Counter* keys were missing under MSRS 2008 Web Service on this server. I repeated the same exercise of exporting and importing the registry keys. After this the Reporting Service started without any problem!

Once I logged off from the call, checked if someone else had faced similar issues. To my surprise, this is a known issue and it is well documented in this Microsoft KB article! Wish I had Googled well before troubleshooting (which I always do!).

 


When was SQL Server restarted?

Over the weekend, I interacted with some of the “wanna be” DBAs. During the interaction I checked with them how they would find when was the SQL Server instance restarted? Everyone had a different approach to find out the information. Here are some of the ways to find out the date & time on which SQL Server service was last restarted.

SQL Server Error Log

By making use of sp_readerrorlog, first time stamp in the SQL Server Error Log can be treated as the time when SQL Server service was restarted.

sp_readerrorlog 0,1,'Copyright (c)'

This approach is not useful if the SQL Server Error Log is cycled manually.

Using sys.dm_os_sys_info DMV

Starting SQL Server 2008, sys.dm_os_sys_info DMV has a column named sqlserver_start_time. This column as the name suggests stores the time when SQL Server was restarted.

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

But this query does not work in versions which are lower than SQL Server 2008.

Using sys.dm_exec_sessions DMV

The following query will get the Login time of the Session Id 1.

SELECT login_time FROM sys.dm_exec_sessions
WHERE session_id = 1;

session_id is created when the SQL Server is started and the login time remains the same until the service is restarted again.

Start time of the Default Trace

The Default Trace is started when the SQL Server is started. The start_time of this trace can also be taken as the time when the SQL Server service was restarted.

select start_time from sys.traces
where is_default = 1

This approach is useful except for the instances where the Default Trace is explicitly disabled.

Creating date of tempdb

The creation date of tempdb database will also be the time when SQL Server service was restarted. This is because the tempdb database is re-created whenever the SQL Server service starts up.

SELECT create_date FROM sys.databases WHERE name = 'tempdb'

The above query works on SQL Server 2005 and above. For SQL Server 2000 the same query will vary a little bit.

SELECT crdate FROM sysdatabases WHERE name='tempdb'

This is the query which I always use to find out when the SQL Server service was restarted.

Configuring SQL Server to use Windows Large-Page allocations

One of the Change Controls implemented today had inspired me to write this post. The Change Control was to enable Trace flag 834 on a SQL Server 2005 instance running on Windows 2008 Server (64 bit). As explained in this article, after enabling TF 834, SQL Server will make use of Large-Page allocations for the Buffer Pool memory. Here is my understanding of Large-Page allocations and its performance benefits.

Page allocations

The Virtual Address Space is made up of a number of pages. These pages are of two sizes. Large and Small. The Small pages are 4 KB in size (8 KB on IA64 systems) and the Large pages are 2 MB in size for x64 systems (16 MB for IA64 systems). The details regarding Virtual Addresses is maintained in a structure called Page Tables. Hence each Virtual Address has a corresponding Page Table Entry in the Page Table. The hardware translates these entries into a format readable by it. Whenever an address translation occurs, the entries in the Page tables has to searched. In order to speed up the lookup process, CPU maintains a cache called Translation Look-Aside Buffer (TLB). TLB works similar to the procedure cache, such that an entry once translated need not be translated again.

With this information it will be easy to understand how Large and Small page allocations make a difference to the performance. When a Virtual Address is making use of Small pages, the number of entries in the Page Table increases, which in turn increases the number of entries in the TLB. For example for few Virtual Addresses to be cached, using Small pages it would require more entries in TLB. More entries means whenever a new translation request is received, more number of cached entries in the TLB needs to be recycled. Hence allocating Virtual Addresses by means of Large pages has a definite performance benefit.

Page allocations and SQL Server

Like I mentioned earlier using Trace Flag 834 would force SQL Server to make use of Large Pages for Buffer Pool. This trace flag is only applicable to 64-bit of SQL Server and this also requires Lock Pages in Memory privilege for the SQL Server service account.

When SQL Server is making use of Large Page allocations, entries similar to the one below are logged in the SQL Server Error Log when the service starts.

Large Page Extensions enabled.
Large Page Granularity: 2097152
Large Page Allocated: 32MB
Using large pages for buffer pool.
10208 MB of large page memory allocated.

If the SQL Server service account does not have the Lock Pages in Memory privilege, this error message is logged.

Cannot use Large Page Extensions: lock memory privilege was not granted.

This article very nicely explains the Large Page allocations and also the fact that SQL Server Enterprise Edition (64-bit) will make use of Large Page allocations without the Trace Flag 834.

This is an attempt by me to cover a very vast topic with whatever I know about it. Hope you’ll like it.