Unable to configure SQL connection using Windows/MSAD authentication protocol

Oliver B Smith

Summary

Unable to configure SQL connection using Windows/MSAD authentication protocol

Content

Hi all,

 

We are currently trying to configure individual user instances of DVD, using individual MSAD accounts for creation of a SQL Server Database Connection.

 

Unfortunately, it seems like DVD will not accept anything that isn't a native SQL account for authentication. I'm assuming this is because of a connection string behind the scenes, but does anyone know if this can be changed?

 

Bear in mind, creation of the SQL Server Database Connection works fine using a native SQL user.

 

We have also ensured that the non-native user account has been provisioned with all security roles available, so it doesn't seem to be related to that.

 

SQL Error logs as below:

 

05/08/2018 15:14:30,Logon,Unknown,Login failed for user 'WIN2012\USER1'. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: 192.168.100.10]

05/08/2018 15:14:30,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 6.

 

OBIS diagnostic log:

 

**********  Task: 1. Running for (mls): 31 **********

Description: Execute Query

Repository Name: ssi

Catalog: Sample Targets Lite

Tenant:

Tenant ID:

Service Name:

User: weblogic

Session ID: 0x74350000

Request ID: 0x74350007

ProcedureID: 1000226

Logical SQL:{call NQSExternalVerifyConnection('<?xml version="1.0" encoding="UTF-8" standalone="no"?><cm:dsConnMetadata xmlns:cm="com.oracle.bi/ConnMetadata/v1"><cm:dsMetadata><cm:cliName>ODBC 3.5</cm:cliName><cm:targetName>SQL Server 2012</cm:targetName><cm:providerName>sqlserver</cm:providerName><cm:useSqlOverHttp>false</cm:useSqlOverHttp></cm:dsMetadata><cm:connectionParameters/><cm:dsSignon><cm:dsn>DRIVER=Oracle 7.1 SQL Server Wire Protocol;HOST=localhost;PORT=1433;DB=DB1;</cm:dsn><cm:host>localhost</cm:host><cm:port>1433</cm:port><cm:serviceName>DB1</cm:serviceName><cm:userName>WIN2012/USER1</cm:userName><cm:password>XXXXXX</cm:password><cm:driverClass>oracle.bi.jdbc.AnaJdbcDriver</cm:driverClass></cm:dsSignon><cm:enablePooling>false</cm:enablePooling><cm:dsConnPoolParams><cm:maxConnections>100</cm:maxConnections><cm:timeOut>600</cm:timeOut></cm:dsConnPoolParams></cm:dsConnMetadata>', '''weblogic''.''dev_test1''')}

 

(I've removed some sensitive details from the above string).

 

Any ideas?

 

Thanks for any help!

Kranthikumar Kommuri

Hi Oliver,

Windows authentication is currently not supported in SQL server connection type. Alternative is to use ODBC connection type. Though there is an additional step to create DSN, you can connect to SQL server using windows authentication.

Hope this helps.

Regards,

KK