SQL server databases migration to Azure SQL VM using Azure Data studio

Sanjeev K 20

Hello,

I am migrating the SQL server databases to Azure SQL VM using Azure data studio but step 6 - validating the connectivity failed with below error.

I am able to connect to SQL server using SSMS from same server.
I can be able to see the "Microsoft.DataMigration" has already registered in subscription.
I opened the connectivity from DMS to Source server (open the 1433 port).

Please help to resolve this error.

Failed to connect to the database. Error message: Cannot connect to SQL Database.

Please contact SQL server team for further support. Server: 'servername', Database: '', User: 'sa'.

Check the linked service configuration is correct, and make sure the SQL Database firewall allows the integration runtime to access.

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)

Access is denied

Mahesh Kurva 1,625 Reputation points Microsoft Vendor

2024-10-16T14:17:46.1366667+00:00
Hi @Sanjeev Koppella,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

It seems like there is an issue with the connectivity issue during your migration process.

Here are a few steps you can take to troubleshoot and resolve this error:

Verify Network Configuration:

Ensure that the firewall rules on both the source and target servers allow traffic on port 1433.

Check if there are any network security groups (NSGs) or other network configurations that might be blocking the connection.

Check SQL Server Configuration:

Confirm that the SQL Server instance is configured to allow remote connections. You can do this in SQL Server Management Studio (SSMS) by right-clicking on the server, selecting “Properties,” and then checking the “Connections” tab, ensure that "Allow remote connections to this server" is checked.

Make sure the SQL Server and SQL Server Browser services are running.

Validate Credentials:

Double-check the credentials you’re using to connect. Ensure that the username and password are correct and have the necessary permissions.

Review Linked Service Configuration:

Make sure the linked service configuration in Azure Data Studio is correct. Verify the server name, database name, and other connection details.

Check Integration Runtime:

If you're using self-hosted integration runtime is properly configured and running. It should have access to both the source SQL Server and the target Azure SQL VM.

Test Connectivity:

Use tools like telnet or ping to test connectivity to the SQL Server from the machine where Azure Data Studio is running. This can help identify if there are any network issues.

Review Logs:

Check the logs for more detailed error messages. This can provide additional insights into what might be causing the connectivity issue.

I would request you to refer the below mentioned links for more information.

https://stackoverflow.com/questions/9945409/how-do-i-fix-the-error-named-pipes-provider-error-40-could-not-open-a-connec

https://zcusa.951200.xyz/en-us/data-migration/sql-server/virtual-machines/database-migration-service?tabs=offline-with-extension

https://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

https://stackoverflow.com/questions/10010062/error-in-login-to-sql-server#:~:text=The%20way%20to%20resolve%20this%20is%20given%20here%2C,ALTER%20LOGIN%20%5Btest%5D%20WITH%20DEFAULT_DATABASE%20%3D%20master%20GO

I hope this information helps. Please do let us know if you have any further queries.
Sanjeev K 20 Reputation points

2024-10-17T06:08:32.8066667+00:00

Thanks @Anonymous

Verify Network Configuration: Yes i opened the both 1433 and 443 ports on Integration run time server and SQL VM.

Check SQL Server Configuration: Yes remote connection enabled and running browser services on VM

Validate Credentials: Yes I can able to connect from SSMS from same machine ( DMS )

Review Linked Service Configuration: Linked server means, Source server name and logins credentials, right

Check Integration Runtime: How to validate( I can able to ping the server from Intergration server)>

Test Connectivity: I can able ping the SQL server from Integration run time server

Integration run time logs:

The gateway did not receive a response from 'Microsoft.DataMigration' within the specified time period.

I have also checked Microsoft.DataMigration already registered on subscription.

I am sure there is connectivity issues between Source SQL server to Integration run time but i have already opened the 443, 445 and 1433 ports on SQL server and integration run time servers.

Source: Azure SQL VM
Destination: Azure SQL VM
Intergration run time installed on Azure VM, all are in same NSG.

Please let me know if i need to open any other ports.
Mahesh Kurva 1,625 Reputation points Microsoft Vendor

2024-10-17T18:21:20.64+00:00

Hi @Sanjeev Koppella,

Thanks for your response.

Linked server means, Source server name and logins credentials, right.

Yes, the linked service configuration includes the source server name and login credentials. You may want to review the linked service configuration to ensure that the information is correct.

How to validate( I can able to ping the server from Intergration server)

Since you are able to ping the SQL server from the integration runtime server, it suggests that there may be an issue with the linked service configuration or credentials. You can try testing the connectivity using the Test Connection feature in Azure Data Studio to identify any issues with the linked service configuration or credentials.

The gateway did not receive a response from 'Microsoft.DataMigration' within the specified time period.

There may be an issue with the integration runtime itself. You may want to check if the integration runtime is running and if there are any issues with the network configuration.

I hope this information helps. Please do let us know if you have any further queries.
Sanjeev K 20 Reputation points

2024-10-18T04:38:43.68+00:00

Hi @Anonymous , Thanks

The issue with IaaS extension. the validation completed successfully but after starting migration, i am getting different error, Please check

Error:

Operation failed: errorCode: InternalServerError, message: An unexpected error occured while processing the request. Tracking ID: '6aedbd16-bf4f-4ca0-816f-e105124d6e7e' See link for more troubleshooting steps: https://aka.ms/dms-migrations-troubleshooting. See link for more troubleshooting steps: https://aka.ms/dms-migrations-troubleshooting.

Thanks
Mahesh Kurva 1,625 Reputation points Microsoft Vendor

2024-10-21T10:30:55.5033333+00:00

Hi @Sanjeev Koppella,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Sanjeev K 20 Reputation points

2024-10-22T13:39:42.69+00:00

Thanks Mahesh, it got solved after SQL IaaS registration on SQL server. Thanks for your help.

Accepted answer

Mahesh Kurva 1,625 Reputation points Microsoft Vendor

2024-10-18T13:39:25.3166667+00:00
Hi @Sanjeev Koppella,

Thanks for your response.

It seems like the internal server error you’re encountering is related to the SQL Server IaaS Agent extension.

Here are some steps to help you troubleshoot and resolve this issue:

Check Extension Health:

Go to the Azure portal and navigate to your SQL virtual machines resource.

Check the Extension health status under the Overview page. If the status is “Unhealthy” or “Failed,” you may need to repair the extension.

Repair the Extension:

In the Azure portal, select your SQL Server VM. Go to SQL IaaS Agent Extension Settings under Help.

If the provisioning state shows as “Failed,” choose Repair to fix the extension. If the state is “Succeeded,” you can check the box next to Force repair to repair the extension regardless of its state.

Verify SQL Server and Services:

Ensure that the main SQL IaaS Agent service and the SQL IaaS Query Service are running on your SQL Server VM. If these services are stopped, the extension will not function correctly.

Retry the Operation:

Sometimes, internal server errors can be transient. Try restarting the migration process after some time.

For more information, please refer the document: https://zcusa.951200.xyz/en-us/azure/azure-sql/virtual-machines/windows/sql-agent-extension-troubleshoot-known-issues?view=azuresql#check-extension-health.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
Mahesh Kurva 1,625 Reputation points Microsoft Vendor

2024-10-22T10:54:58.4666667+00:00

Hi @Sanjeev Koppella,

Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SQL server databases migration to Azure SQL VM using Azure Data studio

0 additional answers

Your answer