Issue with "XPassword" and OLE DB SQL Server Native Client 11.0 Driver in QlikView November 2017 (12.20)

[Sonja_Bauernfeind]

The SQL Server Native 11 Client Driver does not store the encrypted password correctly in the Connection String. This is causing errors with the Connection.

In order to reproduce the error, the following steps are followed:

1. Log into a Server with SQL Server 2012 Native Client installed and QlikView with a user that does not have Windows Authentication to the Database Connection.
2. Open a Blank QVW and Edit the Script with the OLE DB Connection
3. Choose the SQL Server Native Client 11.0 in the Data Link Properties Provider tab                                                                                                              
4. Fill out the Connection Properties for a SQL Server Authorized User and Click "Test Connection" Everything should succeed at this point.                                            
5. However, once you click OK the following error is shown, "Connector connect error: ErrorSource: Microsoft SQL Server Native Client 11.0 ErrorMsg: Login failed for user.

Environment:

QlikView, 12.20 and newer

 

Cause:

This is caused by the SQL Server Native Client 11.0 Driver and the Connection dialog that displays after connecting to the OLE DB Driver in the QlikView Application. This is otherwise known as Integrated Security.

Resolution:

In order to bypass the error message and create a SQL Server Native Client 11.0 Connection for OLE DB the following steps need to be followed in the exact order shown:

1. Open a Blank QVW and Edit the Script with the OLE DB Connection
2. Choose the Microsoft SQL Server Native Client 11.0 in the Data Link Properties Provider tab 
3. Select the Connection Tab Enter the Server Name and User Name
4. Deselect the Blank Password option
5. Add the Password
6. Select the "Allow Saving Password" option
7. Navigate to the All tab
8. Select the "Persist Security Info" Click Edit Value
9. Choose "False" for the Property Value and Click OK                                                                                                                                             

10. Changing the value to "False" or using the Reset Value button will omit the Integrated Security property completely.

Internal Investigation IDs:

• QV-13546