Saturday, September 28, 2013

Virtual Machines, Snapshots and Domain Membership

I have a number of virtual machines that I use for QA. All of these machines have snapshots for easy rollback and all are members in an Active Directory (AD) domain. One of my goals has been to allow all the users in the domain to have administrative rights for these virtual machines, but getting that working has encountered a few hiccups.

Every once in a while, the domain membership breaks. When trying to log in with a domain login through RDP, I get an authentication error, specifically: The Local Security Authority cannot be contacted. When trying to log into the machine directly (through the vSphere Client) using the same domain login, I get a different a different - but also vague error: The trust relationship between this workstation and the primary domain failed.

The short and recommended answer is to rejoin the machine to the domain. This can be done in several creative ways in addition to Microsoft's recommendation, but it doesn't really solve the problem. Microsoft's article does give me a little more insight, though. "For Windows 2000 or Windows XP, the default computer account password change period is every 30 days." The article was written many, many moons ago, but this behavior hasn't changed with newer versions of Windows.

Roughly translated, every 30 days or so, a Windows machine that is part of a domain, renegotiates its connection to the domain. Basically, Windows changes its "machine password" on the domain controller. This new password is only known by the domain controller(s) and the machine. What this means for me is that every time I rollback one of my QA virtual machines, Windows loses its new password and goes back to using its previous password - the one created before the snapshot.

Considering these machines are going to be rolled back to a snapshot often, having to rejoin them to the domain after every rollback is not a viable option. A better solution would be to stop the trust from breaking in the first place. This is actually something I can accomplish pretty quickly thanks to the article Machine Account Password Process from the Ask the Directory Services Team blog.

Unlike Windows user passwords, machine passwords don't expire; they are entirely client driven - meaning the Windows machine itself decides whether to change the password or not. So, as long as no one messes up the Computer account in AD, as long as the machine doesn't change its machine password, we are good to go.

Thanks to that article, there are a couple of ways I can accomplish this, both of them through GPOs.


If you haven't read it and like knowing nitty-gritty details, I highly recommend reading the Machine Account Password Process article.

Friday, September 27, 2013

SQL Server 2012 Integration Services and DCOM Permissions

On my development machine, I noticed I was getting the same DistributedCOM error 28 times every 15 minutes.
The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {FDC3723D-1588-4BA3-92D4-42C430735D7D} to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20) from address LocalHost (Using LRPC). This security permission can be modified using the Component Services administrative tool.
I'm not sure it is possible for this error to be any more cryptic. Luckily, I know a little about DCOM permissions. Roughly translated, there is a process calling a particular COM+ server (also known as a component). When that happens, the process fails to create/initialize the component because of a permissions error.

Debugging the Error

Based on the error, I know a couple of things. I know the process is a Windows service. Only Windows services can run under the Network Service (also known as "NT Authority\Network Service") user. I also know the GUID listed is the CLSID (also known as the Class ID) for the component.

Now, all COM security is configured from a little utility named dcomcnfg.exe. More specifically, the permissions I need to deal with are handled under the "DCOM Config" branch of the tree displayed in dcomcnfg.
Console Root -> Component Services -> Computers -> My Computer -> DCOM Config

So, a quick look at the listed components; and the CLSID can't be found. That's okay. Components can be listed in a variety of ways. The CLSID is only used as a last ditch effort if a more readable name is not stored for the component. To find the name for the component, I need to look it up in the registry under HKCR\CLSID, which gives me something much more useful.


Now I know that the COM+ server is named Microsoft.SqlServer.Dts.Server.DtsServer, and I know that this is definitely related to SQL Server - Data Transformation Services by the name. Switching back to dcomcnfg, the Microsoft.SqlServer.Dts.Server.DtsServer component is not listed either.

What else can the component be named? The registry key also displays an AppID GUID in addition to the component name. This represents the name of the process that hosts the component. Similarly to a component name, I can look up the application name in the registry under HKCR\AppID.


The process listed here is a Windows Service named MsDtsServer110, which is the short name for the SQL Server Integration Services 11.0 service. Switching back to dcomcnfg, the SQL Server Integration Services 11.0 component is listed. Perfect. Now all I have to do is configure it.

