This page provides information about how to manage linked servers on yourCloud SQL instance, including enrolling an instance, adding a linkedserver, and querying a linked server.

Enroll your instance to allow linked servers

To add thecloudsql enable linked servers flag to your instance, use thefollowing command:

gcloudsqlinstancespatchINSTANCE_NAME--database-flags="cloudsql enable linked servers"=on

ReplaceINSTANCE_NAME with the name of the instancethat you want to use for linking servers.

For more information, seeconfigure databaseflags.

Add a linked server

To add a linked server, run the Transact-SQLsp_addlinkedserver command:

EXECmaster.dbo.sp_addlinkedserver@server=N'LINKED_SERVER_NAME',@srvproduct=N'',@provider=N'SQLNCLI',@datasrc=N'TARGET_SERVER_ID'

Replace the following:

• LINKED_SERVER_NAME with the name of the linked server to create.
• TARGET_SERVER_ID with the network name, DNS name, or IP addressfor the linked server. For the instance name, use the formatservername\instancename. If your instance uses a non-standard port, addthe port number. For example,servername\instancename, 8080.

To add user mapping for a user who is currently logged in, run the followingcommand:

EXECmaster.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'LINKED_SERVER_NAME',@useself=N'True'

ReplaceLINKED_SERVER_NAME with the name of the linked server.

To create or update the default remote login and password, and apply it to alllocal logins, run the following command:

EXECmaster.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'LINKED_SERVER_NAME',@useself=N'False',@locallogin=N'LOGIN',@rmtuser=N'USER_ID',@rmtpassword='PASSWORD';

Replace the following:

• LINKED_SERVER_NAME with the name of the linked server.
• LOGIN with the login for the local server.locallogin issysname, with a default of NULL. NULL specifies that this entry applies toall local logins that connect tormtsrvname. If not NULL,locallogin iseither a SQL Server login or a Windows login. If you use a Windows login,it must have access to the SQL Server either directly, or through itsmembership in a Windows group that has access.
• USER_ID with the user logging in.
• PASSWORD with the user password.

Add a linked server with an encrypted connection

To add a linked server using an encrypted connection, run the Transact-SQLsp_addlinkedserver command:

EXECmaster.dbo.sp_addlinkedserver@server=N'LINKED_SERVER_NAME',@srvproduct=N'',@provider=N'SQLNCLI',@datasrc=N'TARGET_SERVER_ID,    @provstr=N'Encrypt=yes;'

Replace the following:

• LINKED_SERVER_NAME with the name of the linked server to create.
• TARGET_SERVER_ID with the name of the target server, or the IPaddress and port number for the target server.

If the name of the server is different from the name in the certificate, youneed to indicate that the SQL Server can trust the server certificate. To updatethe provider string, run the following command:

EXECmaster.dbo.sp_addlinkedserver@server=N'LINKED_SERVER_NAME',@srvproduct=N'',@provider=N'SQLNCLI',@datasrc=N'TARGET_SERVER_ID,    @provstr=N'Encrypt=yes;TrustServerCertificate=yes;'

Replace the following:

• LINKED_SERVER_NAME with the name of the linked server to create.
• TARGET_SERVER_ID with the name of the target server, or the IPaddress and port number for the target server.

Linked server queries

Cloud SQL supports the use of four-part names to query linked servers (servername, database name, schema name, and object name), in addition to thefollowing commands:

• OPENQUERYexecutes a query on a specified server.
• EXECUTEallows you to run dynamic SQL against a linked server.

For more information, seeCompare query remote executionoptions.

Enable remote procedure calls on a linked server

Remote procedure calls (RPC) let you run stored procedures on linked servers. Toadd RPC, you run the Transact-SQLsp_serveroptioncommand with an RPC argument. There are two RPC arguments:

• rpc enables RPC from the specified server.
• rpc out enables RPC to the specified server.

Use the followingsp_serveroption command with therpc argument to enableRPC fromLINKED_SERVER_NAME:

EXECsp_serveroption@server='LINKED_SERVER_NAME',@optname='rpc',@optvalue='TRUE'

ReplaceLINKED_SERVER_NAME with the name of the linked server.

Use the followingsp_serveroption command with therpc out argument toenable RPC:

EXECsp_serveroption@server='LINKED_SERVER_NAME',@optname='rpc out',@optvalue='TRUE'

ReplaceLINKED_SERVER_NAME with the name of the linked server.

Remove the user mapping for a linked server

To remove a user mapping that you previously added, run the followingcommand:

EXECmaster.dbo.sp_droplinkedsrvlogin@rmtsrvname=N'LINKED_SERVER_NAME',@locallogin=N'LOGIN';

Replace the following:

• LINKED_SERVER_NAME with the name of the linked server to drop.
• LOGIN with the login for the local server.locallogin issysname, with a default of NULL. NULL specifies that this entry applies toall local logins that connect tormtsrvname. If not NULL,locallogin iseither a SQL Server login or a Windows login. If you use a Windows login,it must have access to the SQL Server either directly, or through itsmembership in a Windows group that has access.

Remove an enrolled instance from allowing linked servers

To remove linked servers, do the following:

• Drop existing linked servers with the Transact-SQLsp_dropserver stored procedure.

• Remove thecloudsql enable linked servers flag to remove an enrolled instance.

  gcloudsqlinstancespatchINSTANCE_NAME--database-flags="cloudsql enable linked servers"=off

  ReplaceINSTANCE_NAME with the name of the instancewhere you want to remove linked servers.

  Alternatively, you can run the following command to clear all database flags:

  gcloudsqlinstancespatchINSTANCE_NAME--clear-database-flags

  ReplaceINSTANCE_NAME with the name of the instancewhere you want to remove linked servers.

Troubleshoot

EXECsp_serveroption@server='LINKED_SERVER_NAME',@optname='data access',@optvalue='TRUE'

EXECmaster.dbo.sp_addlinkedserver@server=N'LINKED_SERVER_NAME',@srvproduct=N'',@provider=N'SQLNCLI',@datasrc=N'TARGET_SERVER_ID',@provstr=N'Encrypt=yes;TrustServerCertificate=yes;User ID=USER_ID'

What's next

• Learn aboutusing linked servers.