Monthly Archives: May 2013

Using SnagIt to take screenshots when using a Surface

I recently bought a Surface Pro which so far I’m really enjoying however one thing one thing I’ve been having hassles with is taking screenshots for my blog posts. I’m a massive fan of TechSmith’s SnagIt for taking screenshots however I’d always used it simply by hitting the Print Screen button on the keyboard – the Surface keyboard has no Print Screen button so this presented somewhat of a problem.

I put a shout out on Twitter for keyboard shortcuts that would enable me to accomplish the same thing as pressing the Print Screen button. The suggestions that came back can be surmised as:

  • Run the on-screen keyboard and hit the print screen button there.
  • Use Windows’ built-in snipping tool (which has existed since windows Vista)
  • OneNote has a screenshot facility that can be accessed by pressing ALT+S
  • Hardware buttons. Hold the Windows button on the front of the Surface and press the volume down button (thanks to SQLGardner in the comments for reminding me of this one)

The on-screen keyboard works fine and does indeed launch the SnagIt screen clipper exactly the same as pressing the print screen on a real keyboard would do however I found it a bit fiddly as you have to go and open the keyboard, then close it again afterwards. Moreover the on-screen keyboard is, well, on the screen – and you don’t really want that if you’re taking screenshots. Windows’ snipping tool looked like a great option at first until I realised that it can’t be used to take screen clippings of modern (aka Metro) apps – it simply doesn’t work for those and that was a showstopper for me. The OneNote option is a good one especially as it has a very handy “copy to clipboard” button that appears once the screenshot is taken, however it does of course require you to have OneNote installed and that aint cheap! The hardware buttons option is quick and easy and great if all you want is a screenshot of the entire screen but it doesn’t let you pick a portion of the screen and it doesn’t launch SnagIt.

That surmises some options for taking screenshots however only one of them allows me to carry on using SnagIt (which was my ultimate aim here) and that was the rather fiddly on-screen keyboard option. That’s when I remembered that SnagIt allows you to customise the hotkey that is used to take a screenshot. The default hotkey is (as explained in the first paragraph above) the print screen button so all I did was go and change that default – I changed it to CTRL+ALT+P which, to my knowledge, isn’t used by anything else in the OS. This seems to be working fine, so far. here’s a screenshot taken just now on my Surface, using SnagIt, with some appropriate blur effects applied using Snagit’s editor:

image

So there you have it, some options for taking screenshots on a Surface. As I said I’m a big fan of SnagIt but if you do not use SnagIt then i have outlined some other options in the bullet points above.

One final thing, if you *do* use SnagIt then Techsmith have made available a useful video that explains how to use it on Windows 8 at http://www.techsmith.com/snagit.html although this doesn’t cover what to do if your keyboard doesn’t have a print screen button, hence this blog post Smile 

Hope this helps.

@Jamiet

UPDATE, Whaddya know. Just 4 days after I write this and Microsoft announce you’ll soon be able to take a screenshot using just the keyboard. Fn + <spacebar> will do the job apparently! I doubt this will trigger SnagIt for me, but I live in hope!

Logical Query Processing

 

When I first began working with SQL Server several years ago, one thing that really hung me up was, not understanding why I could reference an Alias column in certain parts of my query, but not in all parts of my query. To be more specific, why I could reference my Alias column in my Order By clause, but I couldn’t reference the alias in my Group By, Having, or Where clauses. I never fully understood why this was so until I read about Logical Query Processing in “Microsoft SQL Server 2008 T-SQL Fundamentals” written by Itzik Ben-Gan.

To illustrate the issue, when we run the following query against the AdventureWorks2012 database, referencing an Alias name in the Order By clause poses no issue for SQL Server and the data is returned.

SELECT OrganizationLevel, YEAR(BirthDate) YearOfBirth, MONTH(HireDate) MonthOfHire, sum(VacationHours) TotalVacationHours

FROM AdventureWorks2012.HumanResources.Employee