Fixing the Permissions

The steps listed here are very specific to my error but can easily be adapted to whatever component you need by replacing SQL Server Integration Services 11.0 with the appropriate application name, AppID or CLSID and replacing Network Service with the appropriate Windows user.
  1. In dcomcnfg, right-click on SQL Server Integration Services 11.0 and click on Properties.


  2. Change to the Security tab.
  3. Under Launch and Activation Permissions select Customize.


  4. Click [Edit] to display the Launch and Activation Permissions window.


  5. Click [Add] to open the Select Users or Groups window.


  6. Enter Network Service.
  7. Click [Check Names].
  8. Click [OK] to close the Select Users or Groups window.


  9. Select NETWORK SERVICE in the Group or user names list.
  10. Check the Local Launch and Local Activation permissions.
  11. Click [OK] to close the Launch and Activation Permissions window.
The error should now be resolved. For me, all I have to do is wait 15 minutes to see if the error is logged again. If your error can be duplicated another way, repeat those steps to see if you are still getting the error.

How did I get here?

Perhaps more important than all of this is, "why did I get this error in the first place?" The answer to that, in my case, is that I installed SQL Server 2012 and selected Network Service as the logon account for all the Windows services created by the installer. This is contrary to recommended practices, especially since the SQL Server 2012 installer has the ability to configure Windows accounts specifically for use by the Windows services it creates.

The short answer is that I shot myself in the foot because I didn't follow recommended security practices for the installation of SQL Server. Still, I would have thought that DCOM permissions would have been automatically configured for these components upon installation.

Regardless, it was relatively easy to fix, as long as I knew what DCOM was and how to deal with it.

Update (2013-09-28): Found another useful article for Troubleshooting DCOM and thought I would share.

Saturday, September 21, 2013

Encrypted File System on Windows 7 Home Premium

While Windows 7 Home Premium edition doesn't support EFS, you can get it working in a roundabout way - given you have temporary access to a copy of Windows that does have support for EFS. I found this out while using an external hard drive when passing files between my desktop (Windows Vista Ultimate) and my laptop (Windows 7 Home Premium).

  1. On the machine which does support EFS, go ahead and set up an encryption certificate.
  2. Export the encryption certificate, copy it over to the Windows 7 machine and install it.
  3. Create a folder and mark it as encrypted on the first machine.
  4. Copy the folder onto a USB drive formatted with NTFS.
  5. Finally, copy the folder from the USB drive onto the Windows 7 machine.
Any files created under (or copied to) the encrypted folder will be encrypted, themselves. In addition, this folder can be used as a template for creating additional encrypted folders. So, keeping an empty copy of the folder lying around can be useful.


There are a couple caveats to deal with, though.

  • The hard drives on both machines and the USB drive must be formatted with NTFS.
  • New folders on the Windows 7 machine cannot be marked as encrypted, unless they are children of folders already encrypted.
  • Once a folder or file has been encrypted, it cannot be marked as decrypted on the Home Premium machine.
  • Folders shared on the network cannot be decrypted - regardless of whether the certificate & key are installed on the client machine. This is why a USB drive is required.
I realize this seems a long way to go just to get EFS onto a weaker copy of Windows 7. To be honest, I don't use EFS on my laptop extensively; but the files I keep encrypted, I want them to stay encrypted, regardless of which machine they are stored.

Sunday, June 02, 2013

SQL Server 2012 Install Fails on Server Core 2008 R2 VHD - Object reference not set to an instance of an object.

So, I'm going through the Administering Microsoft SQL Server 2012 Databases book for the 70-462 certification exam. The only way, I could quickly get access to Windows Server Core for the test exercises was to download the VHD provided by Microsoft.

Well, strange thing. It is missing a critical registry key. Without this key, SQL Server fails with a horribly vague error. To make it more fun, while the description tells me to look in a file named summary.txt, there is no file named summary.txt anywhere.
The following error occurred:
Object reference not set to an instance of an object.

Error result: -2147467261
Result facility code: 0
Result error code: 16387

