Error Connecting to MS SQL DB from Jaspersoft Studio

[msaiz]

I am New to JS Studio and attempting to connect to MS SQL DB from Jaspersoft Studio.

 

But I get the followign error:

net.sf.jasperreports.engine.JRException: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'SJMsaizm01'. ClientConnectionId:ee7f330c-a14a-46db-a1c8-ee0b2ad9ccaf

at net.sf.jasperreports.data.jdbc.JdbcDataAdapterService.contributeParameters(JdbcDataAdapterService.java:130)

at net.sf.jasperreports.data.AbstractDataAdapterService.test(AbstractDataAdapterService.java:128)

at com.jaspersoft.studio.data.wizard.AbstractDataAdapterWizard$3.runOperations(AbstractDataAdapterWizard.java:162)

at com.jaspersoft.studio.utils.jobs.CheckedRunnableWithProgress$1.run(CheckedRunnableWithProgress.java:59)

at java.lang.Thread.run(Unknown Source)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'SJMsaizm01'. ClientConnectionId:ee7f330c-a14a-46db-a1c8-ee0b2ad9ccaf

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:254)

at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:84)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2908)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:2234)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)

at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:2220)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1326)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)

at net.sf.jasperreports.data.jdbc.JdbcDataAdapterService.getConnection(JdbcDataAdapterService.java:168)

at net.sf.jasperreports.data.jdbc.JdbcDataAdapterService.contributeParameters(JdbcDataAdapterService.java:128)

... 4 more

 

Our DBA's tell me that we use “Windows authentication”,  but that option is not available.

How do I resolve this?

 

 

[hozawa]

Use something like below for jdbc url:

jdbc:sqlserver://localhost;integratedSecurity=true;

[msaiz]

is this what you mean?

jdbc:sqlserver://USC1DDB64BDEV;databaseName=StJudeMedical_82_DW;integratedSecurity=true;

If so, this is the error: 

[djohnson53]

hozawa probably knows more about this, but try integratedSecurity=SSPI

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28VS.71%29.aspx

When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication. Recognized values are true, false, yes, no, and sspi (strongly recommended), which is equivalent to true.

Also, this is for .NET Framework 1.1, other versions may differ in behavior.

REF: http://stackoverflow.com/questions/1229691/difference-between-integrated-security-true-and-integrated-security-sspi

[msaiz]

If I use "=SSPI", I get the error that only "True" or "False" are valid.

[djohnson53]

The error in the screen shot says, "This driver is not configured for integrated authentication."

I found this discussion about what this error might mean:

http://blogs.msdn.com/b/psssql/archive/2015/01/09/jdbc-this-driver-is-not-configured-for-integrated-authentication.aspx

Google the error text. There are lots of other hits for this too.

[msaiz]

Yes - I read that too and installed "Microsoft JDBC Driver 4.2 for SQL Server" on my system.

 

Still getting the same error.

[hozawa]

I have a wiki page in Japanese on how to setup MS Sql Server 2014 to use with JasperReports Server. I've attached screenshot so you may be able to figure it out from looking at them. I've found the most problem is with SQL Server setup.

http://community.jaspersoft.com/wiki/ms-sql-servernodetaworepotodeshi-itai

 

Using Dynamic ports:

http://community.jaspersoft.com/wiki/dong-de-potowoshi-tutems-sql-servernijie-sok-sitai