WHERE OrganizationLevel = 4 –and YearOfBirth = 1985

GROUP BY OrganizationLevel, Year(BirthDate), Month(HireDate), VacationHours

HAVING VacationHours > 50

ORDER BY YearOfBirth DESC, MonthOfHire DESC

clip_image002

However, if we uncomment the Alias reference in the WHERE clause, SQL Server will return an error

SELECT OrganizationLevel, YEAR(BirthDate) YearOfBirth, MONTH(HireDate) MonthOfHire, sum(VacationHours) TotalVacationHours

FROM AdventureWorks2012.HumanResources.Employee

WHERE OrganizationLevel = 4 and YearOfBirth = 1985

GROUP BY OrganizationLevel, Year(BirthDate), Month(HireDate), VacationHours

HAVING VacationHours > 50

ORDER BY YearOfBirth DESC, MonthOfHire DESC

image

So what exactly is the reason for this error? Well the answer comes down to the way that the query is broken down and processed by SQL Server. The order of query processing in SQL Server is:

1. FROM

2. WHERE

3. GROUP BY

4. HAVING

5. SELECT

6. ORDER BY

Using the above order of processing, our sample query first pulled out all of the data FROM the AdventureWorks2012.HumanResources.Employee table and filters for only data WHERE OrganizationalLevel = 4. Next the query GROUP’s the result set BY OrganizationalLevel, Year(BirthDate), Month(HireDate), and VacationHours. Next the query performs another filter on this group set for anything HAVING VacationHours > 50. Next the query SELECT’s the specified columns from the SELECT list and assigns the Alias names at this point. Lastly the query ORDER’s the result set BY your specified criteria, and then returns the data to the requesting client.

As we see, the alias names were not created until Step 5 (SELECT). As a result, this explains why we are able to reference the Alias names in Step 6 (ORDER BY), but not in Step 2 (WHERE), Step 3 (GROUP BY), or Step 4 (HAVING).

Understanding how Logical Query Processing works will help you write better and more efficient queries. For more information on the topic, I highly recommend reading “Microsoft SQL Server 2008 T-SQL Fundamentals” written by Itzik Ben-Gan.

Enhancements to SQL Server Backup to Cloud in SQL Server 2012 SP1 CU4

Cumulative Update 4 for SQL Server 2012 Service Pack 1 includes enhancements to improve the SQL Server Backup and Restore to Cloud user experience.  The enhancements include performance improvements, cleaning up invalid blobs when backups fail or are interrupted, and PowerShell support.  

To request this update, see this knowledgebase article.

Performance Improvements:

Performance improvements include faster return of restore metadata operations.  The affected operations include:

  • RESTORE HEADERONLY
  • RESTORE FILELISTONLY
  • RESTORE LABELONLY

Cleaning up invalid blobs after an interrupted or failed backup:

If backup operation fails, it may result in an incomplete or invalid blob.SQL Server Backup to URL process attempts to cleanup cleaning blobs that result from a failed backup.   However, if the backup fails due to prolonged or sustained network connectivity failure, backup to URL process may not be able gain access to the blob and the blob may remain orphaned.  In such cases, you have to manually release the lease and delete the blob. This topic has the details on how.

 PowerShell Support:

This cumulative update also includes PowerShell support for the backup to cloud feature with changes to existing cmdlets and 4 new cmdlets.  Following is a brief overview of the changes and sample syntax. 

For more information and code examples, see  the following topics:

 Use PowerShell to Backup Multiple Databases to Windows Azure Blob Storage Service

 SQL Server Backup and Restore to Windows Azure Blob Storage Service

 

Existing cmdlet changes:

The Backup-SqLDatabase, and Restore-SqlDatabase cmdlets have new parameters to support creating a SQL Server backup to or restore from a Windows Azure Storage Container.  The following are the new parameters:

BackupContainer:

