Configuring Database Mail failing with error - @no_credential_change is not a parameter for procedure sysmail_update_account_sp. (Microsoft SQL Server, Error: 8145)

  • Article

Recently we have encountered the following error message while configuring Database Mail in SQL server 2005.

 

Create new account 'XXXX' for SMTP server 'XXXX' (Error)

Messages

Unable to update account XXXX for SMTP server Microsoft.SqlServer.Management.SqlManagerUI.SQLiMailServer.

------------------------------

ADDITIONAL INFORMATION:

Set mail server login password failed for MailServer 'XXXX'. (Microsoft.SqlServer.Smo)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4262.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Set+mail+server+login+password+MailServer&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

@no_credential_change is not a parameter for procedure sysmail_update_account_sp. (Microsoft SQL Server, Error: 8145)

 

Cause

======

System procedures like sysmail_update_account_sp resides in system database. In our case, system databases and SQL Engine were not at same build. Possible cause of this could be that recently patch applied on SQL Server failed.

Verification

==========

1. Please check the version of SQL server by running below command

serverproperty('ProductVersion')

 

In our case output was 9.00.4262

 

2. Please check the version of the resource database by running below command

Select serverproperty('ResourceVersion')

In our case it returned 9.00.4035

 

As we see that there is a version mismatch. This means that the procedure definition in system database may not match with the one sent by client tools (DatabaseEngine.exe).

 

The parameter "@no_credential_change" gets added during the installation of the patch KB970894 (9.00.4262) and since there is a version mismatch we have encountered this issue.

Resolution

=========

To fix this issue reapply the patch (in our case it was KB970894) once again or uninstall and reinstall the patch and make sure that the resource database shows you the correct version.

Ravi
SE, Microsoft SQL Server

Reviewed by

 

Balmukund Lakhani
Senior SE, Microsoft SQL Server