I have described to create linked server to connect to oracle from sql server in my article titled “How To Create a Linked Server To Connect To Oracle” and to authorize a new SQL Login to this linked server in the other article titled “How To Define a New Login On The Linked Server“.
In this article, we will define Linked Server on SQL Server to SQL Server.
Right-click on Server Objects-> Linked Server via SSMS and click new Linked Server.
We write the name of our target instance to the part that writes Linked Server.
In Server Type we choose SQL Server.
We go to the Security tab and choose “Be made using the login’s current security context”.
It is very important that we choose this option.
Because by choosing this option, we guarantee that no user not authorized to the target can not use this linked server.
And then press OK to create the linked server.
But do not forget that you can use this linked server only if the source and target have the same login.
From the same Login we mean not just the names and passwords.
The SIDs of the Source and Destination Login must be the same.
You can find detailed information about SIDs in my article titled “How To Move SQL Server Logins To Another Instance” or from another author “How To Migrate MSSQL Server Logins To Another Instance“.
If you select “Be made using this security context” and enter a login defined in the sql server on the remote login side and the login password in the With password section, all the logins at the source will be accessible if the target login has any privileges.
I do not define any linked server like this because it will create security weakness.
For example, we have an instance named “TESTSUNUCU01\INSTANCE01”.
On this instance, we have two login names xUser and yUser.
On this instance, let’s define linked server to “TESTSUNUCU02\Instance01” instance.
“TESTSUNUCU02\Instance01” Instance will also have only xUser login.
You can transfer logins to another instance.
For do this, you can read my article titled “How To Move SQL Server Logins To Another Instance” or from another author “How To Migrate MSSQL Server Logins To Another Instance“.
Create a database named “deneme” on “TESTSUNUCU02\Instance01” and create a table named “Table1” and insert a few test data.
You may want to read the article titled “How To Create a Database On SQL Server” about creating a database.
Let’s give xUser select right in this database. As described above, let’s define the linked server on the “TESTSUNUCU01\INSTANCE01” Instance.
When defining, choose the login’s current security context.
After you have done this, if you connect to “TESTSUNUCU01\INSTANCE01” instance with xUser and then run the following script, it will give you the data from “TESTSUNUCU02\Instance01”.
|
1 |
Select * From Openquery ([TESTSUNUCU02\Instance01],'select * From deneme.dbo.Table1') |
But you will not be able to use the linked server if you are connected to the “TESTSUNUCU01 \ INSTANCE01” Instance with yUser.
Because we did not transfer yUser to “TESTSUNUCU02\Instance01” with yUser’s SID.
If we defined the linked server by selecting “Be made using this security context” and entering xUser’s information on remote login and password part,
We could use the linked server when we connected to “TESTSUNUCU01\INSTANCE01” with yUser.
