Tag Archives: sql agent

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.

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!

How to disable rollback files in Config.msi folder

Sometime back I came across a question where the SQL Server uninstallation was failing because there was not enough space on the drive to create the .rbf file.

Error 1307. There is not enough disk space to install this file:
C:\Config.Msi\filename.rbf

As evident from the error message, SQL Server installer file was unable to create a Rollback file on the C drive. These .rbf files gets created depending upon whether the Windows Installer (.msi) file needs to rollback its actions if cancelled in between. By design these files are created in the Config.msi folder, which is always located on the drive hosting the Operating System (%systemdrive%). There is no option to change the Windows Installer to generate the .rbf file on any other drive.

The only workaround to this issue is to stop the Windows Installer from generating any rollback files. The below steps needs to be followed to stop Windows Installer from creating rollback files.

Navigate to HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Windows\Installer\DisableRollback in Registry Editor and create a new DWORD value called DisableRollback and set its value to 1.

After creating this value, the rollback files will not be created for any msi files. Since the post did not get updated after my response, I assume that the issue got resolved because of this workaround.

SQL Agent job fails | SELECT permission deined on object ‘sysdbmaintplans’

Today’s post is also about SQL Server Agent and jobs not starting up. Again it is on the same instance where SQL Server Agent was not starting and yesterday I had posted on how it was fixed. SQL Server Agent was running fine and all the scheduled jobs were executing successfully except for the Backup Jobs. These jobs were taking the database backups through the Maintenance Plans (sqlmaint.exe). The job history had only the following message.

Executed as user: domain\AgentAccount. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

To get more information related to the Job failure, I enabled Verbose Logging for the SQL Agent job step is shown in the below screenshot.

As expected the Output file of the Job Step had much more details in it.

Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.2039
Copyright (C) Microsoft Corporation, 1995 - 1998
(null)
Logged on to SQL Server 'InstanceName'
as 'Domain\SQLServerServiceAccount' (trusted)
SELECT permission deined on object 'sysdbmaintplans', database 'msdb', owner 'dbo'
Process Exit Code: 1(Failed) (SQLSTATE 01000) (Message 0) slssqlmain failed (SLSTATE HY000) (Error 500000). The step failed.

Permission denied error again? The SQL Server Agent account had sysadmin privileges and some of the other jobs were successful. The catch was this line in the output file.

Logged on to SQL Server 'InstanceName'
as 'Domain\SQLServerServiceAccount' (trusted)

On this instance the Service Accounts for SQL Server and SQL Server Agent services are different. Even though the job owner was SQL Server Agent service account, the job would connect to the Instance using the SQL Server service account credentials. This is because the SQL Server Maintenance Plans make use of sqlmaint.exe which in turn calls xp_sqlmaint an Extended stored procedure. With this understanding it was easy to track down why the Maintenance Jobs were failing. The SQL Server service account was entered as serviceaccount@domain.com.

Since this server had known issues with this naming convention, all it needed was to enter the service account of SQL Server service to domain\serviceaccount.

When the SQL Server Agent service account was changed to follow the correct naming convention, SQL Server had created a new login which had access to SQL Server through Group Membership.

But the SQL Server service account did not have an individual login through Group Membership. Hence even though the Domain Group of which the service account was a member of had access to the instance, it was not getting the requisite permissions because it was unable to authenticate with the Active Directory. It is all because the way the domain and the trust across the domain had been setup.

The Change Control to modify the naming convention of the SQL Server service account was successfully implemented on Sunday and the Maintenance Jobs have run successfully ever since.

SQL Server Agent does not start | ‘(Unknown)’ is not a member of the SysAdmin role

Last week on one of the SQL Server 2000 instances, the SQL Server Agent service was not starting. This server was on the same domain in which the SQL Server Agent was connecting to SQL Server using sa login and jobs were not running as scheduled. DBA checked if the same issue had happened here. But on this server, SQL Server Agent was connecting to the SQL Server instance using Windows Authentication. The SQL Server Agent error log had the following messages.

2011-01-11 15:01:42 - ! [298] SQLServer Error: 229, EXECUTE permission denied on object 'sp_sqlagent_has_server_access', database 'msdb', owner 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
2011-01-11 15:01:42 - ! [298] SQLServer Error: 229, EXECUTE permission denied on object 'sp_sqlagent_get_startup_info', database 'msdb', owner 'dbo'. [SQLSTATE 42000]
2011-01-11 15:01:42 - ? [100] Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID
2011-01-11 15:01:42 - ? [100] Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID 220
2011-01-11 15:01:42 - ? [101] SQL Server version 8.00.2055 (0 connection limit)
2011-01-11 15:01:42 - ? [102] SQL Server ODBC driver version 3.86.3959
2011-01-11 15:01:42 - ? [103] NetLib being used by driver is DBMSSHRN.DLL; Local host server is
2011-01-11 15:01:42 - ? [310] 8 processor(s) and 2048 MB RAM detected
2011-01-11 15:01:42 - ? [339] Local computer is ServerName running Windows NT 5.2 (3790) Service Pack 2
2011-01-11 15:01:42 - ! [000] SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role
2011-01-11 15:01:43 - ? [098] SQLServerAgent terminated (normally)

