Patrick Purviance – SQL Server DBA Blog
Blogging impactful solutions for SQL Server DBAs worldwide

Archive for the ‘Bugs’ Category

SQL Server 2012 SP1 CU1 Installation Failure – Resolved

Friday, December 28th, 2012

So I was recently installing SQL Server 2012 on a couple of new machines and ran into the following error when attempting to apply SQL Server 2012 SP1 CU1:

Error during SQL Server 2012 SP1 CU1 Update
Script level upgrade for database ‘master’ failed because upgrade step ‘u_tables.sql’ encountered error 25641, state 0, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

This causes the SQL Server installation not only to be incompletely upgraded (Data Quality Services, Full-Text and Semantic Extractions for Search, SQL Server Replication, Database Engine Services fail to be upgraded), but also renders the entire database engine instance unable to start as the master database cannot be upgraded.  Major bummer!

After a bit of digging around, it became apparent that a temporary choice I had made during the initial installation of SQL Server 2012 was the root cause, although in my professional opinion it’s a bug and I’ve submitted a Connect Issue with a provided workaround.  Hopefully MS will fix the issue, but since the workaround exists, who knows.

The root cause for this error condition is security permissions for the SQL Server service accounts.  During installation I chose to leverage the new for 2012, virtual accounts, which are the default configuration option mind you (although I don’t intend and wouldn’t recommend using these over correctly permissioned Domain Accounts).   Essentially, the NT Service\MSSQLServer did not have appropriate permissions to access file system level objects to apply the required scripts to upgrade the master database or the other features that fail.

The workaround here is to simply set the service account to LocalSystem for the duration of the Service Pack/Cumulative Update installation and then setting it back to the virtual account (better yet to a domain account with appropriate permissions permanently).

My biggest concern here is that Microsoft has chosen for the default service accounts during installation to be based on these virtual accounts that later will render the SQL Server instance unusable until you find this post or Microsoft fixes the issue.

Later,

                -Patrick

SSMS 2008 Bug Displaying SQL Server 2005 Database Objects

Wednesday, October 27th, 2010

Just another quick post to help anyone dealing with a rather annoying bug found in the 2008 SSMS. Turns out that there is a permissions/join bug in the 2008 SSMS when users who have lesser privileges attempt to view objects with the SSMS object explorer. If you ever find a user coming to you and declaring, “I used to be able to see all the tables in database XYZ, but after we upgraded to the 2008 tools, I can’t anymore. I dont’ have the problem with the 2008 SQL Servers. Just the old 2005 SQL Server. What gives?”…..check out this link:

Non-DBO Schemas have been the be root cause for all of my affected users so far. Seems they all want to see the metadata on stuff I’m trying to hide from them 😉

The only legitimate fix, to date, is to grant the user VIEW DEFINITION permissions at the database level (I’ve tried granting the permission on individual schemas and objects to no avail :( ).  If it be a developer using 2008 SSMS on a 2005 database server, maybe VIEW ANY DEFINITION is the solution (they should probably have sufficient privileges already on a Dev server). If it be an end user on production machine, however, VIEW DEFINITION on the particular database is more appropriate. Either way, a role with these permissions would be the best practice and easiest to manage.

-Patrick