Kerberos Authentication and SQL Server (Part 1)

It’s been a while since my last post (i.e. almost 1 & 1/2 years, ouch!), but I hope to get back into a more regular pace with my updates going forward.  My next few posts will be a short series related to Kerberos Authentication, particularly in relation to the SQL Server product family.  Kerberos is a critical component needed for some advanced security features and configurations and Microsoft recommends it be enabled in every SQL Server deployment, pending outstanding circumstances.  Some people will find it difficult to setup, but those challenges tend to relate to misunderstanding what’s going on and what permissions are required to get everything working.  In this post, I’ll discuss what Kerberos is and what is needed within your Windows environment for SQL Server to utilize this authentication protocol.

Identifying Databases Not Meeting Backup Service Level Agreements (SLAs)

A quick way to find out if your backups are working as you expect

One of the questions I always ask database administrator interviewees is “What is the number one job of a DBA?”.  I feel the answer says a lot about how this possible candidate thinks, and on another level if they read the job description or not.  What I hope to hear is “Ensure recoverability in the event of a disaster” or something along those lines.  If it wasn’t obvious, I feel strongly that recoverability of your database(s) is priority number one.

Because there is an ever-present need to ensure your backups are current and meeting your establish Service Level Agreement (SLA) with the business, it’s probably a good idea to have a handy query that will get this for you.  I also like to operate on the notion that “No News is Good News”, so the output of this report shows only databases failing the SLA.

Soup to Nuts: Oracle 11g Client on Windows (Part 2)

The Nuts: (Un)Deinstalling the Oracle Client

In part 1 of this two-part post, I described how to install the Oracle 11g Client and how to fully configure it to work for what I believe is a majority of use-cases on Windows.  In this follow-up post, I will discuss how to uninstall (Oracle likes to say deinstall) the client both automatically and manually so you can start over,  upgrade to the latest and greatest client, or walkaway from Oracle being on your machine once and for all.

Soup to Nuts: Oracle 11g Client on Windows (Part 1)

The Soup: Installing The Oracle 11g Client

Chances are higher than 90% that if you work in a corporate environment you are logging onto a Windows OS every morning.  If you happen to dabble in the Oracle domain, chances are also highly likely that you have (or will need) the Oracle client installed on your machine.  The Oracle client installer, like many things Oracle-branded, is based on Java, which can best be described as “tolerated” by Windows (though some corporations are becoming less so).  Frankly, I dislike Java-based installers as they tend not to tie in well with the Windows operating system, and the Oracle client installer is no exception.  Regardless of the underlying technology or my opinion, my need to have the client installed is still very real as I work with both SQL Server and Oracle database solutions.  In my experience, it’s quite easy to end up with either a bad, broken, or partially working Oracle 11g Client installation without even knowing how or why.  This 2-part post will help avoid these situations before they begin or help rectify them if you already find yourself in a bad spot.  Part 1 will outline how and what to install using the installer, and Part 2 will outline how to uninstall the client, both automatically (if possible) and manually (if necessary).

Fully Script out a MSSQL Database Role

A New Hope

SQL Server Management Studio is a phenomenal tool, especially when you want to script out complex tasks.  Do whatever you want in the GUI and click the handy little script button instead of OK and you’ve got a set of raw SQL that you can modify/reuse/etc.  This process works for a good majority of the operations you will find yourself doing as a DBA, but there’s one gaping hole that MS has yet to fill: scripting out Database Roles.  If you manage any level of database security, chances are you are using Database Roles.  If you were lucky enough to create them yourself, it’s not difficult to keep a script library to see what role allows for what permission(s).  However, the moment you inherit a database that contains custom Database Roles, you enter a world of hurt.  Traditionally, you can either painstakingly struggle with the GUI trying to associate various records and checkboxes with actual commands or you can throw in the towel and start from scratch.  Thankfully, there is a new way, as I’ve scripted out a process that will generate the role definition for you.

When Builtin Database Roles Just Don’t Make the Grade

How to create a DDLAdmin-ish database role

A client of mine had a request to create a database role that allowed users to Create, Modify, and Drop Stored Procedures and Views.  Sounds simple enough, right?  Right… (the ellipsis implies sarcasm here).  Part of this request was that no other schema changes should be allowed.  Luckily the users requiring these permissions are developers and not DBAs.  They don’t really care about triggers, synonyms, etc so I find my job a little easier.  The only major objects I need to ensure they can’t touch are the tables; basically everything else is fair-game.

Making NetApp SnapManager “Play Nice”

Implementing NetApp SnapManager for SQL Server

I’m a firm believer in using any software at your disposal to it’s fullest.  There are times though that I curse my own name for adopting this mantra, because let’s face it, some software just refuses to “Play Nice”.  I don’t want to sound too critical here, but NetApp’s SnapManager for SQL Server product is one of those, how shall I say, onerous applications.  If you find yourself running into issues with the tool or are exploring the possibilities of adopting it in your environment, read on as I go over some of its idiosyncrasies.

Truncating Tables Without Elevated Rights

A common request from users is to receive “Truncate Table Rights” within a database.  Because a truncate statement is a DDL command, this isn’t nearly as easy to fulfill as most people would expect.  Faced with this challenge, a DBA might even find themselves choosing between elevating the user’s rights (e.g. including them into the db_ddladmin or db_owner role) or being a bad guy and flat-out denying the request.  Neither option is a win for both parties, so thankfully (in SQL 2005 and later) there is another solution.

Eliminating Static Output Logs

Using Tokens to Create Dynamically Named Job Step Output Logs

There are situations that make me think I need to find and enroll in a “Making Better Google Searches” class.  After some brief digging into Ola Halengren’s Maintenance Solution I inadvertently came across the way of creating log file names dynamically for job step output.  While this may not sound all that wonderful, I’ve asked a lot of people how to do this over the years (including MS support during various incident resolutions) and no one has been able to tell me what it took 2 minutes of digging into Ola’s solution to find.  I think the problem isn’t the folks I asked, but how I asked the question.  Anyway, read on and I will show you the way to create dynamic output log names within a Job Step’s Advanced Tab.

Bucking a “Best Practice”

Why avoiding the “Change port 1433 Best Practice” might be the “Better Practice” to follow

A lot of quality resources in the DBA community (e.g. MS, Jonathan Kehayias, Green SQL, etc.) state that changing a default instance’s port to something other than 1433 is a security “Best Practice.”  While I may agree that you want to avoid using port 1433, I contend there are better ways to go about it.