Issue 2, 2018

SQL Server linked servers

SQL Server’s linked server feature enables fast and easy integration of SQL Server data and non-SQL Server data, directly in the SQL Server engine itself. It has many technical features.

Each is individually described in various locations in SQL Server documentation or knowledge base articles. This guide is an attempt to provide context to these items. Also, we describe the reasons why certain options exist and their ramifications.

Building a linked server connection

Building a linked server connection requires a couple of ingredients:

1. SQL Server
2. A third-party driver, OLE DB or ODBC for the database to be integrated with SQL Server, such as CONNX

Using CONNX, SQL Server easily integrates with all of the data sources supported, including Adabas. Linked servers can be created in two ways: either via the SQL Server Management Studio; or via a stored procedure called sp_addlinkedserver. If there is a need to dynamically create linked servers via a script, then the stored procedure route is better. Please refer to the existing detailed SQL Server documentation on this stored procedure if desired. Using the SQL Server Management Studio, linked servers can be found under “Security Objects." Add a new linked server by right-clicking on “Linked Servers." And then select “New Linked Server…”

Fig.1: Creating a new linked server from the Management Studio

After selecting the name of your linked server connection, you must decide what type of driver to use to access the non-SQL Server database.

Microsoft is moving away from OLE DB and standardizing on ODBC again for access to external data sources. Select the “Microsoft OLE DB Provider for ODBC Driver."

Once the driver has been selected, fill in the remaining fields. The “Product Name” field is not used for any technical purpose. But it is required to complete the linked server connection. Please refer to the documentation of the third-party driver for details on what to supply for the remaining fields. In the provider string field, specify the CONNX driver, along with the name of the CDD, and any desired application filters.

Fig.2: Specifying the linked server driver connection properties

Don’t press the “OK” button yet. Important authentication and security options are located on the “Security” page. This can be selected by clicking Security on the upper left hand side of the dialog. Some databases do not require a username and password to connect. In these cases, select the option “Be made without using a security context." However, most databases require a username and password. This makes the process of connecting to them through SQL server a bit challenging. A case in point: a SQL Server is configured to use Microsoft® Active Directory® logons. But the other database does not support this type of logon. If the desired data source does not support active directory authentication, the mismatch in authentication credentials would make it impossible to connect. The solution to the problem is provided on the following dialog. Here, a static set of credentials for the other database will be used for all linked server connections. Select “Be made using this security context." Then provide the appropriate username and password for the other database.

Fig.3: Specifying the authentication method for the linked server connection

If at all possible, do not use a single set of authentication credentials for all linked server connections. Instead, a separate mapping is established for each SQL Server logon by pressing the “Add” button on the Security page. The “impersonate” option is just another word for using Active Directory authentication for the other database – do not select this checkbox. Additional important options can be found by clicking “Server Options” in the upper left area of the “New Linked Server” dialog.

Fig.4: Specifying the linked server “server options”

The following table lists each of the options and its function:

The important options here are “Collation Compatible” and “Data Access." “Collation Compatible” improves performance. It should be enabled. “Data Access” must be enabled for the linked server to work with queries.

Once all options for the new linked server have been set, press OK to create the linked server. To validate connectivity to the linked server, double click on the linked server name in SQL Server Management Studio. Then, expand the catalog until you can see your tables.

Accessing your non-SQL server data

There are four ways to access data with CONNX with SQL Server: two ways use linked servers, and two ways do not.

Fig.5: Tree view of tables available via a linked server connection

The entire process of creating a linked server can be skipped by using OPENDATASOURCE and OPENROWSET. Both of these options enable direct access to the CONNX driver via SQL without any prior linked server creation. When using these two methods, there are many driver configuration options that are not available for configuration such as collation compatibly.

Additionally, both of these ad-hoc query methods are disabled by default in SQL Server. To enable these ad-hoc methods execute the following commands:

sp\_configure 'show advanced options', 1; RECONFIGURE; Go sp\_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE; Go

The potential danger in enabling this option is that now any user can access data from any OLE DB Provider or ODBC Driver on the SQL Server … not just the ones that have been specifically defined as linked servers. However the option is available and it is up to each admin to determine the risk of these features based on the project and security needs.

Here is an example of the use of OPENDATASOURCE:

SELECT \* FROM OPENDATASOURCE('MSDASQL', 'driver=connx32;DD=c:\cdd\demo4.cdd;application =adabas;UID=xxxxx;PWD=xxxxx') .adalocal.connx75.employees

Fig.6: Example of the OpenDatasource statement

And a corresponding example of the use of OPENROWSET:

SELECT a.\* FROM OPENROWSET('MSDASQL', 'driver=connx32;DD=c:\cdd\demo4.cdd;APPLICATION= ADABAS;UID=XXX;PWD=XXX;', 'SELECT e.first\_name, e.name, en.LANG from adalocal..employees e inner join adalocal..employees\_lang en on (e.isn = en.isn) where e.city = ''PARIS'' ORDER BY e.name DESC') AS a;

Fig.7: Example of the OpenRowset statement

Accessing data with linked server objects

The remaining two ways to query data via a third-party driver involve using linked server objects. The first method involves using a four-part name with the following syntax:

\<Linked Server Name\>.\< Object Catalog\>.\< Object Owner\>.\<Object Name\>

If your third-party driver does not support catalog or owner names, leave those portions empty. For example if a provider does not support catalog names, but does support owner names, the syntax would look like this:

Select \* from MyLinkedServer..richard.products

For a driver that does not support either catalog names, or owner names, it would look like this:

Select \* from MyLinkedServer…products

And for a fully compliant driver that supports both catalog and owner names, like CONNX, it would look like this:

Select \* from MyLinkedServer.oregon.richard.products

The advantage of the four-part name syntax is that it is the easiest and most compact to write. Users can leverage their existing knowledge of SQL Server syntax, and mix SQL Server and non-SQL Server tables in a single SQL statement with ease. The huge disadvantage of using the four-part name syntax is performance. When performing any type of complex SQL operation on multiple tables in the linked server connection, SQL Server will typically download a snapshot each table into SQL server. And, then it performs the filtering and joins operations on those snapshots.

For large tables this is a performance bottleneck. The way to avoid this is to use the fourth and final option of data access – the OPENQUERY syntax.

Using the OPENQUERY syntax, a complex sub-component of a larger SQL statement can be sent directly to the third-party driver. This increases performance. An example of the syntax is shown here:

SELECT a.\* FROM OPENQUERY(ADABAS, 'SELECT e.first\_name, e.name, en.LANG from adalocal..employees e inner join adalocal..employees\_lang en on (e.isn = en.isn) where e.city = ''PARIS'' ORDER BY e.name DESC ') AS a;

Fig.8: Example of the OpenQuery statement

Alternatively, the exec syntax can be used, providing the ability to specify parameters:

exec ('SELECT e.first\_name, e.name, en.LANG from adalocal..employees e inner join adalocal..employees\_lang en on (e.isn = en.isn) where e.city = ? ORDER BY e.name DESC ', 'PARIS') at ADABAS;

Global OLE DB provider options

Each OLE DB provider has eight global options that can be configured for all linked servers for that provider.

Fig.9: Accessing global properties for an OLE DB Provider

Select MSDASQL, as this is the short name for the OLEDB provider for ODBC drivers.

Fig.10: Global properties for an OLE DB provider

In summary, SQL Server provides many ways of integrating CONNX data sources (such as Adabas) and SQL Server data together. Each has its own set of advantages and disadvantages. The security and business needs of each project should be evaluated against the available options to determine what is best for the specified use case.

To learn more, visit www.connx.com.