“Linked Server” is a feature of MS SQL
Server that allows access to non-SQL Server databases through SQL
Server. Only SQL Server Developer and SQL Server Enterprise versions
support Linked Servers. SQL Server Express edition has several
limitations that do not allow configuring or using Linked Servers.
A Linked Server enables the SQL Server
Database Engine to execute commands against OLE DB data sources outside
of the instance of SQL Server. Many types of OLE DB data sources can be
configured as linked servers such as Microsoft Access, Excel, another
instance of SQL Server, etc.
Linked servers offer the following advantages:
- Remote server access.
- The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
- The ability to address diverse data sources similarly.
A linked server definition specifies the following objects:
- An OLE DB provider: DLL that manages and interacts with a specific data source
- An OLE DB data source: Identifies the specific database that can be accessed through OLE DB (although data sources queried through linked server definitions are ordinarily databases, OLE DB providers exist for a variety of files and file formats.)
How to configure a SQL Server Linked Server to connect to Denodo?
Virtual DataPort provides access to ODBC
clients through the PostgreSQL ODBC driver. To install the driver,
follow the steps of the Virtual DataPort Developer Guide Section “Access
through ODBC”.
Once the DSN to a Denodo database is created on the system, a Linked Server to Denodo can be configured.
- Create a User DSN to a Server name (Server1)
- Start the SQL Server Management Studio and under Object Browser expand Server Objects and right click Linked Servers as shown below:
- Click on New Linked Server. On the new window fill in the information as shown to create linked server under the General tab.
- On the Security tab, select the ‘Be made using this security context’ option and enter the credentials to the Denodo Server.
- Remote Login : SQL Server login user name : sa
- Password : Sql Server Password : 123
- Finally, under the Server Options tab, change RPC, RPC Out, and Lazy Schema Validation options to True (by default they are set to False).
- Click Ok, after making all the mentioned changes. This will create the linked server and it will be listed under SSMS Object Browser. Right Click on the new linked server and click on Test Connection
