Refresh of data error: "MS SQL 2014: Unable to connect. Check your data access driver

[former_member234401]

I have connected Lumira 1.31 (desktop) to a SQL server and imported a data set

Now I close down Lumira and reopen – and try to refresh the data source

Get this error

I have taken a look at the below possible solutions – and implemented it

https://archive.sap.com/discussions/thread/3724752

https://launchpad.support.sap.com/services/pdf/notes/2240273/E

but I does not work

All the files SAPVI and sqlsrv.sbo points the the location where the jar file is placed

I use this jar = sqljdbc41 but have also tried with sqljdbc4 and sqljdbc. All with no luck

the sbo file looks like this

<?xml version="1.0" encoding="UTF-8"?><DriverConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../sbo.xsd"> <Defaults> <Class JARFile="dbd_mssql">com.sap.connectivity.cs.java.drivers.jdbc.mssql.MSSQLDriver</Class> <JDBCDriver> <ClassPath> <Path>$ROOT$/drivers/java/dbd_jdbcwrapper.jar</Path> </ClassPath> <Parameter Name="JDBC Wrapper">com.sap.connectivity.cs.java.drivers.jdbc.wrapper.JDBCWrapper</Parameter> </JDBCDriver> <Parameter Name="Family">Microsoft</Parameter> <Parameter Name="SQL External File">sqlsrv</Parameter> <Parameter Name="SQL Parameter File">sqlsrv</Parameter> <Parameter Name="Description File">jdbc</Parameter> <Parameter Name="Strategies File">sqlsrv</Parameter> <Parameter Name="Driver Capabilities">Query,Procedures</Parameter> <Parameter Name="Extensions">sqlsrv,jdbc</Parameter> <Parameter Name="Connection Shareable">Yes</Parameter> <Parameter Name="Shared Connection">No</Parameter> <Parameter Name="Array Fetch Available">True</Parameter> <Parameter Name="Array Fetch Size">10</Parameter> <Parameter Name="Array Bind Available">False</Parameter> <Parameter Name="Max Rows Available">Yes</Parameter> <Parameter Name="Query TimeOut Available">Yes</Parameter> <Parameter Name="Quote Identifiers">False</Parameter> <Parameter Name="Optimize Execute">True</Parameter> </Defaults> <DataBases> <DataBase Active="Yes" Name="MS SQL Server 2008"> <Aliases> <!-- You can add an alias here if you are using some connections that are defined with an older database engine --> <Alias>MS SQL Server 2005</Alias> <Alias>MS SQL Server 2000</Alias> <Alias>MS SQL Server 7.x</Alias> <Alias>MS SQL Server 6.5</Alias> </Aliases> <JDBCDriver> <!-- Uncomment and edit the following lines to define java classes required by JDBC driver <ClassPath> <Path>your jar or class files directory</Path> </ClassPath> --> <!-- Configuration is done for SQL Server 2008 JDBC Driver (2.0) --> <Parameter Name="JDBC Class">com.microsoft.sqlserver.jdbc.SQLServerDriver</Parameter> <Parameter Name="URL Format">jdbc:sqlserver://$DATASOURCE$;DatabaseName=$DATABASE{replace44}lt;/Parameter> </JDBCDriver> <Parameter Name="Extensions">sqlsrv2008,sqlsrv,jdbc</Parameter> <Parameter Name="Force Execute">Always</Parameter> <Parameter Name="SSO Available" Platform="MSWindows">True</Parameter> </DataBase> <DataBase Active="Yes" Name="MS SQL Server 2012"> <JDBCDriver> <!-- Uncomment and edit the following lines to define java classes required by JDBC driver <ClassPath> <Path>your jar or class files directory</Path> </ClassPath> --> <!-- Configuration is done for SQL Server 2012 JDBC Driver (2.0) --> <Parameter Name="JDBC Class">com.microsoft.sqlserver.jdbc.SQLServerDriver</Parameter> <Parameter Name="URL Format">jdbc:sqlserver://$DATASOURCE$;DatabaseName=$DATABASE{replace60}lt;/Parameter> </JDBCDriver> <Parameter Name="Extensions">sqlsrv2012,sqlsrv,jdbc</Parameter> <Parameter Name="Force Execute">Always</Parameter> <Parameter Name="Driver Capabilities">Query,Procedures,Canpicture-lumira-error.pngcel</Parameter> <Parameter Name="SSO Available" Platform="MSWindows">True</Parameter> </DataBase> <DataBase Active="Yes" Name="MS SQL Server 2014"> <JDBCDriver> <!-- Uncomment and edit the following lines to define java classes required by JDBC driver <ClassPath> <Path>your jar or class files directory</Path> </ClassPath> --> <ClassPath> <Path>C:\JDBC\qljdbc41.jar</Path> </ClassPath> <!-- Configuration is done for SQL Server 2012 JDBC Driver (2.0) --> <Parameter Name="JDBC Class">com.microsoft.sqlserver.jdbc.SQLServerDriver</Parameter> <Parameter Name="URL Format">jdbc:sqlserver://$DATASOURCE$;DatabaseName=$DATABASE{replace80}lt;/Parameter> </JDBCDriver> <Parameter Name="Extensions">sqlsrv2014,sqlsrv,jdbc</Parameter> <Parameter Name="Force Execute">Always</Parameter> <Parameter Name="Driver Capabilities">Query,Procedures,Cancel</Parameter> </DataBase> </DataBases> </DriverConfiguration>

[TammyPowlas]

Hello Rene - are you on the latest patch of Lumira, 1.31.4? If not could you update and try again?

[former_member234401]

Hi Tammy - yes I am on version 1.31.4 Build 133

And I have full license.

[former_member234401 · Accepted solution]

Hi - we found the solution. The underlying MS SQL server had a faulty user. We solved it by cloning the old user. then it works

[former_member303244 · Accepted solution]

Hi Rene,

I have the same issue, was this Faulty user a generic user, was the user working okay for other reporting from other sources?

This is because my user is generic user and works ok for all our reporting from our DW, and also used for SSMS and universe connections.We have no issues with any other reporting using this user.

Thanks.

[Former Member]

Hello Rene,

Please check the TCP port number for the SQL database connection. Please go through this post: https://community.spiceworks.com/topic/1987362-cannot-connect-with-server-management-studio and read the answer Priyal, brand representative of Stellar data recovery.

Good Luck!

[former_member234401]

Hi Dasy

Thanks for the link - I have implemented the suggested solution. But with no luck. I will investigate the Firewall and the TCP port on the MS SQL side. I can see that Lumira has a lot of different firewall settings.

[former_member234401]

Hi

I can connect to the MS SQL via Excel run it, restart it and refresh without any problems