Connection Issue When Dynamically Publishing to a Tridion Broker Database with an Instance Name (JDBC)?

Question

The commit phase always fails with this error:

Committing Deployment Failed
Phase: Deployment Prepare Commit Phase failed, Unable to prepare transaction: tcm:0515104-66560,
org.hibernate.exception.JDBCConnectionException: Cannot open connection,
org.hibernate.exception.JDBCConnectionException: Cannot open connection, Unable to prepare transaction: tcm:0-515104-66560, 
org.hibernate.exception.JDBCConnectionException: Cannot open connection,
org.hibernate.exception.JDBCConnectionException: Cannot open connection

This is the configuration that works in databases with default instances (DEV/UAT):

<Storage Type="persistence" Id="brokerdb" dialect="MSSQL" Class="com.tridion.storage.persistence.JPADAOFactory">
            <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" />
            <DataSource Class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
                <Property Name="serverName" Value="ourServerName" />
                <Property Name="portNumber" Value="1433" />
                <Property Name="databaseName" Value="Tridion_Broker" />
                <Property Name="user" Value="TridionBrokerUser" />
                <Property Name="password" Value="xxxxxxxxpassxx" />
            </DataSource>
        </Storage>

However, for our production, using a named instance is inevitable. So we tried this configuration to pass the instance's name but to no avail; we still get the error.

<Storage Type="persistence" Class="com.tridion.storage.persistence.JPADAOFactory" 
    Id="brokerdb"
    Url="jdbc:sqlserver://ourServerName/Tridion_Broker;instanceName=THE_INSTANCE_NAME;domain=DOMAIN_NAME" 
    Username="TridionBrokerUser" 
    Password="xxxxxxxxpassxx" 
    Driver="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
    <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120"/>
</Storage>

Is there anything wrong with the connection string? Or is there a way to pass the instance name using the first pattern; say <Property Name="instanceName" Value="THE_INSTANCE_NAME" /> for example?

score 6 · Accepted Answer · answered Oct 09 '12 at 06:13

Both Nikoli and Gertjan's reference made me realize that the instance name is not required. An alternative is to specify the port to which the instance is running on.

This article showed me how to know which port is being used for the instance.

This configuration worked:

<Storage Type="persistence" Id="brokerdb" dialect="MSSQL" Class="com.tridion.storage.persistence.JPADAOFactory">
        <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" />
        <DataSource Class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
            <Property Name="serverName" Value="ourServerName" />
            <Property Name="portNumber" Value="43333" />
            <Property Name="databaseName" Value="Tridion_Broker" />
            <Property Name="user" Value="TridionBrokerUser" />
            <Property Name="password" Value="xxxxxxxxpassxx" />
        </DataSource>
    </Storage>

I also tried the connection string approach and it worked, too:

<Storage Type="persistence" Class="com.tridion.storage.persistence.JPADAOFactory" 
    dialect="MSSQL" 
    Id="brokerdb" 
    Url="jdbc:sqlserver://ourServerName:43333;databaseName=Tridion_Broker;" 
    Username="TridionBrokerUser" Password="xxxxxxxxpassxx" 
    Driver="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
    <Pool Type="jdbc2" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120"/>
</Storage>

If any of the below responses answered your question, could you please accept that as an answer so it will be helpful for other community members. — Ram G, Oct 10 '12 at 03:25

score 3 · Answer 2 · answered Oct 08 '12 at 11:50

3

You can try the following:

<Property Name="serverName" Value="ourServerName\ourInstanceName" />

answered Oct 08 '12 at 11:50

Puntero

2,310
13
18

score 2 · Answer 3 · edited Oct 09 '12 at 04:02

2

Your original syntax of specifying the URL is correct, however, upon checking the documentation, I can't seem to find a valid property called "domain". This may be your culprit. Try specifying the domain in the username using a '\'.

edited Oct 09 '12 at 04:02

Ianthe the Duke of Nukem

1,581
2
19
37

answered Oct 08 '12 at 12:33

Nickoli Roussakov

3,374
14
22

You're right, domain is not needed in our case. It is integral to the solution. Thank you. – Ianthe the Duke of Nukem Oct 09 '12 at 03:28

score 2 · Answer 4 · answered Oct 08 '12 at 13:55

2

According to this msdn article: http://msdn.microsoft.com/en-us/library/ms378428.aspx

you should configure it like this:

jdbc:sqlserver://[serverName[\instanceName][:portNumber]][;property=value[;property=value]]

answered Oct 08 '12 at 13:55

Gertjan Assies

1,880
13
23

also you seem to be missing the dialect="MSSQL" attribute on the Storage element – Gertjan Assies Oct 08 '12 at 14:27

Siva Charan · Answer 5 · 2012-10-08T21:27:24.890

You should combine both server name and instance name.

<Storage Type="persistence" Id="brokerdb" dialect="MSSQL" Class="com.tridion.storage.persistence.JPADAOFactory">
    <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120" />
    <DataSource Class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
        <Property Name="serverName" Value="ourServerName\\THE_INSTANCE_NAME" />
        <Property Name="portNumber" Value="1433" />
        <Property Name="databaseName" Value="Tridion_Broker" />
        <Property Name="user" Value="TridionBrokerUser" />
        <Property Name="password" Value="xxxxxxxxpassxx" />
    </DataSource>
</Storage>

Another way, I have changed URL little bit for this old representation

<Storage Type="persistence" Class="com.tridion.storage.persistence.JPADAOFactory" Id="brokerdb" Url="jdbc:sqlserver://ourServerName;instanceName=THE_INSTANCE_NAME;databaseName=Tridion_Broker;integratedSecurity=true;" Username="TridionBrokerUser" Password="xxxxxxxxpassxx"    Driver="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
    <Pool Type="jdbc" Size="5" MonitorInterval="60" IdleTimeout="120" CheckoutTimeout="120"/>
</Storage>

Try these URL for the above Old representation:-

jdbc:sqlserver://ourServerName\\THE_INSTANCE_NAME;databaseName=Tridion_Broker;integratedSecurity=true;
jdbc:sqlserver://ourServerName\THE_INSTANCE_NAME;databaseName=Tridion_Broker;integratedSecurity=true;
jdbc:sqlserver://ourServerName;instanceName=THE_INSTANCE_NAME;databaseName=Tridion_Broker;integratedSecurity=true;
jdbc:sqlserver://ourServerName/Tridion_Broker;instanceName=THE_INSTANCE_NAME;domain=DOMAIN_NAME;

For your references:-

The `` didn't work. This is the same as Puntero's answer. As for the "connection string" approach, #2 worked without the `integratedSecurity` parameter. — Ianthe the Duke of Nukem, Oct 09 '12 at 06:08

Connection Issue When Dynamically Publishing to a Tridion Broker Database with an Instance Name (JDBC)?

5 Answers5