I'm an Administrator Why Can't I Copy Files

Hello Dear Reader!  I've been working on setting up a virtual environment for quite some time. When last I wrote about it, I described issues I was having due to cloning the same server that I had set up as my Domain Controller, Dude Where Did my AD Account Go? Troubleshooting Duplicate SID’s. 

Well I’ve gotten further down the road and today I was setting up some new servers to do some work with Availability Groups.  The Servers were cloned, SID’s were different from the Domain Controller, I renamed the servers, added them to the domain, set up my additional hard drives, and started copying over SQL Server files for the installation.  Then I get this error.

“So Balls”, you say, “did you remember to add your domain account as a Local Admin?”


Excellent question Dear Reader, you’re on your game today!  I did.  I even logged on as the local server’s Administrator and double check that my account was listed in the Administrators group.

Initially the way I had gotten around this error was by logging in as the Local Server Administrator.  I found that account had all the correct permissions.  This wasn’t a satisfying resolution.  If I didn’t have access to copy files, what else was I missing?  Would this cause an issue with my SQL Installation?

SAVE ME GOOGLE/BING

Help me help You Windows!  HELP ME HELP YOU!

I was stumped, so I turned to the Intrawebs.  Google/Binging the error message got me some results.   The first couple links didn’t help.  Then I came across a message board from the Windows Server forums, Destination Folder Access Denied – Copying file to root of C:\.


I wasn’t trying to access my C:\ but these steps worked for me.  I’m going to detail out the same steps that Rick Tan did in the forum post.


First open up run and type in gpedit.msc to open the Group Policy Manager.

Next expand the tree by Windows Settings | Security Settings | Local Policies | Security Options.  Scroll to the bottom we are looking for Policies that begin with User Account Control.   First up User Account Control: Behavior of the elevation prompt for administrators.    

You may not want to disable this on your server at work, but these are my VM’s.  I’ve given my account Server Admin level access.  I don’t want to have to answer a prompt every time I need to do Administrative things.  We want to set this to elevate without prompting.

Click OK and let’s move on to our next one.  User Account Control: Detect application installations and prompt for elevation.  Okay I’m an Admin.  I’m going to install things on my server.  Otherwise this account wouldn’t be an Admin.  Let’s disable this, I don’t want to be prompted with “are you sure?” every time I try to install something.

Click okay and now onto User Account Control:  Run all administrators in Admin Approval Mode.  Seriously?  How many times do I need to say I’m sure this Admin account is an Admin?

Click Disabled and OK.  So once we change these policies you no longer get the annoying ARE YOU SURE, ARE YOU SURE?, ARE YOU SURE?,prompts.  Sometimes you just want to yell!  I’m an Administrator, I granted this account Admin access.  YES! IT’S A F#(%ING ADMIN!

“So Balls,” you say, “You okay there?”

Sorry Dear Reader, redundant security frustrates me.  The steps worked for me though.  When I try to copy over SP1 for SQL 2012 it succeeds.  Refreshingly enough, I don't get prompted to ensure I wanted to really run the item I double clicked on.  What a wonderful world.

For these settings to take affect a reboot will be required.  So now it's time to sit back, and enjoy a pop up free world.  As always Dear Reader, Thanks for stopping by!

Thanks,

Brad

Pro SQL Server 2012 Practices Chapter 18 Review Tuning for Peak Load

I'm on a Book!

Hello Dear Reader, early last year I was asked if I would like to contribute to a book.  The concept was get a lot of really great SQL People together and let them write a chapter on a subject that they were passionate about.  Eighteen different top SQL professional’s, at least two MCM’s, many MVP’s, Shake Stir and you get our book, Pro SQL Server 2012 Practices.

Having only written once chapter I hadn’t read the others.  I had an idea I’d read the chapters and then blog reviews.  I shared this idea with Mr. Grant Fritchey (@GrantFritchey|Blog), check out his review of Chapter 12 yesterday,  and he suggested that we get all of the authors to blog reviews.  A lot of people signed on and we’re releasing our reviews one at a time. 

My first review is on Ben Debow’s (@BBQSQL | Website) chapter Tuning for Peak Load.  Ben is a co-founder of SQLHA with MVP Alan Hirt(@SQLHA | Blog).  Ben is a speaker, very active in the SQL Community, and an all around expert.

“So Balls,” you say, “Get to the review all ready!”

Alright Dear Reader, away we go!

TUNING FOR PEAK LOAD

Ben does an amazing job of stepping through many different tools that you can use to assess your environment.  First Ben identifies what Peak Load is.  He talks about the people that should be involved in the process of identifying and tuning for this period, and really steps through the business logic of why these different people should be involved.  Your mileage may vary based on how large or small your shop is, but in bigger shops he is spot on.  He goes on to talk about how you identify where you are today.

This is an important concept.  You cannot measure improvements, or if changes were detrimental, without first knowing as much as possible about your current environment.  We start at a 10,000 foot view of a setting up a topology diagram, Ben also lists a detailed table of Attributes to gather on your servers.  Next up we begin doing a performance assessment.

Ben weaves his way through Perfmon, gives you counters to monitor, and reasons for why you would want to collect them.  We move into a discussion of how to gather profiler data and recommendations what counters you would want to collect.

We move next into Observations.  Ben walks through metric’s he has collected and what they tell him.  This is invaluable to a DBA.  You often hear professionals say “Collect this data” if you’re lucky you hear them say “You want these numbers”, in this case he tells you how he interprets the numbers and what they could mean.  I’m stressing could, because this will help you in diagnosing your server, but each environment will be different.

