sp_pdw_remove_network_credentials (SQL Data Warehouse)

APPLIES TO: noSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

This removes network credentials stored in SQL Data Warehouse to access a network file share. For example, use this stored procedure to remove permission for SQL Data Warehouse to perform backup and restore operations on a server that resides within your own network.

Topic link icon Transact-SQL Syntax Conventions (Transact-SQL)

Syntax

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
sp_pdw_remove_network_credentials 'target_server_name'  

Arguments

'target_server_name'
Specifies the target server host name or IP address. Credentials to access this server will be removed from SQL Data Warehouse. This does not change or remove any permissions on the actual target server which is managed by your own team.

target_server_name is defined as nvarchar(337).

Return Code Values

0 (success) or 1 (failure)

Permissions

Requires ALTER SERVER STATE permission.

Error Handling

An error occurs if removing credentials does not succeed on the Control node and all Compute nodes.

General Remarks

This stored procedure removes network credentials from the NetworkService account for SQL Data Warehouse. The NetworkService account runs each instance of SMP SQL Server on the Control node and the Compute nodes. For example, when a backup operation runs, the Control node and each Compute node will use the NetworkService account credentials to access the target server.

Metadata

To list all credentials and to verify the credentials have been removed, use sys.dm_pdw_network_credentials (Transact-SQL).

To add credentials, use sp_pdw_add_network_credentials (SQL Data Warehouse).

Examples: Azure Synapse Analytics (SQL DW) and Parallel Data Warehouse

A. Remove credentials for performing a database backup

The following example removes user name and password credentials for accessing the target server which has an IP address of 10.192.147.63.

EXEC sp_pdw_remove_network_credentials '10.192.147.63';