Ryan Adams Blog

SQL, Active Directory, Scripting

I’m finally back online after being on vacation for a couple of weeks following my second daughter’s birth.  If you follow me on Twitter or FaceBook you know that Emily Grace Adams was born 11/4/2009 @ 1:46pm.  She weighed 6lbs 9oz and was 18″ long.  She is very healthy and doing extremely well.  I can’t thank my wife Jennifer enough for the sacrifices she has made for our family.  She is an amazing and inspiring woman that I’m proud I can call my wife.

I guess it is now back to work and the real world, but at least the holidays are upon us.  Here our some pictures of our new daughter.

I discovered this little catch back when SQL 2005 released, but it took me awhile to notice it was not working.  One of the cool new things in the 2005 release was database mail.  Finally we didn’t have to setup mail profiles and install Outlook, which meant additional patching for Microsoft Office and possible reboots.  We also no longer had to use an extended stored procedure to send email alerts from the database.

The group I worked in used Microsoft Operations Manager to monitor our environment, which included much more than just our SQL servers.  One day I got an alert from MOM that one of my SQLAgent jobs failed on a job step.  However, I never got an email alert from SQL server that the job had failed.  I had already setup database mail and had even run my code in SSMS, which ran fine and sent email via msdb.dbo.sp_send_dbmail.  In fact, I had even recently received an email from that job and verified that kicking off the job manually resulted in success.  So I started wondering what could be the problem since I knew database mail was working and verified it both by executing code in SSMS and via SQLAgent job.  I also started wondering why I had not noticed this before when it dawned on me that my jobs were set to only send email in the event of a failure.  Well, I would like to think it was skill and not luck that I had not had any job failures up until then.  I’m glad we were using MOM or who knows when I would have noticed the job failures.  This server was a development server that rarely got paid any attention.

After some troubleshooting I started to search the internet to see if anyone else had run across this issue.  My hours of searching turned up nothing so I went back to the troubleshooting drawing board.  Keep in mind this was right after SQL 2005 released so there very well may be plenty of search results for it now.  What I discovered was that the SQLAgent itself has to be mail enabled and assigned to a database mail profile.  The fix was quite simple.  In SSMS right click SQL Server Agent and select properties.  In the left hand pane select the Alert System page.  Next we need to check off “Enable Mail Profile”.  Next we select the mail system from the drop down box and select Database Mail.  If you are still using SQLMail you can also select that, but I highly recommend you start migrating your jobs and any code over to Database Mail.  Lastly we select which mail profile setup in Database Mail that we want to use for sending job messages from SQL Server Agent.

SQLAgent Mail Config

Configuring DBMail for SQLAgent Alerts

If you have ever worked in a company with a split DNS name space or one that has gone through a merger, you have probably dealt with the DNS suffix search list.  This list allows a user to put “companyweburl” in their browser address and it actually resolve.  This works because Windows will cycle through the DNS suffix search list appending each suffix to what was typed into the address bar until it finds a site.  If the list contains contoso.com and litware.com then the OS will attempt companyweburl.contoso.com first.  If it gets a response from that site then off you go.  If it does not then it tries companyweburl.litware.com.

To set the suffix search list, open the properties of your network connection and then open the TCP/IP properties.  Now click Advanced and go to the DNS tab.  Here you can set your suffix list as shown.

DNS Suffix Search List

DNS Suffix Search List

There are two other places you can view your current suffix search list.  The first is by opening a command prompt and typing in “ipconfig /all”.  The second is in the registry and can be found in this location:

HKEY_LOCAL_MACHINESYSTEMCurrentControlSetservicesTcpipParameters

In a large environment making this change on all the clients can be daunting, but there are several solutions.  I won’t go into all the solutions, but VBScript or PowerShell scripts being deployed through domain group policy or user logons are good ones.  The most obvious solution is to just make the change by deploying a domain group policy.  This method has a catch in that it sets itself in a different registry key than if you set it locally through the GUI.  If a GPO pushes the setting then it takes precedence over the local configuration, but you can still set it in the GUI.  This makes you think you are changing it when you really are not.  This is a field engineer troubleshooting disaster.   The only good news is that an “ipconfig /all” will show what is actively being used.  Deploying this setting with a GPO puts its changes in the registry in the following location:

