¶ Microsoft SQL Server Login - Connection failed: Unable to complete login process due to delay in opening server connection
When attempting to connect to a Microsoft SQL Server instance through a client application, if you receive an error stating Unable to complete login process due to delay in opening server connection or similar, it is highly likely that the SQL instance's TCP port is blocked via a firewall on the server PC. It could also be caused by the SQL Server Browser service being blocked on the server PC too. There are many other possible causes of this and other related issues, though for this article we will focus on the firewall settings for (built in to Windows) Windows Defender.
To gain a little more confirmation, open the ODBC Data Sources (32-bit) app and create a user DSN to test the connection to the instance in question.
For more details on how to set up the test connection, please contact LEMR Support.
Once the test connection is set up, run a connection test. You may receive an error as follows:
The next step is to add the firewall exceptions to the server PC hosting the Microsoft SQL Database instance. For simplicity, it is best to add exceptions for the SQL Server Browser executable path and the SQL Server (InstanceNameHere) executables.
If you are using another firewall application other than Windows Defender, please contact LEMR Support.
1. Find the executable path by opening the Services management snap-in then open the properties of both services you need. Copy the path including the .exe (though not including the command line arguments or opening/closing quotation marks) from the Path to executable section on the properties form.
2. Open Windows Defender Firewall with Advanced Security
3. Click on Inbound Rules then click New Rule…
4. When the New Inbound Rule Wizard appears, choose Program then click Next >
5. Copy the first of the executable paths gathered from step 1 into the This program path field and click Next >
6. Choose Allow the connection then click Next >
7. On the When does this rule apply screen, tick only the scopes that you need this firewall to apply for. Then click Next >
Computers part of a Windows Domain should safely be able to use the Domain scope only.
Try to limit this as much as is practical though beware that non-domain networks may include Private and Public networks depending on how it is configured on the client PCs.
8. Set a useful name for the firewall rule based on which executable you are adding the rule for, such as SQL Browser Service Inbound Rule or SQL Server (InstanceNameHere) Inbound Rule so you can find it later if required. Then click Finish.
9. Repeat steps 3 to 8 for each of the executable paths gathered from step 1
10. Now test the application from the client side. If there was a software firewall issue this may have solved the problem.
Other possible causes of connectivity failure to a Microsoft SQL Instance include though are not limited to:
- TCP/IP and/or Named Pipes being disabled for the instance (use the SQL Server Configuration Manager snap-in to enable these protocols where necessary)
- Other networking and network related issues
- Permissions to access the instance and/or database within the instance
- SQL Browser or Instance services not running
- The client application is not pointing to the correct instance or database
- And many more
We can help! If you need assistance, please contact LEMR Support.
¶ SQL Server Setup Error - Attempted to perform an unauthorized operation (error code 0x84BB0001) when Webroot Antivirus is installed on the PC
For 64 bit Microsoft SQL Server instances, check in the following log to confirm:
C:\Program Files\Microsoft SQL Server\[version]\Setup Bootstrap\Log\Summary.txt
Log file location (for SQL 2012): C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log
Log file location (for SQL 2014): C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log
Look for the following:
Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
Message:
Attempted to perform an unauthorized operation.
HResult : 0x84bb0001
FacilityCode : 1211 (4bb)
ErrorCode : 1 (0001)
Data:
WatsonData = Uninstall@{4FF97BB8-1018-4FBD-B28E-D30A5F211ED8}
DisableRetry = true
DisableWatson = true
Stack:
at Microsoft.SqlServer.Configuration.Sco.InternalRegistryKey.OpenSubKey(String subkey, RegistryAccess requestedAccess)
at Microsoft.SqlServer.Configuration.Sco.SqlRegistryKey.OpenSubKey(String subkey, RegistryAccess requestedAccess)
at Microsoft.SqlServer.Configuration.MsiExtension.ArpRegKey.CleanupPatchedProductRegistryInfo()
at Microsoft.SqlServer.Configuration.MsiExtension.SetPatchInstallStateAction.ExecuteAction(String actionId)
at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.<>c__DisplayClasse.<ExecuteActionWithRetryHelper>b__b()
at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(ActionWorker workerDelegate)
Inner exception type: System.UnauthorizedAccessException
Message:
Attempted to perform an unauthorized operation.
HResult : 0x80070005
Stack:
at Microsoft.SqlServer.Configuration.Sco.InternalRegistryKey.OpenSubKey(String subkey, RegistryAccess requestedAccess)To confirm, go into the following related detailed error file:
C:\Program Files\Microsoft SQL Server\[version]\Setup Bootstrap\Log\[date back-to-front]_[HHMMSS]\Detail.txt
Do a search for the following string:
4FF97BB8-1018-4FBD-B28E-D30A5F211ED8Then immediately below this line found in the file, there will be a line that reads as follows:
(01) YYYY-MM-DD HH:MM:SS Slp: Error: Action "Microsoft.SqlServer.Configuration.MsiExtension.SetPatchInstallStateAction" threw an exception during execution.This issue is caused by Webroot Antivirus's self protection mechanism. When SQL Server attempts to install, it will check the versions of applications installed on the system by reading through the keys in the Uninstall area of the Windows registry. When attempting to read this Webroot uninstall key, Microsoft SQL Server's installer will be locked out and will throw this error.
To resolve the problem, please perform the following action:
- Set Webroot into unmanaged mode for the PC with the SQL installation issue. This is required when Webroot SecureAnywhere is managed by the Webroot cloud portal.
- Uninstall Webroot Antivirus then reboot and try installation again. You can install Webroot again after the SQL Server installation is complete.
A method to remove Webroot (when the web console method doesn’t work):
- Reboot in safe mode with networking
- Log on as admin
- Open an elevated command prompt
- Run the following command:
For 64 bit systems: "C:\Program Files (x86)\Webroot\WRSA.exe" -uninstall
For 32 bit systems: "C:\Program Files\Webroot\WRSA.exe" -uninstall - Click Yes to remove Webroot. You may need to enter a captcha text to complete the removal. Wait a minute for the install to complete (there will be no confirmation).
- Rename the folder C:\Program Files\Webroot\ to C:\Program Files\zzWebroot\
- Reboot in normal mode. Webroot will not come back (unless an Remote Management and Monitoring – RMM – application auto installs it).
Another possible solution is temporarily enabling the Silent Audit endpoint policy:
https://answers.webroot.com/webroot/ukp.aspx?pid=17&app=vw&vw=1&solutionid=1897
Silent Audit is a default endpoint policy that comes pre-configured in the Webroot Management console. It is intended as a testing policy, to be used when deploying and testing the Webroot agent to see how it may impact a system or application. It is designed to report threats and not take action on them, helping administrators identify any unidentified software so allow overrides can be added.
It is not recommended to set this policy as the default policy for a Site. This is because it will not automatically remediate all detected threats and if assigned to a device by mistake can result in an infection. Instead, assign it to a limited number of devices and once the evaluation is complete, change the policy back to the standard for that device type.
We have had some success using the Silent Audit policy to allow the SQL Server installer to proceed. However, care must be taken to make sure that the Silent Audit policy is disabled after installing SQL Server.
Other useful Webroot uninstallation resource:
https://answers.webroot.com/Webroot/ukp.aspx?pid=1&app=vw&vw=1&solutionid=1101
¶ SQL Server Setup Error - Attempted to perform an unauthorized operation (error code 0x84BB0001) - Other causes
In addition to Webroot Antivirus causing the unauthorized operation error when installing Microsoft SQL Server (the more common cause of this issue at the time of writing; article above), this error can also occur while installing Microsoft SQL (2012 or newer instance) where permissions on certain registry keys are not set correctly or the “Microsoft Edge” subkey is missing.
Symptom: SQL fails to install. On checking log files, the error states "Attempted to perform an unauthorized operation. HResult : 0x84bb0001"
For 64 bit Microsoft SQL Server instances, check in the following log to confirm:
C:\Program Files\Microsoft SQL Server\[version]\Setup Bootstrap\Log\Summary.txt
Log file location (for SQL 2012): C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log
Log file location (for SQL 2014): C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log
Solution 1: As per https://docs.microsoft.com/en-us/troubleshoot/sql/install/error-set-up-update-instances
If you're running 64-bit Windows 10, version 20H2 (19042.xxx), you must install the Edge browser version 86.0.622.56 or a later version that includes the fix for this problem. To see the version number in Edge, select Settings > About Edge.
Solution 2: As per https://docs.microsoft.com/en-us/troubleshoot/sql/install/error-set-up-update-instances
Add the Full Control permission to the Administrators account on the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\Microsoft Edge
¶ SQL Server Setup Error – There was an error generating the XML document (error code 0x84B10001)
For 64 bit Microsoft SQL Server instances, check in the following log to confirm:
C:\Program Files\Microsoft SQL Server\[version]\Setup Bootstrap\Log\Summary.txt
Look for the following:
Exception type: Microsoft.SqlServer.Chainer.Infrastructure.ChainerInfrastructureException
Message:
There was an error generating the XML document.
HResult : 0x84b10001To solve this, we create a registry DWORD named “ProtectionPolicy” and set it to 1 to enable local backup of the MasterKey instead of requiring a RWDC in the following registry subkey:
HKEY_LOCAL_MACHINE\Software\Microsoft\Cryptography\Protect\Providers\df9d8cd0-1501-11d1-8c7a-00c04fc297eb
We need to create the following key:
- Path : HKLM\Software\Microsoft\Cryptography\Protect\Providers\df9d8cd0-1501-11d1-8c7a-00c04fc297eb
- Name: ProtectionPolicy
- Value : 1 (DWORD and Hexadecimal)
Setting this value to 1 causes DPAPI master keys to be backed up locally rather than using a domain backup. For more information about DPAPI you can read https://support.microsoft.com/en-us/kb/309408
This workaround is documented in https://support.microsoft.com/en-in/help/3000850/november-2014-update-rollup-for-windows-rt-8.1,-windows-8.1,-and-windows-server-2012-r2
Then try the installation again.
¶ Error installing Microsoft SQL Server - Failure to calculate the default value (error code 0x84B10001)
This error can occur while installing Microsoft SQL (2012 or newer instance) where MSDE2000 (SQL Server 2000) exists on the PC.
Symptom: SQL fails to install. On checking log files, the error states "There was a failure to calculate the default value of setting XXX"
Log file location (for SQL 2012): C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log
Notes: On deeper inspection, the following error is also present: Setup Failed: 0x84B10001 sql 20XX hexadecimal value 0x00, is an invalid character. Line N, position NNNNNN
Solution: Remove MSDE2000 SQL engine if possible from Add/remove programs control panel utility. If not possible (and using Amedtec ECGPro software or other software that can support earlier versions of SQL Server) try installing SQL Server 2008R2 manually with the same instance name you require (AMEDTEC).
¶ Error installing Microsoft SQL Server - Wait on the Database Engine recovery handle failed (error code 0x851A001A)
Symptom: SQL Server Database Engine Services Instance Feature fails to install with error 0x851A001A. The instance has still installed itself though the resulting instance will not start in the Windows Services console. The issue described below is only known to affect systems running Windows 11 and with an NVMe storage volume.
For 64 bit Microsoft SQL Server instances, check in the following log to confirm:
C:\Program Files\Microsoft SQL Server\[version]\Setup Bootstrap\Log\Summary.txt
Log file location (for SQL 2012): C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log
Log file location (for SQL 2014): C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log
Look for the following:
Feature: Database Engine Services
Status: Failed: see logs for details
Reason for failure: An error occurred during the setup process of the feature.
Next Step: Use the following information to resolve the error, uninstall this feature, and then run the setup process again.
Component name: SQL Server Database Engine Services Instance Features
Component error code: 0x851A001A
Error description: Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes.To further confirm the cause of the problem, go to the LOGS folder in the installation directory for the instance and review the latest ERRORLOG file.
For 64 bit Microsoft SQL Server instances, this is found in the following path:
C:\Program Files\Microsoft SQL Server\MSSQL[version].[instancename]\MSSQL\Log
Look for the following in the ERRORLOG file:
There have been 256 misaligned log IOs which required falling back to synchronous IO. The current IO is on file C:\Program Files\Microsoft SQL Server\MSSQL[version].[instancename]\MSSQL\DATA\master.mdf.You may also see the following in the Event viewer:
Log Name: Application
Source: MSSQL$OFFICEMEDIC (or other SQL instance's service name here)
Date: 11/10/2023 10:00:00 AM
Event ID: 5178
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: ComputerNameHere
Description:
Cannot use file 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.OFFICEMEDIC\MSSQL\DATA\mastlog.ldf' because it was originally formatted with sector size 4096 and is now on a volume with sector size 16384. Move the file to a volume with a sector size that is the same as or smaller than the original sector size.This may also appear in the Event Viewer:
Log Name: System
Source: Service Control Manager
Date: 21/02/2024 7:01:57 PM
Event ID: 7024
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: DESKTOP-PH2JEIG
Description:
The SQL Server (AMEDTEC) service terminated with the following service-specific error:
WARNING: You have until SQL Server (AMEDTEC) to logoff. If you have not logged off at this time, your session will be disconnected, and any open files or devices you have open may lose data.
Description:
The SQL Server (MSSQLSERVER) service terminated with the following service-specific error:
WARNING: You have until SQL Server (MSSQLSERVER) to logoff. If you have not logged off at this time, your session will be disconnected, and any open files or devices you have open may lose data.
Solution 1:
1. Uninstall the specific Microsoft SQL Server instance that the feature had failed to install for.
Feel free to make a backup of the system or at least any Microsoft SQL data files prior to removal if necessary; be careful not to permanently remove any important data.
2. Open an elevated Command Prompt (CMD, run as Administrator) then execute the following command:
REG QUERY "HKLM\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" /v "ForcedPhysicalSectorSizeInBytes"Continue to the next step if the key does not exist. Continue to step 4 if the key does already exist. Note, if the key exists then you may need to try the next solution for this issue.
3. In the same elevated Command Prompt from step 2, execute the following command:
REG ADD "HKLM\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" /v "ForcedPhysicalSectorSizeInBytes" /t REG_MULTI_SZ /d "* 4095" /f3. Reboot the computer then re-run the Microsoft SQL Server installation.
The installation should now complete without error.
Reference article: https://docs.microsoft.com/en-us/troubleshoot/sql/admin/troubleshoot-os-4kb-disk-sector-size?WT.mc_id=DP-MVP-5440
Solution 2:
Ensure the folders where the SQL Instance will be installed are not compacted/compressed. Often if a disk is running low on available free space, Windows will offer disk cleanup; one of those options is to compress files. The problem with this is that SQL Server uses a special type of low-level file system access that is incompatible with NTFS compression. The disk access used by SQL Server is designed to optimise database page caching for uncompressed data files. But it simply cannot work with NTFS-compressed data files.
1. Uninstall the specific Microsoft SQL Server instance that the feature had failed to install for.
Feel free to make a backup of the system or at least any Microsoft SQL data files prior to removal if necessary; be careful not to permanently remove any important data.
2. Open an elevated Command Prompt (CMD, run as Administrator) then execute the following 2 commands, one line at a time:
cd "c:\Program Files\Microsoft SQL Server\"
compact /u /s /a /iNote: for the “cd” command above, enter the directory path that will be the root of the Microsoft SQL Server installation path (the default path for 64 bit instances on a 64 bit OS is included in the example). You may also need to repeat step 2 for each path where the SQL database files will be stored if you have a custom database/log file locations (.ldf and .mdf files).
3. Reboot the computer then re-run the Microsoft SQL Server installation.
The installation should now complete without error.
Reference article: https://selfdocumentingcode.blogspot.com/2010/04/sql-server-refusing-to-start.html
Other Possible Solutions:
The following article may help if the above solutions do not fix the issue: https://www.mssqltips.com/sqlservertip/4526/sql-server-install-error-wait-on-the-database-engine-recovery-handle-failed/
¶ SQL instance fails to start on boot due to other services that have not started yet
In some cases, particularly on slower computers, SQL Server instances will fail to start on boot due to other services that the instance depends on not having loaded yet. A simple solution is to set the service to Automatic (Delayed Start) as the startup type as follows:
However, one of the problems with this can be that the application that depends on the SQL instance may not start properly if the service is not yet available. To solve this, please follow the instructions below.
1. Set the SQL Server service back to Automatic for startup type
2. Open the Windows Registry Editor and open the registry path HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQL$[InstanceNameHere]\
3. Create a new Multi-String Value called DependOnService then edit the value to include the text LanmanWorkstation
Be sure to press enter after typing LanmanWorkstation to create an empty line after the text - this will prevent an error about the data not containing empty strings
4. Check that the SQL Server service now has a dependency of Workstation
5. Repeat steps 1 to 4 for any other SQL Server services if required
Need Help?
LEMR customers can get help by contacting LEMR Support.