Using this parameter you can specify the URL of the container as the location for your backup files and let the Backup to URL process generate the file names.  Alternatively you can use the BackupFile parameter to specify both the location and the name of the file. You can also use this parameter to set locations for a folder on a disk backup device. This parameter can be useful when backing up multiple databases in a given instance.

SqlCredential:

This parameter allows you to specify the SQL Credential name of object that stores the Windows Azure Storage authentication information.

               Sample Syntax:

  • This command backs up all databases on the server instance ‘ComputerInstance’ to the Windows Azure Blob storage container using the BackupContainer parameter.

 Get-ChildItem SQLSERVER:SQLComputerInstanceDatabases | Backup-SqlDatabase –BackupContainer “https://storageaccountname.blob.core.windows.net/containername” -SqlCredential “SQLCredentialName”

 

  • This command creates a full backup of the database ‘MyDB’. It uses the BackupFile parameter to specify the location (URL) and the backup file name. The SqlCredential parameter is used to specify the name of the SQL Server credential.

 Backup-SqlDatabase –ServerInstance “ComputerInstance” –Database –”MyDB” -BackupFile “https://storageaccountname.blob.core.windows.net/containername/MyDB.bak” -SqlCredential “SQLCredentialName”

 

  • This command restores the full database ‘MyDB’ from the file on the Windows Azure Blob storage service to a SQL Server instance.

 Restore-SqlDatabase -ServerInstance ComputerInstance -Database MyDB -BackupFile “https://mystorageaccountname.blob.core.windows.net/container/MyDB.bak”  -SqlCredential “mySqlCredential”

New cmdlets:

New-SQLCredential

The New-SqlCredential cmdlet creates a new SQL Server Credential object. The SQL Server credential is required when backing up to or restoring from the Windows Azure storage service, and is used to store the Windows Azure storage account name and access key information.

Sample Syntax:

  • This command creates SQL Credential “mysqlcredential” on the current instance of SQL Server.

 New-SqlCredential – name “mysqlcredential” –Identity “storageAccount” –secret “storageAccessKey”

 

Set-SqlCredential

You can set the Identity and password properties for a SQL Credential object using this cmdlet. This cmdlet supports the two following modes of operation:

By specifying the path or location of the credential including the credential name using the –path parameter.

By passing a Smo.Credential object to the object using the –InputObject.

Sample Syntax:

  • This command sets the identity of MySqlCredential to ‘mystorageaccount’.

Set-SqlCredential -Path “SQLSERVER:SQLComputerInstanceCredentialsMySqlCredential” –Identity “mystorageaccount”

  • This command retrieves the credential object from the Get-Credential cmdlet and then pipes it to the Set-Sql Credential cmdlet to set the identity of mySqLCrendential to  ‘mystorageaccount’.

$cred = Get-SqlCredential -Name MySqlCredential

$cred | Set-SqlCredential –Identity “mystorageaccount”

Get-SqlCredential

The Get-SqlCredential returns the SQL credential object.  This cmdlet supports the following modes of operation: 

By specifying the name of the SQL credential and the path of the instance.

By specifying the name of the SQL Credential and the server object.

 

Sample Syntax:

  • This command returns the credential object.

Get-SqlCredential –Name mycredential

 

Remove-SqlCredential

The Remove-SqlCredential cmdlet deletes a SQL Server credential object. This cmdlet supports the following two modes of operation:

By specifying the path or location of the credential and the credential name using the –path parameter.

By passing a Smo.Credential object to the object using the –InputObject parameter.

 

Sample Syntax:

  • The command retrieves the credential object from the Get-Credential cmdlet and then pipes it to the Remove-Sql Credential cmdlet to delete the SQL Credential ‘MySqlCredential’.

$cred = Get-SqlCredential -Name “MySqlCredential”

$cred | Remove-SqlCredential

 

This blog post is about the changes that were made in the Cumulative Update 4 for SQL Server 2012 SP1 release and assumes some amount of familiarity with SQL Server native backup to cloud functionality released in SQL Server 2012 SP1 CU2.  For information on  SQL Server Backup and Restore to Cloud, see the following articles:

 

