This page provides information about how to manage linked servers on your Cloud SQL instance, including enrolling an instance, adding a linked server, and querying a linked server.
Enroll your instance to allow linked servers
To add the cloudsql enable linked servers
flag to your instance, use the
following command:
gcloud sql instances patch INSTANCE_NAME --database-flags="cloudsql enable linked servers"=on
Replace INSTANCE_NAME
with the name of the instance
that you want to use for linking servers.
For more information, see configure database flags.
Add a linked server
To add a linked server, run the Transact-SQL sp_addlinkedserver
command:
EXEC master.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 address
for the linked server. For the instance name, use the format
servername\instancename
. If your instance uses a non-standard port, add the port number. For example,servername\instancename, 8080
.
To add user mapping for a user who is currently logged in, run the following command:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKED_SERVER_NAME', @useself=N'True'
Replace LINKED_SERVER_NAME with the name of the linked server.
To create or update the default remote login and password, and apply it to all local logins, run the following command:
EXEC master.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
is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect tormtsrvname
. If not NULL,locallogin
is either 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 its membership 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-SQL
sp_addlinkedserver
command:
EXEC master.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 IP address and port number for the target server.
If the name of the server is different from the name in the certificate, you need to indicate that the SQL Server can trust the server certificate. To update the provider string, run the following command:
EXEC master.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 IP address and port number for the target server.
Linked server queries
Cloud SQL supports the use of four-part names to query linked servers (server name, database name, schema name, and object name), in addition to the following commands:
- OPENQUERY executes a query on a specified server.
- EXECUTE allows you to run dynamic SQL against a linked server.
For more information, see Compare query remote execution options.
Enable remote procedure calls on a linked server
Remote procedure calls (RPC) let you run stored procedures on linked servers. To
add RPC, you run the Transact-SQL
sp_serveroption
command 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 following sp_serveroption
command with the rpc
argument to enable
RPC from LINKED_SERVER_NAME:
EXEC sp_serveroption @server='LINKED_SERVER_NAME', @optname='rpc', @optvalue='TRUE'
Replace LINKED_SERVER_NAME with the name of the linked server.
Use the following sp_serveroption
command with the rpc out
argument to
enable RPC:
EXEC sp_serveroption @server='LINKED_SERVER_NAME', @optname='rpc out', @optvalue='TRUE'
Replace LINKED_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 following command:
EXEC master.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
is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect tormtsrvname
. If not NULL,locallogin
is either 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 its membership 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-SQL sp_dropserver stored procedure.
Remove the
cloudsql enable linked servers
flag to remove an enrolled instance.gcloud sql instances patch INSTANCE_NAME --database-flags="cloudsql enable linked servers"=off
Replace
INSTANCE_NAME
with the name of the instance where you want to remove linked servers.Alternatively, you can run the following command to clear all database flags:
gcloud sql instances patch INSTANCE_NAME --clear-database-flags
Replace
INSTANCE_NAME
with the name of the instance where you want to remove linked servers.
Troubleshoot
Error message | Troubleshooting |
---|---|
Msg 7411, Level 16, State 1, Line 25
|
The DataAccess option is disabled. Run the
following command to enable data access:EXEC sp_serveroption @server='LINKED_SERVER_NAME', @optname='data access', @optvalue='TRUE' Replace LINKED_SERVER_NAME with the name of the linked server. |
Access to the remote server is denied because no
login-mapping exists. (Microsoft SQL Server, Error: 7416)
|
If you have this issue while establishing an encrypted
connection, you need to try another way to provide the user ID when you
access the linked server. To do this, run the following command:
EXEC master.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' Replace the following:
|
What's next
- Learn about using linked servers.