Table of contentPrerequisites
Microsoft Linked Server ConnectorThis guide will describe a few simple steps to use the Linked Server Connector.
- Physical database – Username/password with sufficient rights to meet the endgoals intended with use of this connector-definition.
- MS SQL Server Management Studio - Username/password with sufficient rights to meet the endgoals intended with use of this connector-definition.
- Accessible physical database(server).
Creating the linked server
GeneralTo establish a connection to other databases, the "Linked Server" principle can be used. Typically, linked servers are used to handle distributed queries. When a client application like DM-ETL+ executes a distributed query through a linked server, SQL Server breaks down the command and executes an openquery to select data from the database configured in the linked server. DM-ETL+ supports connections to other databases based on SQL OLE DB connections with openrowset but this is not recommended due to vulnerabilites for security attacks of the SQL server. No further information is provided concerning the configuration of this type of database connections.
- 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
TasksNote: The following steps apply to all other database connections that DM-ETL+ uses to gather(read) data from databases, including the HPOV Service Desk Database and optionally to write to databases (currently only MSSQL and Oracle are supported for write actions).
We will use the DM-ETL-plus_Data within MSSQL2008R2 as an example to connect through the Linked Server principle.
- Open the MS SQL Server Management Studio
- Select > New Login
- Enter > Username
- Select > SQL Server Authentication
- Select > Database: select the database name used to read data from and optionally to write data to.
- Select > User Mappings tab
- Select > DM-ETL-plus_Data
- Select > Role public
- Select > Role db_datareader and db_writer (only when write actions to this database are required).
- Select > OK
- select > Server Objects > Linked Servers
- select > New Linked Server
- Linked server > Give an appropriate name to the linked server.
- Select > Other data source (do not select SQL Server and when Oracle is used. Select Microsoft OLE DB Provider for Oracle)
- Provide Name > Microsoft OLE DB Provider for SQL Server (In this case, the connection will be made to an MS SQL database)
- Data Source > The name and optionally the instance (ServerNameInstanceName) of the SQL Server containing the database for this connection.
- Catalog > The database name.
- Select > Security Tab
- Select > Be made using this security context:
- Enter > Username as created earlier for this datasource.
- Enter > The password corresponding with the username provided.
- Select > OK. The Linked Server is created.
Creating the Connector
GeneralThe preferred connection method for all database sources is based on Linked server connections. The linked server connections offer a much better security and better performance. DM-ETL+ will use OPENQUERY to collect data from a DM-ETL+ linked server data source In terms of security there are two main advantages;
- Neither the database nor the user or password are visible in the query when OPENQUERY is used.
- No ad hoc execution, that requires system admin rights for the user that performs the query or special registry key settings, are required.
- When disallowance of ad hoc access is enabled on the SQL server, the server is less vulnerable to security attacks.
- Before you can start with the configuration of Linked server data sources within DM-ETL+, the linked servers need to be created on the SQL server where the DM-ETL+ and DM-ETL-plus_Data database are installed on.
- Details how to create MS Linked servers on the DM-ETL+ SQL database server can be found in the previous chapter.
DetailsTo create a MS Linked Server datasource in DM-ETL+, the following steps need to be taken: See Figure 7.
- Datasource name >Use a logical name for this data source.
- Manual entry > When access to the master database is not allowed and / or when the list linked server stored procedure in the master database cannot be executed due to security policies, the linked server dropdown list will not show any linked servers. When this is the case select the Manual entry checkbox and fill in the linked server name.
- Provider > When the Manual entry option is selected you need to select the appropriate provider from the dropdown list for the database the linked server is created for.
- Linked server > A dropdown list of the MS linked server created for this particular data source on the DM-ETL+ database server.
- Non ansi* > This option will only be selectable when the linked server has Microsoft OLEDB Provider for Oracle as the data provider.
- Data source is used for TableConsolidation* > This option needs to be checked when DM-ETL+is allowed to write / consolidate data to this database.
- Direct datasource > This a DM-ETL+ datasource that needs be created via the Settings > Add > MS SQL Server menu.