SQL Server Backup and Restore to Cloud Simplified

 

Getting Started Tutorial

 


Originally posted at http://blogs.msdn.com/b/sqlosteam/

Get Ready for TechEd North America 2013

Get Ready for TechEd North America 2013TechEd North America takes place next week, June 3rd – 6th in New Orleans, Louisiana. While the city promises good times, gumbo and Po boys, you can expect a whole lot of technical goodness to come your way from the conference itself. From the Foundational Sessions to the Hands on Labs, be prepared to soak it all in. Also come prepared with your list of questions to our Ask the Expert sessions, which are scheduled all week long throughout the conference.

We’re packed and ready for TechEd North America 2013 and we hope you are too! Be sure to come back here and visit us next week, when we’ll give you the week’s highlights for SQL Server at TechEd and other exciting news, straight from the conference floor.

In the meantime, you’ll find more great tips and advice for your visit to New Orleans in these informative posts:

Microsoft’s new 3D data visualization BI product – GeoFlow

Microsoft has introduced a new BI product that will provide 3D data visualization using Bing Maps. This product is still in beta and is codenamed �GeoFlow� but should be out late 2013 or early 2014. This preview will allow you to plot geographic and temporal data visually, analyze that data in 3D, and create visual tours. It is a really cool product and I can�t wait to use it in a business atmosphere.

Play on Twitter to Enter the Daily SQL Server Answer & Question Challenge and Sweepstakes

How much do you know about SQL Server, really? Test your knowledge and play our daily SQL Server A & Q Challenge on Twitter to find out and earn a chance to win a PowerStick (ARV $75)!

To kick off the festivities for TechEd North America next week, we’ve got a fun new game and daily sweepstakes for SQL Server fans to enter and play, whether you’re at home or at the event in New Orleans. The challenge and daily sweepstakes begins Monday, June 3rd at 8 am CT and ends on June 6th, and all you have to do enter the daily sweepstakes is:

  • SQL Server Answer & Question ChallengeFollow the @SQLServer Twitter account;
  • Beginning June 3rd, watch for the SQL Server “answer of the day” to be posted by @SQLServer at approximately 8 am CT each day.
  • From your account, reply with the correct question that corresponds to the answer of the day a la Jeopardy style to @SQLServer;
  • Your tweeted reply must include the hashtag “#SQLTechEd”;
  • Your entire tweeted reply should be no longer than 140 characters in length including the #SQLTechEd hashtag;
  • You may only reply to the Sweepstakes answer the day on the day that it is posted; you must enter each daily prize period separately. (A “day” begins at 9 am CT and ends at 2 pm CT each day during the Sweepstakes entry period.)
  • A qualifying reply is your entry into the Sweepstakes.
  • You may only submit one response to the question per day. If you submit more than one response, all of your responses (including the first) will be disqualified for the day;
  • Prizes are limited to one prize per promotional entrant, so if you are selected as a winner, you will be ineligible for additional prizes.

So mark your calendars for this A & Q game and sweepstakes and we’ll see you next week on Twitter!

*     *     *

OFFICIAL RULES – SQL SERVER ANSWER & QUESTION SWEEPSTAKES

NO PURCHASE NECESSARY. COMMON TERMS USED IN THESE RULES:

These are the official rules that govern how the ‘SQL Server Answer & Question Challenge’ (“Sweepstakes”) promotion will operate. This promotion will be simply referred to as the “Sweepstakes” throughout the rest of these rules. In these rules, “we,” “our,” and “us” refer to Microsoft Corporation, the sponsor of the Sweepstakes. “You” refers to an eligible Sweepstakes entrant.

WHAT ARE THE START AND END DATES?

