SQL Deployment fails with network error

paraggadkari

The SQL Server was added to the environment through the "Add SQL Server" action. The instance was successfully added and shows "Online" in the envrionment dashboard.

However, when we try to deploy the release it fails with following error:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

We know for sure that there is no firewall blocking this connection and that instance name, server etc credentials are correct.

Any help will be appreciated.

Regards,
PG

paraggadkari

In case logs are required:

014-01-17 10:00:52 -05:00 INFO Evaluating deployment variables
2014-01-17 10:00:52 -05:00 INFO Begin deployment
2014-01-17 10:00:52 -05:00 DEBUG Calling C:\Program Files (x86)\Red Gate\Deployment Manager\Server\..\Agent\RedGate.Deploy.Agent.exe to deploy package XXXXXXXX_2.1.0 1.0.1
2014-01-17 10:00:57 -05:00 INFO Tried to load C:\Program Files (x86)\Red Gate\Deployment Manager\Agent\Plugins\RedGate.Deploy.SqlServerDbPackage.AgentPlugin\SQLite.Interop.dll as potential plugin, but failed.
2014-01-17 10:00:57 -05:00 INFO System.BadImageFormatException: Could not load file or assembly 'file:///C:\Program Files (x86)\Red Gate\Deployment Manager\Agent\Plugins\RedGate.Deploy.SqlServerDbPackage.AgentPlugin\SQLite.Interop.dll' or one of its dependencies. The module was expected to contain an assembly manifest.
2014-01-17 10:00:57 -05:00 INFO File name: 'file:///C:\Program Files (x86)\Red Gate\Deployment Manager\Agent\Plugins\RedGate.Deploy.SqlServerDbPackage.AgentPlugin\SQLite.Interop.dll'
2014-01-17 10:00:57 -05:00 INFO at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
2014-01-17 10:00:57 -05:00 INFO at System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
2014-01-17 10:00:57 -05:00 INFO at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks)
2014-01-17 10:00:57 -05:00 INFO at System.Reflection.RuntimeAssembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, Boolean suppressSecurityChecks, StackCrawlMark& stackMark)
2014-01-17 10:00:57 -05:00 INFO at System.Reflection.Assembly.LoadFrom(String assemblyFile)
2014-01-17 10:00:57 -05:00 INFO at RedGate.Deploy.Agent.Plugins.PluginsModule.GetPotentialPluginAssemblies()
2014-01-17 10:00:57 -05:00 INFO
2014-01-17 10:00:57 -05:00 INFO WRN: Assembly binding logging is turned OFF.
2014-01-17 10:00:57 -05:00 INFO To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
2014-01-17 10:00:57 -05:00 INFO Note: There is some performance penalty associated with assembly bind failure logging.
2014-01-17 10:00:57 -05:00 INFO To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].
2014-01-17 10:00:57 -05:00 INFO
2014-01-17 10:00:57 -05:00 INFO Deployment Agent version 2.3.18.7
2014-01-17 10:00:57 -05:00 INFO
2014-01-17 10:00:57 -05:00 INFO Detected plugin IConvention RedGate.Deploy.SqlServerDbPackage.AgentPlugin.DbPackageConvention, RedGate.Deploy.SqlServerDbPackage.AgentPlugin, Version=0.0.2.1115, Culture=neutral, PublicKeyToken=7f465a1c156d4d57 (loaded from C:\Program Files (x86)\Red Gate\Deployment Manager\Agent\Plugins\RedGate.Deploy.SqlServerDbPackage.AgentPlugin\RedGate.Deploy.SqlServerDbPackage.AgentPlugin.dll)
2014-01-17 10:00:58 -05:00 INFO Begin deployment of package: XXXXXXXX_2.1.0.1.0.1
2014-01-17 10:00:58 -05:00 INFO Deployment Agent information is below:
2014-01-17 10:00:58 -05:00 INFO - Machine name: XXXXXXXX-XXXX
2014-01-17 10:00:58 -05:00 INFO - Is 64-bit OS: True
2014-01-17 10:00:58 -05:00 INFO - Is 64-bit process: False
2014-01-17 10:00:58 -05:00 INFO - Service user name: SYSTEM
2014-01-17 10:00:58 -05:00 INFO - CLR version: 4.0.30319.261
2014-01-17 10:00:58 -05:00 INFO - Current directory: C:\Windows\system32
2014-01-17 10:00:58 -05:00 INFO - OS version: Microsoft Windows NT 6.0.6002 Service Pack 2
2014-01-17 10:00:58 -05:00 INFO - Agent version: 2.3.18.7
2014-01-17 10:00:58 -05:00 INFO Installing package XXXXXXXX_2.1.0.1.0.1 from uploaded package cache into C:\Windows\TEMP\erux2fjp.qx5\Packages\..\Applications\XXXXXXXXXXXX\XXXXXXXX_2.1.0\1.0.1
2014-01-17 10:01:00 -05:00 INFO Installing 'XXXXXXXX_2.1.0 1.0.1'.
2014-01-17 10:01:01 -05:00 INFO Successfully installed 'XXXXXXXX_2.1.0 1.0.1'.
2014-01-17 10:01:01 -05:00 INFO The following variables are available:
2014-01-17 10:01:01 -05:00 INFO - [RedGatePackageName] = 'XXXXXXXX_2.1.0'
2014-01-17 10:01:01 -05:00 INFO - [RedGatePackageVersion] = '1.0.1'
2014-01-17 10:01:01 -05:00 INFO - [RedGatePackageNameAndVersion] = 'XXXXXXXX_2.1.0.1.0.1'
2014-01-17 10:01:01 -05:00 INFO - [RedGateEnvironmentName] = 'XXXXXXXXXXXX'
2014-01-17 10:01:01 -05:00 INFO - [RedGateReleaseNumber] = '1.0.1'
2014-01-17 10:01:01 -05:00 INFO - [RedGateReleaseNotes] = ''
2014-01-17 10:01:01 -05:00 INFO - [RedGateDeploymentComments] = ''
2014-01-17 10:01:01 -05:00 INFO - [RedGateProjectName] = 'XXXXXXXXXXXXX'
2014-01-17 10:01:01 -05:00 INFO - [RedGateProjectDescription] = 'XXXXXXXXXXXXX'
2014-01-17 10:01:01 -05:00 INFO - [RedGateTaskId] = 'deployments-3618'
2014-01-17 10:01:01 -05:00 INFO - [RedGateReleasedBy] = 'XXXXXXXXXXXXX'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDeployedBy] = 'XXXXXXXXXXXXX'
2014-01-17 10:01:01 -05:00 INFO - [RedGateMachineName] = 'XXXXXXXXXXXXX'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabaseServer] = 'XXXXXXXX-XXXXXXXX\DEV'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabaseIntegratedAuthentication] = 'False'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabaseUserName] = '*****'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabasePassword] = '************'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabaseName] = 'XXXXXXXXXXXX'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabaseMode] = 'upgrade'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabaseAllowDynamicUpgrade] = 'True'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabaseAllowPreDeployValidation] = 'False'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabaseAllowPostDeployValidation] = 'False'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDatabaseAbortOnWarningLevel] = 'High'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDeploymentAgentVersion] = '2.3.18.7'
2014-01-17 10:01:01 -05:00 INFO - [RedGateDeploymentAgentMode] = 'single-shot-deployment-agent'
2014-01-17 10:01:01 -05:00 INFO The package has the following requirements: RedGate.Deploy.SqlServerDbPackage.1069b0e8-ff61-414e-95bb-cea5878180d4
2014-01-17 10:01:01 -05:00 INFO Conventions will be run in the following order:
2014-01-17 10:01:01 -05:00 INFO - Run PreDeploy.ps1
2014-01-17 10:01:01 -05:00 INFO - Apply .config transforms
2014-01-17 10:01:01 -05:00 INFO - Update appSettings/connectionStrings in .config(s)
2014-01-17 10:01:01 -05:00 INFO - Copy package
2014-01-17 10:01:01 -05:00 INFO - Database package deployment plugin
2014-01-17 10:01:01 -05:00 INFO - Run Deploy.ps1
2014-01-17 10:01:01 -05:00 INFO - Configure IIS
2014-01-17 10:01:01 -05:00 INFO - Delete package file
2014-01-17 10:01:01 -05:00 INFO - Run PostDeploy.ps1
2014-01-17 10:01:01 -05:00 INFO Looking for PowerShell scripts named PreDeploy.ps1
2014-01-17 10:01:01 -05:00 INFO Calling PowerShell script: 'C:\Windows\TEMP\erux2fjp.qx5\Packages\..\Applications\XXXXXXXXXXXX\XXXXXXXX_2.1.0\1.0.1\RedGate\PreDeploy.ps1'
2014-01-17 10:01:09 -05:00 INFO Script 'C:\Windows\TEMP\erux2fjp.qx5\Packages\..\Applications\XXXXXXXXXXXX\XXXXXXXX_2.1.0\1.0.1\RedGate\PreDeploy.ps1' completed.
2014-01-17 10:01:09 -05:00 INFO Script output:
2014-01-17 10:01:09 -05:00 INFO
2014-01-17 10:01:09 -05:00 INFO
2014-01-17 10:01:09 -05:00 INFO Username : XXXXXXXX-XXXX$
2014-01-17 10:01:09 -05:00 INFO Execution Policy : Bypass
2014-01-17 10:01:09 -05:00 INFO Processor Architecture : AMD64
2014-01-17 10:01:09 -05:00 INFO Working Directory : C:\Windows\TEMP\erux2fjp.qx5\Applications\XXXXXXXXXXXX\XXXXXXXX_2.1.0\1.0.1
2014-01-17 10:01:09 -05:00 INFO Powershell Version : 2.0
2014-01-17 10:01:09 -05:00 INFO
2014-01-17 10:01:09 -05:00 INFO
2014-01-17 10:01:09 -05:00 INFO
2014-01-17 10:01:09 -05:00 INFO Executing powershell script C:\Windows\TEMP\erux2fjp.qx5\Packages\..\Applications\XXXXXXXXXXXX\XXXXXXXX_2.1.0\1.0.1\RedGate\PreDeploy.ps1...
2014-01-17 10:01:09 -05:00 INFO Return code of PowerShell script: 0
2014-01-17 10:01:09 -05:00 INFO Looking for any configuration transformation files
2014-01-17 10:01:09 -05:00 INFO Looking for appSettings and connectionStrings in any .config files
2014-01-17 10:01:09 -05:00 INFO The package has been installed to: C:\Windows\TEMP\erux2fjp.qx5\Packages\..\Applications\XXXXXXXXXXXX\XXXXXXXX_2.1.0\1.0.1
2014-01-17 10:01:09 -05:00 INFO If you would like the package to be installed to an alternative location, please specify the variable 'RedGatePackageDirectoryPath'
2014-01-17 10:01:09 -05:00 INFO Running DB Deploy plugin 0.0.2.1115 on package [XXXXXXXX_2.1.0\1.0.1]
2014-01-17 10:01:10 -05:00 INFO Configured with SQL Authentication, user name: XXXXXX (you can switch to Windows Authentication in the Environments menu, by editing the database server and setting "Authentication mode" to "Windows Authentication")
2014-01-17 10:01:25 -05:00 ERROR 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
2014-01-17 10:01:25 -05:00 ERROR Caused by:
2014-01-17 10:01:25 -05:00 ERROR -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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
2014-01-17 10:01:25 -05:00 ERROR -Caused by:
2014-01-17 10:01:25 -05:00 ERROR --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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
2014-01-17 10:01:25 -05:00 ERROR Command RedGate.Deploy.Agent.Commands.SingleShotDeploymentCommand failed
2014-01-17 10:01:25 -05:00 ERROR RedGate.Deploy.Shared.Startup.CommandException: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
2014-01-17 10:01:25 -05:00 ERROR
2014-01-17 10:01:25 -05:00 ERROR Server stack trace:
2014-01-17 10:01:25 -05:00 ERROR at RedGate.Deploy.Agent.Commands.SingleShotDeploymentCommand.ExecuteInner()
2014-01-17 10:01:25 -05:00 ERROR at System.Threading.Tasks.Task.InnerInvoke()
2014-01-17 10:01:25 -05:00 ERROR at System.Threading.Tasks.Task.Execute()
2014-01-17 10:01:25 -05:00 ERROR
2014-01-17 10:01:25 -05:00 ERROR Exception rethrown at [0]:
2014-01-17 10:01:25 -05:00 ERROR at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
2014-01-17 10:01:25 -05:00 ERROR at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccess(Task task)
2014-01-17 10:01:25 -05:00 ERROR at RedGate.Deploy.Shared.Startup.CommandProcessor.<ProcessAsync>d__4.MoveNext()

Mike U

We've seen this error once or twice since introducing the direct SQL Server connection option, but haven't yet managed to work out what's going on.

The 'error: 26' message does give us a bit of a clue though; some Googling points me in the direction of this MSDN article: http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

Would you be able to follow the diagnostic steps listed in that article and let me know what results you find?

paraggadkari

Since the SQL server is running various instances could there be a problem with the dynamic ports assigned instead of 1433?

FYI SQL Browser does list all the ports correctly.

Mike U

The problem will actually be with access to port 1434 on the server - either sending to it or receiving the response back. I believe that this is a fixed port on the SQL Server that is used for querying for information about all available SQL Server instances. From the article I linked you to:

Every time client makes a connection to SQL Server named instance, we will send a SSRP UDP packet to the server machine UDP port 1434. We need this step to know configuration information of the SQL instance, e.g., protocols enabled, TCP port, pipe name etc. Without these[sic] information, client does [not] know how to connect the server and it fails with this specified error message.

In a word, the reason that we get this error message is the client stack could not receive SSRP response UDP packet from SQL Browser.

In this situation, the client is the deployment process running on the Deployment Manager server.