Blog Archives

Verdasys Digital Guardian and SQL Server


I’m writing this post as a reminder for myself and possibly to help out the poor souls that may suffer the same fate as me.

There’s a software out there called “Digital Guardian” which is a data loss protection tool. Your computer may be running this software without you knowing: your system administrators may have installed it in order to prevent users from performing operations that don’t comply to corporate policies and may lead to data loss incidents.

For instance, Digital Guardian can prevent users from writing to USB pendrives and walk out of the office with a copy of the data in their pocket. Actually, this is just one of the policies than can be enforced by Digital Guardian: it’s a complete data protection framework that offers many powerful features.

The bad news is Digital Guardian relies on an agent daemon that runs very deep in the operating system and modifies the OS behaviour based on the policies defined by the system administrators. Most of the time, the user is notified of the tool’s intervention with explicit messages, stating that the operation is not permitted by corporate policies.

Sometimes (here comes the painful part) things randomly fail without any meaningful indication that Digital Guardian is responsible of the failure. Instead of getting sensible policy violation messages, you may get generic error messages that won’t be anywhere easy to troubleshoot. Sometimes, errors are not even due to policy violations, but are caused by the modifications in  the OS behaviour introduced by Digital Guardian itself.

For instance, when installing SQL Server, you may be presented this error message:

DG_setup_error

Is the error message “No more data is available” anywhere helpful? Not really.

I spent countless hours trying to understand what went wrong and I finally understood the cause of the failure when a coworker pointed out that Digital Guardian was running on that particular server.

What happened here?

Digital Guardian clumsily tries to hide itself. If you look for it in the installed programs applet in Control Panel you won’t find it. It also tries to hide itself in the registry, so when you enumerate the registry key “HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Digital Guardian Agent” you will get an error.

In one of the early stages, SQL Server’s setup verifies what software is installed in the machine and when it encounters Digital Guardian’s registry key, it fails miserably.

The only way to get past the error is to disable Digital Guardian.

Are you comfortable with running SQL Server on a machine with such a tool installed?

OK, you managed to install SQL Server by disabling Digital Guardian: now what?

  • What if SQL Server crashes?
  • What if everything turns horribly slow?
  • What if you get data corruption?
  • What if…?

Tools that interact with the OS at such low level scare the hell out of me. Anything that you install and run on a machine with such a tool becomes completely unreliable in my opinion. SQL Server was not intended to run against a modified OS and it was not tested to run like that.

SQL Server has its own security tools. They may not be perfect, but it’s how the product was intended to work and, frankly, they’re largely sufficient for 99% of the use cases. Probably, enabling TDE is better than preventing everyone from writing to USB drives.

If you think SQL Server security features are not enough for you, go on and activate one of those pesky tools. But let me ask: are you sure that you fall in that 1% ?

Check SQL Server logins with weak password


SQL Server logins can implement the same password policies found in Active Directory to make sure that strong passwords are being used.

Unfortunately, especially for servers upgraded from previous versions, the password policies are often disabled and some logins have very weak passwords.

In particular, some logins could have the password set as equal to the login name, which would by one of the first things I would try to hack a server.

Are you sure none of your logins has such a poor password?

PowerShell to the rescue!

try {
    if((Get-PSSnapin -Name SQlServerCmdletSnapin100 -ErrorAction SilentlyContinue) -eq $null){
        Add-PSSnapin SQlServerCmdletSnapin100
    }
}
catch {
    Write-Error "This script requires the SQLServerCmdletSnapIn100 snapin"
    exit
}

cls

# Query server names from your Central Management Server
$qry = "
SELECT server_name
FROM msdb.dbo.sysmanagement_shared_registered_servers
"

$servers = Invoke-Sqlcmd -Query $qry -ServerInstance "YourCMSServerGoesHere"

