Unable to create subscription/domain: IP address does not exist

Symptoms
An attempt to create a subscription / domain fails with the following error:
Error: Unable to find service node for ip address with id=2
Error: Failed domain creation: Unable to update domain data: IP address with ID ‘2’ does not exist
It is not possible to manage Web Hosting Access. An attempt fails with the following error:
Error: Unable to find service node for ip address with id=2

Cause

Database inconsistency.
Resolution

Create a backup of the psa database:


# mysqldump -uadmin -p`cat /etc/psa/.psa.shadow` psa  > /root/psa.sql

Check if the IP_Addresses table has record(s) with an IP address that does not exist in the ip_pool tables:


    mysql> select * from ip_pool where ip_address_id not in (select id from IP_Addresses);
    +----+---------------+--------+
    | id | ip_address_id | type   |
    +----+---------------+--------+
    |  1 |             2 | shared |
    +----+---------------+--------+
    1 row in set (0.00 sec)


    mysql> select * from IP_Addresses where id =2;
    Empty set (0.00 sec)


    mysql> select id, serviceNodeId, ip_address from IP_Addresses;
    +----+---------------+--------------+
    | id | serviceNodeId | ip_address   |
    +----+---------------+--------------+
    |  1 |             1 | x.x.x.x      |
    +----+---------------+--------------+
    1 rows in set (0.00 sec)

As can be seen in this example, the ip_pool table includes an entry regarding a nonexistent IP address ( ip_address_id = 2);

Delete this record from the ip_pool table:


    mysql> delete from ip_pool where ip_address_id=2;
    Query OK, 1 row affected (0.00 sec) 

You experience the following error message:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

error

Instrucions to enable MSSQL remote connecton.

Run SQL Server Configuration Manager.
start

Protocols for MSSQLServer

The next good thing to check is the SQL Server Network Configuration. Open the SQL Server Configuration Manager, unfold the node SQL Server Network Configuration and select Protocols for MSSQLServer (or whatever the name of your SQL Server instance is).

Go to SQL Server Network Configuration > Protocols for SQLEXPRESS.

tcp

Make sure TCP/IP is enabled. Right-click on TCP/IP and select Properties.

enable

Now:

Select the IP Addresses Tab. Verify that, under IP2, the IP Address is set to the computer’s IP address on the local subnet.

ip
Scroll down to IPAll. Make sure that TCP Dynamic Ports is blank. Make sure that TCP Port is set to 1433.

ipall
Apply and Click OK.

Make Sure the SQL Server Browser is running. Go to Services and set the start to automatic. Then start the service.

Add a firewall rule to allow port 1433

fw

Make user Remote Connections are enabled on your SQL Server database.

Open SQL Server 2008 Management Studio, connect to the server in question, right click the server.

prop

Navigate to Connections and ensure that Allow remote connections to this server is checked.

remote

Other resources:

http://blogs.msdn.com/b/walzenbach/archive/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008.aspx
https://msdn.microsoft.com/en-us/library/ms191464.aspx