HKEY_LOCAL_MACHINESOFTWAREPoliciesMicrosoftWindows NTDNSClient

It should be noted that this setting is only available in GPOs where AD is running Windows Server 2003 or greater.  Here is the path where you can find the setting in a GPO.

Computer Configuration → Policies(If you’re running Windows Server 2008) → Administrative Templates → Network → DNS Client → DNS Suffix Search List

Here is an example.  A GPO has been deployed to Machine1 setting the DNS Suffix Search List to “contoso.com”.  On the top you can see that “litware.com” is the only suffix set in the GUI.  On the bottom you can see from an “ipconfig /all” that the suffix search list only has “contoso.com”

DNS Suffix Search List in the GUI

DNS Suffix Search List in the GUI

Actual Current DNS Suffix
Actual Current DNS Suffix

As you can imagine, there is bound to be a very frustrated desktop engineer.  Since the GPO and GUI put their settings in different registry keys you never see the GPO settings in the GUI.  For this reason I would warn against using this GPO setting.  I recommend the following work around solutions.

  • VBScript or PowerShell deployed via logon script
  • VBScript or PowerShell deployed via GPO startup or logon script
  • GPO using a direct registry change to the local GUI registry key

In part 1 we looked at how “Run As” works in Windows XP and in part 2 we looked at how it works in Windows Vista.  Now let’s see how it works in Windows 7.

 GUI

Vista Run As Context Menu

Vista Run As Context Menu

In Windows 7 you can hold down the shift key and right click ADUC to get the full context menu.  Here you will see two options called “Run as Administrator” and “Run as Different User”.  The “Run as Administrator” option makes use of the new User Account Control (UAC) security feature.  The good news is if you are logged in with a privileged account and simply run ADUC without any options, the application only has user access to the local machine.  This applies even if the account is in the local administrators group.  If you run it with the “Run As Administrator” option, UAC elevates the account by combining the second part of the SID with the first part of the SID (SID filtering).  The first part describes the regular user account and the second part describes the elevated privileges.  This works exactly as it did in Windows Vista.  This is Microsoft’s way of solving the issue of companies making every day knowledge workers administrators on their machine and reducing the attack surface.  If you select the Run as Administrator option, Windows 7 runs the application using the entire account SID so that the process (and only that process) now has local administrator permissions.

Although user account control was probably the most persecuted new feature after Windows Vista launched, it was actually a really good thing.  In my opinion it certainly has a place in the corporate world, but proved to be a daunting inconvenience in the home user world.  If you still think UAC is a bad thing and too inconvenient go google “linux +sudo”.  Microsoft is not the only one to implement such features.  Microsoft has gone to great lengths to keep this security feature in Windows 7, but greatly reduce the annoyance factor of multiple “Allow or Deny” prompts.

The problem is that “Run as Administrator” is a UAC function and should not be used as a “Run As” replacement.  Although you can set the “Run as Adminstrator” option to prompt for alternate credentials, that application will run as a local administrator and under the supplied alternate credential.  See part 2 of this series for more information.  What we really want is for the application to run under the locally logged on non-privileged account and only pass the alternate credentials to the remote application.  This where “Run as Different User” comes in.  This is the “Run As” replacement and uses the secondary logon service.  The issue here as that it works exactly as it did in Windows XP where it uses the supplied alternate credentials to run the local application. Once again we are left without a secure option in the GUI.

CLI

Executing “Run As” from the CLI is exactly the same in Windows 7 as it was in Windows Vista (see Part 2).  Again it requires elevating the account using UAC, so you might as well just use “Run As Administrator” from the GUI.

The sad part is that although the SysInternals add-on that gave us exactly what we needed in Vista installs in Win 7, it still acts like it did in XP.  The application will run under the supplied alternate credentials on the local box instead of just supplying them to the remote application.

Conclusion

My suggestion for the most secure option for each OS would be…

XP – Use “Run As” from the command line and supply the /netonly switch