We move into using PAL, http://pal.codeplex.com, to interpret and report on our Perfmon counters we’ve been collecting.  A quick aside if you want to set up PAL and get it working  read the documentation, there are two add ins.  One is tools for Office 2013 Web Components, another is Microsoft Log Parser, not listed but required as well is Microsoft Chart Controls for Microsoft.NET Framework 3.5.  If you do not have that last one you’ll get a nice little .NET error when trying to generate the report.

From there we move onto DMV’s and gathering index statistics.  Ben discusses gathering Index Usage Statistics from sys.dm_db_index_operational_stats, no script is listed to verify, but the columns discussed are from sys.dm_db_index_usage_stats.  As long as you get the right DMV the content is solid and I found the Costly Indexes description very interesting!

Finally Ben helps you devise a plan to actually implement the analysis into a plan you can implement.  The thing I love is that you can ask 20 brilliant SQL minds to do the same thing, and you’ll get 20 different variations of the same thing.  Ben looks for some things that I had not considered.  I enjoyed the chapter immensely and look forward to implementing what I’ve learned.

If you pick up the book please feel free to drop me a line and tell me what you think!

As always Thank You for stopping by!

Thanks,

Brad

How Do You Query Maintenance Plan Package Metadata?

 Hello Dear Reader!  Since I’ve joined Pragmatic Works I’ve learned a lot, seen a lot, and assisted in interviewing a lot.  One of the things I’m often asked in the interviewing process is, “What is the best thing about working for Pragmatic Works?”   My answer is always the same, the People. 

We have some really brilliant people, and when you get one of us to work with you get a whole crew backing them up. 

  I have folks like MVP Jorge Segarra (@SQLChicken | Blog), Kathi Kellenberger (@AuntKathi|Blog), and MVP Jody Roberts(@Jody_WP|Blog)  and many others that are a phone call away.

 We have a DL, Distribution List, for out BI Geniuses, the DBA crew, and for many other incredible areas our business covers.  So today my buddy and fellow DBA Chad Churchwell (@ChadChurchwell | Blog) tossed out a question to the DBA DL, “Has anyone ever found a way to query the maintenance plans in SQL Server… they are nothing more than SSIS packages, but I am trying to go is find a way to programmatically get the backup location of the backup database task within the maintenance plans.”

“So Balls”, you say, “You figured this out on your own, and answered the question?”

Excellent question Dear Reader!  No, I didn’t. 

The answer was a great collaboration, I got a little bit, MCM and MVP Jason Strate (@StrateSQL | Blog) ran further with it, and Chad came back and delivered a great script.   To cut to the chase go read Chad’s blog with the final script here.

HOW’S IT MADE!?

http://ratemydesktop.org/pm/78H0/yay_science.html

When you make a Maintenance Plan in SQL Server it saves it internally as an SSIS package.  Instead of browsing through the tables in the MSDB database I opened up profiler and clicked through the GUI.  I created a folder on my C:\ called FindMe and another named FindMe2.

I then let the trace run as I created an Maintenance plan to backup AdventureWorks2012.  I saved it.  Closed it and then reopened and modified the folder path.  

I started looking through the profiler trace for the folder text.  I figured it would be passed through as a parameter.  It wasn’t.  Instead I found a call to msdb.dbo.sp_ssis_putpackage.  

Parameter @P6 for sp_ssis_putpackage was a rather large hexadecimal blob.  Using sp_helptext I ran the following script.

sp_helptext 'sp_ssis_putpackage'

The output showed me that @P6 was part of @packagedata and it was saved as column packagedata, an image data type, in table msdb.dbo.sysssispackages.  I queried the column I translated FindMe2 to hex, 46696e644d6532. I searched the package for the hex string and found it!  So I knew that I had arrived at the right place.

*(If you want to check my hex take each number as a 2 number pair, ie 46 translate to binary 01000110 then translate the binary to ascii = F 69=i 6e=n 64=d 4d=M 65=e 32=2, FindMe2).

I first cast the  image to a varchar(max), and lo and behold it was actually in XML format!  So I wrote this query.

SELECT name

      ,CAST(CAST(packagedata as varbinary(max)) as xml) AS PackageSource

      ,packagetype

       ,id

      ,description

      ,createdate

  FROM msdb.dbo.sysssispackages

This gave me an XML document I could see that contained all of the metadata.  I passed this around to the collective brain trust and Jason Strate immediately responded with an XQUERY to get the data Chad needed.

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS

, 'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)

,ssis AS (

    SELECT name

        , CAST(CAST(packagedata AS varbinary(MAX)) AS XML) AS package

    FROM [msdb].[dbo].[sysssispackages]

    WHERE name = 'MaintenancePlan'

   )

SELECT s.name

    ,c.value('(SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath)[1]', 'NVARCHAR(MAX)')

FROM ssis s

    CROSS APPLY package.nodes('//DTS:ObjectData') t(c)

WHERE c.exist('SQLTask:SqlTaskData/@SQLTask:BackupDestinationAutoFolderPath') = 1

Chad then took it to the next level by writing the script to be compatible in SQL 2005 and SQL 2008 and up.  To see the final version though go check outhis blog on it!

WRAP IT UP

Every place that I’ve worked the people made the difference.  Having a solid team to bounce ideas and questions off of not only helps you grow, but it helps them as well.

So a very cool day collaborating with my fellow DBA’s, and it netted a script and some background info I thought you might be able to use.  

It was a lot of fun for me to learn, and I got a new script to toss in my tool box.

Until next time, Thanks for stopping by, and make mine SQL!

Thanks,

Brad