Connecting to Azure SQL Server using Hibernate JDBC connection

Question

Hibernate connection works well with local postgresql and it automatically creates tables, but when we want to use Azure SQL Server (Microsoft SQL Server) and we change driver name etc., we didn't get any error but it didn't work. (didn't create tables)

jdbc.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbc.databaseurl=jdbc:sqlserver://xxxx.database.windows.net:1433;database=xxxx
jdbc.password=xxxx
jdbc.user=xxx@xxxx
jdbc.encrypt=true
jdbc.hostNameInCertificate=*.database.windows.net
jdbc.loginTimeout=30;
jdbc.dialect=org.hibernate.dialect.SQLServerDialect

<bean id="propertyConfigurer"
      class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer"
      p:location="/WEB-INF/jdbc.properties" >
</bean>

<bean id="dataSource"
    class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"
    p:driverClassName="${jdbc.driverClassName}"
    p:url="${jdbc.databaseurl}" p:username="${jdbc.user}"
    p:password="${jdbc.password}">
</bean>

<bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="packagesToScan">
        <list>
            <value>com.xxx.entity</value>
        </list>
    </property>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${jdbc.dialect}</prop>
            <prop key="hibernate.c3p0.min_size">5</prop>
            <prop key="hibernate.c3p0.max_size">20</prop>
            <prop key="hibernate.c3p0.idle_test_period">60</prop>
            <prop key="hibernate.c3p0.max_statements">100</prop>
            <!--<prop key="hibernate.hbm2ddl.auto">${database.hbm2ddl.auto}</prop>-->
            <!--<prop key="hibernate.show_sql">${database.show_sql}</prop>-->
            <!--<prop key="hibernate.format_sql">${database.format_sql}</prop>-->
        </props>
    </property>
</bean>

@ManuPK has the right recommendations. Can you share the format of the connection string? Here is an example: jdbc:sqlserver://dp-test1.database.windows.net:1433;databaseName=test1;user=admin@dp-test1;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=.database.windows.net;loginTimeout=30; Cheers, <br>Meet — meet-bhagdev
– meet-bhagdev, Commented Feb 26, 2016 at 23:40

ManuPK · Accepted Answer · 2016-02-26 15:16:18Z

The problem is in the jdbc connection url, property database=test1 should be databaseName=test1. Read the JDBC specifications and SQL Server Driver spec for more details on this.

When the databaseName is not found in the connection url, the driver tries to write to master db, which is not allowed in Azure SQL Server.

I got the permission issue in connecting to Azure SQL Server via JDBC driver in a spring hibernate application with the below logs

2016-02-26 19:22:37.958 ERROR 11424 --- [lication.main()] org.hibernate.tool.hbm2ddl.SchemaExport : HHH000389: Unsuccessful: create table users (id numeric(19,0) identity not null, email varchar(255) not null, name varchar(255) not null, primary key (id)) 2016-02-26 19:22:37.964 ERROR 11424 --- [lication.main()] org.hibernate.tool.hbm2ddl.SchemaExport : CREATE TABLE permission denied in database 'master'.

Azure portal gives you the auto-created connection url (which is pretty cool), I got one as below,

jdbc:sqlserver://dp-test1.database.windows.net:1433;database=test1;user=admin@dp-test1;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=.database.windows.net;loginTimeout=30;*

As you can see, there is an issue in this url.The property database=test1 should be databaseName=test1. I will update the Azure Team about this and let's hope they will fix it soon.

I got my local application connected to Azure SQL Server DB after the changes.

Adam · Accepted Answer · 2014-04-24 14:24:43Z

0

Azure SQL is actually quite different from a local MS SQL Server and has specific requirements.

From no error message I am taking a stab in the dark but check that you have a clustered index on each table being created. It is a requirement on Azure.

answered Apr 24, 2014 at 14:24

Adam

16.2k7 gold badges68 silver badges112 bronze badges

Collectives™ on Stack Overflow

Connecting to Azure SQL Server using Hibernate JDBC connection

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related