0

We are on Sitecore 9.0.1, Publishing Service 3.1.3 and Azure PaaS.

We are facing few issues in content publishing. During debugging we found out in Publishing Service the Link Database is set to Core DB which should be Web DB.

When we updated the sc.publishing.xml to change the Link DB we were seeing the following exception:

 [Error] [ "DATABASE NAME" ] - Looking for data schema version : 1. Found: 0

When we are trying to upgrade the schema using the following command:

Sitecore.Framework.Publishing.Host schema downgrade –-force

It is throwing the exception below. Did anyone experienced this?

[20:47:24 INF] Schema Downgrade
[20:47:27 INF] Downgrading all databases to version [ 0 ]

[20:47:27 ERR] There was an error dropping Schema Sitecore.Framework.Publishing.Data.Common.Sql.Schema for DATABASE NAME
System.Data.SqlClient.SqlException (0x80131904): Could not find the type 'Publishing_Data_FieldDataType'. Either it does not exist or you do not have the necessary permission.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject 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.InternalEndExecuteNonQuery(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
   at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryAsync(IAsyncResult asyncResult)
   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Dapper.SqlMapper.<ExecuteImplAsync>d__29.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.Framework.Publishing.Data.SchemaInstaller.<>c__DisplayClass17_0.<<Apply>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.Framework.Publishing.Data.AdoNet.NoRetryConnectionBehaviour.<ExecuteAsync>d__10`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.Framework.Publishing.Data.AdoNet.DatabaseConnection`1.<ExecuteAsync>d__27`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.Framework.Publishing.Data.SchemaInstaller.<Apply>d__17.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Sitecore.Framework.Publishing.Data.SchemaInstaller.<RunDowngradeScripts>d__15.MoveNext()
ClientConnectionId:b2a5a362-a528-4aac-9105-90b442e16551
Error Number:218,State:1,Class:16
ClientConnectionId before routing:23b74648-7a64-45f0-8951-adf28e841232
Routing Destination:cf2894f5e316.tr257.southcentralus1-a.worker.database.windows.net,11050
Improve this question

1 Answer 1

Reset to default
3

It ended up with the Permission issue with SQL User defined per database in connection string. It need to be SQL admin user not the database user which are created during installation.

To run the Upgrade or Downgrade command, please make sure the sc.publishing.xml is using the SQL Admin credentials. Once commands are successfully executed, you can switch them back to the database specific users.

Improve this answer
1
  • I hope future versions of Sitecore Publish Service will enforce providing a connection string on the command line or similar, since a connection string in sc.publishing.xml should never have such high privileges as needed to perform a schema change. Right now it's a bit too easy to forget a connection string with admin privileges in the config after installing & configuring SPS
    – mikaelnet
    Feb 28, 2020 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.