This challenge starts at 8 am CT on June 3, 2013 and ends at 2 pm CT on June 6, 2013.  Sweepstakes consists of 4 daily Prize Periods. Each Prize Period will begin at 8 am CT and end at 2 pm CT each day during the Entry Period. You may enter one time per prize period.

You are eligible to enter this Sweepstakes if you meet the following requirements at time of entry:

· You are professional or enthusiast with expertise in SQL Server or Business Intelligence and are 18 years of age or older; and

· If you are 18 of age or older, but are considered a minor in your place of residence, you should ask your parent’s or legal guardian’s permission prior to submitting an entry into this Sweepstakes; and

· You are NOT a resident of any of the following countries: Cuba, Iran, North Korea, Sudan, and Syria.

· PLEASE NOTE: U.S. export regulations prohibit the export of goods and services to Cuba, Iran, North Korea, Sudan and Syria. Therefore residents of these countries / regions are not eligible to participate

· You are NOT an employee of Microsoft Corporation or an employee of a Microsoft subsidiary; and

· You are NOT involved in any part of the administration and execution of this Sweepstakes; and

· You are NOT an immediate family (parent, sibling, spouse, child) or household member of a Microsoft employee, an employee of a Microsoft subsidiary, or a person involved in any part of the administration and execution of this Sweepstakes.

· This Sweepstakes is void wherever prohibited by law.

To enter the daily sweepstakes:

At 8:00 am CT each day a new Sweepstakes “answer of the day” will be posted by @SQLServer on Twitter You will earn a Sweepstakes Entry when you do all of the following within the Entry Period:

1. Sign in to your Twitter account. If you do not have an account, visit www.twitter.com to create one. Twitter accounts are free.

2. Once logged into your Twitter account follow the links and instructions to become a follower of SQLServer at @SQLServer.

3. From your own Twitter account, reply to @SQLServer with the correct question that corresponds to the posted Sweepstakes answer of the day. You may only reply to the Sweepstakes question the day it is posted; you must enter each daily prize period separately

4. Your tweet must contain the #SQLTechEd hashtag to be eligible for entry and your tweet must not be longer than 140 characters, including the #SQLTechEd  hashtags

Limit one Entry per person and Twitter account per daily Prize Period. For the purposes of these Official Rules, a “day” begins at 8 am PT and ends at 2 pm each day during the Entry Period. If you reply with more than one answer per day, all replies received from you that day will be automatically disqualified.

We are not responsible for entries that we do not receive for any reason, or for entries that we receive but are not decipherable for any reason, or for entries that do not include your Twitter handle.

We will automatically disqualify:

  • Any incomplete or illegible entry; and
  • Any entries that we receive from you that do not meet the requirements described above.

WINNER SELECTION AND PRIZES

On or around 3 pm CT each day beginning June 3 and ending on June 5, we, or a company acting under our authorization, will randomly select one winner from among all eligible Entries received in the daily Prize Period to win a PowerStick, approximate retail value, US$75. On or around 3 pm CT on June 6th, we or a company acting under our authorization will randomly select one winner from among all eligible Entries received in the daily Prize Period to win a PowerStick and SQL Server branded hooded sweatshirt, total ARV of $150.  Prizes are limited to one prize per promotional entrant, so if you are selected as a winner during this promotion, you will be ineligible for additional prizes.

Selected winners will be notified via a Direct Message (DM) on Twitter within 48 business hours of the daily drawing. The winner must reply to our Direct Message (DM) within 48 hours of notification via Direct Message on Twitter. If the notification that we send is returned as undeliverable, or you are otherwise unreachable for any reason, or you do not respond within 48 business hours, we will award the prize to an alternate winner as randomly selected. Only one alternate winner will be selected and notified; after which, if unclaimed, the prize will remain unclaimed. Prizes may be delivered to the winner in person if they are present at the Tech Ed North America event, or they may be shipped to the winner within two weeks of each winner’s confirmed response.

