CODEWITHSUNDEEP
sql-serverhibernatesql-server-2014mssql-jdbcsqljdbc
user429137
user429137

Reputation:

Why is default schema being ignored when using SQL Server integrated security?

I have migrated a web application running on Wildfly 9 from using SQL authentication to using windows authentication. I also need to update a java utility that does some maintenance tasks directly on the database. The web application works fine, but the utility seems to ignore the default schema setting in the hibernate configuration. Tracing on SQL Server shows the queries coming from wildfly have myschema.<table name> but the queries from my utility have just <table name>.

There is a login (DOMAIN\myservice) on the SQL Server for the domain account that wildfly service runs under, and I run the util as the same account.

The login is mapped to a user on the database with the same name (DOMAIN\myservice).

The user has a default schema of dbo.

util-hibernate.cfg.xml

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.SQLServer2008Dialect</property>
        <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
        <property name="hibernate.connection.url">jdbc:sqlserver://sqlhost\INSTANCE:1433;databaseName=MYAPP;integratedSecurity=true</property>
        <property name="hibernate.connection.pool_size">5</property>
        <property name="hibernate.default_schema">myschema</property>
        <property name="hibernate.show_sql">true</property>

        <mapping resource="....hbm.xml"/>
        <mapping resource="...hbm.xml"/>
        ...
    </session-factory>
</hibernate-configuration>

war-hibernate.cfg.xml

<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.datasource">jdbc/MYAPP</property>
        <property name="hibernate.default_schema">myschema</property>

        <mapping resource="....hbm.xml"/>
        <mapping resource="...hbm.xml"/>
        ...
    </session-factory>
</hibernate-configuration>

wildfly datasource

<datasource jndi-name="java:jdbc/MYAPP" pool-name="MyDataSource" enabled="true">
    <connection-url>jdbc:sqlserver://sqlhost\INSTANCE;databaseName=MYAPP;integratedSecurity=true</connection-url>
    <driver>sqljdbc4.jar</driver>
    <pool>
        <max-pool-size>10</max-pool-size>
    </pool>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker" />
        <validate-on-match>false</validate-on-match>
        <background-validation>true</background-validation>
        <background-validation-millis>30000</background-validation-millis>
    </validation>
</datasource>

In the logging for my utility I see:

SettingFactory:222 - Default schema: myschema

But further down I see the error:

com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '<table name>'

Where the table is in myschema.

I have tried updating hibernate mapping files with:

<hibernate-mapping schema="myschema">
    <class name="MyType" table="table_name">

And 

<hibernate-mapping>
    <class name="MyType" schema="myschema" table="table_name">

But that didn't work either.

Versions of things

Upvotes: 2

Views: 1812

Answers (1)

user429137
user429137

Reputation:

It turns out the utility was using a SQLQuery that I hadn't noticed and it seems hibernate does not append the default schema name to that automatically.

Upvotes: 1

Related Questions