Please review the summary.txt log for further details
Slightly more - but also unhelpful - information can be found in the Component Updater log file generated by the installer.
Exception summary:
The following is an exception stack listing the exceptions in outermost to innermost order
Inner exceptions are being indented

Exception type: System.NullReferenceException
    Message: 
        Object reference not set to an instance of an object.
    Data: 
      DisableWatson = true
    Stack: 
        at Microsoft.SqlServer.Configuration.MsiExtension.ArpRegKey.CleanupPatchedProductRegistryInfo()
        at Microsoft.SqlServer.Configuration.MsiExtension.SetPatchInstallStateAction.ExecuteAction(String actionId)
        at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
        at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun, ServiceContainer context)
Anyway, after a couple of hours of messing around with it, I find a post on the Microsoft Forums that finally sheds some light on it.  Apparently the HKLM\Software\Microsoft\Windows NT\CurrentVersion\Uninstall registry key doesn't exist in the distributed virtual hard drive. Either adding this key or running an installer that creates it seems to fix the problem.

Hopefully, this will save others some debugging time and effort.

Friday, May 31, 2013

Configuring DEP on Windows Server 2008 R2 from a 32bit NSIS Installer - Revisited

Thanks to another blog, The Old New Thing, I found out there is another way to handle writing to the 64bit Registry from a 32bit program - without the needing to deal with file redirection.  Well, almost without the need for it. Starting with Windows Vista, a special alias %windir%\Sysnative was added to allow access to the System32 directory even when running a 32bit program.

Unfortunately, Windows XP and Windows Server 2003 don't have this feature; and since we still work with both of these versions of Windows, I cannot incorporate it into our code.  Anyway, since the original post is one of my more popular, I thought I would update the code.
var /GLOBAL NSISRegPath
StrCpy $NSISRegPath "SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers"

var /GLOBAL EXERegPath
StrCpy $EXERegPath "HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers"

${IF} ${AtLeastWinVista}
    WriteRegStr HKLM "$NSISRegPath" "$INSTDIR\program.exe" "DisableNXShowUI"
    ${IF} ${RunningX64}
        ExecWait '$WINDIR\SysNative\reg.exe add "$EXERegPath" /v \
            "$INSTDIR\program.exe" /d "DisableNXShowUI"'
    ${ENDIF}
${ENDIF}

Note: Lines that end with a backslash represent long lines that have been wrapped.

Thursday, May 30, 2013

How To Encrypt SQL Server Connections - Part 2

In my previous post, I covered the steps necessary to enable and enforce encryption for all connections to Microsoft SQL Server. Now, this was nothing more than a step-by-step of what to do. It covered nothing about why or how important this is. Hopefully, I can impress upon the reader how important it is to encrypt connections. Considering the effort involved is extremely minimal - less than an hour of work - this should be the first step after installing SQL Server.

How Vulnerable is your Data?

