SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Changing Linked Server Passwords

By Thomas Liddle,

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.

    Linked Server Properties Dialog

    Figure 1 - Example or defined Remote Login

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

    Linked Server Security Properties

    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.

 
Total article views: 1578 | Views in the last 30 days: 4
 
Related Articles
FORUM

changing passwords at next login?

How do users change passwords at next login when they are sql server logins?

FORUM

Password change

Password change

FORUM

change password

change password

BLOG

PowerShell – Change SQL Server Login Password

Here’s a quick post detailing a PowerShell script that can be used to change the password for a SQL ...

FORUM

Reset Password for linked server login

Reset Password for linked server login

Tags
linked server    
security    
 
Contribute