3

Deployer log

C:\SDL\Web\log\deployer-combined\cd_core.2017-07-25.log

2017-07-25 10:22:26,669 ERROR SqlExceptionHelper - 
Cannot create PoolableConnectionFactory (Cannot open server "51.140.184.11"
requested by the login.  The login failed. 
ClientConnectionId:e5d82acd-29ac-4b06-bf43-d9381f845cb3)
5

This was initially a bit of a mystery until I checked the IP of the azure SQL server.

Googling for “azure sql cannot create PoolableConnectionFactory” I came across “Failed to Create PoolableConnectionFactory When Connecting to Azure SQL Server Database from Spring / Hibernate Tomcat Application” on MSDN with a recommendation to use user=username@servername in the connection string.

So I checked on Azure for the recommended JDBC connection string enter image description here

Resolution

Edit C:\SDL\Web\deployer-combined\config\deployer-conf.xml

<State>
    <Storage Adapter="mssql" driver="com.microsoft.sqlserver.jdbc.SQLServerDriver">
        <Property Name="host" Value="azurename.database.windows.net"/>
        <Property Name="port" Value="1433"/>
        <Property Name="database" Value="Tridion_Broker"/>
        <Property Name="user" Value="TridionBrokerUser@azurename"/>
        <Property Name="password" Value="********"/>
    </Storage>
</State>

Edit C:\SDL\Web\deployer-combined\config\cd_storage_conf.xml

<Storage Class="com.tridion.storage.persistence.JPADAOFactory" Id="defaultdb" Type="persistence" dialect="MSSQL">
    <Pool CheckoutTimeout="120" IdleTimeout="120" MonitorInterval="60" Size="10" Type="jdbc"/>
    <DataSource Class="com.microsoft.sqlserver.jdbc.SQLServerDataSource">
        <Property Name="serverName" Value="azurename.database.windows.net"/>
        <Property Name="portNumber" Value="1433"/>
        <Property Name="databaseName" Value="Tridion_Broker"/>
        <Property Name="user" Value="TridionBrokerUser@azurename"/>
        <Property Name="password" Value="*********"/>
    </DataSource>
</Storage>

PowerShell UpdateConfJDBC.ps1 ,

function UpdateConfJDBC()
{
<#
.SYNOPSIS
 Update SDL Microservice JDBC Configuration 

.DESCRIPTION
Set JDBC name in the cd_storage-config.xml files for all SDL
Microservices in the SDLWeb path
The default cd_storage_conf.xml jdbc "user" must be changed to "user@azuredbname" 
otherwize connection login fails with error 
"sql cannot create PoolableConnectionFactory"
see 
https://tridion.stackexchange.com/questions/17586/publishing-fail-on-sdl-web-8-5-content-delivery-with-azure-sql 

.EXAMPLE
 UpdateConfJDBC -filename "C:\SDL\Web\deployer-combined\config\cd_storage_conf.xml" -Verbose

 .EXAMPLE
 UpdateConfJDBC -filename "C:\SDL\Web\deployer-combined\config\deployer-conf.xml" -Verbose


 #>
param(
    [Parameter(Mandatory=$true, ValueFromPipeline=$true)]
    $filename
)

        Write-Verbose "Update Conf JDBC Processing $($filename)" 
        $Conf = New-Object System.Xml.XmlDocument
        $Conf.Load($filename)

        $state = $Conf.Deployer.State
        if ($state)
        {
            $user = $state.Storage.Property | Where-Object { $_.name -eq 'user' }
            $sqlhost = $state.Storage.Property | Where-Object { $_.name -eq 'host' }
            if ($sqlhost.value -like "*.database.windows.net")
            {
                $dbname = $sqlhost.value.split(".")[0]
                $user.value = [string]::Format("{0}@{1}", $user.value.split("@")[0],$dbname)
                Write-Verbose "    Updated Deployer.State.Storage.Property.user.value to $($user.value)"
                $Conf.Save($filename) 
            }
        }


        $Storage = $Conf.Configuration.Global.Storages.Storage | Where-Object { $_.Class -like 'com.tridion.storage.persistence.JPADAOFactory' }

        if ($Storage)
        {
            $user = $Storage.DataSource.Property | Where-Object { $_.name -eq 'user' }
            $sqlhost = $Storage.DataSource.Property | Where-Object { $_.name -eq 'serverName' }
            if ($sqlhost.value -like "*.database.windows.net")
            {
                $dbname = $sqlhost.value.split(".")[0]
                $user.value = [string]::Format("{0}@{1}", $user.value.split("@")[0],$dbname)
                Write-Verbose "    Updated Configuration.Global.Storages.Storage.DataSource.Property.user.value to $($user.value)"
                $Conf.Save($filename) 
            }
        }

}

I hope this helps you to move your solutions to use Azure SQL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.