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 all versions of SQL Server 2012, 2014 and the newly released SQL Server 2016, and even on Windows Server 2016 Technical Preview 5.
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 (or 2014) 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.
UPDATE FOR SQL EXPRESS USERS: If you need to leverage this trick on a SQL Server Express install, you can use the "Winmgmt" service - Windows Management Instrumentation, aka WMI service.
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.
Yesterday a member of the SQL community who I deeply admire sent me a DM on Twitter asking whether I had done a SQLFamily post for Thomas LaRock’s (blog|@SQLRockstar) Meme Monday for November. I replied that I did not, and I regretted not having done so. A subtle DM followed my response: “Get on it, you have all week”. And indeed I must. So here’s an attempt to express some of my feelings on a community that has catapulted my career like nothing else before I embraced it.
Nanos Gigantium Humeris Insidentes
I stand on the shoulders of giants. My SQLFamily has given me support at all levels. Professionally and personally. There is never a lack of will to help and provide advice to others in this community. And I do my best to help. On #SQLHelp on Twitter, via email, or even on the phone. I expect no retribution, because I know that when and if I do run into problems, my SQLFamily will be there for me.
I have met some of the most humble, dedicated and most professional people in the SQL community. And some of them have pretty big titles: MVPs, MCMs, Regional Mentors, and even leaders of PASS, SQLCAT members, and even PMs and Devs on the SQL Server team.
All are welcome, and that includes YOU!
I have also met some people that are rather reserved and don’t participate as much in the community, for whatever reason. Be as it may, let it be know to all that we are a very welcoming community – heck, some of my closest friends and people I can count on in the community have completely opposite political views. We share one goal: to get better and help others get better.
Even if you are a lurker – my hope is that one day you’ll decide to give back some of what you have learned.
You have to take it to the next level
On one of my previous jobs as an IT Supervisor I used to tell my team all the time about the benefits of continuous education and self-driven learning. Shortly after I left that job, the company went bankrupt and some of my staff got laid off – some without any severance pay whatsoever. I eventually found out that some of them had a really hard time finding another job, because their skills were simply outdated. They had become stale professionals. Don’t be one of them.
If you don’t take advantage of these learning resources, somebody else will – and that person has an advantage over you when applying for that awesome job position that got opened. There’s a severe shortage of good DBAs and DB Devs out there. What’s your excuse for not being excellent?
Even if your knowledge of SQL Server is at the beginner level, really – you have no excuse to get better. Just go to SQLUniversity and learn from there. Don’t get stale!
To all of you in the SQL community who put so much time and energy into helping others, my deepest gratitude to you. I can’t wait to meet you all again at the next event and share our SQL stories over a pint of beer (or a shot of Jaeger)
It looks like my talk at SQLSaturday 92 in Portland, OR was a big hit – and I get to deliver it at the 2011 PASS Summit!
If you’re at the Summit this week, go check out my talk – I promise I will show you techniques and tips that will be very useful to you.
Friday, 10:15 – 11:30am, Room 4C1-2
Followed in the afternoon by my Lightning Talk “Quick Tips to Enhance tempdb Performance” at 2:30pm, Room 201.
I’m honored to speak at the Summit this year – and I hope to meet you there!
Quick post - I got news that I will be speaking at the PASS Summit 2011. What a huge honor. As I understand it, I am the first Venezuelan ever to speak at PASS. I'm super excited!
The Lightning Talk is called "Quick Tips to Enhance tempdb Performance", and I have to practice this talk a lot. I'll have exactly 5 minutes to deliver it, and discussions about tempdb performance can go on for hours. So I need to keep it concise and to the point.
A full lineup of all Lightning Talks can be found here: http://www.sqlpass.org/summit/2011/SummitContent/LightningTalks2011.aspx
Also, I changed my Twitter handle to @DBArgenis, to better reflect who I am, my passion and commitment towards the SQL Server community.
Can't wait for the Summit now.
We had an email thread at work where someone was asking for assistance with an unknown sa password on a SQL instance on a new laptop. “No big deal” – I said to myself – “You can reset the sa password by starting SQL in single-user mode” (If you haven’t heard about this, have a look here).
And then I started thinking – but what if this happen to be a production server with tons of users and suddenly we found ourselves locked out of SQL Server? Starting with SQL Server 2008, the BUILTIN\Administrators group is not made a member of the sysadmin server role unless you add it explicitly. You’d have to bring the server down to reset the sa password. Downtime is not good eats.
Yeah, well – don’t get a false sense of security. Members of the local Administrators group can still login to SQL Server. With full sysadmin privileges.
(Taken from http://www.flickr.com/photos/librarianinblack/5898803951/in/photostream)
How? By impersonating the NT AUTHORITY\SYSTEM login that is created by Setup and granted sysadmin privileges by default. You can easily do this with PsExec (using the –s switch) or creating a Windows Scheduled Task running under the SYSTEM account.
Let me show you what I mean:
By default, things look like this on SSMS on a default installation (this particular instance just happens to be named, other than that it’s all default values for setup – MAGMA is the name of my test VM and SQLPS is the name of the domain)
As you see, the Administrators group is not listed as a login. Which means that if you try to login using the Administrator account, you’ll get a nice error window that says:
I happen to have a copy of PsExec on my C:\bin directory, so I will use it to illustrate this example. Let’s kickoff a cmd window (running as Administrator) that will run PsExec and launch another cmd window running as cmd – the parameters I used for PsExec are: Interactive (-i) and Run as System (-s):
I ran “whoami” on the child cmd window to show who’s running it. That was easy, eh?
Now, I just launch sqlcmd and run a couple of statements:
And voilà – I’ve created a new login for the Administrator user and made it a member of the sysadmin server role, all while running under NT AUTHORITY\SYSTEM.
So what is Microsoft’s guidance in regards to this NT AUTHORITY\SYSTEM login? To leave it as is – here is the KB article that states it. It was written for SQL Server 2005 and it hasn’t been updated since. I asked some of my contacts at CSS and they confirmed that the article is still considered valid for 2008 and 2008 R2. And that would be my humble recommendation as well. On a heavily secured installation though, you will definitely want to remove that login and perform careful testing of the side-effects of removing it.
Edit: As Jorge Segarra pointed out below, NT AUTHORITY\SYSTEM is no longer provisioned as member of the sysadmin server role by setup in SQL 11. I confirmed the behavior on a Denali CTP3 instance I setup from scratch.
The SQL Server team today tweeted (on their @SQLServer account) a SQL Server Denali Desktop Background, a.k.a. Wallpaper – and they were kind enough to let me post it here.
Here is the regular version:
And here is the Widescreen version:
We recently worked on a transactional replication setup that involved a very active VLDB and a subscriber being located on a different datacenter. What made it even more interesting is that the WAN link was not particularly fast. In this post, I would like to mention a few of the challenges we faced while and how we got past them, in the hopes that our experience can help you in your future endeavours.
The Problem with Slow Distribution Servers
If your distribution server is slow, your replication performance will tank. You will get behind on transactions and might not ever catch up. In our scenario we had a distribution server that was outdated. The server was running Windows Server 2003 and SQL Server 2005. In our case, this was the biggest issue. After we moved to a new distribution server that was running Windows Server 2008 R2 and SQL Server 2008 R2, our performance increased greatly. One of the biggest benefits in moving to Windows Server 2008 R2 is the set of enhacements made to the TCP/IP stack - particularly send/receive TCP windows. For more information, see this article on Technet.
Careful with WAN Accelerators
While WAN accelerators can be fantastic in a myriad of scenarios, in our testing we noticed that the device wasn't really optimizing replication traffic - and it was actually causing latency. With the help of our Networking team, we made sure that traffic from the distributor to the subscriber was skipped by the WAN accelerator. Obviously, your mileage will vary - so test accordingly.
Use Pull Subscriptions
Our initial setup was done using a Push subscription. This was a big mistake. While Push subscriptions are easier to setup and maintain, their performance across WAN links is plain dismal. The MSDN team at Microsoft put out a great White Paper on Geo-Replication Performance which is, in my opinion, required reading for replication to other datacenters. We saw huge performance gains when we switched to Pull. Orders of magnitude faster. Put simply - never use Push subscriptions across WAN links.
One of the design decisions made in our scenario that I would like to point out: we intentionally kept the distribution database near the publisher (i.e., on the same datacenter) - the reason behind this is simple: if your level of confidence in your WAN link isn't that high, the concern becomes the Log Reader agent and getting the Transaction Log to clear reliably and constantly.
Initialize the Subscriber from a Backup
With a WAN link and high latency involved, initialization of the subscriber from a backup is your best bet. We saved ourselves a lot of headaches by doing it. Creating and transferring a snapshot of a VLDB is out of the picture when you're concerned with WAN latency. In our scenario, the publisher was running SQL Server 2005 and backups were being taken using LiteSpeed. We transferred the most recent full backup to the remote datacenter using robocopy (could have used FTP also) plus the latest differential taken after changing the properties of the publication to allow initialization from backup. Restored at the subscriber using LiteSpeed tools, and then used the Extractor utility to create native-format backup files to initialize from backup. This is because you cannot initialize from a LiteSpeed backup, as SQL needs to read LSN information from the backup file and it uses a system stored procedure for that purpose. Here are some tips: you only need the first backup file created by Extractor to initialize. Also, you don't have to initialize with a differential backup - you can use a T-Log backup just as well.
Here is a good post on Initialization from Backup at ReplTalk that might be helpful if you run into issues.
There are other replication features that help reduce the amount of commands sent across to the subscriber. Namely:
Thanks to Adam Machanic (Blog | Twitter) I now have a new home for a blog. SQLPS.com is no more.
It is truly an honor to be able to blog here in SQLblog.
Expect posts ranging from very technical to not technical at all. I hope with my posts I can give back a little to the SQL Server community who has helped me grow my career so much. Now that I have opened the doors to this blog I ask you: Is there anything you would like to see me discuss in this space?