How To Change The Remote Login Password For The Linked Server

dbtut

6 years ago

When you create a linked server to connect to Oracle, you need to put an oracle user into the linked server.

Before reading this article, I recommend you read below to articles.

“How To Create a Linked Server To Connect To Oracle“,

“When Running Linked Server Created For Oracle, SQL Server Service Crashes. Error Code:0xc0000374”

This oracle user, which you will set up while creating a linked server, may be connected to some databases on oracle and has select permission, or may have more authority.

It is not a good idea for the MS SQL Database expert to know the user’s password to connect to a schema in ORACLE.

we do not want to have authority in a database where we do not manage.

Therefore, even if you have defined remote login during the installation phase, you must tell the application developer to change the password for that user on the oracle side, and then update the password on the linked server.

This, of course, applies to the previously defined oracle linked servers.

In newly defined linked servers, you should define the linked server without ever learning this password by letting the application developer create the remote user directly.

First of all, in order to change the linked server’s password or to create a remote login, you need to give that login the alter any linked server and alter any login privileges.

But with these privileges, the application developer will be able to update other linked servers and alter other logins.

So you have to give this privilege to the application developer only when you are on your side.

When I do this, I am either with the application developer or by phone and talking to him or her.

Now let’s see how this permission is given.

Give The LAlter Login Permission:

Right-click on the SQL Server Instance and select Properties.

Then we select login in the following Logins or roles section.

And we select the Alter any login and alter any linked server that appear at the bottom and press OK.

After giving the authorization, we tell the application developer to log in with their login.

Change Remote Login Password:

And we want from the application developer to do the following.

From Server Objects-> Linked Servers, find the relevant linked server and right click on it and say properties.

A screen like the one below will be opened.

If you have previously defined a remote login, the name of the login will be written in the section that says Local Login.

The remote user section has a user name that is authoritative in Oracle.

The remote password section also has the password of the user who has the authority of oracle.

If remote login has been defined before, we only want to update the application developer’s password from remote password.

If you do not define remote login as dba during the installation of linked server (which is correct), you should tell the application developer to complete the process by entering Add, Local Login, Remote User and Remote Password on this screen.

Or you can run the following script.

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'XServer', @locallogin = 'myUser' , 
@useself = N'False', @rmtuser = N'oracleuser', @rmtpassword = N'oracleuserşifre'

1 2	EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'XServer', @locallogin = 'myUser' , @useself = N'False', @rmtuser = N'oracleuser', @rmtpassword = N'oracleuserşifre'

It will be safer to select “Not be made” from the “Connect a will not be defined in the list above, connections will” screen on the above screen.

Anyone outside the local login you define will not be able to use this linked server.

Once this is done, you should revoke the alter any linked server, alter any login privileges you gave at the server level.

Control Everything For Security:

Since I am a man who cares about security a little bit, I check to see if a login has been created with the help of the following script or if the password of a login has been changed.

As a result of this query, I am looking at the login datasets that have create date or update date today, and check if there is anything out of the control.

select * from sys.syslogins order by createdate desc

1	select * from sys.syslogins order by createdate desc

I check to see if there is a change in other linked servers in the same instace.

You can learn the linked servers on the same intance with the help of the following scripts.

You can also access the Server Objects section via SSMS.

Especially, is another login defined as local login to other linked servers?

select * from sys.sysservers

1	select * from sys.sysservers

exec sp_linkedservers

1	exec sp_linkedservers