Some days back we had implemented a Change Control to revoke sysadmin privileges for the BUILTIN\Administrators login on this instance. It usually happens that the SQL Server service accounts are part of the Local Administrators group on the Operating System and the DBA forgets to add those logins explicitly while revoking access for Builtin\Administrators. However on this instance, the Domain group which had the SQL Server service accounts as its members was granted sysadmin privileges on the instance and SQL Server Agent was correctly configured to start using the same account.

Even though the service account had sysadmin privileges, the SQL Server was not able to validate it. Moreover from the error message it looked as if a null value was passed for the login name while connecting to the instance.

SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role

As a knee-jerk reaction we planned to restart the SQL Server service assuming that the instance was not able to connect to the Active Directory to validate the service account login. Since this is a Production instance restarting the SQL Server service required several approvals for the Change Control. Hence it was decided to temporarily change service account of SQL Server Agent and enter the correct service account again.

After entering the same Service Account details again in the SQL Server Agent properties, the following error messages were displayed.

It was now confirmed that the SQL Server Agent service was not able to authenticate the service account details with Active Directory. On all the servers on this domain, the service accounts were entered as serviceaccount@domain.com. The second error message indicated that the service account details in this format could not be verified and it also suggested try changing the Service Account to domian\serviceaccount format. This domain was part of a Domain Forest which had several child domains in it.

Going by the error message, we entered the service account details as domain\serviceaccount and the SQL Server Agent service started successfully!

xp_sendmail: Procedure expects parameter @user, which was not supplied.

On one of the Production instances a job which sends the details of blocking sessions was failing. This is the same instance which was rebuilt on Tuesday. On checking the job history, it was found that the job was failing while trying to send the email.

Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @user, which was not supplied.

The error message states that the parameter @user was not supplied. I was surprised to see this message since xp_sendmail extended stored procedure does not have a parameter named @user. Since all the databases were migrated by moving the LUNs from an old server, I wondered if the sqlmap70.dll which xp_sendmail uses was of older version.

When I compared the version of this dll with that of a dll on a *working* instance, both were similar. Hence the dll issue was ruled out. Since all the databases were moved “as it is”, I planned to stop and start the SQLMail session.

EXEC xp_stopmail
go
EXEC xp_startmail
go

SQLMail stopped without any errors but while starting the SQLMail session the same error was thrown. This confirmed that there is an issue with the way the Mail Profile was setup for the SQL Server service account. The Profile Name drop down box did not have any values in it.

Also in the Control Panel the Mail applet was missing which appears only when a profile has been created for the first time.

This clearly explains why xp_sendmail was failing. I wish the error message was more descriptive. Now the Mail Profile has to be created for the SQL Server service account by following the below steps.

  • Logon to the server using the credentials of SQL Server service account
  • Open MS Outlook and configure the profile using the Mailbox details of the SQL Server service account
  • In the SQLMail configuration (Enterprise Manager –> Support Services –>SQL Mail window select the newly created Mail Profile and click Ok.

Since my posts are always “blah blah in text” today I tried my hand at creating 2 videos and embed them here. Don’t expect much from them, they are just pictorial representation of the bullet points mentioned above.

Configure Outlook

Configure SQLMail

SQL Agent jobs do not run as scheduled

On Friday the Application Team started complaining that their jobs have not run as scheduled on a SQL Server 2000 instance. But the DBA team did not receive any alerts either regarding the job failure or regarding the SQL Server instance/SQL Server Agent being down.

The DBA started working on that ticket. As usual he suspected that the SQL Server Agent might have gone down. But it was not the case, Agent was up and running fine. Did any of the jobs fail? No, all the jobs had completed successfully. Did someone modify the job? No, the jobs were untouched for a long time. Was those daily jobs’ schedule changed? No, it was scheduled to run every day.

The job details screen was interesting. Even though the jobs were scheduled to run everyday except Sunday, the last run date was showing as 4th and the next run was 7th.

How did the jobs did not run as scheduled on 5th and 6th? The best place to look for the answer was the SQL Server Agent Error Log. It was full of error messages like these.

SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
Logon to server '(local)' failed (JobManager)
SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
Logon to server '(local)' failed (ConnAttemptCachableOp)
SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
Logon to server '(local)' failed (ConnAttemptCachableOp)
SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
Logon to server '(local)' failed (SaveAllSchedules)

For every action that the SQL Server Agent tried to do, it was getting an error Login failed for user ‘sa’. This error message seemed very similar. I had come across a situation where the SQL Server Agent was not starting because of same issue and I had talked about it here. Right away checked the Connection properties of SQL Server Agent.

Now the Error Messages in SQL Server Agent Log made sense. The password of sa login was changed on the 4th of this month and none of the jobs have run ever since. Since the Agent was configured to connect to the SQL Server Instance using the credentials of sa account, all the functionalities of SQL Server Agent were failing with error.

Why SQL Server Agent was configured this way? All I can say is that it wasn’t me 😉 This was done when the server was *built*. The DBA on duty did a great job in processing a Change Control real quick and changed the connection properties to Use Windows Authentication and restarted the SQL Server Agent service. The jobs ran successfully as scheduled and everyone ended the week on a happy note!