# Extract SQL Server logins
# Why syslogins and not sys.server_principals?
# Believe it or not, I still support a couple of SQL Server 2000
$qry_logins = "
SELECT loginname, sysadmin
FROM syslogins
WHERE isntname = 0
AND loginname NOT LIKE '##%##'
"

$dangerous_logins = @()

$servers | % {
    $currentServer = $_.server_name
    $logins = Invoke-Sqlcmd -Query $qry_logins -ServerInstance $currentServer

    $logins | % {

        $currentLogin = $_.loginname
        $isSysAdmin = $_.sysadmin

        try {
            # Attempt logging in with login = password
            $one = Invoke-Sqlcmd -Query "SELECT 1" -ServerInstance $currentServer -Username $currentLogin -Password $currentLogin -ErrorAction Stop
            # OMG! Login successful
            # Add the login to $dangerous_logins
            $info = @{}
            $info.LoginName = $currentLogin
            $info.Sysadmin = $isSysAdmin
            $info.ServerName = $currentServer
            $loginInfo = New-Object -TypeName PsObject -Property $info
            $dangerous_logins += $loginInfo
        }
        catch {
            # If the login attempt fails, don't add the login to $dangerous_logins
        }

    }
}

#display dangerous logins
$dangerous_logins

Do you need sysadmin rights to backup a database?


backupLooks like a silly question, doesn’t it? – Well, you would be surprised to know it’s not.

Obviously, you don’t need to be a sysadmin to simply issue a BACKUP statement. If you look up the BACKUP statement on BOL you’ll see in the “Security” section that

BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles.

But there’s more to it than just permissions on the database itself: in order to complete successfully, the backup device must be accessible:

[…] SQL Server must be able to read and write to the device; the account under which the SQL Server service runs must have write permissions. […]

While this statement sound sensible or even obvious when talking about file system devices, with other types of device it’s less obvious what “permissions” means. With other types of device I mean tapes and Virtual Backup Devices. Since probably nobody uses tapes directly anymore, basically I’m referring to Virtual Backup Devices.

VDI (Virtual Backup device Interface) is the standard API intended for use by third-party backup software vendors to perform backup operations. Basically, it allows an application to act as a storage device.

The VDI specification is available here (you just need the vbackup.chm help file contained in the self-extracting archive).

If you download and browse the documentation, under the “Security” topic, you will find a worrying statement:

The server connection for SQL Server that is used to issue the BACKUP or RESTORE commands must be logged in with the sysadmin fixed server role.

Wait, … what???!?!??!! Sysadmin???????

Sad but true, sysadmin is the only way to let an application take backups using the VDI API. There is no individual permission you can grant: it’s sysadmin or nothing.

Since most third-party backup sofwares rely on the VDI API, this looks like a serious security flaw: every SQL Server instance around the world that uses third-party backup utilities has a special sysadmin login used by the backup tool, or, even worse, the tool runs under the sa login.

In my opinion, this is an unacceptable limitation and I would like to see a better implementation in a future version, so I filed a suggestion on Connect.

If you agree with me, feel free to upvote and comment it.

Windows authenticated sysadmin, the painless way


Personally, I hate having a dedicated administrative account, different from the one I normally use to log on to my laptop, read my email, write code and perform all the tasks that do not involve administering a server. A dedicated account means another password to remember, renew periodically and reset whenever I insist typing it wrong (happens quite frequently).

I hate it, but I know I cannot avoid having it. Each user should be granted just the bare minimum privileges he needs, without creating dangerous overlaps, which end up avoiding small annoyances at the price of huge security breaches.

When I was working as a developer only, I was used to having my windows account registered as sysadmin on my dev box and, when I switched to a full time DBA role, it took me a while to understand how important it was to have a different sysadmin user for the production servers.

That said, one of the things that makes the use of dedicated administrative accounts awkward and frustrating is windows authentication in SSMS. While extremely handy when the user that has to log on to the database is the same logged on to windows, integrated security becomes pesky and uncomfortable when the database user is a different one.

