A little over a year ago I embarked on an adventure with Microsoft Consulting Services that would have me travel over 160,000 miles throughout the United States.
During this time I met great customers, made great friends, and had many lunches, dinners and drinks with members of the larger SQLFamily.
The one family I kept missing was my own – travel made me miss them too much to enjoy my work as a consultant as much as I once did. I knew going in that I’d have to travel a lot, but never expected the travel would take such a toll on me – and my family.
I loved being a consultant. I loved working directly with customers and helping them work through their challenges. I loved learning new things as I helped them. I loved teaching them new things as I helped them. I loved coming up with creative solutions that solved a myriad of problems and saved hundreds of thousands of dollars. But I humanly couldn’t handle the travel anymore.
Once decided that I needed to make a change before I went nuts, I came up with five requirements for my next adventure:
1) It would have to be super fun. The environment would have to be dynamic and filled with enthusiastic folks.
2) It would have to be flexible enough to allow me to continue with my SQL Server community involvement. Helping and teaching others keeps me sane.
3) It would have to pose great engineering challenges. My ADHD kicks into high gear otherwise.
4) Minimum to no travel requirements.
5) Manageable commute to work, with occasional work from home schedule.
And so I am happy to tell you all that I found such an adventure.
Starting April, I will be part of the Database Engineering team at SurveyMonkey.
SurveyMonkey is headquartered in Palo Alto, CA – but the database team is actually spread across HQ, the Portland, OR office, and now the Seattle, WA office with me working there.
If you can’t understand why I’m so happy to be a part of this company, I’ll give you an idea by linking you to the employee perks page. And although absolutely fantastic, those are just the perks – what I really like about this company is the people who work for it – and I’m glad I’m joining them.
I’m looking forward to what’s next.
Retaking on this blog series after an extended hiatus. I hope you will enjoy today’s topic on documentation.
Boring Work? Nope – An Opportunity
One of the often neglected and yet extremely important tasks of any DBA is to document his/her environment. I can certainly relate to those of you who haven’t written a single page of documentation for your environment – I was there before. And I felt bad.
The good news is that documentation doesn’t have to be painful. My friends at Brent Ozar Unlimited recently posted a video on documentation – you can find it in this link – and they touch on that very same subject: There ARE ways to document your environment that will leave you greatly satisfied with the results, and some of you might even enjoy the process. Your manager will love you for it – and you won’t feel bad for the new guy who replaces you when it’s time to move on.
How About Word Documents and Excel Sheets in SharePoint?
Not perfect. Let’s face it. Writing pages and pages of documentation for your database environment (or ANY IT environment) in Word is tiresome. Excel sheets can have tons of information and might be easy to peruse. But no matter how good your formatting is, how many pictures and Visio diagrams you throw at it, you’ll always get the sense that the document you spent days working on will just end up in the SharePoint site and collect dust there. SharePoint can handle changes and versioning for you, if you use it correctly – so that’s a plus. But think about ease of access to the documents – ease of editing. Discussions around it. Even automated updates! Is a SharePoint folder the way to go for that? (Or a UNC path, for those of you without SharePoint?)
An Old Friend Can Work Marvels
You’ve been to WikiPedia. More than once. You know how useful it is, and how, thanks for the built-in search function and Search Engines, easy it is to find information on it.
How about using a wiki engine for your documentation? Can’t picture that in your head? Hear me out: wikis are VERY easy to create, edit and collaborate on – you just click on Edit and away you go. They have built-in version control, discussion pages, categories, lists, and pretty much everything you need to have a versatile documentation site.
Picture this: a wiki page for every environment, listing all the servers that belong to it – and linking to individual wiki pages with tons of information about those servers, including former incident highlights, detailed information about the server (CPUs, RAM, OS Version, SQL Instances, patches, team(s) who own the server, escalation paths for incidents, and much more.
The best part: these pages that have lots of information can be updated via an automated process. WHAT? AUTOMATED DOCUMENTATION YOU SAY? Yes. Think scripts. Scripts that will go over your environments on a regular basis and update information on them in the wiki. Does that sound cool or what? All you need is to understand the capabilities of your wiki engine (think API), and pair it up with your favorite scripting language. For me, it’s a slam-dunk: I’ll use PowerShell to automate the information gathering process, and a wiki engine that runs on SQL Server – I’ll show you a few options in a bit.
Now imagine that you give access to your NOC/Helpdesk to this wiki site: you don’t want them to change anything on it, so you set permissions for read-only access to the site. The benefits would be great: now the NOC has easy access to information on a given server that just alerted, and if you saved notes for that type of alert for that server, instructing Operators to collect certain information before they call you, that can save precious time and help you troubleshoot the issue. You might even let the NOC track incidents on a particular section of the wiki page for that server, so you can figure out if there is a pattern that you need to address.
YMMV on your organization’s needs – but this can be a very valuable tool to keep your business running efficiently. I have worked for teams that relied heavily on Wikis for documentation - and saw first hand how this greatly improves access to information for our team. How you use it in your environment and how you adapt it to your requirements is entirely up to you – I only wanted to give you a few ideas on how you might put this to your advantage.
Which Wiki Engine Should I Use?
There are TONS of wiki engines out there: WikiPedia has a page (not frequently updated, alas) that lists some of them. Being the SQL Server guy that I am, I’m naturally inclined to use something powered by SQL in the backend. The most widely used wiki engine, developed by the Wikimedia Foundation, is a typical LAMP (Linux/Apache/MySQL/PHP) application. If you have experience with Apache, PHP and MySQL, you should use that. It’s updated very frequently, and it’s got a large community behind it.
Now, if you have SharePoint, you may or may not know that it comes with a wiki engine as well – and that’s detailed here.
Recently I used FlexWiki , which uses SQL Server as a backend. Being a SQL database on the backend, you back it up and keep it safe as the rest of your backups – just don’t use the same servers you’re trying to document to host the wiki engine’s database. If you lose that server, you lost access to the documentation about it.
Unfortunately, FlexWiki isn’t being actively maintained. But look at the list of Wiki software – you’re bound to find something that fits your needs.
No More Excuses – Get To It!
If you have no documentation/poor documentation. Go take care of it, now. Go create and/or reuse scripts that gather information about your environment. Be proactive about it – make sure those scripts get updated. Show your work to your peers and your boss. Feel confident that you have control of your environment, and rock on!
What Are Your Thoughts?
I’d love to hear what you’re doing in your environment as far as documentation goes. Will you leave some comments below? Also, if you feel like giving some feedback on the DBA Best Practices series, I’d appreciate it.
Until next time,
If you were looking for Paul Randal’s [Blog|Twitter] DR poster and couldn’t
find it anywhere on the net, I have good news for you: I saved a copy on my PC
before it was taken down, and (with Paul’s permission) wanted to make it
So, without further ado, enjoy the poster.
(I’m taking a break from my DBA Best Practices blog series to talk about this today – the series continues after the Holidays!)
I love to be proved wrong. Really, I do.
Recently we had a discussion in an distribution list where somebody asked whether a SQL Server 32-bit instance could address more than 4Gb of RAM when running on top of a 64-bit OS. One of the really smart guys in Microsoft SQL Server Support, Karthick P.K. [Blog|Blog|Twitter] replied “sure, just add more RAM and enable AWE – SQL will use that memory”. I was much convinced that this was incorrect, so I jumped in and said that AWE does nothing under those circumstances. After all, that is what I had read in the past in many different articles. I even had some SQL Server MVPs who have been around the block forever agreeing with me.
But the good news is that I was wrong.
AWE in fact does nothing on 64-bit instances of SQL Server. But on WOW64 - Windows on Windows 64-bit, the subsystem of Windows x64 that allows you to run 32-bit processes and instances of SQL Server, AWE does allow you to address memory above 4Gb. Let me show you.
Side note: AWE is now deprecated - removed in SQL Server 2012. The last version of SQL Server that supports AWE is SQL Server 2008 R2. Because of this, a SQL Server 2012 x86 instance won’t be able to enjoy over 4Gb of memory – even if running on WOW64.
I setup a vanilla VM in my lab with a 64-bit OS with 8Gb of memory. MSINFO32 looks like this on the VM:
I installed a 32-bit SQL Server 2008 R2 SP2 instance on the VM. @@VERSION looked like this:
I did not enable AWE. I did grant the “Perform Volume Maintenance Tasks” and “Lock Pages in Memory” privileges using secpol.msc (not pictured!). I wanted to see what memory consumption would look like without AWE enabled.
This screenshot shows the permissions granted to the SQL Server Service Account (NETWORK SERVICE, in this case) – used “whoami /priv”, a quick and dirty way to get the privileges of the service account using xp_cmdshell. Ugh, xp_cmdshell. It’s okay, it’s just a test.
I went ahead and created a test database with just one big table – big enough to use all the buffer pool memory on the instance when a size-of-data operation is executed on it. I did set Max Server Memory to 6Gb.
Here’s the CREATE table script:
CREATE TABLE [dbo].[testTable](
[id] [int] IDENTITY(1,1) NOT NULL,
[wideColumn] [nvarchar](4000) NULL,
PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[testTable] ADD DEFAULT (replicate(N'A',(4000))) FOR [wideColumn]
Next, I went ahead and inserted 1M rows on the table:
INSERT INTO [dbo].[testTable] DEFAULT VALUES
If you do the math very quickly, you will agree with me that this not so little table will be more than 6Gb in size. Let’s check anyway:
Cool! The table is large enough for the purpose of this test. But how does memory utilization look like after that 1M row insert?
That’s less than 4Gb. Next I turned on AWE, and restarted the instance. Then I queried the ERRORLOG to confirm that AWE was enabled:
Okay, since I had just restarted the instance my buffer pool was gone – needed to force a size of data operation on the large table. Rebuilding the clustered index did just fine (I know, that’s probably overkill). Then I checked sys.dm_os_process_memory again:
And there we go! A lot more than 4Gb being used now that AWE is in play. I had set Max Server Memory to 6Gb, as I detailed earlier.
It looks like there’s quite a lot of incorrect documentation regarding this, so I wanted to clear out any confusion – and learn something new along the way. Don’t you love SQL Server?
Happy Holidays and Happy New Year!
P.S.: If you are running IA64 (Itanium) - then this doesn't apply to you. AWE does not work at all on IA64.
Digital World, Digital Locks
One of the biggest digital assets that any company has is its secrets. These include passwords, key rings, certificates, and any other digital asset used to protect another asset from tampering or unauthorized access.
As a DBA, you are very likely to manage some of these assets for your company - and your employer trusts you with keeping them safe. Probably one of the most important of these assets are passwords. As you well know, the can be used anywhere: for service accounts, credentials, proxies, linked servers, DTS/SSIS packages, symmetrical keys, private keys, etc., etc.
Have you given some thought to what you're doing to keep these passwords safe? Are you backing them up somewhere? Who else besides you can access them?
Good-Ol’ Post-It Notes Under Your Keyboard
If you have a password-protected Excel sheet for your passwords, I have bad news for you: Excel's level of encryption is good for your grandma's budget spreadsheet, not for a list of enterprise passwords.
I will try to summarize the main point of this best practice in one sentence: You should keep your passwords on an encrypted, access and version-controlled, backed-up, well-known shared location that every DBA on your team is aware of, and maintain copies of this password "database" on your DBA's workstations.
Now I have to break down that statement to you:
- Encrypted: what’s the point of saving your passwords on a file that any Windows admin with enough privileges can read?
- Access controlled: This one is pretty much self-explanatory.
- Version controlled: Passwords change (and I’m really hoping you do change them) and version control would allow you to track what a previous password was if the utility you’ve chosen doesn’t handle that for you.
- Backed-up: You want a safe copy of the password list to be kept offline, preferably in long term storage, with relative ease of restoring.
- Well-known shared location: This is critical for teams: what good is a password list if only one person in the team knows where it is?
I have seen multiple examples of this that work well. They all start with an encrypted database. Certainly you could leverage SQL Server's native encryption solutions like cell encryption for this. I have found such implementations to be impractical, for the most part.
Enter The World Of Utilities
There are a myriad of open source/free software solutions to help you here. One of my favorites is KeePass, which creates encrypted files that can be saved to a network share, Sharepoint, etc. KeePass has UIs for most operating systems, including Windows, MacOS, iOS, Android and Windows Phone.
Other solutions I've used before worth mentioning include PasswordSafe and 1Password, with the latter one being a paid solution – but wildly popular in mobile devices.
There are, of course, even more "enterprise-level" solutions available from 3rd party vendors. The truth is that most of the customers that I work with don't need that level of protection of their digital assets, and something like a KeePass database on Sharepoint suits them very well.
What are you doing to safeguard your passwords? Leave a comment below, and join the discussion!
This blog post is part of the DBA Best Practices series, on which various topics of concern for daily database operations are discussed. Your feedback and comments are very much welcome, so please drop by the comments section and be sure to leave your thoughts on the subject.
When I was a DBA, the first thing I did when I sat down at my desk at work was checking that all backups had completed successfully. It really was more of a ritual, since I had a dual system in place to check for backup completion: 1) the scheduled agent jobs to back up the databases were set to alert the NOC in failure, and 2) I had a script run from a central server every so often to check for any backup failures.
Why the redundancy, you might ask. Well, for one I was once bitten by the fact that database mail doesn't work 100% of the time. Potential causes for failure include issues on the SMTP box that relays your server email, firewall problems, DNS issues, etc. And so to be sure that my backups completed fine, I needed to rely on a mechanism other than having the servers do the taking - I needed to interrogate the servers and ask each one if an issue had occurred. This is why I had a script run every so often.
Some of you might have monitoring tools in place like Microsoft System Center Operations Manager (SCOM) or similar 3rd party products that would track all these things for you. But at that moment, we had no resort but to write our own Powershell scripts to do it.
Now it goes without saying that if you don't have backups in place, you might as well find another career. Your most sacred job as a DBA is to protect the data from a disaster, and only properly safeguarded backups can offer you peace of mind here.
"But, we have a cluster...we don't need backups"
Sadly I've heard this line more than I would have liked to. You need to understand that a cluster is comprised of shared storage, and that is precisely your single point of failure. A cluster will protect you from an issue at the Operating System level, and also under an outage of any SQL-related service or dependent devices. But it will most definitely NOT protect you against corruption, nor will it protect you against somebody deleting data from a table - accidentally or otherwise.
Backup, fine. How often do I take a backup?
The answer to this is something you will hear frequently when working with databases: it depends.
What does it depend on? For one, you need to understand how much data your business is willing to lose. This is what's called Recovery Point Objective, or RPO. If you don't know how much data your business is willing to lose, you need to have an honest and realistic conversation about data loss expectations with your customers, internal or external. From my experience, their first answer to the question "how much data loss can you withstand?" will be "zero". In that case, you will need to explain how zero data loss is very difficult and very costly to achieve, even in today's computing environments.
Do you want to go ahead and take full backups of all your databases every hour, or even every day? Probably not, because of the impact that taking a full backup can have on a system. That's what differential and transaction log backups are for.
Have I answered the question of how often to take a backup? No, and I did that on purpose. You need to think about how much time you have to recover from any event that requires you to restore your databases. This is what's called Recovery Time Objective. Again, if you go ask your customer how long of an outage they can withstand, at first you will get a completely unrealistic number - and that will be your starting point for discussing a solution that is cost effective.
The point that I'm trying to get across is that you need to have a plan. This plan needs to be practiced, and tested. Like a football playbook, you need to rehearse the moves you'll perform when the time comes. How often is up to you, and the objective is that you feel better about yourself and the steps you need to follow when emergency strikes.
A backup is nothing more than an untested restore
Backups are files. Files are prone to corruption. Put those two together and realize how you feel about those backups sitting on that network drive. When was the last time you restored any of those?
Restoring your backups on another box - that, by the way, doesn't have to match the specs of your production server - will give you two things: 1) peace of mind, because now you know that your backups are good and 2) a place to offload your consistency checks with DBCC CHECKDB or any of the other DBCC commands like CHECKTABLE or CHECKCATALOG. This is a great strategy for VLDBs that cannot withstand the additional load created by the consistency checks.
If you choose to offload your consistency checks to another server though, be sure to run DBCC CHECKDB WITH PHYSICALONLY on the production server, and if you're using SQL Server 2008 R2 SP1 CU4 and above, be sure to enable traceflags 2562 and/or 2549, which will speed up the PHYSICALONLY checks further - you can read more about this enhancement here.
Back to the "How Often" question for a second. If you have the disk, and the network latency, and the system resources to do so, why not backup the transaction log often? As in, every 5 minutes, or even less than that? There's not much downside to doing it, as you will have to clear the log with a backup sooner than later, lest you risk running out space on your tlog, or even your drive. The one drawback to this approach is that you will have more files to deal with at restore time, and processing each file will add a bit of extra time to the entire process. But it might be worth that time knowing that you minimized the amount of data lost. Again, test your plan to make sure that it matches your particular needs.
Where to back up to? Network share? Locally? SAN volume?
This is another topic where everybody has a favorite choice. So, I'll stick to mentioning what I like to do and what I consider to be the best practice in this regard. I like to backup to a SAN volume, i.e., a drive that actually lives in the SAN, and can be easily attached to another server in a pinch, saving you valuable time - you wouldn't need to restore files on the network (slow) or pull out drives out a dead server (been there, done that, it’s also slow!).
The key is to have a copy of those backup files made quickly, and, if at all possible, to a remote target on a different datacenter - or even the cloud. There are plenty of solutions out there that can help you put such a solution together. That right there is the first step towards a practical Disaster Recovery plan. But there's much more to DR, and that's material for a different blog post in this series.
I had an email thread going with a prominent member of the SQL Server community today, where he confessed that he didn’t attend any sessions during the PASS Summit last week. He spent all of this time networking and catching up with people.
I, personally, can relate.
This year’s Summit was another incarnation of that ritual of SQL Server professionals meeting to share their knowledge, experience, and just have a wonderful time while doing so.
It’s been a few days after the Summit is over, and I’m definitely dealing with withdrawal.
My name is Argenis, and I’m a #SQLFamilyHolic.
(This post is part of the T-SQL Tuesday series, a monthly series of blog posts from members of the SQL Server community – this month, Chris Yates is hosting)
After the success of the “Demystifying DBA Best Practices” Pre-Conference that my good friend Robert Davis, a.k.a. SQLSoldier [Blog|Twitter] and I delivered at multiple events, including the PASS Summit 2012, I have decided to blog about some of the topics discussed at the Pre-Con. My thanks go to Robert for agreeing to share this content with the larger SQL Server community.
This will be a rather lengthy blog series - and as in the Pre-Con, I expect a lot of interaction and feedback. Make sure you throw in your two cents in the comments section of every blog post.
First topic that I’ll be discussing in this blog series: The thing of utmost importance for any Database Administrator: the data. Let’s discuss the importance of backups and a solid restore strategy.
Care to share your thoughts on this subject in the comments section below?
This is something that comes up rather regularly at forums, so I decided to create a quick post to make sure that folks out there can feel better about SQL Server 2012.
If you read this Web article, “Features Supported By Editions of SQL Server 2012” as of time of writing this post, you will see that the article points out that these two features are not supported on x64 Standard Edition. This is NOT correct. It is most definitely a documentation bug – one that unfortunately has caused some customers to sit on a waiting pattern before upgrading to SQL Server 2012.
Database Mail and SMO indeed work and are fully supported on SQL Server 2012 Standard Edition x64 instances. These features work as they should.
I have contacted the documentation teams internally to make sure that this is reflected on next releases of said Web article.
Today Jeffrey Langdon (@jlangdon) posed on #SQLHelp the following questions:
So I set to answer his question, and I said to myself: “Hey, I haven’t blogged in a while, how about I blog about this particular topic?”. Thus, this post was born.
(If you have never heard of Ghost Records and/or the Ghost Cleanup Task, go see this blog post by Paul Randal)
1) Do ghost records get copied over in a backup?
If you guessed yes, you guessed right. The backup process in SQL Server takes all data as it is on disk – it doesn’t crack the pages open to selectively pick which slots have actual data and which ones do not. The whole page is backed up, regardless of its contents.
Even if ghost cleanup has run and processed the ghost records, the slots are not overwritten immediately, but rather until another DML operation comes along and uses them.
As a matter of fact, all of the allocated space for a database will be included in a full backup.
So, this poses a bit of a security/compliance problem for some of you DBA folk: if you want to take a full backup of a database after you’ve purged sensitive data, you should rebuild all of your indexes (with FILLFACTOR set to 100%). But the empty space on your data file(s) might still contain sensitive data! A SHRINKFILE might help get rid of that (not so) empty space, but that might not be the end of your troubles. You might _STILL_ have (not so) empty space on your files!
One approach that you can follow is to export all of the data on your database to another SQL Server instance that does NOT have Instant File Initialization enabled. This can be a tedious and time-consuming process, though. So you have to weigh in your options and see what makes sense for you. Snapshot Replication is another idea that comes to mind.
2) Does Compression get rid of ghost records (2008)?
The answer to this is no. The Ghost Records/Ghost Cleanup Task mechanism is alive and well on compressed tables and indexes. You can prove this running a simple script:
CREATE DATABASE GhostRecordsTest
CREATE TABLE myTable (myPrimaryKey int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
myWideColumn varchar(1000) NOT NULL DEFAULT 'Default string value')
ALTER TABLE myTable REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
INSERT INTO myTable DEFAULT VALUES
DELETE myTable WHERE myPrimaryKey % 2 = 0
TraceFlag 2514 will make DBCC CHECKTABLE give you an extra tidbit of information on its output. For the above script: “Ghost Record count = 5”
Until next time,
I’ve lost count of how many times I’ve heard that phrase.
Are you specialized? On something? Or anything at all? Has that been a good or a bad thing? Why?
Are you the SQL guy at work? Or the one who does everything?
Do you code? And configure wireless routers at work also?
If you had to pick one thing to specialize on, what would it be?
Over the course of my career I’ve worn many many hats. I always felt I was doing fine, had a stable job, but wasn’t quite fond of my prospects for the future. Then a friend said that I should focus on one thing and be the best at it. And while I’m most certainly NOT the best at it, I’ve gotten progressively better on it, to the degree that I’ve been called an ‘Expert’ by some (hate that word!) – I’d rather be called ‘knowledgeable’. My career took off like a rocket after I specialized, and certainly choosing to focus on one thing (SQL Server, in my case) has been one of the best decisions I’ve ever made. I've also been careful of not forgetting my roots as a SysAdmin – and always try to keep up with changes on the Windows/SAN/Networking front, but not with the same level of intensity.
So, in this installment of T-SQL Tuesday I’d like to ask you to blog about your experience. Tell us why you specialized, or why you’d like to specialize. If you don’t think that specialization is a good thing, tell us why. Discuss. Argue your point(s).
- Your post must be published between 00:00 GMT Tuesday March 13th, 2011, and 00:00 GMT Wednesday March14th, 2011
- Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
- Trackbacks should work, but if you don’t see one please link to your post in the comments section below so everyone can see your work
Optional, but highly encouraged…
- Include a reference to T-SQL Tuesday in the title of your post
- Tweet about your post using the hash tag #TSQL2sDay
- Consider hosting T-SQL Tuesday yourself. Adam Machanic keeps the list.
I took the MCM lab a couple of weeks ago and passed. I still don’t believe it - it’s been quite the journey.
There have been many decisive moments in my career; one of those was back in 2010 when I decided I would work on obtaining the certification after Brent Ozar blogged rather extensively about his experience with the program. I progressively worked on aspects of SQL Server that were out of my comfort zone – and that paid off. But simply put, I have the cert - but still don’t consider myself a true Master. And I probably never will.
I might be okay in certain areas of SQL, maybe even good in others - but I don’t master them all. I intend on getting better on those areas, but that won’t happen overnight.
SQL Server is a mammoth of a product. It’s actually a suite of products – and the MCM certification focuses on only one aspect of it: the Database Engine. No SSAS, no SSIS, no SSRS.
So you might be wondering: Is it worth it then? Why work so hard on obtaining a certification that calls you “Master” when it doesn’t test your skills on every single feature of SQL Server?
To me, it was. And then some.
The fact that I value most after going through the program? Knowing exactly what I don’t know. In other words, looking at a problem and knowing that I won’t be able to solve it right away, yet still I will have an idea of where to begin digging for information to conquer it.
You see, when I decided to go for the MCM I would have probably ranked myself a 6/10. So I went and trained myself on those areas where I felt I wasn’t strong enough. I read blogs, I watched videos, I attended SQLSkills immersion events (lucky me!), I went to SQLSaturdays, and even a couple of PASS Summits – but all of that was theory, not a whole lot of practice. And when you get to the MCM Lab, you better had practiced. You better had familiarized with that particular feature you’re being challenged with – at least a tiny bit. Learning from BOL on the fly during the MCM Lab is probably just going to be a waste of time – trust me on that one.
The other thing I learned along the way? The power of networking. If you are a SQL Server professional, in any function – DBA, Dev or what have you – allow yourself a couple of minutes to brag about what you do: our community is awesome. Better than anything I’ve seen out there. I haven’t met a single person in the SQL community that’s not willing to help a colleague.
And that’s precisely what helped me prepare the most for the MCM: helping others. Perhaps that will help you get there as well?
Since it looks like everything I blog about lately is showing how to get onto SQL instances to which you don’t have formal credentials, I figured what the heck – let’s do a post on SQL 2000.
Earlier on today Saurabh Sapra [twitter] sent a tweet to SQL Server MVP Thomas LaRock [blog|twitter]:
To which Tom replied:
I was flattered. But I had no posts on that subject. So in turn, I replied:
It turns out that it is straightforward. And I will show you how to do just that – recover the SA password of a SQL 2000 instance. It’s easier than you think. As a matter of fact, I’m going to show you how to recover the password for any SQL login that has a current session established against a SQL 2000 instance.
Caveat – and this is important – you cannot recover the password if the user does not have a session established. I.E., if it is not logged on to SQL. I haven’t heard of a way to recover an SA password if there isn’t a connection established to SQL. If you know how to do this, please drop me a line.
Let’s dive right into it.
On Enterprise Manager, I registered the local instance using the SA login – then dropped the BUILTIN\Administrators login.
Nothing shocking there – just showing you that there is at least one active connection to SQL (Enterprise Manager itself) and that there are no other SQL logins on the box.
Next, I opened up a CMD window, and created a memory dump of the sqlservr.exe process. I can do this because I am a local Windows Administrator on this box. To achieve this I used the SysInternals’ ProcDump utility. Then I took the memory dump and parsed it through strings.exe, another SysInternals utility, essentially filtering from the memory dump anything that doesn’t look like a string.
Now I opened dump.txt in Notepad, and searched for the host name of my SQL 2000 box immediately followed by sa. In this case my VM is named “HURRICANE”, so I searched for “HURRICANEsa”
And there you have it. The password is found between the string “HURRICANEsa” and the string “MS SQLEM(LOCAL)ODBC”. The SA password for this instance is “Password@99!”.
Obviously your memory dump will look different than mine, given that your SQL client string will probably be different than the one Enterprise Manager uses – which I leveraged for this example. Also, my SQL 2000 instance had just been restarted, so I had less ‘stuff’ in memory than you probably will. You might even get a false positive. My point: YMMV.
As I mentioned above, this technique also works for any SQL login with an established connection, regardless of its privileges on the server. Moreover, this technique also works on SQL Server 2005 and (I believe – haven’t tested it) some builds of 2008 and 2008 R2. It wasn’t too long ago that the SQL team at Microsoft started to finally clear this area of memory after a session’s credentials have been verified.
Hope this was useful.
UPDATE: A couple of folks asked me which build I used to test this. I originally tested it on SP4a, and later confirmed with a more recent build, 8.00.2187 (but not the latest one that’s available only to customers with support agreements).
Edit: I have confirmed that this is still valid for the RTM version of SQL Server 2012.
If you recall one of my previous blog posts, titled Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again I exploited the fact that NT AUTHORITY\SYSTEM was granted membership to the sysadmin server role by setup in SQL Server 2008 R2 and below to gain access to a SQL instance to which I had no access, since as Administrator on the box I could launch a cmd session as NT AUTHORITY\SYSTEM with Sysinternals’ psexec utility.
My friend and SQL Server MVP Jorge Segarra [blog|twitter] correctly pointed out shortly after that NT AUTHORITY\SYSTEM is not a member of the sysadmin server role in SQL Server 2012, codename Denali. And as of Release Candidate 0 for this version, this holds true.
What also holds true as of RC0 is that the Service SID for a number of services (at least three, the SQL Engine itself, SQL VSS Writer and Winmgmt) are members of the sysadmin role. And so in this post I’d like to demonstrate that it is possible to exploit one of these services' level of access to hop onto a 2012 instance as sysadmin.
The target: a named SQL instance called “DENALI_RC0” on one of my desktop PCs. Having dropped my login on SQL, when I try to logon to the instance I get the usual message:
I picked a service to become “the victim”. The SQL VSS Writer service seemed to be a good candidate: innocuous enough. If I stop it and restart it, no big deal.
I launched regedit and browsed to HKLM\SYSTEM\CurrentControlSet\services\SQLWriter - this is what I saw:
Now being an Administrator of this PC as I am, I went ahead and renamed sqlwriter.exe to sqlwriter.exe.orig, and put a copy of SQLCMD.EXE on C:\Program Files\Microsoft SQL Server\90\Shared.
Then I renamed SQLCMD.EXE to sqlwriter.exe.
Obviously kicking off the SQL VSS Writer service was not going to do anything – just error out:
So I replaced the ImagePath for sqlwriter on the registry with this:
"C:\Program Files\Microsoft SQL Server\90\Shared\sqlwriter.exe" -S CSHQAFERNANDEZD\DENALI_RC0 -E -Q "CREATE LOGIN [CORP\Argenis.Fernandez] FROM WINDOWS; EXECUTE sp_addsrvrolemember @loginame = 'CORP\Argenis.Fernandez', @rolename = 'sysadmin'"
And now I kick off the sqlwriter service again, expecting it to error out…but with a nice side effect.
Sure enough, launched SSMS 2012 and was able to login. And guess what, my login has sysadmin privileges.
And so I’m sure some of you have already yelled “SECURITY HOLE!!!!” by now – yeah, to a degree…but remember kids, if you’re a local Administrator on the box, you already own the box. Very little applications like SQL Server can do to protect themselves from a “rogue” Admin. Maybe a few adjustments to the security model for Windows’ SCM (Service Control Manager) are needed here, but I’ll let you decide on that.
Yesterday a Developer at work asked for a copy of an SSIS package in Production so he could work on it (please, dear Reader – withhold judgment on Source Control – I know!). I logged on to the SSIS instance, and when I went to export the package…
Oops. I didn’t have that password. The DBA who uploaded the package to Production is long gone; my fellow DBA had no idea either - and the Devs returned a cricket sound when queried.
So I posed the obligatory question on #SQLHelp and a bunch of folks jumped in – some to help and some to make fun of me (thanks, @SQLSoldier @crummel4 @maryarcia and @sqljoe). I tried their suggestions to no avail…even ran some queries to see if I could figure out how to extract the package XML from the system tables in msdb:
SELECT CAST(CAST(p.packagedata AS varbinary(max)) AS varchar(max))
FROM msdb.dbo.sysssispackages p
WHERE p.name = 'LePackage'
This just returned a bunch of XML with encrypted data on it:
I knew there was a job in SQL Agent scheduled to execute the package, and when I tried to look at details on the job step I got the following:
Not very helpful.
The password had to be saved somewhere, but where??
All of a sudden I remembered that there was a system table I hadn’t queried yet:
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
WHERE sj.name = 'Run LePackage'
“Well, that’s really secure”, I thought to myself.