If you are a potential winner, we may require you to sign an Affidavit of Eligibility, Liability/Publicity Release within 10 days of notification. If you are a potential winner and you are 18 or older, but are considered a minor in your place of legal residence, we may require your parent or legal guardian to sign all required forms on your behalf. If you do not complete the required forms as instructed and/or return the required forms within the time period listed on the winner notification message, we may disqualify you and select an alternate, randomly selected winner.

If you are confirmed as a winner of this Sweepstakes:

  • You may not exchange your prize for cash or any other merchandise or services. However, if for any reason an advertised prize is unavailable, we reserve the right to substitute a prize of equal or greater value; and
  • You may not designate someone else as the winner. If you are unable or unwilling to accept your prize, we will award it to an alternate potential winner; and
  • If you accept a prize, you will be solely responsible for all applicable taxes related to accepting the prize; and
  • If you are otherwise eligible for this Sweepstakes, but are considered a minor in your place of residence, we may award the prize to your parent/legal guardian on your behalf.

WHAT ARE YOUR ODDS OF WINNING? 
Your odds of winning this Challenge depend on the number of eligible entries we receive and the creativity of the answers as judged by a panel of three (3) members of the Business Intelligence Team at Microsoft.

WHAT OTHER CONDITIONS ARE YOU AGREEING TO BY ENTERING THIS CHALLENGE? 
By entering this Challenge you agree:

· To abide by these Official Rules; and

· To release and hold harmless Microsoft, and its respective parents, subsidiaries, affiliates, employees and agents from any and all liability or any injury, loss or damage of any kind arising from or in connection with this Challenge or any prize won; and

· That Microsoft’s decisions will be final and binding on all matters related to this Challenge; and

· That by accepting a prize, Microsoft may use of your proper name and state of residence online and in print, or in any other media, in connection with this Challenge, without payment or compensation to you, except where prohibited by law

WHAT LAWS GOVERN THE WAY THIS CHALLENGE IS EXECUTED AND ADMINISTRATED? 
This Challenge will be governed by the laws of the State of Washington, and you consent to the exclusive jurisdiction and venue of the courts of the State of Washington for any disputes arising out of this Challenge.

WHAT IF SOMETHING UNEXPECTED HAPPENS AND THE CHALLENGE CAN’T RUN AS PLANNED? 
If cheating, a virus, bug, catastrophic event, or any other unforeseen or unexpected event that cannot be reasonably anticipated or controlled, (also referred to as force majeure) affects the fairness and / or integrity of this Challenge, we reserve the right to cancel, change or suspend this Challenge. This right is reserved whether the event is due to human or technical error. If a solution cannot be found to restore the integrity of the Challenge, we reserve the right to select winners from among all eligible entries received before we had to cancel, change or suspend the Challenge. If you attempt to compromise the integrity or the legitimate operation of this Challenge by hacking or by cheating or committing fraud in ANY way, we may seek damages from you to the fullest extent permitted by law. Further, we may ban you from participating in any of our future Challenge, so please play fairly.

HOW CAN YOU FIND OUT WHO WON? 
To find out who won, send an email to
v-daconn@microsoft.com by June, 30, 2013 with the subject line: “SQL Server A&Q Winners”

WHO IS SPONSORING THIS CHALLENGE? 
Microsoft Corporation 
One Microsoft Way 
Redmond, WA 98052

Stored procedure debugging in SSDT and LocalDB

Stored procedure debugging is a feature that has existed in SQL Server Management Studio (SSMS) for some time (since before SQL Server 2005 I believe) yet in my experience not many folks make use of it, indeed many folks aren’t even aware it exists. In this post I shall try and shine a light on what I believe is an underused feature in the SQL Server toolset.