Vista – Download the SysInternals add-on called Shellrunas.  Use it from the GUI and always select the one labeled “netonly”.

Windows 7 – Use the “Run As Different” user option from the GUI.  It is the worst option of the 3 but the only viable one.  Let’s cross our fingers that MS will put out another Shellrunas add-on through SysInternals.

PART 1

 

PART 2

In part 1 of this series we discussed how “Run As” does not work exactly as one might think.  Unless you use the CLI and supply the /netonly switch, the application runs locally under the supplied alternate credentials.  The behavior we desire only comes with the CLI and /netonly switch where the application runs under the locally logged in account.  The supplied alternate credentials are only used over the network with the remote application.  We specifically looked at these behaviors in Windows XP, so now let’s look at what Windows Vista brings to the table.

GUI

Run As Administrator Context Menu

Run As Administrator Context Menu

The first thing you will notice in Windows Vista is that you will always see the context menu shown on the left whether you hold down the shift key while right clicking or not.  That’s right.  “Run As” is no longer in the OS!  You will, however, notice an option called “Run As Administrator”.  Although it looks and sounds like the same thing as the old Run As in Windows XP, it is in fact something entirely new.  This option is part of a new security feature implemented in Windows Vista called UAC (User Account Control).  I will not be going into detail on UAC since that is out of scope for this series and deserves a series itself.  However for the sake of clarity, it allows a user to be in the local administrators group without running as an administrator for the entire logon session.  The user is essentially logged on as just a member of the user group.  It allows them to elevate to their administrator status for just the duration of completing a task that requires those privileges (SID filtering).

Using the “Run As Administrator” option simply elevates the account you are logged on with to have administrative permissions locally.  This means that there is no prompt for alternate credentials.  This option of UAC can be changed in the local security policy or via group policy by changing the “Behavior of the  elevation prompt for administrators in admin approval mode” option to “Prompt for Credentials”.  The net effect is the same as doing a simple Run As in Windows XP where the application runs locally and remotely under the supplied alternate credentials.  The only difference is that the application is little more secure running under UAC than it was under the secondary logon service of Windows XP.  Since Microsoft took away the GUI version (which remember did not work securely as we wanted it to in Part1 of the series under Windows XP), let’s see if the CLI still works.

CLI

After typing the command into the command prompt like we did in Part1 you will see this.

Run As from the command prompt

Run As from the command prompt

Please note that I am running this with ADUC (dsa.msc) and you will not get this error for all administrative tools and applications like “Computer Management”.  For whatever reason only some MMC snap-ins require administrative privileges on the local machine.  This means that I have to right click “Command Prompt” and select “Run As Administrator” and then type in my “Run As” CLI command.  Of course, you might as well just right click ADUC and select “Run As Administrator” and skip the command prompt altogether.

So are we without hope and doomed to run remote applications with lowered security?  Can we use a Microsoft product and adhere to Microsoft’s own best practice of least user privilege?  I worked with MS for over a year while Vista was in development to get a solution that many others had asked for and Microsoft agreed was needed.  In fact, I estimated that the company I work for would lose $423,000 a year in lost productivity not including the potential for loss from the lack of security.  (BTW…turns out my company didn’t care that I prevented them from incurred costs, they only care when you remove a cost they are already incurring).  Okay off my soap box.  The solution was an add-on tool MS released through SysInternals that not only returns the Windows XP version of “Run As”, but also gives you the option to use it from the GUI with the /netonly switch.  Now we have the perfect solution of the CLI /netonly switch but this time in the GUI.  This saves precious time for domain administrators, SQL DBAs using SSMS, and custom applications.  You can find the add-on for Vista here on the SysInternals web site called ShellRunas.

PART 3

