8/16/04

This article, and any updates to it, will also be posted on my
discussion forum at:

http://www.maplebacon.com/sbbs/Threa...ThreadID=13183

This post describes issues concerning the implementation of SSL
certificates on SQL Server 2000 clusters. These notes are derived from
lessons learned while implementing SSL on a cluster. The
implementation ended up taking three weeks and involved four engineers
at Microsoft to resolve. There was at least 30 hours spent on the
phone with Microsoft support and eventually some of my issues had to
be resolved with a line-by-line source-code review by a senior
engineer at Microsoft. This is how we eventually discovered bug # 3
that is mentioned below. I wrote the bulk of this document in order to
document the issue for our network operations, but then realized that
this might be a good resource for the community. There seems to be
very little documentation, either officially from Microsoft or in the
community, on SSL in SQL Server, how it is implemented, and most
certainly on troubleshooting SQL SSL issues. I would assume that this
will get a lot better with SQL Server 2005 as, to me, it seems that
SSL support in SQL 2000 was bolted on as an after-thought. That being
said, through my MSDN account support incidents, Microsoft worked very
hard to resolve my problem. In the end they did, and to top it off,
they made the incident non-decrement so I didn't even have to burn an
incident to get it resolved. It is because of things like this, that,
despite there being problems that may arise, I continue to be highly
satisfied with MSFT products and support.

So, here goes:

After SQL Server is installed and operational on all nodes in the
cluster and failover is working, SSL can be installed. If SQL Server
isn't running properly on all nodes or there are other cluster issues
in existence, do not attempt to install SSL as it will only complicate
matters.

This document assumes that the cluster nodes are running Windows
Server 2003, that there is a DC running Windows Server 2003, that the
DC is running a certificate authority, and that the CA is configured
as an Enterprise CA Note: an Enterprise CA is different than needing
to run Windows Server 2003 Enterprise edition. An Enterprise CA can be
installed on Windows Server 2003 Standard Edition, though the
functionality of an Enterprise CA on Standard Edition is slightly
different than the functionality of an Enterprise CA on Enterprise
Edition. Enterprise CA simply means a CA that is AD-integrated as
opposed to a Stand-alone CA that is not AD-integrated. One of the
features of an Enterprise CA is that all domain members (servers,
workstations, etc.) will implicitly trust all certificates issued by
the CA. With a stand-alone CA, the CA root certificate must be
installed on each machine that will be trusting certificates issued by
the stand-alone CA. If your configuration varies from the above,
you'll need to adjust accordingly, but the main points and steps below
should still apply.

I describe below three bugs that you should be aware of that are
involved with SQL and SSL. All three of these bugs were discovered
while troubleshooting my situation with Microsoft support. KB articles
(and a bug fix on one of them) are coming on these issues at some
point, but they do not exist at the time of this writing.

BUG NOTICE I:

SQL server 2000 SP3A will not properly load a certificate that has
been configured with an email address as part of the certificate's
properties. When completing the certificate request leave the optional
email address property blank.

BUG NOTICE II:

SQL Server 2000 SP3A will not properly load a certificate that has
been loaded into the computer account's personal certificate store
(i.e. local machine store) unless the certificate was loaded into the
personal store under the account with which the SQL Server service
runs. This means that all certificate requests should be made while
logged in under the SQL Server service account and that the same
account should be used to load the certificate into the computer
account's personal store.

BUG NOTICE III:

SQL server 2000 SP3A has an issue where if the SQL Server cluster name
(not the name of the cluster itself) is longer in length than the
length of the SQL Server cluster IP address, then certificates will
not be loaded properly. For example, if the IP address of the SQL
Server cluster is 10.1.1.1 and the name of the SQL Server cluster is
MYSQLCLUSTER then the length of the name (12 characters) is longer
than the length of the IP address (8 characters) and SSL certificates
will not be properly loaded. There is a work-around to this issue
described later in the document. The next section assumes that the
name of the cluster is equal or shorter in length than the length of
the IP address. See the end of this article if you want a technical
description on why this bug exists.

SSL install steps:

Assuming you aren't subject to bug notice III above, the process of
installing SSL on a SQL server cluster is fairly simple. Ensure that
SQL server is not running. From each node in the cluster, you must log
in and prepare a certificate request. As noted above, you must log in
under the SQL Server service account when requesting and installing
the certificate. The easiest way to make the certificate request is to
connect to the CA through the web UI. In the web UI make an advanced
certificate request and use the Web Server template. (You can find
instructions on how to use the web UI for creating advanced
certificate requests all over the web.) In the name field of the
request, put the FQDN of the SQL Server cluster (i.e.
sqlclust.domain.com). As stated above, leave the email field blank.
Fill in the rest of the fields as desired, but do not use any
punctuation. Just letters, numbers, and spaces. Leave the key options
set to their defaults with the exception of being sure to check "Store
certificate in the local computer certificate store." Click submit and
when prompted to do so install the certificate by clicking on the
link. Repeat this process on each node in the cluster. Bring SQL
server up on each node to ensure that it starts properly. Do not
enable "Force Encryption" on the server at this point, even if that is
your ultimate desire. Go to the end of this article and read the
section on testing.

