Connection with SQL Server Azure

Hello.

We're trying to connect Nuxeo to a SQL Server Azure instance with the wizard configuration interface.The instance is OK and we have a private client connected to it. But Nuxeo failed to connect to that SQL instance. Here the exception in logs:

[http-bio-0.0.0.0-8080-exec-1] [org.nuxeo.wizard.RouterServlet] java.sql.SQLException: I/O Error: DB server closed connection.

It works on a local instance but not on an Azure instance. Here the wizard conf:

Database Type: MS SQL Server Database Name: MyDatabase Database user: user@myAzureSqlInstance Database Server host name: myAzureSqlInstance.database.windows.net Database server port: 1433

The port is openned.

0 votes

1 answers

3456 views

ANSWER

Copy URL

Florent Guillaume

Which Nuxeo version are you using? For a number of configurations you must use the Microsoft JDBC driver instead of the provided open source one. See NXP-10234 and NXP-10219 for more.

12/09/2013

AxelF

Ok we changed the connection string and we are using JDBC instead of JTDS driver. Now we have another problem: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Se cure Sockets Layer (SSL) encryption. Error: "java.security.cert.CertificateException: Failed to validate the server name in a certificate during Secure Sockets Layer (SSL) initialization. The server name is *.database.windows.net, the name in certificate is xxxx.". Our nuxeo version is 5.8

12/10/2013

AxelF

Ok we managed to make it work. As said before, we changed for the JDBC Driver, and set encrypt parameter in the connection string to false. Now we meet a new problem. When we're trying to get authentified, an error occured, saying that the table [user2group] must have an clustered index. Actually, it have an index, but non-clustered.

12/10/2013

AxelF

ok actually i have something like 30-40 exceptions… ie: "Authentication failed: close failed", "ERROR [Nuxeo-Work-default-1] [com.hp.hpl.jena.db.impl.PSet_TripleStore_RDB] SQLException caught during insert40054", "org.nuxeo.ecm.core.api.ClientException: Failed to get lock info on 042b0fcd-0f0e-4b94-9eb7-f9079b3f02c6"… What's wrong???

12/10/2013

Florent Guillaume

I don't know, I would need to see more detailed logs. But don't post them here but on a site like pastebin.

12/10/2013

AxelF

Here the logs when attempting to logging in: http://pastebin.com/bukWcgcs

12/11/2013

AxelF

I tried to restart the server with a clean database. Here are the whole logs: http://pastebin.com/QfAASCd5

12/11/2013

AxelF

Ok. Each time the server is starting after several minutes it says: "Starting process is taking too long - giving up".

12/12/2013

AxelF

Ok we managed solve some exceptions. But the "starting process is taking to long" is still here. And when we're trying to logging in: http://pastebin.com/KR1sdJ1E

A table seems to have a non-clustered index, but which one?

12/12/2013

Florent Guillaume

It's happening in the Jena code (used for relations on old Nuxeo versions and still checked for compatibility reasons in recent versions) so the tables must be some of the JENA_* tables.

12/12/2013

AxelF

The problem could be that I don't have any table containing JENA in table_name. There are 153 tables.

12/13/2013

AxelF

We're still stucked with this Azure SQL database problem. We can't get it…

12/18/2013

ANSWERS

First, for Azure the Microsoft JDBC driver (sqljdbc4.jar) should be used as the open source jTDS driver can't manage to authenticate with Azure.

The tables user2group (and maybe group2group if you use groups of groups) are indeed created without clustered indexes. You may want to recreate them with the proper clustered index:

DROP INDEX [groupid_idx] ON [user2group];
DROP INDEX [parentgroupid_idx] ON [group2group];
CREATE CLUSTERED INDEX [groupid_idx] ON [user2group];
CREATE CLUSTERED INDEX [parentgroupid_idx] ON [group2group];

0 votes

Copy URL

Authentication Required

Connection with SQL Server Azure

Invite people to answer

What's new in Quandora?