Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Microsoft Dynamics CRM 4.0 - Importing Organization fails with "The connection's state is closed"
Article ID: 2003564 - View products that this article applies to.
When you import a Microsoft Dynamics CRM 4.0 organization, you may experience a timeout or a hang in the MMC console window hosting the Microsoft Dynamics CRM Deployment Manager.
The Microsoft CRM Deployment Manager window may show up as a white screen during this time and Windows Task Manager will show the program "Deployment Manager" as "Not Responding". This may even happen if you have increased the OLEDBTimeout setting for Microsoft Dynamics CRM 4.0 to a value greater than when the error was experienced.
If you enable platform tracing, using the steps in KB 907490, How to enable tracing in Microsoft Dynamics CRM, http://support.microsoft.com/default.aspx?scid=kb;EN-US;907490 , you will see an error like the following in the platform trace files (Note that the default log for Importing an organization will be located by default in the %appdata%\Microsoft\MSCRM\Logs\Import.log file for the user who installed Microsoft Dynamics CRM 4.0.
19:03:33| Error| Import Organization (Name=Test2, Id=fb88688b-46a2-de11-8a64-0013724e5a05) failed with Exception:
System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method)
at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command)
at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate)
at Microsoft.Crm.CrmDbConnection.SetTransactionIsolationLevel(IsolationLevel il)
at Microsoft.Crm.CrmDbConnection.Dispose(Boolean disposing)
at Microsoft.Crm.SqlExecutionContext.Dispose(Boolean disposing)
at Microsoft.Crm.Tools.Admin.ImportOrganizationInstaller.Import(Guid organizationId, String organizationUniqueName, String organizationFriendlyName, String sqlServerName, String databaseName, Uri reportServerUrl, String privilegedUserGroupName, String sqlAccessGroupName, String userGroupName, String reportingGroupName, String privilegedReportingGroupName, ICollection`1 users, MultipleTenancy multipleTenancy)
You may also see the following error messages logged indicating SQL Server timeouts or indicating that all SQL pooled connections are in use:
"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."
This is caused by a race condition in the internal call of the GetConnection method in Microsoft .NET Framework 2.0 that has been addressed in a Hot Fix for Microsoft .NET Framework 2.0.
Check the system.data.dll file properties on the Microsoft Dynamics CRM server in the %windir%\Microsoft.Net\Framework\v2.0.50727 for x86 servers and %windir%\Microsoft.Net\Framework64\v2.0.50727 if the Microsoft Dynamics CRM server is installed on x64 Windows Server. Verify if the version is less than 2.0.50727.1813. If it is less than 2.0.50727.1813, then install the hotfix listed in the KB article below.
To resolve this problem, install the hotfix that is mentioned in the following article in the Microsoft Knowledge Base:
948815 Availability of the .NET Framework 2.0 post-Service Pack 1 hotfix rollup package for System.Data.dll and System.Data.OracleClient.dll
You must have the .NET Framework 2.0 Service Pack 1 installed to apply this hotfix.
2. Install the latest Microsoft CRM 4 Hotfix (Update Rollup 8 or later) and add the OptimizeOrgImport registry key value documented in the following KB article: http://support.microsoft.com/kb/977867
You do not have to restart the computer after you apply this hotfix.
SECONDARY RESOLUTION - Note that these steps may need to be performed in addition to the application of the hotfix listed above.
If you have a Microsoft CRM Dynamics 4.0 organization database that is larger than 5 GB, you may need to increase the OLEDBTimeout setting in order for the organization import to complete successfully. After the import of the organization has completed you should either delete this registry key or set the value of it between 60 to 300 decimal to prevent long running Microsoft Dynamics CRM queries from causing SQL Server blocking. You can do this by adding the following REG_DWORD key on the Microsoft Dynamics CRM 4.0 server by using the directions below:
1. On the Microsoft Dynamics CRM 4.0 Server, click Start, click Run, type regedit, and then click OK.
2. Locate the following registry subkey: HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM.
3. If the OLEDBTimeout key does exist, skip to step 5.
4. If the OLEDBTimeout key does not exist, create a new one:
a. Right-click MSCRM, click New, and then click DWORD Value.
b. Type OLEDBTimeout for the name.
5. Right click OLEDBTimeout, choose modify, enter 86400 in Value data, and then mark the radio button next to Decimal. This will represent 86,400 seconds, which is equal to 24 hours.
6. After the import organization process has completed successfully, change the value of HKEY_LOCAL_MACHINE\Software\Microsoft\MSCRM\OLEDBTimeout to a value between 60 and 300 decimal. If you are not sure of what value to set this to, set it to 30 decimal, which is the default.
Note that you may also need to increase the maximum number of TCP ephemeral ports available via the instructions below:
To resolve this problem, increase the number of TCP ports that are available on both the Microsoft Dynamics CRM and the Microsoft SQL Servers. Then, reduce the length of time that these TCP ports must wait before they can be re-used. To do this, modify the MaxUserPort registry setting and the TcpTimedWaitDelay registry setting as follows. This will increase the maximum ephemeral TCP ports to 65534 and reduce the time these ports are recycled to once every 30 seconds compared to once every four minutes.
To create this entry, follow these steps:
1. Click Start, click Run, type regedit, and then click OK.
2. Locate and then click the following registry subkey: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters
3. Right click Parameters, point to New, click DWORD Value, and then type MaxUserPort.
4. Right click MaxUserPort, click Modify, and then type 65534 for Value data, and set the radio button next to Decimal.
Note: This value controls the number of dynamic ports that are available. The valid range for this value is 5,000-65,534. Specifically, this parameter controls the maximum port number that is used when a program requests any available user port from the system.
5. Locate the following registry subkey: HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Tcpip\Parameters
6. Right click Parameters, point to New, click DWORD Value, and then type TCPTimedWaitDelay.
7. Right click TCPTimedWaitDelay, click Modify, type 30 for the Value data, and set the radio button next to Decimal.
Note: This parameter determines the length of time that a connection stays in the TIME_WAIT state when it is closed. When a connection is in the TIME_WAIT state, the socket pair cannot be re-used. This is also known as the "2MSL" state. Typically, this value is two times the maximum segment life time on the network.
8. Exit Registry Editor.
9. Restart the Microsoft Dynamics CRM server.
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.