Problem

A linked server in SQL Server allows users to query remote heterogenous servers.  These servers can be database servers like SQL Server, Oracle, MySQL, etc.  At times, administrators of those servers might have to change the password on a mapped login or it could be changed without your knowledge.  You will see the error message below when this occurs.

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.

You can check the remote server Error Log to determine the cause.  In this case, on the remote server ‘SERVER2\MSSQL02’, We see the following error message.

Message

Login failed for user 'svcRemoteLogin'. Reason: Password did not match that for the login provided. [CLIENT: 127.0.0.1]

Solution

After we retrieved the new password from the system administrator of SERVER2\MSSQL02, we will need to update the linked server.  There are two ways to do this.  You can either do it via SQL Server Management Studio (GUI) or T-SQL.  This article will describe both.

SQL Server Management Studio

Follow these steps to change the password in SSMS.

1. First, 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 linked server were you want to change the password on (in this example 'SERVER2\MSSQL02')

5. Click Properties. You should see an image like Figure 1.

   

   Figure 1 - Example or defined Remote Login

6. Click Security. You should see an image like Figure 2.

   

   Figure 2 - Example of Mapped Server Logins

From here, there are two options available to you.  If the linked server is configured for login's that are not defined (Figure 1), you would simply change the password in the "With Password" text box and click OK.

If the linked server is configured with mapped local logins, you will need to change the password for each local login that is mapped to the remote login (Figure 2).  In some cases, there may be both configured.  In which case you will have to change it in both locations.

T-SQL

The sp_addlinkedsrvlogin stored procedure is used to create or update mapping between local instances of SQL Server and remote servers.

sp_addlinkedsrvlogin
  @rmtsrvname ='' -- The remote server name
, @useself = ''  -- Determine if impersonating local logins should be used
, @locallogin = ''  -- Is the login local on the server
, @rmtuser = '' -- Remote Login User
, @rmtpassword = '' -- Remote Login Password

Our statement below will change the password on the linked server 'SERVER2\MSSQL02' for login svcRemoteLogin to N3wPa$$w0rd.

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

Our statement below will change the password on the linked server 'SERVER2\MSSQL02' for the local login 'LocalUser' that is mapped to the remote login 'svcRemoteLogin' with the password N3wPa$$w0rd.

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

Conclusion

Changing password on linked servers is an extremely easy function.  In most environments this is not rarely, however, understanding how to quickly change the password on a linked server could save downtime for users trying to do queries over that linked server.