Publishing fail on SDL Web 8.5 Content Delivery with Azure SQL

Question

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)

Answer 1

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

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.