Creating SharePoint Search fails; throws "profile name is not valid" exception

Question

SharePoint 2013 SQL Server 2014

I've been trying to create a new Search Service Application and I've having problems. I've tried to create the Search Service Application both by using Powershell and using the Central Admin interface. The creation fails and issues the same exception/message (listed below). I'm following the Powershell template listed on Todd Klindt's site, which has worked for me on other SharePoint Farms in the past. I believe this may be SQL Mail related but I haven't had any luck finding any information linking SQL Mail and this issue. If it's not SQL Mail what "profile" is the error referencing?

I've been bashing my head for a few days now and any help (especially diagnostic help) would be appreciated. If you have questions, please ask. I would be more than happy to clarify.

Thanks,

Matt

error listing below

---

Errors were encountered during the configuration of the Search Service Application. System.Data.SqlClient.SqlException (0x80131904): profile name is not valid at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObjec>t stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSourc>e1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.SharePoint.Utilities.SqlSession.ExecuteNonQuery(SqlCommand command) at Microsoft.SharePoint.Administration.SPDatabase.EnsureSqlDatabase(SqlConnecti>onStringBuilder connectionString) at Microsoft.SharePoint.Administration.SPDatabase.SetDatabaseOptions(SqlConnect>ionStringBuilder connectionString, Dictionary2 optionsOld, Dictionary2 optionsNew) at Microsoft.SharePoint.Administration.SPDatabase.Provision(SqlConnectionString>Builder connectionString, TextReader provisioningScriptStream, Dictionary2 options) at Microsoft.Office.Server.Search.Administration.SearchDatabase.Provision(Boole>an sqlRestore) at Microsoft.Office.Server.Search.Administration.SearchServiceApplication.Creat>e(String name, SearchQueryAndSiteSettingsService service, SPIisWebServiceApplicationPool applicationPool, SPIisWebServiceApplicationPool adminApplicationPool, SPDatabaseParameters adminDBParameters, SPDatabaseParameters propertyStoreDBParameters, SPDatabaseParameters crawlStoreDBParameters, SPDatabaseParameters analyticsReportingStoreDBParameters, SPDatabaseParameters linksStoreDBParameters, SPPartitionOptions partitionOptions, Boolean cloudIndex) at Microsoft.Office.Server.Search.Administration.SearchService.c__DisplayClass3>.b__2() at Microsoft.Office.Server.Search.Administration.SearchService.CreateApplicatio>n(String name, CreateSearchApplicationDelegate createSearchAppDelegate) at Microsoft.Office.Server.Search.Administration.SearchService.CreateApplicatio>n(String name, SPDatabaseParameters adminDBParameters, SPDatabaseParameters propertyStoreDBParameters, SPDatabaseParameters crawlStoreDBParameters, SPDatabaseParameters analyticsReportingStoreDBParameters, SPDatabaseParameters linksStoreDBParameters, SPIisWebServiceApplicationPool applicationPool, SPIisWebServiceApplicationPool adminApplicationPool, SPPartitionOptions partitionOptions, Boolean cloudIndex) at Microsoft.Office.Server.Search.Administration.SearchService.CreateApplicatio>n(String name, SPIisWebServiceApplicationPool applicationPool, SPIisWebServiceApplicationPool adminApplicationPool, Boolean cloudIndex) at Microsoft.Office.Server.Search.Administration.SearchService.CreateApplicatio>nWithDefaultTopology(String name, Type serviceApplicationType, SPIisWebServiceApplicationPool applicationPool, SPIisWebServiceApplicationPool searchAdminApplicationPool, Boolean cloudIndex) at Microsoft.Office.Server.Search.Administration.SearchConfigWizard.CreateSearc>hApp() at Microsoft.Office.Server.Search.Administration.SearchConfigWizard.ProvisionSe>archServiceApplication() at Microsoft.Office.Server.Search.Administration.SearchConfigurationJobDefiniti>on.ExecuteTimerJob() ClientConnectionId:8f84d5c4-e8f6-4b71-83e0-561792cb7c2e Error Number:14607,State:1,Class:16

Answer 1

I was able to correct this issue after a few days. I hope what I learned helps someone.

I used SQL Profiler, capturing the SP:StmtStarting event class, and saw that just before the error was raised, error number 14607, the stored procedure msdb.dbo.sysmail_verify_profile_sp was called. This is the stored procedure that actually raises the error preventing the search service from being provisioned. I couldn't find any help documents for this stored procedure but when I read the text of the stored procedure, it didn't seem too difficult to understand. There are many conditional blocks inside the stored procedure, but it was the last one that raised the error. I altered the stored procedure, placing both a PRINT statement and a RAISEERROR statement inside the final condition in order to find the name of the SQL Mail profile it was saying was invalid. Then, I again tried to create the search service application through the central admin interface. I used the central admin interface because I found the error messages were more verbose.

The RAISEERRROR statement I inserted showed in the error message, telling me the profile name was DB_Email_Profile. I added this profile in SQL Server Database Mail Configuration Wizard. I made the profile the default public and private profile and tried to create the search service application again. It worked.