SAP Knowledge Base Article - Public

2181269 - Issue with Windows AD Connectivity from Lumira Desktop to SQL Server

Symptom

  • In Lumira Desktop, when making JDBC connection to Microsoft SQL Server using Windows Active Directory credentials, you encounter the following error:
    • com.microsoft.sqlserver.jdbc.SQLServerException: Integrated authentication failed. ClientConnectionId:<###>

      6-16-2015 1.30.07 PM.png

Environment

  • SAP Lumira Desktop, version 1.25
  • Microsoft SQL Server 2008 R2

Reproducing the Issue

  1. Open Lumira Desktop
  2. Click "New Document"
  3. Choose "Query with SQL"
  4. Select "MS SQL Server 2008 - JDBC driver" from the list
  5. Change the "Authentication Mode" dropdown from "User specified username and password" to "User Windows Authentication"
  6. Put in the SQL Server hostname and port number
  7. Specify the target database name (if needed)
  8. Click "Connect" button
  9. Error message is popped up

Cause

Lumira client tool connectivity to data sources via JDBC or "Query with SQL" is limited to basic authentication only. The information provided in this article is not officially supported by SAP and provided as-is. Customers have reported this works for the Lumira Desktop/Discovery clients but not for Lumira server. See "2628644 - "This driver is not configured for integrated authentication" error when viewing Lumira document in Launchpad" for more information.

Resolution

In order to connect to SQL Server with Windows AD credentials, you need to complete the prerequisites configuration. The steps are documented in Lumira Desktop Installation Guide, chapter 4.1 (as of version 1.25).

If the prerequisites steps are done correctly, and you are still not able to establish the connection, please try the following workaround:

  1. Open the file "C:\Users\Public\sapvi\cs_drivers.xml" in a text editor. This file keeps track of where the database JDBC driver are stored on the client machine. Make sure the <Path> parameter is pointing to the correct location of the SQL Server JDBC driver (sqljdbc4.jar or sqljdbc41.jar).
  2. In the Microsoft SQL Server JDBC Driver 4.0/4.1 package that you downloded, you should find the file "sqljdbc_auth.dll" under the folder "<MSSQL_JDBC_Drvier_Dir>\sqljdbc_4.0\enu\auth\x64\".
  3. Make a copy of the "sqljdbc_auth.dll" file, and place it in the Lumira Desktop installation directory. By default it is "C:\Program Files\SAP Lumira\Desktop\"
  4. Re-launch Lumira Desktop
  5. Click "New Document", choose "Query with SQL", then select "MS SQL Server 208 - JDBC Driver" from the list
  6. In the next screen, do not change the "Authentication Mode" dropdown, make sure "Use specified username and password" is selected
  7. Do not enter any username or password
  8. Enter the SQL Server hostname and port number, as well as the target database name (if needed)
  9. Check the "Save Password" option
  10. Click on the "Advanced" button, copy and paste the following line into the JDBC Driver Properties field:
    integratedSecurity=true,authenticationScheme=NativeAuthentication

    6-16-2015 3.41.49 PM.png
  11. Click OK to save the changes
  12. Click the "Connect" button
  13. You should now be logged in to SQL Server and see the list of databases and schemas.
  14. In the query window on the right hand side, you can run this query to confirm you are connected to SQL Server using your Windows AD credentials:
    select CURRENT_USER;

Keywords

lumira, desktop, lum, lums, mssql, sqlserver, sqlsrv, sqlsvr, 2008, 2012, 2014, jdbc, winad, ad, sso, single, sign, on, sign-on, connect, failed, not, working, doesn't, work, can, not, can't, cannot, , KBA , BI-LUM-DIS , Lumira Desktop/Discovery , Problem

Product

SAP Lumira, desktop edition 1.0