Linked servers are mostly created with a SQL authentication account. It is important to ensure that these SQL authentication accounts are not disabled or locked out. In this article, we will learn how to update the password of SQL accounts from a Central SQL Server.
Linked servers and SQL authentication
Consider the password of the SQL account is changed. For each linked server that is using this SQL account as a mapped login, the password has to be updated. But the task does not stop with this. The password has to be updated on each SQL Server for which we have created a corresponding linked server. If we miss doing so, when we run distributed queries through linked servers, the query will fail as the SQL account will no longer have access to the actual server.
Updating password is now a simple task
Let’s assume that you have 100 linked servers on your machine. A single SQL account is used as mapped login for all these linked servers. Now there are 2 methods to update the SQL account’s password. Method 1 is through GUI i.e., using SQL Server Management Studio. Method 2 is through TSQL.
In Method1, you have to add all linked servers to your Registered Server’s list. Then run Script1 on all those registered servers. Before running the Script1 make sure that you update the script with proper login name and password. However, you have to manually open each linked server and update the password in the Security tab.
ALTER LOGIN <LOGINNAME> WITH PASSWORD = '<NEWPASSWORD>';
In Method2, we will be running a TSQL script from a Central Server. This is the server where we have created Linked Servers. This method is completely automatic except the part that you have to update the SQL Account name and the password in the script before executing it. This script reads Linked Server names from the Central Server and saves it in a table. A cursor loops through each of these servers connects to it and updates the password of the SQL account. Using the same cursor, a password is updated in each Linked Server.
CREATE TABLE TBL1 (cservername VARCHAR(200)) INSERT INTO TBL1 SELECT ss.NAME FROM sys.Servers ss LEFT JOIN sys.linked_logins sl ON ss.server_id = sl.server_id LEFT JOIN sys.server_principals ssp ON ssp.principal_id = sl.local_principal_id WHERE ss.Server_id <> 0 DECLARE @loginname AS VARCHAR(100) DECLARE @loginpasword AS VARCHAR(100) SET @loginname = 'linkedserveruser' SET @loginpasword = 'newpassword' DECLARE @cmd NVARCHAR(4000) DECLARE @cmd2 NVARCHAR(4000) DECLARE @vsvrname VARCHAR(200) DECLARE cur1 CURSOR FOR SELECT cservername FROM TBL1 OPEN cur1 FETCH NEXT FROM cur1 INTO @vsvrname WHILE @@FETCH_STATUS = 0 BEGIN PRINT @vsvrname SET @cmd2 = 'ALTER LOGIN ' + @loginname + ' WITH PASSWORD = ''''' + @loginpasword + '''''' SET @cmd = 'EXEC (''' + @cmd2 + ''') at [' + @vsvrname + ']' PRINT @cmd EXEC (@cmd) SET @cmd2 = 'sp_addlinkedsrvlogin @rmtsrvname = ''' + @vsvrname + ''', @useself = ''FALSE'', @locallogin = NULL, @rmtuser = ''' + @loginname + ''', @rmtpassword = ''' + @loginpasword + '''' PRINT @cmd2 EXEC (@cmd2) FETCH NEXT FROM cur1 INTO @vsvrname END CLOSE cur1 DEALLOCATE cur1 GO
Even after updating the password, if you are still not able to connect to a linked server, databases of the target server might be corrupted. Repair corrupted SQL Server databases and then try again to fetch remote data using linked servers.
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Outlook recovery and excel recovery software products. For more information visit www.datanumen.com