A linked server in SQL Server allows users to query remote heterogeneous servers. These servers can be database servers like SQL Server, Oracle, MySQL, etc. However, there may be instances where the password for a mapped login on a linked server needs to be changed. This can happen if the password is changed by the server administrator or if it is changed without your knowledge. When this occurs, you may encounter the following error message:

Msg 7202, Level 11, State 2, Line 1 Could not find server ‘SERVER2\MSSQL02’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

To resolve this issue, you will need to update the linked server with the new password. There are two ways to accomplish this: using SQL Server Management Studio (GUI) or T-SQL. Let’s explore both methods.

Using SQL Server Management Studio

Follow these steps to change the password in SSMS:

1. Open SSMS and connect to the server hosting the linked server.
2. In Object Explorer, expand the Server Objects folder.
3. Expand Linked Servers.
4. Right-click on the linked server where you want to change the password (e.g., ‘SERVER2\MSSQL02’).
5. Click Properties. You should see a window similar to Figure 1.

Figure 1 – Example of Defined Remote Login

Next, click on the Security tab. You should see a window similar to Figure 2.

Figure 2 – Example of Mapped Server Logins

From here, you have two options:

• If the linked server is configured for logins that are not defined (Figure 1), simply change the password in the “With Password” text box and click OK.
• If the linked server is configured with mapped local logins (Figure 2), you will need to change the password for each local login that is mapped to the remote login. In some cases, there may be both configurations, in which case you will have to change it in both locations.

Using T-SQL

The sp_addlinkedsrvlogin stored procedure is used to create or update the mapping between local instances of SQL Server and remote servers. Here is an example of how to change the password on a linked server using T-SQL:

USE [master]
GO
sp_addlinkedsrvlogin
  @rmtsrvname ='SERVER2\MSSQL02',
  @useself = 'FALSE',
  @locallogin = NULL,
  @rmtuser = 'svcRemoteLogin',
  @rmtpassword = 'N3wPa$$W0rd'

In the above example, the password on the linked server ‘SERVER2\MSSQL02’ for the login ‘svcRemoteLogin’ is changed to ‘N3wPa$$w0rd’.

If you have a local login that is mapped to the remote login, you can use the following syntax:

USE [master]
GO
sp_addlinkedsrvlogin
  @rmtsrvname ='SERVER2\MSSQL02',
  @useself = 'FALSE',
  @locallogin = 'LocalUser',
  @rmtuser = 'svcRemoteLogin',
  @rmtpassword = 'N3wPa$$W0rd'

In this case, the password on the linked server ‘SERVER2\MSSQL02’ for the local login ‘LocalUser’ that is mapped to the remote login ‘svcRemoteLogin’ is changed to ‘N3wPa$$w0rd’.

Conclusion

Changing the password on linked servers in SQL Server is a straightforward process. While it may not be a common occurrence, understanding how to quickly change the password on a linked server can save downtime for users trying to perform queries over that linked server.