SQL Project Deploy to SQL Server on VM through Visual Studio Team Services

0
votes

I am trying to setup SQL Server DACPAC project deploying to SQL Server on a VM through Visual Studio Team Services Continuous Integration scripts. Here is my build definition enter image description here

enter image description here For server name I entered public IP of the VM, for Admin Login I entered Admin Account user name on that VM,

Then I got the following error in build

[error]Microsoft.PowerShell.Commands.WriteErrorException: Deployment on one or more machines failed. System.Management.Automation.Remoting.PSRemotingTransportException: Connecting to remote server xx.xxx.xx.xx failed with the following error message : WinRM cannot complete the operation. Verify that the specified computer name is valid, that the computer is accessible over the network, and that a firewall exception for the WinRM service is enabled and allows access from this computer. By default, the WinRM firewall exception for public profiles limits access to remote computers within the same local subnet. For more information, see the about_Remote_Troubleshooting Help topic.

Then I tried the following powershell commands on SQL VM

Enable-PSRemoting -Force
winrm quickconfig -transport:http

Second command hang up with the following message

Configure LocalAccountTokenFilterPolicy to grant administrative rights remotely to local users.

Then I tried the following enter image description here

Inbound ports enter image description here

netstat -ant|findstr 5985 enter image description here

1
sql-serverpowershellazurecontinuous-integration
@Walter-MSFT no - HaBo
Hi, do you open port 5985 on Windows Firewall? - Shui shengbao
probably I need to add listener for 5986. please check my updated question with screen shots. When I selected WinRm from Inboud rules option, it auto populated 5986 as port so I added it as is, then when I read at different places it is by default 5985 so I added custom inbound port for 5985 - HaBo
If you want winrm listening on 5986, you need configure https certificate. - Shui shengbao
This link will help you configure winrm listen on 5986, you could check it. I test it many times, it works for me. - Shui shengbao

1 Answers

1
votes

Firstly, you should ensure winrm service is listening.

netstat -ant|findstr 5985

By default, the port is listening on 5985. If the service is not listening, please execute

winrm quickconfig

Secondly, you should open port on VM Windows Firewall and Azure NSG(Inbound rules).

enter image description here

For test, you should ensure you could winrm your SQL VM on your local PC.