THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Argenis Fernandez

  • Leveraging Service SIDs to Logon to SQL Server 2012 and SQL Server 2014 Instances with Sysadmin Privileges

    Edit: I have confirmed that this is still valid for the all versions of SQL Server 2012, and SQL Server 2014 - and even on Windows Server 2012 R2.

     

    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:

    image

    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:

    image

    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:

    image

    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.

    image

    Sure enough, launched SSMS 2012 and was able to login. And guess what, my login has sysadmin privileges.

    image

    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.

    Cheers,

     

    -Argenis

  • Tale of an Encrypted SSIS Package in msdb and a Lost Password

     

    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…

    LePackage_Password

    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:

     

    EncryptedXML

    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:

    RunLePackage

    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:

    SELECT sjs.command
        FROM msdb.dbo.sysjobs sj
        JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id = sjs.job_id
        WHERE sj.name = 'Run LePackage'

    The result:

    ResultsLePackage

    “Well, that’s really secure”, I thought to myself.

    Cheers,

    -Argenis

  • A (Late) Meme Monday Post: On SQLFamily

     

    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

    480px-Library_of_Congress,_Rosenwald_4,_Bl._5r

    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!

    Thank You

    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)

    Cheers!

    -Argenis

  • Regular Session at the 2011 PASS Summit: Troubleshooting SQL Server with SysInternals Tools

     

    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.

    Details:

    Session DBA-415

    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!

  • Speaking at PASS Summit 2011 - Lightning Talk Accepted!

    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.

  • Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again.

     

    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.

    interesting_lock

    (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)

    image

    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:

    image

    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):

    image

    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:

    image

    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.

    Cheers!

     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.

  • SQL Server Denali Wallpaper

    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:

    SQLServerDenali

    And here is the Widescreen version:

    SQLServerDenali_Widescreen

    Enjoy!

  • Transactional Replication and WAN links

    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.

    Other Optimizations

    There are other replication features that help reduce the amount of commands sent across to the subscriber. Namely:

    Happy Publishin'!

  • Blogging Again

    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?

    Cheers!

More Posts « Previous page
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement