Ошибка безопасности ssl microsoft ole db

• All QlikView versions
• All Qlik Sense versions

Resolution:

The source and destination servers do not have aligned TLS versions enabled.

Please verify what version you have installed and confirm if you need to upgrade your data source: https://support.microsoft.com/en-us/kb/3135244 

Recommended Fix:

Upgrade your data source so that the TLS versions between the data source and Qlik Sense or QlikView server match.

Alternative Fix:

Align the TLS versions used. Note that the example we use here configures TLS 1.0. We do not recommend the use of TLS 1.0, it is simply used for demonstration purposes.

If the registry keys are not present, the script under the following article should add them leaving only TLS 1.2 enabled TLS and SSL Support in Qlik Sense: How to configure Qlik Sense and TLS . Then TLS 1.0 may be enabled as mentioned below.

TLS 1.0 can be enabled with the following registry changes:

• [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Server] «Enabled»=dword:00000001
• [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Server] «DisabledByDefault»=dword:00000000
• [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Client] «Enabled»=dword:00000001
• [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.0Client] «DisabledByDefault»=dword:00000000

If the organization policy requires TLS 1.1 to be disabled, this can be done after the installation completes:

• [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.1Server] «Enabled»=dword:00000000
• [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.1Server] «DisabledByDefault»=dword:00000001
• [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.1Client] «Enabled»=dword:00000000
• [HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSecurityProvidersSCHANNELProtocolsTLS 1.1Client] «DisabledByDefault»=dword:00000001

Related Content:

• TLS and SSL Support in Qlik Sense: How to configure Qlik Sense and TLS 

YouTube Video (Better Quality)

I was approached with a case where the customer was trying to create a Data Source within the Power BI Admin Center and received the following error when trying to test the connection and having Encrypt Connection selected.

Failed to test connection. [DBNETLIB][ConnectionOpen(SECDoClientHandshake()).]SSL Security error.

When we get an SSL error, we are talking about Certificates and trying to encrypt traffic between the client and the Data Source.  The client in this case will be the Data Management Gateway.

While we are using Power BI, this is a great example of just a regular connectivity issue.  This is not a Power BI specific issue.  We would get a failure in a different application as well, such as Management Studio.

They were using the Microsoft OLE DB Provider for SQL Server as the provider.  This is a really old provide and we should move to the .NET Provider or SQL Server Native Client if possible.  Although depending on how you pulled information into Excel, that may be the provider listed and we need to match that as I’ve described in other blogs.

One of the reasons I recommend moving off of it is due to the error message itself.  The OLE DB Provider error is fairly generic and not overly helpful.  With the .NET Provider and SQL Server Native Client, the messaging has been updated and can give you more details.  Here is what the error is from the SQL Server Native Client.

SSL Provider: The certificate chain was issued by an authority that is not trusted.

This is due to the certificate that SQL Server is presenting.  Based on the information in the certificate, and the certificate is invalid.  This can happen for multiple reasons.  In this case the error indicates that it was because we don’t trust the certificate, which is a fairly specific error versus the generic error from the OLE DB Provider.

How to correct it

To correct this, we have to deal with certificates.  In our case, the certificate used by the SQL Server is not within the Trusted Root Certification Authorities store of the machine running the Data Management Gateway.

To review the Trusted Root store, we can use MMC to do this.  Open MMC and add the Certificates Snap In. 

When you add that, you’ll presented with a dialog about how you want to manage certificates.  In what context?  You have three options:  My user account, Service account and Computer account.

I always use the Computer account context.  This will cover everything on the machine.  If you use My user account, it will be for your specific user.  So, Management Studio would start working, but the Data Management Gateway would still fail as that doesn’t run under your user account.  There are reasons why you would want to do either a service account or your user, but you need to know what you are doing and what your scenario is to understand how things will be affected.  For the purposes of this walk through, I’m going with Computer account.

After we add the Snap In within MMC, we will see several folders.  There are two we want to focus on.  Personal and Trusted Root Certification Authorities.

The Personal store is where the certificates reside that you can actually use.  The Trusted Root store are the items that we trust that could be part of the certificate chain.

For our purposes, and to correct the issue, we are interested in the Trust Root store.  When we select the Certificates folder under the Trusted Root, these are all of the Certification Authorities (CA) that we trust.  So, if any certificate originates from any of these, they will be trusted by the system.  VeriSign is an example of one that is in this folder.  So, any certificate that comes from VeriSign, we will trust because it is a known organization.

What is missing here is the item that will cause us to trust the certificate that is being presented by SQL Server.  There are different ways to create a certificate.  You can generate a self signed certificate.  You can get a certificate from a known CA such as VeriSign.  Or your organization may have their own Certification Authority.  In my case, I did a self signed certificate.  There are also different ways to do that.  You can create it through IIS, but in my case I used makecert to generate it.  This can be found in the Windows SDK.

Here is the makecert command I used to generate the server certificate.

makecert -r -pe -n «CN=guyinacubesql.guyinacube.com» -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp «Microsoft RSA SChannel Cryptographic Provider» -sy 12

There are a couple of things that are required here.  The eku parameter I used indicates it is a Server Certificate.  Also, the sky parameter indicates exchange.  These are both required.  This was run on the SQL Server.

I created the certificate and then exported it to a PFX file.  PFX includes the private key.  We really only need the public cert which could just be a CER file.  I always include Extended Properties on the export.

I then go to the machine running the Data Management Gateway, and within the MMC/Certificates window, we can right click on the Certificates folder under the Trusted Root store, right click, go to All Tasks and select Import.  We can enter the password for the PFX package and Include all extended properties.

We then want to place the certificate within the Trusted Root Certification Authorities store.

Once all of that is done, we should see our certificate listed within the Trust Root store.

The key symbol on the Cert icon indicates that it has the Private key. You will notice that the others don’t have the private key, and that’s fine.  So, now if we go back and test the connection on the Data Source, it will connect successfully because we trust the CA.

If we change the provider over to SQL Server Native Client 11, we will actually get another error.

SSL Provider: The target principal name is incorrect.

SQL Server Native Client is a little more strict in its certificate validation. We used the NetBIOS name for the server name.  However the name in the Certificate is the Fully Qualified Domain Name (FQDN).  So, they don’t match.  If we change the server name to the FQDN, it will then work correctly.

We care about the Certificate Path

The reason this failed is because we didn’t trust the root CA.  This comes down to the Certification Path.  When you open a certificate, there will be a Certification Path tab.  For a self signed certificate, you will only have that certificate listed.

You may have multiple items listed.  If there is a red X on any item here, then the certificate will not be trusted.  Here is an example of one that isn’t trusted.

That’s when we need to add that to the Trusted Root store, like we just did, to get it to be trusted. 

Domain Certificate

I mentioned that you could have a Certification Authority within your organization (such as your Domain).  I can create a Domain Certificate multiple ways, but the easiest way for me is to just do it on a machine that has IIS installed.  When you go to the server, and look at Server Certificates, an option on the left says Create Domain Certificate.

When you create that certificate, you will see the CA Certificate as a root within the Certification Path tab.

From the domain perspective, every machine joined to the domain will have that CA Certificate in the Trusted Root store, so no action is needed.  It would just work.

Adam W. Saxton | Microsoft Business Intelligence Support – Escalation Services@GuyInACube | Mixes | YouTube | Facebook.comguyinacube

I have come here today after speding days trying to solve an issue. I have this ASP.Net application that I have been working on since 2010.

Recently a new client showed interest in using the application. Of course, there were several requirements that my application needed to have. This client is very cautious when talking about security.

One of the most important requirements was the support for the protocol TLS 1.2 They would disable older protocols (SSL 3.0, TLS 1.0), and only enable TLS 1.2

As soon as they did that in their server my application stopped working. It was working before just fine, and the reason they need to apply this configuration is because they want to let users connect to the app through the Internet, not only inside their lan.

This is the error I am getting when trying to connect to the application

[OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (SECCreateCredentials()).]SSL Security error.]
   System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +497
   System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +100
   System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions) +57
   System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +1143
   System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +314
   System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +23
   System.Data.OleDb.OleDbConnection.Open() +52
   XS.dbUtils.TConexao.conexao@0(String sessionid) +1103
   XS.dbUtils.TConexao.conexao(String sessionid, Boolean novaconexao) +70
   XS.dbUtils.TUtils.QryOpen2(String sessionid, String sql, Object[] Parametros) +65
   XS.dbUtils.TUtils.QryValues(String sessionid, String sql, Object[] Parametros, String[] DefaultValue) +41
   GestaoEstrategica.TLoginNovo.Page_Load(Object sender, EventArgs ev) +411
   System.Web.UI.Control.OnLoad(EventArgs e) +103
   System.Web.UI.Control.LoadRecursive() +68
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1381

Here is the enviroment:

• Asp.Net Application running with .Net 4.5.2
• SQL Server 2012
• Today I tried to connect to an instance using SQL Server 2016

Here are the things I have found and tried in order to solve this problem:

• First I thought my application would not work with TLS 1.2, so I did what it is saying here blogs.perficient.com/microsoft/2016/04/tsl-1-2-and-net-support/

  ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12

• Second I started to think it was related to the SQL Version. I found this page here talking about an update:  blogs.msdn.microsoft.com/sqlreleaseservices/tls-1-2-support-for-sql-server-2008-2008-r2-2012-and-2014/

• The page above led me to this link support.microsoft.com/pt-br/help/3135244/tls-1-2-support-for-microsoft-sql-server I figured I needed that upate in order to solve the issue. However, none of that worked.

• Last thing I did was to start an instance of Windows Server 2016 with SQl Server Standard 2016. According to the link above, the SQL Server 2016 already comes with support to TLS 1.2. However, I am getting the same error DBNETLIB][ConnectionOpen
  (SECCreateCredentials()).]SSL Security error

It has been almost a month doing research, reading foruns, and I can’t solve this problem. Can someone help me out? I don’t know what else to try. This is the first time I have to deal with this kind of security, so it has been really hard to understand what
is going on.