• SQL Server Cast



    This time I want to discuss a support case that took me some days to get fixed. This case may be of interest for anyone having problems connecting a SQL Server installation to an Oracle data source using SQL Server Integration Services (SSIS).

    In this case my customer was trying to connect to an Oracle Server in order to copy several tables into SQL Server 2005. SQL Server Integration Services is a great tool for this purpose, you can configure how the copy job takes place using SQL Server Business Intelligence Development Studio (aka BIDS) and check from an user-friendly interface how each step is executed. BIDS is in fact a version of Visual Studio 2005 (the executable name is devenv.exe) with the components required to work with the different SQL Server services and components.

    My customer was executing BIDS from her own computer without issues but was finding a problem while working with BIDS from the server where SQL Server was installed. The errors she was getting were:

    [Connection manager "mib.bigcorp.com.gca"] Error: SSIS Error Code DTS_E_OLEDBERROR. 
    An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.
    Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80004005
    Description: "ORA-06413: Connection not open.".

    [OLE DB Source [1]] Error:
    The AcquireConnection method call to the connection manager "mib.bigcorp.com.gca"
    failed with error code 0xC0202009. 
    There may be error messages posted before this with more information on why the
    AcquireConnection method call failed.

    Since the customer was using the Microsoft OLE DB provider for Oracle to setup the connection, I did a research on the available documentation in Microsoft Support website using the terms "oracle", "oledb" and "provider". I did find several documents discussing different problems but one of them was of special interest here:

    INFO: Limitations of Microsoft Oracle ODBC Driver and OLEDB Provider (KB244661)

    SUMMARY: This article describes the limitations of the current version of Microsoft ODBC Driver and OLE DB Provider for Oracle. These components are currently in maintenance mode. No new updates are planned for future releases of these components including their support against versions later than Oracle 8i.

    According to this article, it is not recommended to make use of the Microsoft OLE DB Provider for Oracle if the Oracle server is a version beyond 8i (my customer was using version 9i). This does not mean that the Microsoft OLE DB Provider did not work at all (indeed I was able to preview the remote table in the Oracle server and test connectivity successfully from within SSIS using this provider) but the functionality offered will be reduced and there was a good chance some incompatibility problem happened.

    I asked the customer to install the Oracle client on the server (different versions of the Oracle client software are available here). This client provides the required OLE DB Provider from Oracle that enables connectivity to an Oracle data source. Only some hours later the customer back to me saying "I have installed the Oracle client, but the only Oracle OLE DB component I am able to select in SSIS is the Microsoft one". Ok, I knew that one from a different support incident I had in the past. SQL Server Integration Services runs as a 32-bit application on a 64-bit server, as you can quickly check using Windows Task Manager:


    This means that, unless you install the Oracle Client for 32-bit on the 64-bit server, you won't be able to access the Oracle OLE DB Provider. This applies not only for the Oracle OLE DB, but for any other 32-bit provider. You can read a full explanation about why you need the 32-bit components in your 64-bit server here:

    The 32-bit SSIS Designer [BIDS] displays only 32-bit providers that are installed on the local computer. To configure a connection manager to use a specific 64-bit provider, you must install the 32-bit version of the provider on the development computer for use at design time. Even though the 32-bit version of the provider is installed, you can still run the package in 64-bit mode, both in the development environment and after deployment. The 32-bit and 64-bit versions of a provider have the same ID. Therefore, the SSIS runtime will select the appropriate version of the provider to use.

    But having the 32-bit component is not enough, we need both the 32-bit and 64-bit components installed side-by-side. Wit only the 32-bit Oracle client the package will fail when executing from BIDS, this is because there is only one SSIS service on a server, and it is indeed a 64-bit process when running on a 64-bit server as we can see on this Process Explorer screen capture (process name is MsDtsSrvr.exe):



    Ok, we have now the SSIS package using the Oracle OLE DB provider from Oracle and we have verified that both the 32-bit and 64-bit version of the Oracle client are installed on the Windows server.

    However, the package kept failing with the same error.

    After several hours tracing down the error with the help of Sysinternals Process Explorer, Process Monitor and the Internet we did find the a possible cause for this error. Apparently some Oracle client versions are affected by a bug (Oracle bug number 3807408) that causes the client networking layer to incorrectly parse program locations that contain parenthesis in the path. You can find a brief discussion of this behavior on this thread in Oracle forums.

    As you probably know, on a Windows Server x64 installation, the default program files directory is "C:\Program Files\" but all the 32-bit components are installed in "C:\Program Files (x86)\" directory. Since my customer did not have access to Oracle support at that time, we decide to test if this was the root cause of our problem copying the contents of the "C:\Program Files (x86)\Microsoft Visual Studio 8" folder under a new Program Files folder called "C:\ProgramFilesx86". We started BIDS (devenv.exe) from the new directory and executed the package both from BIDS and SQL Server, this time successfully.

    The trick finally did the job and my customer was able to successfully import the required tables into SQL Sever, however, I do not think this is a good solutions (much less "elegant" solution) and I recommended her to contact Oracle support for a version of the Oracle client no affected by this bug.

  • SQL Server Cast

    Enabling Certificate for SSL on a SQL Server 2005 Clustered Installation

    This week I has been working on a case about the installation of a certificate on a SQL Server cluster. My customer wanted to use certificates to encrypt client connections via Secure Sockets Layer (SSL), a method supported by SQL Server 2005 that allows to secure communications between clients and database servers (you can read more information about this functionality on Books Online). Although my customer was currently working with SSL on SQL Server standalone servers he was finding problems installing the certificate on a new cluster. Although setting up the certificate and encryption in a standalone installation is pretty straightforward (see KB316898) cluster installation differs, and requires extra configuration steps.

    Obtaining the certificate

    Since I was not familiar with the certificate installation on a clustered SQL Server instance, I started to read the available documentation in SQL Server Books Online, and more specifically, the how-to provided in the Enable Encrypted Connections to the Database Engine (SQL Server Configuration Manager) article and the Microsoft Knowledge Base article KB316898, How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console (this one only intended for standalone installations).

    First I needed a certificate to do the testing. I started installing an stand-alone root certification authority (CA) on my lab domain following the guidelines describe here. I did also installed the convenient web enrollment feature, which allows a client to request a certificate from a CA using a web browser (if you do not want to deal with all the CA stuff, read "A Simpler Method to Obtain Certificates for Testing" at the end of this post).

    I did request the certificate from one of the SQL Server nodes. According to the Books Online article, the certificate should be installed using the SQL Service account and it has to be placed on the local computer > personal certificates store:

    Certificates are stored locally for the users on the computer. To install a certificate for use by SQL Server, you must be running SQL Server Configuration Manager under the same user account as the SQL Server service unless the service is running as LocalSystem, NetworkService, or LocalService, in which case you may use an administrative account.

    This is because the certificate contains a private key that needs to be accessed by SQL Server service account for the SSL communication to work. If we use any other user account (say, local administrator) SQL Server service account will not be able to access the private key and the authentication will fail. The certificate can be optionally installed on the current user certificate store, but installing it on the computer certificate store makes the certificate available for any user.

    I did logon in the server using the SQL Service account, since this is a cluster, this has to be a domain account. I did open the web enrollment site (by default under http://CAName/certsvr, where CAName is the Certification Authority's hostname) and requested a new Server Authentication Certificate. The most important thing at this point is to specify the fully qualify DNS name of the clustered instance in the "Name" property for this certificate; the SSL encryption will not work if we specify the name of the cluster node:

    If you want to use encryption with a failover cluster, you must install the server certificate with the fully qualified DNS name of the failover clustered instance on all nodes in the failover cluster. For example, if you have a two-node cluster, with nodes named test1.your company.com and test2. your company.com and a failover clustered instance of SQL Server named fcisql, you must obtain a certificate for fcisql.your company.com and install the certificate on both nodes.

    In my case, the SQL Network Name cluster resource was called "SQL-2005" so I did specify sql-2005.contoso.lab as Name property:

    Certficate Request using Web Enrolment


    While requesting the certificate, make sure that the option "Mark keys as exportable" is enabled so the private key is available when exporting and importing the certificate in the rest of the cluster nodes. I did also check the "Store certificate in the local computer certificate store" so I do not have to import the certificate later. After submitting the certificate request I did back to my CA and approved the request. Finally I returned to the web enrollment site to install the certificate.

    At this time the server authentication certificate was installed but I still needed to install the root CA certificate; the Books Online article linked earlier explains why this is needed:

    If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is vouched for by the chain of certificates that lead to the trusted root authority. Such server validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate that is used by the server.

    If the root CA certificate is not installed, the SQL virtual server certificate cannot be verified against a a trusted certification authority, this can be quickly checked accessing the installed certificate properties:

    Certificate Properties


    I did obtain a CA root certificate from my test Certification Authority and installed it on the Trusted Root Certification Authority store for the local computer.

    The certificate must meet some requirements to be used for SSL (the requirements can be found on the Certificate Requirements section of this Books Online article). To make sure this certificate fulfilled al the requirements I did export the certificate to a local directory and check its details using certutil.exe command-line utility (more info about this utility can be found here). Additionally, I did also export the SSL certificate from the first cluster node (we will need to import this certificate on each of the cluster nodes later).

    Make the Certificate Works for SSL

    The certificate used by SQL Server to encrypt connections is specified in the following registry key:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\SuperSocketNetLib\Certificate

    This key contains a property of the certificate known as thumbprint that identifies each certificate in the server. I did find out that in a SQL Server clustered installation this key is not correctly updated with the corresponding certificate thumbprint. In my case, after importing the certificate, the registry key did show a null value:

    Certificate Registry Key in SQL Server


    This registry key should contain the thumbprint property of the certificate we want to use so I did copy the thumbprint from the certificate details tab and pasted it into Notepad to remove the extra spaces:

    Certificate Thumprint


    and finally copied the corresponding thumbprint string on the "Certificate" registry key (the "Certificate" registry key requires the certificate thumbprint to be included with no extra spaces):

    Editing certificate thumbprint in Notepad


    Just after the registry change, I performed a cluster failover on the second cluster node and rebooted the first cluster node.

    On the second cluster node I imported both the certificate issued for the virtual SQL Server and the root CA certificate. I did perform the previous steps to have the certificates installed and the thumbprint string copied on the corresponding registry key. Finally, I did also rebooted this second cluster node. Once all the cluster nodes were up and running I checked that all of them had the same thumbprint string on the "Certificate" registry key. If your certificate has been correctly configured, you will see the following message logged on the SQL Server ERRORLOG file on the next service startup:

    2008-02-18-23:37:25.01 Server     The certificate was successfully loaded for encryption

    To enable SSL in SQL, I did change SQL Server network protocol properties for the clustered instance setting the "Force Encryption option" to "Yes". You will need to restart the SQL Server service for the change to take effect, but once it is done all the communication attempts with your database will need to be encrypted:

    Protocol Properties Configuration


    Note that if you try to select the corresponding SSL certificate on the "Certificate" tab of the SQL Server Protocols properties, you will see that the installed certificate does not show up. This behavior is a known issue in a clustered installation. SQL Server configuration manager search by default on the local computer personal certificates store and tries to mach an existing certificate with the fully qualified domain name (FQDN) of the local computer. Since the installed certificate is not associated to the cluster node FQDN but with the virtual SQL Server FQDN, the corresponding certificate is not shown on the GUI. To make sure what certificate is in use for SSL check the corresponding thumbprint string on the abovementioned registry key.

    In addition to server, the client has to be also configured to support communication over SSL. To do so I imported the certificate on the client computer using the Certificates snap-in and configured the "Force protocol encryption" to "Yes" under the SQL Native Client Configuration (this option is available via the "Flags" page).

    Testing the Connection

    From my client PC I did test the connection to the remote SQL Server using the "Encrypt Connection" option available in SQL Server Configuration Manager. To verify that the connection was indeed encrypted I fired up a network analyzer to have a view of the traffic between my computer and the remote SQL Server. The SSL protocol was indeed being use in this connection, as we can see in Network Monitor screen capture:


    In this post I have tried to provide you with the required procedure to have a certificate installed and configured for SSL in a clustered SQL Server installation. The links included in this post will provide you with a better understanding of this procedure and a much better foundation to implement encrypted client-server communications on your infrastructure.

    A Simpler Method to Obtain Certificates for Testing

    If you are in trouble trying to find a Certification Authority for your tests or if you do not want to deal with the CA installation and configuration process, you can use the MakeCert.exe certificate creation tool. This Books Online article describes how to quickly setup you certificate requirements for your own testing using MakeCert.exe.

Page 1 of 1 (2 items)