SSL installs steps with work-around for BUG NOTICE III:

Log on to the first cluster node to which you want to install SSL.
Again, be sure to log in under the SQL Server service account. To work
around this bug, you must make the certificate request with the
certreq command line tool. Using the web interface or the MMC GUI will
not work. The reason for this is that because this work-around
requires that the **SAME** certificate must be installed on each node
in the cluster, the private keys must be marked as exportable. The web
server template and all other templates that implement the Server
Authenticate use do not allow the private keys to be exported. By
using certreq and a custom template file we can generate a certificate
that will have the Server Authentication use and allow the private
keys to be exported.

1. Create an .inf file called webserver.inf and paste the following
info into the file:

[Version]
Signature= "$Windows NT$"

[NewRequest]
Subject = CN=sqlclust.domain.com
KeySpec = 1
KeyLength = 1024
Exportable = TRUE
MachineKeySet = TRUE
UseExistingKeySet = FALSE
ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
ProviderType = 12
RequestType = PKCS10
KeyUsage = 0xa0

[EnhancedKeyUsageExtension]
OID=1.3.6.1.5.5.7.3.1

Replace "sqlclust.domain.com" after "CN=" above with the FQDN of your
SQL Server cluster.

2. Open a command prompt at the location of the above file and run the
following command:

certreq -new webserver.inf webserver.req

3. Then run the following command:

certreq -submit -attrib "CertificateTemplate:WebServer" webserver.req
webserver.crt

4. Finally, run the following command:

certreq -accept webserver.crt

5. Create a new MMC console and add the Certificates snap-in. When
adding the snap-in be sure to choose the "Local Computer" store when
you are prompted.

6. Navigate the left tree to Personal\Certificates. In the right pane
there should be a certificate with the CN name specified in the
request in step 1.

7. Right-click the certificate and click All Tasks | Export. Follow
the wizard being sure to specify that you want to export the private
keys. Save the exported certificate with a .PFX extension, such as
sqlclust.domain.com.pfx, in a place that will be accessible from all
nodes in the cluster.

8. After the certificate is exported, double click the certificate in
the Certificates MMC snap-in and click on the Details tab.

9. Find the Thumbprint property in the list of available fields and
highlight the property. Once highlighted, copy (CTRL-C) the thumbprint
value (a series of hex values separated by spaces) and paste it into a
new Notepad document. Close the certificate snap-in. Here is an
example of a Thumbprint:

56 e9 ea f8 4e 78 c5 1b 71 71 a1 fc 86 f3 60 17 45 21 d8 af

10. In the Notepad document, do a find and replace to replace all the
spaces with commas. Then add a ",00" to the end of the string. When
you are finished you will have a value that looks like:

56,e9,ea,f8,4e,78,c5,1b,71,71,a1,fc,86,f3,60,17,45 ,21,d8,af,00

11. Make the contents of the Notepad document match the following
registry file syntax:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\SuperSocketNetLib]
"Certificate"=hex:56,e9,ea,f8,4e,78,c5,1b,71,71,a1 ,fc,86,f3,60,17,45,21,d8,af,00

12. Save the document with a .reg extension, such as certificate.reg

13. Bring the SQL Server cluster offline by using the Cluster
Administrator.

14. Import the .reg file into the registry by double-clicking on it.
This will add the Certificate thumbprint to the registry and will
force SQL server to load the certificate with the specified
Thumbprint. This is needed to work-around BUG NOTICE #3. This registry
value is briefly described in KB article #276553.

15. Because the MSSQLServer registry key is check-pointed by the
Cluster Service, if the SQL node were restarted the registry changes
would be rolled back to their previous settings and the thumbprint
would be lost. In order to avoid this, the check point must be updated
before bringing SQL server back online. Use the ClusterRecovery.exe
tool that comes with the Windows Server 2003 Resource Kit to do this.

16. In the ClusterRecovery tool enter the name of the cluster (this is
the name of the cluster itself, not the SQL server cluster name).
Select the "Restore cluster resource checkpoints" radio button option
and click next.

