sys.sp_rda_test_connection (Transact-SQL)

• 06/10/2016
• 2 minutes to read
• Contributors
  • 
  • 
  • 
  • 

THIS TOPIC APPLIES TO: SQL Server (starting with 2016)Azure SQL DatabaseAzure SQL Data Warehouse Parallel 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

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.

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.

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.

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.