Previously, I have described the definition of a linked server to connect to Oracle in my article titled “How To Create a Linked Server To Connect To Oracle“.
Now I will describe the new SQL Login authorization process to this linked server.
Let’s assume we define a linked server named “deneme” and authorize “denemeUser” on a linked server.
The developer will not want to give this login’s password to someone else, since the User is the application user.
And because of this, he/she may want a new login that is authorized to use the linked server.
In such a case, we create login. I mentioned in the article titled “How To Create a Login On SQL Server(Manage Logins)”.
Then we go to the Server Objects-> Linked Servers via SSMS, we right-click on the linked server and click properties.
On the left side of the tab, we come to the security tab and select the login we want to authorize in Local Login.
Then we enter the user name defined in ORACLE in the remote user part and the password in the Remote Password part.
If you click Ok, it may give you an error. This is usually because you do not have the authority to use this linked server, even if your login is sysadmin.
To test, add your own login to LocalLogin and click OK(you must enter remote login and password), then right click the linked server and test connection.
As an important point, you should always select “Be made using the current security context” in the options under “connections will:”.
In this way, no local login that is not authorized on the remote server will not be able to use this linked server.
This is especially important when SQL Server defines a linked server to SQL Server.
I mentioned this topic in the article “How To Create a Linked Server To Connect To SQL Server From SQL Server“.