When I have attempted to use debugging within SSMS in the past I have invariably run into problems. I can remember as recently as December 2010 trying to debug a stored procedure on a remote server and it required ports to be opened up on the firewall and other such hoopla – that’s not a pleasant experience when all you want to do is hit F10 and start stepping through your code. Happily in SQL Server 2012 the feature showed up in SQL Server Data Tools (SSDT) which in short is a Visual Studio-based tool aimed squarely at developers rather than DBAs and the advantage this brings is something called LocalDB. LocalDB is quite simply a free, lightweight edition of SQL Server that gets installed with SSDT, lives on your local box, and whose intended use is to help you write code (I do need to blog about LocalDB in more depth at some point). Due to LocalDB being on your local machine debugging T-SQL code there is its as close as its going to get to “it just works”.

Let’s take a look at how this happens, I’m going to demonstrate by debugging stored procedures in an open source project I’m working on called SSIS RestartFramework, the source code (currently) lives at http://ssisrestartframework.codeplex.com/SourceControl/latest in case you want to download it and follow along at home.

As with many features in SSDT the debugging feature is accessed via SQL Server Object Explorer (SSOX). From there one can right click on a stored procedure and select “Debug Procedure…”:

image

Selecting that option displays a dialog where one specifies values for the parameters of the stored procedure:

SNAGHTMLd5abc

Clicking OK launches a query window with some scaffolding code that calls the stored procedure with the specified values:

image

From there one can step through the code, executing each line in turn, using F10 & F11 just as if this were .Net code. Notice how, in the screenshot below, we are stepping through the code of the [RestartFramework].[PutETLJob] stored procedure which we stepped into (using F11) from the scaffolding code above.

image

Furthermore this screenshot depicts the Locals pane displaying the value of all in-scope variables and also the very cool ability to pin a variable (in this case @vRowCount) so that it “floats”, making it easy to see the value and also to see it changing.

I should point out that you do not need to have your stored procedures in LocalDB in order to debug them using SSDT, you can connect to any instance of SQL Server from SSOX and then launch the debugging experience.

This has a been an overview of the T-SQL debugging features in SSDT. Let me know if it proves useful to you.

@Jamiet

Encrypting and Decrypting SQL Server Stored Procedures, Views and User-Defined Functions

You work in a shop that puts business or application logic in SQL Server using stored procedures, views and functions to return values to the calling applications or perform tasks. This is not unusual for companies that use the SQL Server layer to perform business tasks, such as finance operations, or incorporate application functionality into the programmability layer. You wish to preserve secrecy on some procedures, views or functions in order to maintain security. But how secure is this and is there a way to decrypt these encrypted objects?

SQL Server Service won’t start after changing service account – service-specific error %%-2146885628

Yesterday I was at a site
where they decided to change the service account for the SQL Server services on
a set of systems. After changing the service accounts, SQL Server restarted
just fine on all machines except one.

I had used the SQL Server
Configuration Manager to make the changes (important to not just use the
Services applet in Administrative Tools) but I got the typical error telling me
that the service wouldn’t start in a timely fashion. The server was running SQL
Server 2008 R2 SP2.

Looking in the system
event log produced the following errors:

The SQL Server (MSSQLSERVER) service terminated with
service-specific error %%-2146885628.

A fatal error occurred when attempting to access the
SSL server credential private key. The error code returned from the
cryptographic module is 0x8009030d. The internal error state is 10001.

I spent a while looking
for info on the last error and found a site where they discussed that it was
generated when the service account could not read the machine keys that were
stored in the  C:ProgramDataMicrosoftCryptoRSAMachineKeys
folder. The article then discussed how to add read permissions for the keys one
by one.

It suddenly dawned on me
that it was probably a problem with the permissions on the folder instead.
Checking the permissions on that folder made me realize that the local Administrators
account should have the ability to read it. The service account was meant to be
a member of the local Administrators account but had not been added to that
group on this machine.

Adding the service
account to the local Administrators group on the machine (note: not the domain
administrators account) fixed the issue and the service started again, until I could get the correct account permissions set in the morning when other staff came back. (In the comments I’ve added a list of what’s actually required).

Hope this helps someone
else. (And helps me the next time I see this and have forgotten what it was
J)