In my recent past, I have helped around 10 customers who have had similar problems while installing SQL Server Cluster on windows. So I thought it would be a nice idea to pen it down as a blog post so that it can help others in future. In this blog post we will discuss about Installation Fails With Error – A Constraint Violation Occurred.
The issue which we saw was that SQL Server cluster setup would create a network name resource in failover cluster manager and it would fail. Here is the message which we would see in the setup
The cluster resource ‘SQL Server (SQLSQL)’ could not be brought online due to an error bringing the dependency resource ‘SQL Network Name (SAPSQL)’ online. Refer to the Cluster Events in the Failover Cluster Manager for more information.
Click ‘Retry’ to retry the failed action, or click ‘Cancel’ to cancel this action and continue setup.
When we look at event log, we saw below message (event ID 1194)
Log Name: System
Date: 20/06/2016 19:55:45
Event ID: 1194
Task Category: Network Name Resource
Cluster network name resource ‘SQL Network Name (SAPSQL)’ failed to create its associated computer object in domain ‘internal.sqlauthority.com’ during: Resource online.
The text for the associated error code is: A constraint violation occurred.
Please work with your domain administrator to ensure that:
– The cluster identity ‘WINCLUSTER$’ has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘WINCLUSTER$’.
– The quota for computer objects has not been reached.
– If there is an existing computer object, verify the Cluster Identity ‘WINCLUSTER$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.
Another client got “Access is denied” messages instead of “A constraint violation occurred” in above event ID. My clients have informed that they have logged in as domain admin so Access denied is impossible. Error from another client is below.
I explained all of them that when network name is created in a cluster, it would contact active directory (AD) domain controller (DC) via Windows Cluster Network name computer account also called as CNO (Cluster Network Object). So, whatever error, we are seeing are possible because the domain admin account (windows logged in user account) is not used to create a computer object for SQL in AD.
To solve this problem, we logged into the domain controller machine and created the Computer Account: SAPSQL (called as VCO – Virtual Computer Object). Gave the cluster name WINCLUSTER$ full control on the computer name. If we carefully read error message, we have the solution already listed there. Then clicked on the retry option in the setup. The setup continued and completed successfully.
Here are the detailed steps (generally done on a domain controller by domain admin):
- Start > Run > dsa.msc. This will bring up the Active Directory Users and Computers UI.
- Under the View menu, choose Advanced Features.
- If the SQL Virtual Server name is already created, then search for it else go to the appropriate OU and create the new computer object [VCO] under it.
- Right click on the new object created and click Properties.
- On the Security tab, click Add. Click Object Types and make sure that Computers is selected, then click Ok.
- Type the name of the CNO and click Ok. Select the CNO and under Permissions click Allow for Full Control permissions.
- Disable the VCO by right clicking.
This is also known as pre-staging of the VCO.
Hope this would help someone to save time and resolve issue without waiting for someone else assistance. Do let me know if you ever encountered the same.
Reference: Pinal Dave (https://blog.sqlauthority.com)