Just to prove how easy it is to get access to the underlying data, I installed Wireshark on my dev machine (actually, I already had it installed - it's a great tool!), started it up and entered the filter "tds" (all lower case).  This shows me all the SQL statements going out of my machine and all the responses that come back.  While the responses are still TDS encoded, it isn't difficult to parse out the data - especially strings - just by looking at it.  This can give me access to a boatload of information.



The only thing SQL Server really tries to protect is SQL authentication. Starting with SQL Server 2005, the authentication process attempts an SSL handshake before authentication occurs. For the purposes of this post, I am assuming the client supports SSL authentication and the handshake is successful. Please note that this is very different than Extended Protection for Authentication, which is an additional layer on top of SSL encryption. Also, the encryption described here is limited to authentication. After authentication succeeds, the SSL channel is shut down and the connection reverts to an unencrypted state.

Anyway, when connecting to a SQL Server that isn't configured to use SSL, the server will search for an appropriate server authentication certificate (with accessible private key and matching the NetBIOS name or FQN ) in the Windows server certificate store and use that. If one is not found, a self-signed certificate is generated on-the-fly and is used specifically to protect the authentication portion of the connection. Any subsequent SQL statements or RPC calls are left unencrypted - and so are the results that are sent back.

Note: SQL Server actually checks for certificates at startup. If a certificate is added or becomes accessible after startup, SQL Server will not use it. Related, if a certificate is removed or becomes inaccessible after startup, SQL Server gracefully falls back to using a self-signed certificate where necessary.

Configuring SQL Server to Require Encryption

If you haven't read it yet, please, please, please, read my previous post. Getting SQL Server configured to require encryption is extremely simple. You can even use a self-signed certificate if need be. As long as connections to your SQL Server are unencrypted your data is vulnerable to anyone with a packet sniffer and access to your network.

Configuring a Data Source to Require Encryption

Configuring a Data Source to require encryption on the client side is also easy. Both the SQL Server driver and the SQL Server Native Client driver in the ODBC Data Source Administrator provides a checkbox for enabling encryption. On the fourth page of the wizard, simply check the "Use string encryption for data" setting and save the Data Source. All connections using that Data Source will now require encryption and perform client side validation of the server's certificate.

Note: This only tells the client to require encryption.  It does not enable encryption, something which must be done on the server.

SQL Server driver wizard

SQL Server Native Client driver wizard

What Validation Checks Occur on the Client Side?

Since validation occurs (I'm assuming) via WinVerifyTrust, all the bells and whistles that come with certificate validation in Windows occur. Although, there are a couple of small differences in how the SQL Server driver and the SQL Server Native Client driver handle client side validation. The SQL Server Native Client driver includes more detailed descriptions of any SSL errors, while the SQL Server driver simply returns a generic SSL failure error. In addition, the SQL Server Native Client driver includes a principal name check, which compares the server name in the Data Source (excluding the SQL Server instance name) against the common name in the server's certificate. 

For example, if the SQL Server is using a certificate with a FQN but a Data Source only specifies the NetBIOS name, the validation will fail.  So, sqlserver\myinstance will fail if the server's certificate has a common name of sqlserver.domain.com, but sqlserver.domain.com\myinstance will succeed. The same goes for specifying the IP address instead of a server name.

Digging a little deeper into the validation check and how WinVerifyTrust works, the certificate must be signed by a trusted root certificate or have intermediary certificates that are signed by a trusted root certificate. The certificate can be purchased from any Certificate Authority, can be created from an internal Windows Server Certificate Authority, can be created from an internal OpenSSL Certificate Authority (as long as the certificate authority roots are distributed to all Windows machines), or even - and this is a little wacky - a self-signed certificate (as long as the certificate is distributed as a trusted certificate - not necessarily trusted root certificate - to all Windows machines).

Yes.  Even a self-signed certificate will work. As long as the client machines trust the certificate, client validation will pass. If you don't enable encryption on the client side, the certificate doesn't even have to be trusted on the client machines.

Given that any SQL Server should only be internally accessible and client side validation is not required, there is not really even a reason to purchase an SSL certificate. Any certificate will do, as long as it fits the requirements for SQL Server.

Notes

Use Cases

For the purposes of this discussion, I have included a list below of all the applicable scenarios relating to client and server encryption settings.  Hopefully, this gives an idea of the underlying functionality and answer some common questions.
  • Client does not require encryption and server is configured to use a specific certificate but not require SSL. Both the SQL Server driver and the SQL Server Native Client driver will work without errors. The connection will be unencrypted, although the authentication process will use the server's certificate for encrypting the authentication handshake. Certificate validation is completely skipped on the client side.
  • Client does not require encryption and server is configured to require SSL. Both the SQL Server driver and the SQL Server Native Client driver will work without errors. The connection - authentication and otherwise - is encrypted using the server's certificate. Certificate validation is completely skipped on the client side.
  • Client requires encryption, but server doesn't have access to a certificate & key.  Both the SQL Server driver and the SQL Server Native Client driver will generate SSL errors and abort the connection. Since the client is demanding an SSL connection and the server only has a self-signed certificate it generated on-the-fly, the certificate validation fails.
  • Client requires encryption, but server is not configured to use SSL - although it has access to a certificate and key. Both the SQL Server driver and the SQL Server Native Client driver will work without errors (if the certificate passes validation checks on the client side). Since the client is demanding an SSL connection, the server does its best to find and use one. If more than one server authentication certificate and private key are accessible, you have no guarantee which certificate it will use.
  • Client requires encryption and server is configured to use SSL but not require it. Both the SQL Server driver and the SQL Server Native Client driver will work without errors (if the certificate passes validation checks on the client side). Since the client is demanding an SSL connection, all interaction is encrypted using the server's certificate. 
  • Client requires encryption and server is configured to require SSL. Both the SQL Server driver and the SQL Server Native Client driver will work without errors (if the certificate passes validation checks on the client side). Since the client is demanding an SSL connection, all interaction is encrypted using the server's certificate. 

Client Side Errors

During the course of my testing, there are three SSL errors that can occur on the client side.  Well, at least, three errors that I found when using the [Test Data Source...] button in the ODBC Data Source Administrator window.  There are probably many more. One from the SQL Server driver and two from the SQL Server Native Client driver. I have included screenshots below of each.


Generic SSL error from the SQL Server driver

Certificate Chain SSL error from the SQL Server Native Client driver

Principal Name SSL error from the SQL Server Native Client driver

Friday, April 19, 2013

How To Encrypt SQL Server Connections

Open the Logical Certificate Store for the Local Machine

  1. Start the Microsoft Management Console (mmc.exe)
  2. Under the File menu select Add/Remove Snap-in… to launch the Add or Remove Snap-ins window.
  3. Select Certificates from the list of Available Snap-ins.
  4. Click [Add >] to launch the Certificates snap-in configuration window.
  5. Select Computer account
  6. Click [Next]
  7. Select Local computer
  8. Click [Finish] to close the Certificates snap-in configuration window.
  9. Click [OK] to close the Add or Remove Snap-ins window.

Create a Certificate Request

  1. In the Microsoft Management Console, select Certificates (Local Computer) on the left side of the window. This will display a list of certificate categories.
  2. Right-click on the Personal on the right side of the window.
  3. Click on Create custom request under Add Tasks -> Advanced Operations to launch the Certificate Enrollment wizard.
  4. Click [Next] to go to the Certificate request page
  5. From the Template dropdown select (No template) Legacy key and leave the Request format as PKCS #10.
  6. Click [Next] to go to the Certificate information page.
  7. Expand the request by clicking on the clip_image004 button on the right side of the window.
  8. Click [Properties] to launch the Certificate Properties window.
  9. On the General tab, in the Friendly Name field, enter SSL Certificate for SQL Server. The friendly name can actually be anything, but it should be easily distinguishable as the certificate for the SQL Server.
  10. On the Subject tab, add the Common Name attribute with the actual name of the server. If the server is part of a Windows domain, it must be the fully qualified name of the server including the domain. If the server is not part of a Windows domain, it must be the NetBios name of the server.
  11. Also on the Subject tab, add any other attributes required by your Certificate Authority
  12. On the Private Key tab, change the Key Type to Exchange.
  13. Expand the Key options group on the Private Key tab and change the Key size to 2048.
  14. Expand the Key permissions group on the Private Key tab, check Use custom permissions.
  15. Click [Set permissions] to open the Permissions window.
  16. From here add the start up account for SQL Server service. This is the “Log on as” Windows account used by the SQL Server service. If necessary, this can be configured later.
  17. Click [OK] to close the Permissions window.
  18. Click [OK] to close the Certificate Properties window.
  19. Click [Next] in the Certificate Enrollment wizard to go to the Export page of the wizard.
  20. Enter (or browse for) a File Name for the certificate request export.
  21. Click [Finish].
Once complete, a Certificate Request file will be generated and can be turned into a Certificate by any certificate authority.

Generate Signed Certificate

<<insert magic here>>

I leave it up to the reader to decide how to generate a signed certificate.  Whether using a 3rd party certificate authority, a Windows certificate authority in Active Directory, or generating a self-signed certificate, all will work.  And, YES, a self-signed certificate will work - at least, at this stage in the game.  While the SQL Server can require encrypted connections, it is up to the client to decide whether certificate validations occur. This will be covered later in Part 2.


Import the Signed Certificate

  1. In the Microsoft Management Console, select Certificates (Local Computer) on the left side of the window.
  2. Right-click on the Personal on the right side of the window.
  3. Click on Import under All Tasks to launch the Certificate Import Wizard.
  4. Click [Next].
  5. Enter (or browse for) the signed certificate file generated by the certificate authority.
  6. Click [Next].
  7. Click [Next].
  8. Click [Finish] to close the Certificate Import Wizard.

Configure SQL Server to Use Encrypted Connections

  1. Start the SQL Server Configuration Manager.
  2. Expand SQL Server Network Configuration.
  3. Right-click on Protocols for MSSQLSERVER. If SQL Server is installed as an instance, MSSQLSERVER will actually be the name of the instance.
  4. From the context menu, click Properties to launch the Protocols for MSSQLSERVER Properties window.
  5. On the Flags tab, select Force Encryption and change the value to Yes.
  6. On the Certificate tab, select the SSL Certificate for SQL Server certificate from the dropdown. The certificates listed here will be listed by the Friendly Name on the certificate. If not specified, the Common Name will be listed instead.
  7. Click [OK] to close the Protocols for MSSQLSERVER Properties window.
  8. In the SQL Server Configuration Manager window, select SQL Server Services.
  9. Right-click on SQL Server (MSSQLSERVER) on the right side of the window.
  10. From the context menu, click Restart.

Notes


Minimum SSL Certificate Requirements

In some cases, the certificate may not appear in the SQL Server Configuration Manager window. Below are the absolute minimum requirements for a certificate to show in the window and for it to work with SQL Server.
  • The Template used must be (No template) Legacy key. This allows the Key Type to be changed.
  • Private key must have a Key Type of Exchange.
  • The Common Name (or Issued To) attribute must be the same as the server name. If part of a domain, this will be the fully qualified domain name of the machine. If not part of a domain, it will simply be the NetBios name of the machine.
  • The Enhanced Key Usage for the certificate must allow for Server Authentication (1.3.6.1.5.5.7.3.1).
  • The certificate & private key must be stored in the logical certificate store for the local computer.
  • The private key must be accessible to the Log On as Windows account for the SQL Server service. Even if inaccessible, the certificate will display, but the service will fail to start. 

SQL Server Fails to Restart

If permissions are not properly configured, the exception 0x8009030d may occur during SQL Server startup and be logged in the SQL Server ERRORLOG file. Because SQL Server has been configured to require encrypted connections, this will prevent the SQL Server service to start. The full text of the error will be similar to the following:
The server could not load the certificate it needs to initiate an SSL connection. It returned the following error: 0x8009030d. Check certificates to make sure they are valid.
To resolve the issue, modify the permissions for the certificates private key as covered in Configure Private Key Permissions. Alternately, reconfigure the SQL Server to use unencrypted connections as covered in Configure SQL Server to Use Unencrypted Connections.

Configure Private Key Permissions

If the SQL Server fails to start, check the permissions to the certificate’s private key.
  1. In the Microsoft Management Console, select Certificates (Local Computer) on the left side of the window.
  2. Double-click on the Personal category on the right side of the window.
  3. Double-click on Certificates on the right side of the window.
  4. Right-click on the certificate and click Manage Private Key…under All Tasks. This will open the Permissions window for the certificate’s private key.
  5. From here add the start up account for SQL Server service. This is the “Log on as” Windows account used by the SQL Server service.
  6. Click [OK] to close the permissions window.

Configure SQL Server to Use Unencrypted Connections

If all else fails, reverting to unencrypted connections may be the only way to restore access to the SQL Server.
  1. Start the SQL Server Configuration Manager.
  2. Expand SQL Server Network Configuration.
  3. Right-click on Protocols for MSSQLSERVER. If SQL Server is installed as an instance, MSSQLSERVER will actually be the name of the instance.
  4. From the context menu, click Properties to launch the Protocols for MSSQLSERVER Properties window.
  5. On the Flags tab, select Force Encryption and change the value to No.
  6. On the Certificate tab, click [Clear].
  7. Click [OK] to close the Protocols for MSSQLSERVER Properties window.
  8. In the SQL Server Configuration Manager window, select SQL Server Services.
  9. Right-click on SQL Server (MSSQLSERVER) on the right side of the window.
  10. From the context menu, click Restart.