No big deal, but launching SSMS as different user brings in some small annoying issues:

  1. SSMS must be opened choosing “Run as…” from the context menu.
    It’s the most common way to run a program as a different user, but I would happily live without this additional step.
  2. The user’s credentials have to be typed in.
    OK, seems trivial, but I find it annoying. Typically, users with elevated privileges are subject to more stringent password policies, that means longer passwords, no dictionary words, symbols. Having to type such a password once a day is enough for me.
  3. No drag & drop from other windows: neither files, nor text
    This limit is imposed by windows, that filters the messages between processes in different security contexts.
  4. Whenever there is more than one instance of SSMS running, it’s impossible to predict which one will open a file on double click
    It’s like russian roulette. Want to play?
  5. Settings are stored separately. Each modification to program settings has to be made on both profiles.
    Application settings are stored somewhere under the user profile folder, or in the registry. In both cases, each user has different settings, stored in different locations.
  6. Save and load dialogs point to different folders
    By default, SSMS points to the user’s documents folder.

How to overcome these annoyances? A simple solution comes from a small tool released from Sysinternals in January 2010.

Desktops

There are dozens, maybe hundreds of applications that allow windows users to create virtual desktops, similar to those found in Linux, but Desktops is different. To say it by Mark Russinvich’s (blog|twitter) words:

Unlike other virtual desktop utilities that implement their desktops by showing the windows that are active on a desktop and hiding the rest, Sysinternals Desktops uses a Windows desktop object for each desktop. Application windows are bound to a desktop object when they are created, so Windows maintains the connection between windows and desktops and knows which ones to show when you switch a desktop.

In other words, Desktops is able to create a whole desktop process and then run new windows bound to that process. This also means that the main desktop process (explorer.exe) can be started in a different security context, simply terminating and restarting it. All the windows started from that moment on will be bound to their originating desktop process, hence to the same security context.

Let’s see how this can be achieved:

  1. Download and install Sysinternals Desktops
  2. Open task manager, find the process named “explorer.exe” and note down its PID
  3. Create a new desktop and activate it
  4. Open task manager, find and kill the explorer process that has a PID different from the one you noted down
  5. From task manager, start a new process: “runas /user:somedomain\someuser explorer.exe”

Done! A new explorer process will be started with the credentials you supplied.

Smooth, isn’t it? Well, not much, still too complex for me:

  • The PID from the original explorer process has to be noted down before creating the new desktop: thereafter it will be impossible to determine which desktop belongs to a process
  • By default, windows restarts automatically explorer whenever it is killed, making our efforts in vain.

In order to work around these problems, I coded a small C# application called RestartExplorer that identifies the explorer process bound to the current desktop and restarts it as a different user.

The code is straightforward and you will find it attached to this post. For those not so comfortable with Visual Studio, I also attached a compiled version.

The core functionality consists of just a few rows of code:

// Create a ProcessStartInfo object to pass credentials
System.Diagnostics.ProcessStartInfo psi = new System.Diagnostics.ProcessStartInfo();
if (!username.Equals(""))
{
    fixed (char* pChars = password.ToCharArray())
    {
        pass = new System.Security.SecureString(pChars, password.Length);
    }
    psi.UserName = user;
    psi.Password = pass;
    if(!domain.Equals(System.Environment.MachineName))
        psi.Domain = domain;
}

//Runs the Explorer process
psi.FileName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Windows), "explorer.exe"); // c:\windows\explorer.exe

//This has to be set to false when running as a different user
psi.UseShellExecute = false;
psi.ErrorDialog = true;
psi.LoadUserProfile = true;
psi.WorkingDirectory = "c:\\";

try
{
    //kill current explorer process
    IntPtr hWnd = FindWindow("Progman", null);
    PostMessage(hWnd, /*WM_QUIT*/ 0x12, 0, 0);
    //start a new explorer with the credentials supplied by the user
    System.Diagnostics.Process.Start(psi);
}
catch (Exception e)
{
    throw e;
}

