Accessing SQL Server from Azure DevOps Pipeline

0
votes

Problem

I have setup an Azure Pipeline, and the problem I have is that during the Test Assemblies part of the Pipeline it is failing as it can't resolve the connection string/find my SQL Server.

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) ---> System.ComponentModel.Win32Exception: The network path was not found

In this scenario, I want to avoid creating a mock and ideally have the code directly communicate with SQL Server.

The SQL Server is sat on an Azure VM, so it's not a 'Azure SQL Database' service.

What I've Tried

I have opened the ports on the Azure VM to allow traffic from all regions/IP ranges used by Azure DevOps (I have found online that this is required). I have also tried pointing the connection string inside the Pipeline 'Test Assemblies' to a different (on-premise) windows server which holds an instance of SQL Server, but it is still not able to find/connect to SQL.

When connecting to SQL from my local (both the Azure VM and on-premise server) I can connect. We also have a few other services using the SQL Server sat on the Azure VM, so I am confident that the connectivity issue is with connecting from an Azure Pipeline.

Has anyone had this issue before?

Update

I have updated the connection string to directly refer to the port i.e.

**.***.**.***,1433

And the error message has now changed to:

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: TCP Provider, error: 0 - The wait operation timed out.) ---> System.ComponentModel.Win32Exception: The wait operation timed out
1
sql-serverazure-devopsazure-pipelinesazure-vm
Named Pipes Provider seems like a problem. Have you tried forcing a TCP/IP connection? ref: Troubleshoot connecting to the SQL Server Database Engine – AlwaysLearning
Could you make sure that your current pipeline can connect to your SQL server machine? You could test it by the task like Windows machine file copy to check if your pipeline could access the SQL Server. – Leo Liu-MSFT
I have added a command line script and pinged the IP address of the Azure VM with SQL Server on, and it times out. Does that achieve the same information as the windows machine file copy task? – Brummy
@Brummy, Yes. I think Azure devops is still blocked by your Azure VM. Did you just add the IP address of azure devops to the whitelist docs.microsoft.com/en-us/azure/devops/organizations/security/…, but did not add the IP address of the machine where the hosted agent is located to the whitelist?docs.microsoft.com/en-us/azure/devops/pipelines/agents/… – Leo Liu-MSFT
Any update for this issue? Have you resolved this issue? If not, would you please let me know the latest information about this issue? – Leo Liu-MSFT

1 Answers

0
votes

I now have it working.

After having the Azure DevOps region changed from West Europe to UK South, my DevOps can now communicate with SQL Server on my VM (with the required IP addresses white listed as per Leo Liu-MSFT's comment).