{"id":5961,"date":"2025-11-03T17:51:23","date_gmt":"2025-11-03T14:51:23","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=5961"},"modified":"2025-11-04T13:18:41","modified_gmt":"2025-11-04T10:18:41","slug":"pos-mporoyme-na-syndesoyme-sql-server-allon-sql","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-syndesoyme-sql-server-allon-sql\/","title":{"rendered":"How can we connect SQL Server to another SQL Server using Linked Server?"},"content":{"rendered":"

THE SQL Server<\/strong> allows us to connect and execute queries on remote Database Instances that do not necessarily have to be in the same RDBMS. In this article we will see specifically how to do it from one instance of SQL Server to another instance of SQL Server using Linked Server<\/strong>.<\/p>\n\n\n\n

In previous<\/a> article we had seen how to connect SQL Server to Oracle using PolyBase<\/strong>. The main difference is that with Linked Server we don't simply read data but we can execute Stored Procedures and modify the data with inserts, updates, deletes.<\/p>\n\n\n\n

* All the differences between Linked Server and PolyBase can be read from here<\/a>.<\/em><\/p>\n\n\n\n

The installation<\/h2>\n\n\n\n

First, we connect to SQL Server from SQL Server Management Studio<\/strong> and we choose Server Objects<\/em> and right click Linked Server<\/em>, New Linked Server\u2026<\/strong><\/em>:<\/p>\n\n\n\n

\"How
01<\/figcaption><\/figure>\n\n\n\n

In the tab General <\/em>we define the name we want to have Linked Server<\/em>, as Server type Provider<\/em> Microsoft OLE DB Provider for SQL Server<\/em><\/strong> and as Data source<\/em> the name of the SQL Server instance we want to connect to:<\/p>\n\n\n\n

\"How
02<\/figcaption><\/figure>\n\n\n\n

In the tab\u00a0Security\u00a0<\/em>we choose\u00a0Be made using this security context\u00a0<\/em>and we enter the user credentials of the remote SQL Server instance to which we will connect:<\/p>\n\n\n\n

\"How
03<\/figcaption><\/figure>\n\n\n\n

*Be careful, with the option \u201cBe made using this security context\u201d any user who has access to the local server can run queries on the remote server with the permissions of the user \u201cdbadmin\u201d that we used in the example, the alternative option if we want to limit this is \u201cBe made using the login's current security context\u201d which will essentially run the query with the permissions it has on the remote server<\/em> the user who executes it, but its use requires that the two servers belong to the same domain and the use of accounts with Windows Authentication in Active Directory.<\/em><\/p>\n\n\n\n

<\/p>\n\n\n\n

By completing the Linked Server we can with the use openquery<\/a> call the Linked Server we created and define the query we want to run on the other SQL Server instance:<\/p>\n\n\n\n

select * from openquery(ws2,'select * from demo_db.dbo.users');<\/pre>\n\n\n\n
\"How
04<\/figcaption><\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n