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

Archive for the ‘Security’ 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

SQL Server Agent – Proxy/Credential Permissions for SSIS Packages

Tuesday, January 31st, 2012

Just a quick self-help note today……if you ever see the error message below with a SQL Agent Job calling an SSIS Package stored in SQL Server (MSDB database) and leveraging a Proxy built on a specific credential, ensure the credential under the proxy has membership in the db_ssisoperator role in MSDB.  Without it (or a bunch of individual object level permissions granted), the credential account doesn’t have permissions to objects needed to get the SSIS Package and execute it.  Remember that the credential account must also have the required permissions on all the objects within the SSIS Package as well or you’ll just be off to fix another error after you correct this one….

Could not load package “xyz” because of error 0xC001404A. Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E09 (The EXECUTE permission was denied on the object ‘sp_ssis_getfolder’, database ‘msdb’, schema ‘dbo’.).

Even after ensuring the Proxy was configured and granted SQL Server Integration Services subsystem access, I got this error.  It wasn’t until granting the credential account access to MSDB and then membership in the db_ssisoperator role did it work.  Seems like a shortcoming in MS’ design to me.  Either that or a bug.  If you intentionally gave the proxy access to the SSIS subsystem as an administrator, why not take care of granting the permissions necessary to carry out such a feat? 

To be honest, this is the first time I’ve run into this error.  I don’t recall ever having to grant MSDB permissions before, but if this helps you, glad I ran into it.

         -Patrick

Hey DBAs, how do you provide passwords to end users?

Monday, June 6th, 2011

So and end user has decided they need a new login created on your SQL Server to complete a project/assignment. Immediately a question explosion happens in your mind, right? What will the login name be? What will this login need access to? What will be done with it? Who will have access to the password? How complicated should the password be? You get the point.

Well, today I’m offering up a solution for one of the questions that may seem menial, but to me it’s a pet peeve. How to provide the password to an end user in a simple way without putting it in an email or typing it in an IM or writing it on a sticky note (you wouldn’t do that would you??? hmmmm). Yes, a phone call is a perfectly good way of controlling the sitaution, but I often find people people always want to copy and paste and sometimes I’d rather not make myself instantly accessible to an end user and this is a nice buffer. Below is a process I’ve used for years to provide this information and it’s treated me well.

Secure-Table Method
Once you have the login and password defined in your SQL Server and have granted the appropriate permissions:
1) Create a table (often I just do this in master as every login should have public access to the DB) defined with two columns; one for the login name and one for the password
2) Insert the login name and the password into the table you created
3) Grant SELECT permissions ON the table you created TO the end user who needs to know (only you, the end user you granted permissions to, and any other System Admins should then have access to this table)
4) Once the user has confirmed they have the login name and password, simply drop the table

Additionally, you could create a database specifically for this purpose and add a layer of encryption, but that’s up to you.

I realize this may be a bit overkill for some. But I like security, and I like sleeping at night. Putting a password into any insecure media, able to be accessed by who knows who, and for how long, asks me to question how long I should expect to keep my job.

Anyway, just a solution I thought I’d document. If it helps anyone, great. If not, only cost me about 30 minutes of my life 😉

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