Once run, the application simply asks for the credentials of the users that will run explorer.exe bound to the current desktop:

By clicking OK, the explorer process gets terminated and immediately restarted under the specified security context. This creates a brand new desktop process, entirely dedicated to our administrative account.

To switch back to the regular users’ desktop, you just have to press the hotkey combination you set up in Desktops’ control panel or click on the tray icon, thus implementing something very similar to windows’ “quick user switch”, but quicker and more versatile.

Desktops tray panel

Desktops control panel

So far we have solved our main issues:

  1. SSMS can be started normally, by double clicking its icon
  2. No credentials to type
  3. Drag & drop allowed from any window in this desktop
  4. SSMS opens the .sql files double clicked in this desktop

Sharing settings

We still have to find a way to share application settings between different windows accounts.

We spent hours and hours configuring SSMS with our favourite keyboard settings, templates and all the other things that make our lives easier: we don’t want to set up everything from scratch for our administrator user. Is there a way to share the same settings we set up for the regular user?

Of course there is, and, again, it comes from Sysinternals and it is named Junction.

Junction is a tool that allows creating symbolic links on the NTFS file system. The concept of symbolic links has been present for many years in UNIX operating systems: symlinks are anchors to files or folders residing on different paths in the file system, that are treated as if they were physical files or folders in the path they are linked in.

In order to share settings between two users, we could create a symbolic link from the administrator user’s profile folder to the regular user’s profile folder, as represented in this picture:

Unfortunately, the profile folder contains a special file, named NTUSER.dat (the user’s registry keys), that cannot be opened concurrently on a desktop operating system.
The only possible solution is linking each subfolder in the profile path:

A quick and easy way to accomplish this task is running this script:

Set fs = createObject(“Scripting.FileSystemObject”)
Set ws = createObject(“WScript.Shell”)

JunctionPath = “d:\Downloads\Junction\Junction.exe”

For each dir in fs.getFolder(“.”).subFolders
	DestPath = “..\AdminUser”
	If NOT Fs.folderExists(DestPath) Then
		Fs.createFolder(DestPath)
	End if
	If NOT Fs.folderExists(DestPath & “\” & dir.name) Then
		call ws.run(JunctionPath & “ “”” & DestPath & “\” & dir.name & “”” “”” & dir.path & “”””, 0, true)
	End if
Next
MsgBox “Profile linked successfully!”, vbInformation

Instructions:

  1. Copy the script code and save it as createJunction.vbs in the profile folder the links will point to (For instance, “c:\documents and settings\RegularUser”)
  2. Update “DestPath“ with the name of the user that will link the profile (For instance, “AdminUser”)
  3. Update “JunctionPath” with the path to Junction.exe
  4. Create a backup copy of the profile we will substitute with the links
  5. Delete all the folders in the admin profile, but keep all the files (especially NTUSER.dat)
  6. Go back to the folder where you saved the script and run it

For each subfolder in the regular user’s profile, the script will create a junction in the administrator user’s profile folder. This will not be directly visible from explorer: junctions are not different from real folders. Running DIR from the command prompt, instead, will reveal that we are dealing with something completely different:

WARNING!!  Symbolic links act exactly as normal folders: this means that deleting a folder that is instead a symbolic link, will delete the link’s target folder. In other words, deleting a file from AdminUser’s profile folder, will delete the file from RegularUser’s profile! Be careful!


What we achieved is a complete sharing of settings between our users. Going back to our original issue list:

  1. Settings are saved in the same path. Every change in the program settings will be automatically saved in the original profile.
  2. Open and save dialogs point to the same documents folder.

Mission accomplished!

However, registry settings will not be shared. There is a tool (RegLN) that allows creating symbolic links in the registry, but, personally, I don’t feel like exploring this possibility, that I find a bit dangerous.

In the end, SSMS settings are saved in the profile folder, which we already have shared. This is enough for me.

RESOURCES: