Month: February 2020

Doctor DBA in the Multiple Domains of Insanity

I don’t know about you but, very few companies I have worked for in the last 10 years have had only 1 domain for me to support. At the very least you might get 2 or 3 to separate out prod, uat and dev. The companies I have worked for though have been growth through acquisition. Rarely do the old domains ever go away. Sometimes you are lucky and they all trust each other and you only need 1 account to log onto each of those domains. Usually you have 1 or 2 that don’t have any trust and usually that means you have to RDP into the servers to do anything because you can’t use management studio from your local machine.

That is not strictly true, barring firewalls preventing you from getting to the servers I am going to show you how to trick SSMS into thinking it is someone else. If there is trust between domains and your account in the other domain is allowed to be used on your local machine, then that is easy you shift right click on your SSMS icon and do a run as different user.

Let’s be honest though when is it ever that easy. So the first thing we need is the command to use:

C:\Windows\System32\runas.exe /netonly /user:bz\bzugg “C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\Ssms.exe”

You can see we are doing a runas command but we are specifying the switch /netonly which according to the help for runas:

Next we give it the domain and the user account followed by the path and exe to run. Notice we are not storing the password, which is a glorious sacrifice to the Info Sec gods and brings them much happiness. So where to place this mystical command? I copy the shortcut to SSMS to my desktop that way I don’t mess up any of the standard shortcuts. The normal shortcut looks like this:

Once we have it copied to the desktop we can right click on it and go to properties.

Once we are in properties we are going to add this part to the being of what is already in the target. Be sure to leave a space after the user.

C:\Windows\System32\runas.exe /netonly /user:bz\bzugg

Once you have added the runas command then click ok. You will now see that the icon for this SSMS shortcut has changed.

And now when you run your edited shortcut it prompts you for your password.

Type your password for your account and hit enter. I will warn you that it may take longer to load and connect to servers but the trade off maybe worth it. For instance if you can’t copy and paste scripts via RDP or you have to pass it through several file-share servers to get it to all your target servers, this could be really helpful.

One other word of warning once you have connected it will look like you are connected with the account you have logged into your local machine with.

But if you run:

select distinct login_name from sys.dm_exec_sessions

You will see that you are actually connected with the account you specified in the runas command.

I hope this helps calm some of the insanity that torments the multiple domains that we all deal with.

The Case of the Automated DBA

If you are thinking the title of this blog post sounds like an Erle Stanley Gardner book, that is where the inspiration comes from. Gardner wrote the books about Perry Mason and his cases that were the basis for the Raymond Burr television series. Sometimes as DBAs we wish we had a Perry Mason type figure to defend us, but we rarely ever get that.

In this story we’ll pretend that we have a defense attorney, because the prosecution is making a to put us in jail for life. Maybe it isn’t quite that extreme but for some people it feels that way. The prosecutor claims that the defendant (DBAs) can’t keep the systems running, and performing. Additionally time and time again when they make changes they are done correctly and cause further impact. For all these charges the prosecution seeks the strictest punishment in the land, life in jail (AKA automate the DBA and fire the person).

I know many people out there have been in this position before, I have. At first I challenged the thought of automation. “How can they automate this?”, “They are going to automate me out of a job!”, and “No computer can do what I do!” were some of the thoughts and statements I have made. The turning point for me came when I started seeing all the things that I couldn’t get done. Things like patching SQL Server, planning and executing migrations to newer versions of SQL Server, and sleeping at night because I would get paged out for something that is a 5 second fix and as I said at 3am “A monkey could do this, why am I getting paged?”

So a show of hands how many people have been in that place before? It is time to be realistic about what automation can do to help us and dispel the fear of it replacing us.

So why should we automate?

  1. Consistency
  2. Remove Human Error
  3. Reduce alerts
  4. Reduce repetitive tasks
  5. Improve reliability
  6. Sleep easier

What should be automated?

  1. SQL Installs
  2. Patching
  3. Anything that triggers an Alert and is fixed with a simple script
  4. Deployments (The great white whale of the DBA)

I plan to dive into these topics in more depth over time. What I want to illustrate is that while the prosecution is going overboard asking to get rid of the DBA in favor of automation, DBAs are going overboard in refusing to consider it. We need to admit that there are things better suited to automation, and agree to a bargain with the prosecution that meets in the middle. In the end the goal should be letting the DBA focus on bigger issues and reducing how many times they get called at night.