sys.sp_rda_test_connection (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Tests the connection from SQL Server to the remote Azure server and reports problems that may prevent data migration.

Syntax


EXECUTE sys.sp_rda_test_connection  
   @database_name = N'db_name',   
   @server_address = N'azure_server_fully_qualified_address',  
   @azure_username = N'azure_username',   
   @azure_password = N'azure_password',  
   @credential_name = N'credential_name'  

Arguments

@database_name = N'db_name'
The name of the Stretch-enabled SQL Server database. This parameter is optional.

@server_address = N'azure_server_fully_qualified_address'
The fully qualified address of the Azure server.

  • If you provide a value for @database_name, but the specified database is not Stretch-enabled, then you have to provide a value for @server_address.

  • If you provide a value for @database_name, and the specified database is Stretch-enabled, then you don't have to provide a value for @server_address. If you provide a value for @server_address, the stored procedure ignores it and uses existing Azure server already associated with the Stretch-enabled database.

    @azure_username = N'azure_username
    The user name for the remote Azure server.

    @azure_password = N'azure_password'
    The password for the remote Azure server.

    @credential_name = N'credential_name'
    Instead of providing a user name and password, you can provide the name of a credential stored in the Stretch-enabled database.

Return Code Values

In case of success, sp_rda_test_connection returns error 14855 (STRETCH_MAJOR, STRETCH_CONNECTION_TEST_PROC_SUCCEEDED) with severity EX_INFO and a success return code.

In case of failure, sp_rda_test_connection returns error 14856 (STRETCH_MAJOR, STRETCH_CONNECTION_TEST_PROC_FAILED) with severity EX_USER and an error return code.

Result Sets

Column name Data type Description
link_state int One of the following values, which correspond to the values for link_state_desc.

- 0
- 1
- 2
- 3
- 4
link_state_desc varchar(32) One of the following values, which correspond to the preceding values for link_state.

- HEALTHY
The between SQL Server and the remote Azure server is healthy.
- ERROR_AZURE_FIREWALL
The Azure firewall is preventing the link between SQL Server and the remote Azure server.
- ERROR_NO_CONNECTION
SQL Server can't make a connection to the remote Azure server.
- ERROR_AUTH_FAILURE
An authentication failure is preventing the link between SQL Server and the remote Azure server.
- ERROR
An error that's not an authentication issue, a connectivity issue, or a firewall issue is preventing the link between SQL Server and the remote Azure server.
error_number int The number of the error. If there is no error, this field is NULL.
error_message nvarchar(1024) The error message. If there is no error, this field is NULL.

Permissions

Requires db_owner permissions.

Examples

Check the connection from SQL Server to the remote Azure server

EXECUTE sys.sp_rda_test_connection @database_name = N'<Stretch-enabled database>'  
GO  

The results show that SQL Server can't connect to the remote Azure server.

link_state link_state_desc error_number error_message
2 ERROR_NO_CONNECTION <connection-related error number> <connection-related error message>

Check the Azure firewall

USE <Stretch-enabled database>  
GO  
EXECUTE sys.sp_rda_test_connection  
GO  

The results show that the Azure firewall is preventing the link between SQL Server and the remote Azure server.

link_state link_state_desc error_number error_message
1 ERROR_AZURE_FIREWALL <firewall-related error number> <firewall-related error message>

Check authentication credentials

USE <Stretch-enabled database>  
GO  
EXECUTE sys.sp_rda_test_connection  
GO  

The results show that an authentication failure is preventing the link between SQL Server and the remote Azure server.

link_state link_state_desc error_number error_message
3 ERROR_AUTH_FAILURE <authentication-related error number> <authentication-related error message>

Check the status of the remote Azure server

USE <SQL Server database>  
GO  
EXECUTE sys.sp_rda_test_connection   
    @server_address = N'<server name>.database.windows.net',   
    @azure_username = N'<user name>',   
    @azure_password = N'<password>'  
GO  

The results show that the connection is healthy and that you can enable Stretch Database for the specified database.

link_state link_state_desc error_number error_message
0 HEALTHY NULL NULL