Many companies follow the Microsoft best practice of “Least User Privilege”.  The idea is to provide a user or service the least amount of security privileges required to accomplish its task.  One of the most common tactics is using a seperate account for administrative tasks like application access or domain administration.  Using this tactic means that Domain Administrators do not logon to their workstations with their Domain Administrator credentials, where a piece of malware on that machine would inherit those all powerful permissions.  Instead they logon using a non-priviledged account.  So how do they perform administrative tasks without logging off and back on?  The answer is “Run As”, which utilizes the “Secondary Logon” service to run an application under alternate credentials.  However, you might be wondering if “Run As” works as advertised by mitigating this risk factor.  I will address that concern in this three part series, but the answer changes depending on whether you run it from the GUI or CLI and also what operating system you are using.  In this first part of the series let’s go look at how it works in Windows XP.

Windows XP

In Windows XP, if you want to run an application under alternate credentials you usually have to hold down the shift key while you right click on the application launcher.  Some application launchers do not require holding down the shift key for the “Run As” option to appear in the context menu.  Why some require it and some do not is still a mystery.  What we are trying to discover here is whether this option works as advertised from a security stand point.  Note: Make sure you do not have any MMC consoles open to avoid confusion when testing the below scenarios.

GUI

Test Scenario 1.  Go to Start→Programs→Administrative Tools and click Active Directory Users and Computers (hereafter referred to as ADUC).  Now right click the taskbar and open Task Manager. Click the processes tab and make sure to put a check mark in show processes from all users.  Scroll through the list and look for the “MMC” process.  You should see that it is running under the account you are logged in with.  This exercise verifies that ADUC is running under the credentials of the locally logged in user account as expected.  Let’s see what happens when we run it as an alternate user.

Test Scenario 2.  Close the ADUC MMC console.  Now let’s open it again, but this time hold down the shift key, right click ADUC, and select “Run As” from the context menu.  An authentication window will open, so enter an alternate user name and password.  Once ADUC opens, switch back to your Task Manager window or open a new one if you already closed it.  Look for the “MMC” process and you should see that it is running under the alternate account you provided and not the account you are logged in with.

At this point you are probably wondering when we will see something out of whack.  Everything is working as advertised, right?  Well if you stop and think about it, things are really not as good as they seem.  Assuming you used a domain administrator account, ADUC is now running with administrative privileges on both the domain and local workstation.  We now essentially have a gateway where malicious code on the workstation has unlimited permissions to the domain and domain code or scripts have access to the local machine.  By now I hope things are becoming a little more clear, but just in case it’s still a little muddy let’s play out this scenario.  You go to edit a GPO script, but don’t know that the script has been infected with a virus.  If ADUC had been running as a non-privileged user on your local machine then little or no damage might have occurred, but remember it is running under your alternate domain administrator account.  The virus now has access to your profile where it puts a script that goes out and starts deleting accounts from the domain.  Now we have a serious problem, and with this behaviour of “Run As” you might as well have logged in with your domain administrator account.  The good news is that there is switch you can throw when using the CLI that will change this behaviour.

CLI

The syntax for runas is:

runas /user:mydomainnamemyusername “Program EXE

Example 1:

runas /user:contoso.comjoe.blow “mmc dsa.msc”

If you run the above example providing your alternate credentials and check Task Manager to see what the process is running under, you will see that it is running as the account you supplied.  This is identical to the results from test scenario 2 for the GUI.  Close the ADUC MMC.

Example 2:

runas /user:contoso.comjoe.blow /netonly “mmc dsa.msc”

In this example if you check Task Manager to see what the process is running under, you will see that it is now running as the locally logged on user and NOT the supplied alternate credential.  The /netonly switch tells the secondary logon service to use the supplied credentials for remote access only.  Now we are cooking with fire and have a much more secure way to run remote applications with high privileged accounts.

The good news is that we now have a secure solution, but the downside is having to type all that in for every application every time we need to run it.  Of course we can always write a handy script to help out and in Windows Vista we could write a sidebar gadget (which I have done).  Windows Vista, however, adds a whole other caveat and we will be looking at that in the next article of this series.

PART 2

I finally decided to get on the blogging band wagon.  I started this blog for two reasons.  First and foremost is personal career development, and second is to keep an archive.  I want to send a special thanks to Brent Ozar (@BrentO on Twitter) for his excellent blog on how to get started blogging.  If you are thinking about starting your own blog I highly recommend you read his article HERE.