17. Select SQLServer from the drop-down list and click Restore. The
checkpoints are now updated and the new registry key will be
replicated on each node in the cluster whenever that node becomes the
active node.

18. Go to each node on the cluster and open up the certificate store
as described in steps 5 & 6. Be sure you have logged into each node of
the cluster with the SQL Server service account.

19. Right click the Certificates folder and select All Taks | Import
to import the certificate that was exported in step 7. Complete the
wizard steps to import the certificate including the private keys.

20. Repeat steps 18 & 19 on all nodes in the cluster.

21. Bring SQL Server online through Cluster Administrator. Move the
SQL Server group to each node in the cluster to ensure that SQL Server
comes online on each node.

22. SSL encryption should now be enabled.

Testing:

Even if you desire to enable "Force Encryption" at the server, do not
enable it until you are positive that SSL is working by following the
testing steps below. If SSL is not properly configured and "Force
Encryption" is configured on the server, you will not be able to get
SQL server to start until the issue is resolved. Depending on how your
have the SQL Server resource setup, the Cluster Service may enter a
long loop of trying to bring SQL online and SQL Server failing to come
online.

To test SSL encryption, go to a client that has Query Analyzer
installed and configure the SQL **Client** Network Utility to force
encryption on the client. Then run Query Analyzer and see if a
connection can be made to the cluster.

If a connection can be made, encryption is likely working, through the
only way to be positive that it is working is to use a packet sniffer
and inspect the network traffic to verify that it is encrypted.

If a connection can't be made, you'll likely get one of two errors in
Query Analyzer:

- Encryption not supported on SQL Server; or
- SSL Security error

Both of these errors are entirely generic and don't tell very much
about the real underlying problem, but you can tell one important
thing based on which error you get:

If you get the first error (Encryption not supported on SQL Server) it
means that SQL server was unable to load any SSL certificate. The
resolution to these problems will be on the server. If you get the
second error (SSL Security error) then you can be sure that SQL Server
loaded SOME certificate, but it may not be the correct one. To resolve
this second error, the problem can be on the server (wrong certificate
loaded) or it could be a client issue like a DNS issue where the
client can't resolve the IP to the FQDN in the certificate and back to
the IP, or the client might not trust the root CA that issued the
certificate to SQL Server. It can be very helpful to know this bit of
information (whether or not a certificate was loaded by SQL Server) to
further diagnose troubles.

Finally, Microsoft has available a tool called SQLCert.exe that is a
command line tool that can be used to help diagnose SQL SSL
certificate issues. In essence, this tools runs through the same steps
that SQL Server does upon start-up when trying to load a certificate.
When running this tool you should be logged on with the SQL Server
service account. I received this tool from Microsoft as part of them
working with me to resolve my SSL issues. It doesn't have a license
agreement with it, so unless I hear otherwise from Microsoft, the
SQLCert.exe tool will be available for download from my website:

http://www.maplebacon.com/sbbs/Threa...ThreadID=13184

Technical info on why bug #3 above occurs:

I have been developing software with VB and VB.NET for a number of
years and my understanding of C, C++, pointers, and the working with
the Win32 API is generally limited, so this bit of info may not be
entirely accurate, but I think it is pretty close from my discussions
with and observations of the Microsoft engineer who debugged this
issue at the SQL Server source code level.

When SQL server starts up on a cluster, it looks in the registry for
the the IP address and name of the cluster. It then uses the name of
the cluster to locate the proper certificate in the certificate store.
This works as expected when the length (in characters) of the cluster
name is shorter than or equal to the length of the IP address. When
the cluster name is longer, an error occurs retrieving the cluster
name, and ultimately the certificate can't be loaded.

The reason is that the SQL server code allocates a string buffer of
512 bytes, it then makes a call to a Win32 registry function to get
the size of the IP address string in the registry, it then passes the
string buffer of 512 bytes to the same function but tells the
functions that the size of the buffer equal to the size of the IP
address and the function fills the buffer with the IP address. Rather
than next going and getting the size of the computer name in the
registry and readjusting the perceived size of the buffer, the code
simply passes the same 512 byte buffer into the next function call,
and specifies the same buffer size as was allocated for the IP
address. The registry function properly detects a buffer that is too
small and returns an empty buffer and an error result. So, even though
the actual allocated buffer is 512 bytes, the registry function thinks
it only has the number of bytes to work that are specified in the
function call. Since this size specification is derived from the size
of the IP address rather than the cluster name, when the cluster name
is longer than the IP address, SQL server fails to load the
certificate.

If you have any comments or questions, stop by my discussion board:

http://www.maplebacon.com/sbbs/

Thanks,
Brian Saville