Sunday, December 16, 2018

[Patching] Beware of SQL Server 2016 SP2 CU3 bug if running Replication and SA is not named SA!

TL;DR – if the SA account is not named SA (because, as per MS and Corporate, we rename it), this patch fails with this error and the server shuts down. 
(note the spots in bold).  Fix is to start the service with a T910 trace flag, rename that formerly-known-as-SA account to SA, then stop the service and start service back up via SERVICES.


Worth reading:
https://blog.sqlauthority.com/2018/04/04/sql-server-upgrade-error-alter-database-statement-not-allowed-within-multi-statement-transaction/

DESCRIBES THE PROBLEM AND SOLUTION:
https://feedback.azure.com/forums/908035-sql-server/suggestions/35805787-sql-server-2016-sp2-cu3-patch-breaks-if-replicatio

We just spent a good 30+ minutes trying to solve this.  Fortunately, after reading Pinal Dave’s post about it (top post), was able to figure out that I needed to google different terms, and found the above post which is far more applicable. (2nd link)

PREVENT:
Rename the SA account to SA before patching, at least for this specific patch. (2016 SP2 CU3)

THE FIX, if you patched and it doesn’t start back up:
1) From a command window, running AS ADMINISTRATOR, run this:
NET START MSSQLSERVER /T902
2) Connect via SSMS. Security->Logins, find the long-random-string-of-letters.  (Alternatively, find it in syslogins, but this appears simpler)
3) Right-click on the former-SA-account, and choose “Rename”
4) Rename to SA. 
5) Go back to the command window, run
NET STOP MSSQLSERVER /T902
6) Go back to the SERVICES, and start the SQL Service.  It should come up.