Missing mssqlsystemresource database

Last night, one of our Development servers had a service pack get pushed to the server and automatically install.  I was unaware our patching process was automatically patching all of our servers which are not in Production.  Not a good idea, I would rather control this process and deploy the patches in a uniform process.

After the server was patched, the patch required a reboot.  When the server rebooted, Sql Server would not start up.  After reviewing the Windows Application log and Sql Server log, I quickly discovered the mssqlsystemresource database was missing.  I did search for it just in case it was placed somewhere else, but to no avail.

If the database was misplaced, I could have used this article just to move it back to the proper location, http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/22/error-945-severity-14-state-2-database-mssqlsystemresource-cannot-be-opened.aspx.

In my situation, the database was not misplaced, but it was gone.  Next I read the blog, http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9ddc5d52-5835-405f-ad38-ac25022373f4/.  As you can read, I needed to find a server with the same version number to copy the files from the working server to the broken server.  I opened the Sql Server error log to get the version of Sql Server.  I scrambled around the office looking for a server with the same version number of the broken server.  No luck.

What is a DBA to do?  I have the version number but what do I need to install.  Thanks to our http://sqlserverbuilds.blogspot.com/ which shows use all the versions and links to the website for the download. I started to think how could I get this server back online, create a VM, install Sql Server 2008 R2 with cumulative update 1. I could then copy the files I needed from the new server to the old server.  Do I need to save the Master and MSDB databases?  This could take a while.  I need this server back online.

Instead, I went to Add/Remove program, found the Sql Server program and did a repair installation.  After the repair completed, my server was back online.  Yea!

But I am not finished, what do I need to do to prevent this same issue in the future.  The files look like a database, why can’t I just back them up in Sql Server like other databases. Microsoft does not let you do that.  But per this article, http://msdn.microsoft.com/en-us/library/ms190190.aspx, you can backup the files for emergency recover.  If needed, you can use the backup files for recovery but be carefully as the article states.  Finally, I think I am protected here.  Time to move on to the next issue.

Executing a Sql Server 2012 SSIS package with a Proxy Account with least rights across Domains

Note:  This is for the new package deployment process in Sql Server 2012.

Situation: I need to execute a Sql Server 2012 Integration Services package from the new SSISDB database by a Sql Server Agent job.  This package needs to run across servers and across domains that has only a one way trust relationship. Due to security issues, I will not use Sql Server Authentication accounts.  Domain B Server B will pull data from Domain A Server A.  Domain B trust Domain A but Domain A does not trust Domain B.  Server A is a Sql Server 2008 R2 server and Server B is a Sql Server 2012 server. All SSIS packages will reside on Sql Server 2012 in the Integration Services Catalog SSISDB database.

Setup:

Domain A  –  Server A

Domain B – Server B

Solution:

When we create a new job to run the SSIS package, by default the package will run with the account which runs Sql Server Agent.  In our scenario, Sql Server agent is running on Server B with a domain account which resides in Domain B.  This Sql Server agent account can’t be added to Server A so it will not be able to access data on Server A.  I need the SSIS package to get data from Server A.  Because of the domain relationship, I can’t use the Sql Server Agent account running on Server B to run my SSIS package as it does not have access to Server A.

Here is how I solved the issues.  First, we need to setup a new domain account which can talk to both domains.  The new Windows Authentication account is setup in Domain B.

Add Windows Login

Add Windows Login

We added the new Windows Account into Sql Server login and added the account to the database on Server A where I need to get my data from.

Add Database User

Add Database User

In my case, we then gave the account the right to select the data from the tables we need to access on Server A. You could also give the account db_datareader but since you have gone to this extent to secure the database, I would only give it access only to the tables it needs to read. To make it easier to move between environments like Dev, QA and Prod, I would add a database role and assign all security to the database role.

Add Database Role

Add Database Role

Next we need to add the Windows Account login to the database role.

Add Database User to Database Role

Add Database User to Database Role

All security on the tables and stored procedures should be granted to the database role.

Grant Object Security to Database Role

Grant Object Security to Database Role

On Server B, we add the new Windows Account into Sql Server login.  Add Login to Server BSince this is going to running the job, we need to create a proxy setting for this account. To setup the proxy, first I added the Windows Account as a Security Credential.  For this you will need the password of the Windows Account.

Add Credential

Add Credential

After setting the Windows Account as a Security Credential account, under Sql Server Agent, I added the Windows Account to Proxies.

Add Credential to Sql Agent Proxy for SSIS

Add Credential to Sql Agent Proxy for SSIS

The new Windows Account was added under SSIS Package Execution in the Proxies area.  Next we will add principals, SqlAgentOperatorRole and db_ssisadmin to the new proxy account.  Add Proxy Account PrincipalsBoth of these principals are found under type, select the MSDB role.  In addition to the proxy principals, we also add proxy reference to each job step which uses the proxy account.

Add Job steps with Proxy account to add to this screen

Add Job steps with Proxy account to add to this screen

Next we need to add security to the SSISDB.

Add Security to SSIS DB

Add Security to SSIS DB

At the project folder level in the SSISDB, we need to add permissions of read and execute objects.  When you deployed the SSIS packages, a new folder is created under project folder called projects.  This will create a Project with the same name as your solution.

Add Security to SSIS Project

Add Security to SSIS Project

You will right click on your project, select properties. On the properties page, you will see the permission information.  Add the Windows Account and give it Read and Execute permissions. You can now create your Sql Agent Job using new security.

Add Job Step with new security

Add Job Step with new security

You should now be able to execute your job. For assistance, please contact Tom at ArmorDBA@gmail.com.

This is a new beginning, writing.

Hello my fellow warriors, this is a first for me.  I have never been able to put my thoughts into words on a blog, but I love to speak at my Sql Server user group and Sql Saturday events.  So be nice and I will try to share information with you on securing data.  I am a DBA which also must meet high security standard and audits.  Let’s see what we can learn together.  If you have questions, I will try to answer them or find an answer for you. Let’s see where this goes and make sure you put that armor around your data.

Tom